OLAP Extensibility Model
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
- Extend the query to fill MSP_View tables from MSP_Web Tables
- Modifying the Breakout functions that copy data from view tables to staging tables
- 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.
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.
EPM/SharePoint Consultant & Project Server Trainer
Mob: +92 345 4257573,