Transcript Slide 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
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 2
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
Slide 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
Slide 4
Microsoft EPM Solution 2003
Slide 5
Microsoft EPM Solution 2007
OLAP Data
Slide 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
Slide 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
Slide 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
Slide 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
Slide 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
Slide 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
Slide 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
Slide 13
Introduction to OLAP
FACT Table
OLAP Cubes allow a lot of
flexibility
Work
Measure
Dimensions can be regrouped to provide a
different view of the resulting
measure
Project
Dimension
Resource
Dimension
Period
Date
Dimension
Slide 14
Introduction to OLAP
FACT Table
An OLAP Cube needs at least
one FACT table
Work
Measure
Manipulation of the
Dimensions produces the
result from the FACT Table
Measures
Period
Date
Dimension
Project
Dimension
Department
Dimension
Slide 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)
Slide 16
Project Server Database
FACT Tables
MSP_ASSN_FACT
Project Measures such as
Project Work and Cost
Analysis Manager
MSP_RES_AVAIL_FACT
Resource Availability
Measures
Slide 17
First OLAP Demo
Collaboration of EPM Tools
Microsoft SQL Analysis Services
Microsoft Excel Pivot Tables
Using Microsoft Project Database
Add Tables
Slide 18
Updating OLAP Cubes
Project Server 2003 refreshes the OLAP Cube
Tables using a scheduled task from Admin >
Manage Enterprise Features
Slide 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
Slide 20
Project Server Database
OLAP 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 21
Updating OLAP Cubes
OLAP Processing is scheduled via Project Web Access 2003.
Refresh MSP_CUBE tables
Build internal 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
Slide 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
Dimension
Project
List
Dimension
Resource
List
Dimension
Time
Measure
Measure
Work
Cost
Slide 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
Slide 24
Setup FACT and Dimension Tables
FACT
Table
Dimension
Table
Slide 25
Connect to Cube via Excel
The Data > Import
feature of excel
allows a connection to
Microsoft OLAP
Services
Slide 26
The Excel Pivot Table
Pivot Table Toolbar
Slide 27
Project Center
In this Demo Environment Projects have been published
to Project Server and assigned to the PMI or MPA group
Slide 28
The Excel Pivot Table
Created from Published Projects
Slide 29
Overview of Cube Structure
Main Project Server Cube Tables
MSP_CUBE_PROJECTS
MSP_CUBE_RESOURCES
MSP_CUBE_TIME_BY_DAY
Main OLAP Tables
Slide 30
Connecting an Enterprise Outline Code to
the Cube
Custom Outline Codes
MPA PMI Group Code
Add Project Outline Code
Slide 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
Slide 32
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.
Slide 33
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
Non-timephased
Project
Task
Resource
Assignment
Project
Task
Resource
Assignment
Timesheet
EPM Timesheet
Windows Sharepoint Services: Risks, Issues, Deliverables
Slide 34
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 35
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 36
Questions and Answers
?? No such thing as a stupid question ??
Answers ---- we’ll try to have smart answers
Slide 37