1 / 37

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

seanr
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. Acronyms • EPM • Enterprise Project Management – Centralized control, Shared Resources, Portfolio / Project Reporting and tracking at the enterprise level. • OLAP • Online Analytical Processing – Used to describe the multi-dimensional databases also know as OLAP Cubes • SOAP • Simple Object Access Protocol – Communication Protocol used to transfer data in a Microsoft EPM Solution. • CBS • Cube Building Service. • RDB • Report Database used in EPM 2007

  4. 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

  5. Microsoft EPM Solution 2003

  6. Microsoft EPM Solution 2007 OLAP Data

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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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)

  17. 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

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

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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. Setup FACT and Dimension Tables FACT Table Dimension Table

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

  27. The Excel Pivot Table Pivot Table Toolbar

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

  29. The Excel Pivot Table Created from Published Projects

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

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

  32. 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

  33. OLAP in EPM 2007 (1) • OLAP Services Improved in EPM 2007 • Project Web Access itself now supports customization of the OLAP Cubes. • PWA supports: • Adding Enterprise Custom fields as dimensions or measures • Adding Calculated measures to the cube • Included is a central location for reports that use OLAP Cubes.

  34. OLAP in EPM 2007 (2) • Project Server Interface (PSI) CubeAdmin Web Service has programmatic managing and scheduling of cube building. • There are 11 default cubes created for: • Timephased data • Project • Task • Resource • Assignment • Non-timephased • Project • Task • Resource • Assignment • Timesheet • EPM Timesheet • Windows Sharepoint Services: Risks, Issues, Deliverables

  35. 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

  36. 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

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

More Related