olap cubes and pivot tables leveraging the power of a microsoft epm solution epm customization...
TRANSCRIPT
OLAP Cubes and Pivot Tables
Leveraging the Power of a Microsoft EPM Solution
EPM Customization Series Part 1
February 21st, 2007
Brendan Giles , PMP, MCP
Slide Slide 22
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
Slide Slide 33
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
Slide Slide 44
Microsoft EPM Solution 2003
Slide Slide 55
Microsoft EPM Solution 2007
OLAP Data
Slide Slide 66
Components of an EPM Solution
Microsoft Office Project Professional EPM Client Tier Creation of Project Plans Scheduling Reporting Connection to Project Server Office Integration
Slide Slide 77
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
Slide Slide 88
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
Slide Slide 99
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
Slide Slide 1010
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
Slide Slide 1111
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
Slide Slide 1212
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
Slide Slide 1313
Introduction to OLAP
OLAP Cubes allow a lot of flexibility
Dimensions can be re-grouped to provide a different view of the resulting measure
WorkMeasure
ProjectDimension
ResourceDimension
PeriodDate
Dimension
FACT Table
Slide Slide 1414
Introduction to OLAP
An OLAP Cube needs at least one FACT table
Manipulation of the Dimensions produces the result from the FACT Table Measures
WorkMeasure
PeriodDate
Dimension
ProjectDimension
DepartmentDimension
FACT Table
Slide Slide 1515
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)
Slide Slide 1616
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
Slide Slide 1717
First OLAP Demo
Collaboration of EPM Tools Microsoft SQL Analysis Services Microsoft Excel Pivot Tables Using Microsoft Project Database
Add Tables
Slide Slide 1818
Updating OLAP Cubes
Project Server 2003 refreshes the OLAP Cube Tables using a scheduled task from Admin > Manage Enterprise Features
Slide Slide 1919
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
Slide Slide 2020
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
Slide Slide 2121
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
Slide Slide 2222
Project Server Database Measures and Dimensions
Dimension Time Period Project List Project Versions Resource List
Measures Work Actual Work Cost Actual Cost
SQL AnalysisManager
DimensionResourceList
DimensionTime
DimensionProject List
Measure
WorkMeasure
Cost
Slide Slide 2323
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
Slide Slide 2424
Setup FACT and Dimension Tables
FACT Table
Dimension Table
Slide Slide 2525
Connect to Cube via Excel
The Data > Import feature of excel allows a connection to Microsoft OLAP Services
Slide Slide 2626
The Excel Pivot Table
Pivot Table Toolbar
Slide Slide 2727
Project Center
In this Demo Environment Projects have been published to Project Server and assigned to the PMI or MPA group
Slide Slide 2828
The Excel Pivot Table
Created from Published Projects
Slide Slide 2929
Overview of Cube Structure
Main Project Server Cube Tables MSP_CUBE_PROJECTS MSP_CUBE_RESOURCES MSP_CUBE_TIME_BY_DAY
Main OLAP Tables
Slide Slide 3030
Connecting an Enterprise Outline Code to the Cube
Custom Outline Codes MPA PMI Group Code
Add Project Outline Code
Slide Slide 3131
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
Slide Slide 3232
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
Slide Slide 3333
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
Slide Slide 3434
Questions and Answers
?? No such thing as a stupid question ?? Answers ---- we’ll try to have smart answers