OLAP Cubes and Pivot Tables - PowerPoint PPT Presentation

Loading...

PPT – OLAP Cubes and Pivot Tables PowerPoint presentation | free to download - id: 7a3440-OTFjZ



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

OLAP Cubes and Pivot Tables

Description:

Title: Slide 1 Author: Brendan Giles Last modified by: Cosklo, Ron Created Date: 1/23/2007 2:35:36 AM Document presentation format: On-screen Show (4:3) – PowerPoint PPT presentation

Number of Views:37
Avg rating:3.0/5.0
Slides: 35
Provided by: Brenda184
Learn more at: http://www.mpug.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: OLAP Cubes and Pivot Tables


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 gt 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 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

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

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 Resource List Dimension Time
Dimension Project List Measure Work Measure 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 its 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 gt 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 ---- well try to have smart answers

?
About PowerShow.com