Finally the time came where I have to extend the portfolio analyzer cube to add additional fields. So I started my RND and felt that I must share the information that I am getting out of resource kit and other SDKs. I do not claim any responsibility for my articles and this information is sent to you as is written by jerry. Any comments and suggestion are welcome.
Portfolio Analyzer – Usage and Extensibility
In Most organization analysis of project data become very important to predict the future. Although project server 2003 provides a great engine fro the data analysis but some time it shows limitation for custom scenarios where it is required to add new custom codes to extend its functionality. So whenever you decided to use this functionality there is only one point to go and that point is Portfolio Analyzer OLAP Extensions available for download at microsoft.com. Download and install it in your system. It contains the Documentation and samples to use for the extensibility.
Before moving it let set some code for reuse words
PA = Portfolio Analyzer, OLAP = Online Analytical Process, PWA = Project Web Access, PS = Project Server, SQL = SQL Server, SQL AS = SQL Server Analysis Services, DSO, Decision Support Objects
Portfolio analyzer helps users of Project server in performing complex data analysis of project data by generating OLAP cubes of SQL Server 2000 Analysis Services. Project Server uses this cube to show data inside Project server views. Microsoft Office Web Components i.e. Pivot tables and Pivot Charts are used to give access to users to work on the data for grouping and filtering. All this functionality helps PWA users to gain visibility and insight of project portfolio better than ever.
Architecture of Portfolio Analyzer
Project server uses OLAP (Online Analytical Process) for portfolio analyzer. PA uses components in all three tiers of Project server 2003
1. Database Tier
SQL AS is used to create the OLAP Cube and AS use tables that are generated by the middle tier of project server.
2. Business objects
This tier is responsible of generating FACT and dimension tables based on PWA Rules and data inside table of project server db.
3. Client Tiers
IE is the client tier that uses Pivot table and Pivot chat of Microsoft Office Web Components.
When PWA admin provides the database for portfolio analyzer, a virtual cube is created that hold two cubes a. MSP_ASS_FACT and MSP_AVAIL_FACT. MSP_ASS_FACT contains all the assignment data of the projects and with start time dimensions, project type, booking type etc.. as well as some resource data with dimension of Custom outline codes created by project or resources. MSP_AVAIL_FACT cube contains resource calendar and availability information with time phased data. This cube also holds time and resource dimensions and any defined resource outline codes.
Cube Building Base
PS build OLAP on SQL AS OWC bind with OLAP CUBE directly
Note: OWC binds with OLAP cube directly without having any security implementation. So make sure to create ROLES that has access to OLAP Cube using AS manager.
Cube Building Process
When cube building process started a DLL ProjOLAP.dll is loaded into the memory of the server. There are two stages inside CUBE Development
1. Generating Staging Tables
Normally is the most time taking process and requires lot of processing.
a. PS Business objects checks permission, and calls PDS (Project Data Services) to gather the required data filtered by the parameters of PWA (dates and names). b. PDS calls the methods and create Staging tables referred to as Data Warehousing
c. All the staging table are renamed as MSP_CUBE_TABLE NAME
2. Generating a Virtual Cube
The process starts when staging tables are building and ready for cube generation.
a. Cube building service ProjOLAP.dll calls the generation process to use DSO (Decision Support Objects to interact with AS.
b. AD Generates the Cube based on staging tables
c. All Three cubes MSP_ASS_FACT, MSP_AVAIL_FACT and the cube specified by the user in PWA is created in Analysis Server.
This came to the end of the first part of my articles on extending Portfolio analyzer and Analysis Services Cube. I will be posting the next articles very soon.
Microsoft Project server 2003 uses SQL Server Analysis services Cube generation process to allow users of PWA to perform complex calculations and analysis on the data saved inside project server database. It is called Portfololio Analyzer in Project server 2003. Comments and Suggestions are welcome.
End of Part I