Amherst H. Wilder Foundation: Leveraging SharePoint as a Strategic Platform for Planning and Budgeting

2. SESSION CODE: BIC02 Amherst H. Wilder Foundation: Leveraging SharePoint as a Strategic Platform for Planning and Budgeting Nick Giuliano Business Applications Manager Amherst H. Wilder Foundation

3. GNet Group Greg Fair Business Development Manager Chaitanya Khaladkar Miracle Worker Karl Lacher BI Program Director www.gnetgroup.com PARTNER

4. Interactive Session Overview • Overview: Amherst H. Wilder Foundation • Business Conditions: The need for something better – NOW • Solution Architecture – Microsoft BI-Stack / SharePoint / InfoPath • Technical Drill-down • Solution Demonstration • Lessons Learned • Open Discussion

5. The Amherst H. Wilder FoundationA Brief History • The Wilder Foundation has served at-risk children and families, the frail elderly, and the greater St. Paul, MN community for over 100 years • Founded in 1906 by entrepreneur Amherst H. Wilder • $2.6M Endowment created by the Wilder family wills • Operating foundation with an annual budget of $40M • Thousands of individuals and families served each year • Wilder family’s vision: “…to aid and assist poor, sick, aged, and otherwise needy people of St. Paul, without regard to, or discrimination on account of, any such persons by reason of their nationality, sex, color, religious scruples or prejudices.” • Website: www.wilder.org

6. Business ConditionsThe Need for Something Better - NOW • “Here for Good” • Economic reality and sustainability • “The trustees of an endowed institution are the guardians of the future against the claims of the present. Their task is to preserve equity among generations.” - Nobel Laureate James Tobin • Tactical Challenges: Multiple “sources of the truth”, inadequate analysis tools, and the legacy budgeting system ceased functioning • Needed: Accurate, true single “source of the truth” and a streamlined, efficient budget planning solution that would integrate with our intended BI strategy • Must be easy to use, intuitive and require little training

7. Business ConditionsThe Value of A Good Partner (From Challenge Comes Opportunity) • RFP Process for Planning and Budgeting Solution • Selected GNet with the intention of implementing PerformancePoint Planning • January 22, 2009 – PerformancePoint Planning no longer available • January 28, 2009– New, innovative approach was agreed upon • Collaborative effort to create and deliver a custom application hosted on SharePoint and leveraging the Microsoft BI Stack • Innovative approach using InfoPath as a Rapid Application Development tool • The budget completed in time for Board review and approval • The planning effort was cut in half with only 15 users (previously 50)

8. Business ConditionsBenefits of the Solution (From the Business Perspective) • Became the true “single source of the truth” for planning and budgeting • Enabled users to see the impact of their decisions in real-time • Delivered fast, accurate reporting with Excel integration • Supported critical business rules and maintained process integrity • Provided users a familiar browser-based experience • Assured quick user acceptance and use with minimal training • Reduced the effort and staffing required to produce the annual budget • Set the foundation for an integrated data management system that combines strategic planning, forecasting, business modeling, budgeting, and reporting • Resulted in a Microsoft Case Study #4000006626

9. Business ConditionsBenefits of the Solution (From the IT Perspective) • Developed using Rapid Application Development methodology • Benefitted from out-of-the box integration with Microsoft BI Stack • Delivered reporting via SSRS and SSAS through SharePoint • Leveraged Active Directory groups for integrated security • Furthered migration from legacy systems and technology • Empowered technical team to push for innovative solutions • Solidified the progression towards SharePoint as our primary business process platform

10. Solution ArchitectureA Unique Name for A Very Useful System • Wilder • Online • Management • Budgeting • Analytic • Tool W O M B A T

11. Solution ArchitectureThe Budget Planning Model

12. Server 2008 SharePoint 2007 lnfoPath 2007 Excel 2007 • SQL Server 2008 • SSRS • SSAS • SSIS PerformancePoint Server 2007 ProClarity 6

13. Solution ArchitectureData Elements Business Rule sets Security Configuration Budget Data Entry Depreciation (ETL) General Ledger (ETL) Derived Data

14. Solution ArchitectureETL Sources – Methods – Timing

15. Solution ArchitectureETL Sources – Methods – Timing

16. Solution ArchitectureDatabase Model Business Rule Sets Security Configuration Budget Data Entry Depreciation (ETL) Derived Data

17. Technical Drill-downThe WOMBAT Database • Business Rule Tables • Security and User Configuration Tables • Budget Data Entry Tables • Financial Data (GL Extracts) Tables • Employee Data (HR Extract) Table • Derived Data Tables DEMO

18. Technical Drill-downStored Procedures • Modular Design • Parameter-Driven • InfoPath Forms Invoked (CRUD) • SSRS Invoked DEMO

19. Technical Drill-downReporting • SQL Server Reporting Services (SSRS) • SQL Server Analytical Services (SSAS) DEMO

20. Technical Drill-downInfoPath Forms • Budget Form (End-User Application) • Administrative Forms DEMO

21. Wilder Online Management Budgeting Analytic Tool DEMO

22. Future DirectionsWe Are Just Getting Started • SQL Server 2008 R2 • SharePoint 2010 • Comprehensive Business Intelligence Solution with roll-out at year end (BISON) • Excel 2010 & Power-Pivot • WOMBAT v3 (InfoPath application optimizations and enhancements) • Position SharePoint 2010 as the key Business Process Platform • Redesign and hosting of Intranet on SharePoint 2010 • Redesign and hosting of public website and resources on SharePoint 2010 • Document management pilot program

23. Lessons LearnedFrom Our Experiences • InfoPath and the BI stack powered by SharePoint provides an exceptional development platform for key business resources • InfoPath can be used successfully as a Rapid Application Development tool to develop business applications • Application and database architecture need to assure that best practices still apply • Application-specific databases need to be created outside of the SharePoint DB environment when necessary • Limiting the complexity and number of views in your InfoPath forms helps to manage performance and maintainability • Define your data connections and stored procedure calls carefully • Selection of a Certified partner whose skills and vision complement your team is key to effective collaboration

24. Open Discussion

25. Track Resources • Resource 1 • Resource 2 • Resource 3 • Resource 4

26. Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

27. Related Content • BIU08-INT | Using Microsoft SQL Server 2008 R2 Report Builder 3.0 • BIU204 | Top 10 Reasons for Using SQL Server Report Builder and Reporting Services • BIO201 | BI Power Hour! • BIO01-HOL | Developing KPIs and Scorecards with PerformancePoint Services in Microsoft SharePoint Server 2010

28. Complete an evaluation on CommNet and enter to win!

29. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.