1 / 34

OLAP Cubes and Pivot Tables

OLAP Cubes and Pivot Tables. Leveraging the Power of a Microsoft EPM Solution. Brendan Giles , PMP, MCP. February 21 st , 2007. EPM Customization Series Part 1. Overview. Components of a Microsoft EPM Solution Review the purpose of each component in EPM

thaler
Download Presentation

OLAP Cubes and Pivot Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OLAP Cubes and Pivot Tables Leveraging the Power of a Microsoft EPM Solution Brendan Giles , PMP, MCP February 21st, 2007 EPM Customization Series Part 1

  2. Overview • Components of a Microsoft EPM Solution • Review the purpose of each component in EPM • How the components are integrated to form an EPM Solution • How to extend a Microsoft EPM using Online Analytical Processing (OLAP) • Summary • Next Steps using Visual Studio (2nd Presentation) • Questions and Wrap-up

  3. Components of Microsoft EPM? • What software products make up a Microsoft EPM solution? • Microsoft Office Project Professional • Microsoft Office Project Server • Windows Sharepoint Services • SQL Server • SQL Analysis Services • Microsoft Office Excel • Others? • Do they work together? • Yes • Can they work alone? • Yes

  4. Microsoft EPM Solution 2003

  5. Microsoft EPM Solution 2007 OLAP Data

  6. Components of an EPM Solution • Microsoft Office Project Professional • EPM Client Tier • Creation of Project Plans • Scheduling • Reporting • Connection to Project Server • Office Integration

  7. Components of an EPM Solution • Microsoft Office Project Server • EPM Middle Tier • Web Application access via Project Web Access • EPM Administration • Connection to Client and Database Tiers • Project, Task, Resource Web UI • Portfolio Analysis Web UI • Time Tracking Web UI • Office Integration

  8. Components of an EPM Solution • Windows Sharepoint Services • Project Web Site Support • Document Management • Risk and Issue Tracking • Project Announcements • Online Team Interaction • Connection to Database Tier • Office Integration

  9. Components of an EPM Solution • SQL Server • Hosts Project Server Databases • Host Sharepoint Databases • Database Maintenance • Data Transformation Services • Online Team Interaction • Connection to SQL Analysis Services • Includes a new Reporting Database in EPM 2007 • Office Integration

  10. Components of an EPM Solution • SQL Analysis Services • Online Analytical Processing (OLAP) • Supports Portfolio Analyzer • Manages OLAP Cubes • OLAP Cubes accessible through Project Web Access and Excel • Supports OLAP Database Maintenance

  11. Components of an EPM Solution • Microsoft Office Excel • OLAP Pivot Tables • Import / Export with PWA and Microsoft Office Project • Analyze Time Phased Data in Excel • Microsoft Office • Copy Picture to Office Wizard • OLE DB Integration

  12. Introduction to OLAP • OLAP Databases are multi-dimensional databases • Consist of Measures and Dimensions • Measures come from FACT tables • Types of Measures • FACT Table Fields • Calculated Measures using FACT Table Fields • Types of Dimensions • Date • Standard

  13. Introduction to OLAP • OLAP Cubes allow a lot of flexibility • Dimensions can be re-grouped to provide a different view of the resulting measure FACT Table

  14. Introduction to OLAP • An OLAP Cube needs at least one FACT table • Manipulation of the Dimensions produces the result from the FACT Table Measures FACT Table

  15. Introduction to OLAP • OLAP Database Queries (MDX) • Show me work (Measure) • by Project (Standard Dimension) • by Quarter (Date Dimension) • Show me Cost (Measure) • For MPA Projects (Standard Dimension) • For PMI Projects (Standard Dimension) • By Quarter (Date Dimension)

  16. Project Server Database FACT Tables • MSP_ASSN_FACT • Project Measures such as Project Work and Cost • MSP_RES_AVAIL_FACT • Resource Availability Measures Analysis Manager

  17. First OLAP Demo • Collaboration of EPM Tools • Microsoft SQL Analysis Services • Microsoft Excel Pivot Tables • Using Microsoft Project Database • Add Tables

  18. Updating OLAP Cubes • Project Server 2003 refreshes the OLAP Cube Tables using a scheduled task from Admin > Manage Enterprise Features

  19. Updating OLAP Cubes • Project Server 2007 keeps it Reporting Database up-to-date with the main Project Server database • The Project Server 2007 Reporting Database is the source of OLAP Data • The are a number of pre-designed or internal OLAP Cubes available in Project Server 2007

  20. Project Server DatabaseOLAP Related Tables • 2003 MSP Cube Tables • MSP_CUBE_PROJECTS • MSP_CUBE_RESOURCES • MSP_CUBE_TIME_BY_DAY • MSP_CUBE_DATE_FIELDS • 2007 Reporting Database • MSP_EpmResource • MSP_EpmProject • MSP_TimeByDay • ……and much more

  21. Updating OLAP Cubes • User defined custom OLAP Cubes can be refreshed on a pre-defined schedule via a Data Transformation Services (DTS) package created in SQL Server • OLAP Processing is scheduled via Project Web Access 2003. • Refresh MSP_CUBE tables • Build internal OLAP Cubes

  22. Project Server Database Measures and Dimensions • Dimension • Time Period • Project List • Project Versions • Resource List SQL Analysis Manager • Measures • Work • Actual Work • Cost • Actual Cost

  23. Ready Made OLAP User Interface • Microsoft Excel contains OLAP Services functionality • Connects to OLAP Datasource • Creates Pivot Tables to consume OLAP data • Contains a wealth of report formatting templates • Can be setup by EPM Administrator or user • Source of data and it’s maintenance do not concern the user • Allows users to leverage their knowledge of excel to view and manipulate OLAP dimensions and measures without the need for a custom UI

  24. Setup FACT and Dimension Tables FACT Table Dimension Table

  25. Connect to Cube via Excel The Data > Import feature of excel allows a connection to Microsoft OLAP Services

  26. The Excel Pivot Table Pivot Table Toolbar

  27. Project Center In this Demo Environment Projects have been published to Project Server and assigned to the PMI or MPA group

  28. The Excel Pivot Table Created from Published Projects

  29. Overview of Cube Structure • Main Project Server Cube Tables • MSP_CUBE_PROJECTS • MSP_CUBE_RESOURCES • MSP_CUBE_TIME_BY_DAY • Main OLAP Tables

  30. Connecting an Enterprise Outline Code to the Cube • Custom Outline Codes • MPA PMI Group Code • Add Project Outline Code

  31. Using the Pivot Tables • Organizing the Fields • Pivot Table Templates • New assignments in EPM automatically refelected in the Pivot Table • Pivot Table Usage • New Assignments • Project Plan Updated • Cube Refreshed • Pivot Table shows updates

  32. Summary • Microsoft EPM Solution components work together or alone • SQL Analysis Services supplies OLAP Cubes • Microsoft Excel supplies a User Interface to the OLAP data via Pivot Tables • These EPM components can be used to develop custom reporting solutions • Microsoft Excel users have a ready made “window” into Project Server Database data

  33. My Next Session • Custom EPM Solutions “read-to-go” • Microsoft Project Association Presentation planned for November of 2007 • Microsoft Project VBA for EPM • Managed Code Built in Visual Studio .NET • Windows based (C# and VB.NET) • Web Based (ASP.NET) • Designed to extend EPM 2003 and EPM 2007 Brendan Giles , PMP, MCP

  34. Questions and Answers • ?? No such thing as a stupid question ?? • Answers ---- we’ll try to have smart answers ?

More Related