OLAP Extensibility Model

OLAP Extensibility Model

Introduction

 

This is the 2nd part of my articles on Extending the OLAP Cube and Portfolio analyzer for Project server 2003.  My Last article was a general overview of the Portfolio analyzer and OLAP Server on how OLAP and SQL Server Analysis Services work.  This article is about the Extensibility Model of OLAP and various methods to extend the OLAP cube. 

 

As we know that Microsoft project server store all the project data in MSP_Web tables in Project server database.  When a project is published this data is send to MSP_View tables for view processing.  OLAP Cube collects data from these MSP_VIEW tables to generate staging tables to place data for analysis.  These tables are named as MSP_Cube tables.  OLAP cube generation process collects the data from the staging tables and stores in inside MSP_Portfolio Analyzer cubes table called MSP_CUBE.  These table are stored all the dimensions and measurements of analysis of data.  If want to extend OLAP there are following ways

  1. Extend the query to fill MSP_View tables from MSP_Web Tables
  2. Modifying the Breakout functions that copy data from view tables to staging tables
  3. Modifying the OLAP breakout function that calls PA to generate MSP_Cube tables that behaves like staging tables for Cube Generation.

 

It seems like a three step process in which

Step1 is the time when you ad more data to publishing process to save in MSP_Web tables.

Step2 is the time when you use custom code to extend and add data to staging tables using UserStatingTablesUpdate function (Available in Starter Kit)

Step2 is the time when you use Custom code to reprocess extensions in staging tables and regenerate the cube using UserOLAPupdate function (Available in Starter Kit)

 

If we need to Extend the view tables we have to add more data to MSP_View tables by changing the schema of the tables that have the information needed to extend.  We also have to modifying the publishing query by updating QYLIBSQL.SQL file add more data to MSP_View Tables, this file is available in Project server installation (Bin) Directory.  You will note that the file contains the quires that are used to add data to view tables.  Many of these queries has extended version but they are commented out in the file. If we need to extend the cube with new fields we just need to comment the normal query and uncomment the extended query which will select the extended fields from the MSP_View tables.

Summary

In this article I talked about the extensibility model of OLAP.  How OLAP cube can be extended what are the ways to do and where updates are required.  But there are other ways to extend the cube by using various tools that are build into Portfolio Analyzer user interface like Calculated fields, MDX, Variance Fields, Special chart Characters and by using Microsoft Office web components.

 

This comes to the end of this article.  I will be posting about the step/code of extending OLAP cube in coming days with a real time problem I am working in.  If you have any question, comments or feedback please doesn’t hesitate to ask or share because sharing is power.

Best Regards,

Jerry

EPM/SharePoint Consultant & Project Server Trainer

Lahore, Pakistan

Mob: +92 345 4257573,

yasir.attiq@gmail.com

www.geocities.com\jerry_yasir

http://jerryyasir.blogspot.com

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s