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

 

Windows Live Local


Microsoft Corp. introduced today a beta version of Windows Live Local, built on the Virtual Earth platform beta-released in July but improving on it by adding multiple aerial vantage points, driving directions, and customizable maps.  Want to know more

 

http://www.microsoft.com/presspass/press/2005/nov05/11-01PreviewSoftwareBasedPR.mspx

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

 

Visual Studio .Net Launch Blog 3


Visual Studio .Net Launch

My blog about the launch of Visual Studio.net 2005, SQL Server 2005 and Biztalk Server 2005 continues.  I completed the first two sessions in my last blog so I am continue with the third one here.  This session was the mixture of SQL server and Visual studio .Net 2005.  Chad explained some basics of SQL server and Visual studio .Net 2005 and demo about programmability in SQL server 2005.  During this session some interesting video where shown these videos you can find some here. Audience really liked the video about Share Pain features of Microsoft.  Another very interesting point about the sessions is the WOO-HOO and BOO-HOO chants.  Chad asked everyone to say WOO-HOO when they got excided and BOO HOO when they are not so the session was full of fun and it helped people to be worked.  After some videos Aseel demonstrated how to create a stored procedure in Visual Studio.Net 2005 using Visual Basic.Net.  He also demonstrated how to create a stored procedure acting as a web service.  Then there was break for tea.

 

Visual Studio Team System

The third session was about Visual studio .Net team services.  Chad explained the idea behind Visual studio.Net team services. According to him the visual studio .Net team services offers tightly integrated and extensible lifecycle tools.  These tools enable software development teams to reduce the complexity of development and facilitate better communication and collaboration throughout the development process.  Visual studio .Net team system covers all the basic roles in software Development life cycle.  These roles includes

 

  • Project Managers
  • Designers
  • Software Developers
  • Testers
  • Infrastructure development

 

Using Visual studio Team System these roles can

Reduce the Complexity of delivering modern service oriented solutions

Facilitate Collaboration between software teams, speeding development time, and ensure predictability and reliability to Dev. Process.

Customize and Extend the software with internal tools and process frameworks.

 

It also includes guidelines for architecture and processes and build in solution accelerators that will help project teams to deliver Mission Critical Software solutions.  At the end and during the sessions Chad shows some demos of the Visual studio .Net class designer and using the work items in the development lifecycle of projects.  This comes to the end of this session next is about Smart Client Applications.

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

Visual Studio .Net Launch


Visual Studio .Net Launch

Smart Client Application using Windows Forms 2.0

 

This session was a bit quicker because time was short so Chad goes very fast.  He started by introducing the updates in Windows forms 2.0.  He told audience about the upgrade in Data Grid control which was a point of failure in Windows forms 1.1. He talked about Data binding in Windows forms and explained how easy is to use the typed and un-typed datasets.  Visual studio .Net 2005 includes a brand new Dataset designer that will help developers to easily and efficiently create Datasets which will remove the problems XML based datasets. He told audience about the updates in Visual Studio.net designer for windows forms and about new windows controls like menus. 

The best thing of the day was the introduction and demonstration of the ClickOnce deployment.  ClickOnce deployment will help developers to easy deploy, manage and update their application using simple some design time clicks.  This will reduce the complexity of creating setups and patches.  Using click once the developer will simply points to the web server where the application will be saved for the deployment.  The user will be told to visit the site and install the application directly to their computers.  Another very important thing about click once application is the automatic update of the application, when an application is updated by the developer and published, when the application starts on the client computer it will automatically checks for any updates from the site.  If any update is available then it will ask the users to update.  This is really a cool feature. 

This comes to the end of this session.  Lunch was served and I must say it was really delicious.  After lunch we have to wait an hours before people from CA present their company to the audience.  According to Most of the guys there it was looking irrelevant because most of the guys there were developers and students but the presentation was targeted towards business man. 

At the end of CS presentation prize ceremony started and few guys got small prizes like Hallow, Mouse, Keyboards and one lucky winner got certificate to get Xbox 360.  That was the end.  Overall very good event and well done Microsoft.

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