For our first feature discussion, I will introduce you to Project “12” Cube Building Services that represent a part of our offerings for Enterprise Project Management Reporting. It also happens to be the feature I own, so I am very excited to share with you the work we have done.
Project Server 2003 ships a foundation to enterprise reporting and data analysis with the Portfolio Analyzer cube. In Project Server “12” we have expanded the idea to offer a rich set of data ready to consume, out of the box. The Cube Building Services offers an environment to truly provide business intelligence and insight to businesses using Project.
We have increased the number of cubes offered to 11 cubes and 3 virtual cubes. The cubes are built using the Project Server “12” Reporting infrastructure. This infrastructure includes a dedicated Reporting SQL database that contains all Project Server “12” data that has been published. This data is incrementally update at real time as data gets published, tremendously improving the performance for the cubes as well.
The list of cubes offered are the following (this may be subject to change):
1. Project Non Timephased
2. Task Non Timephased
3. Assignment Non Timephased
4. Assignment Timephased
5. Resource Non Timephased
6. Resource Timephased
7. EPM Timesheet
The virtual cubes offered are the following (also subject to change):
1. Portfolio Analyzer – backwards compatible with Project Server 2003
2. Project Timesheet
3. Project WSS
During the Project Conference, one of the top questions I was asked regarding the cube schema was whether the Time dimension has a Week level and the answer is YES! In addition to the Time dimension, we’ll also offer a Fiscal Time dimension that will map to fiscal periods defined in the Project Server though it will not follow the traditional Time dimension hierarchy (Year > Quarter > Month…).
The Cube Building Services in the Project Web Access interface provides a very centralized set of cube administrator pages.
Cube Building Settings
Similarly to Project Server 2003, this page helps administrator setup the cube builds and define the cube settings:
· Analysis Services server name
· Data range to be included on the cubes
· Frequency to which the cubes should be updated
The Cube Building Services in Project Server “12” has an easy to use interface to allow customization of the cubes by using Enterprise Custom Fields. For example, you can add Location custom field to your Assignment Timephased cube as a dimension. That would allow you to analyze Actual Cost sliced on Location over a period of time. This is very simple example, but you can get the idea of the powerful analysis you will be able to do with this.
We also offer an interface to add calculated members without having to write any custom code just the appropriate MDX formula. For example, for Profit, the formula would be something like [Revenue] – [Cost]
Once the customizations are save, they will be added to the cubes the next time the cube is built.
Cube Building Status
This page has been added to help administrators verify the status of the building process and troubleshoot if there are any errors, such as the Analysis Services server name is invalid.
Note: Project Server “12” also has an amazing Queue feature that allows great job management on the server side but I’ll save the details for a future post.
The Cube Building Services has been built primarily in Analysis Services 2000 though it successfully builds in Analysis Services 2005 as well. For those of you who did have a chance to use these cubes in the Beta 1 release, this was not working at that point but it has been fixed. We will also support mixed configurations, the final support configuration document has not yet been finalized but our test team has been making good progress validating many of the scenarios we want to support.
I would really like to hear how in depth you would like the topics to go into. I can really drill down on a few of them if there is interest. I will start new feature discussions with an overview post like this one to give you a background on the work done for the feature in P12.
In the new cube, you offered the formula [Revenue]-[Cost]. Does this mean that the cube can include costs calculated using all non-zero rates? So if there is a Standard rate (rate customer charged) and a Rate B (internal rate) there will be two sets of costs calculated, one for each rate? The other question that appears on the newsgroups from time to time is having a rate for a resource specific to one project to represent an agreed rate negotiated with the client. can this be handled for Enterprise resources in Proejct Server 12?
The [Revenue]-[Cost] was just an example of how a simple MDX calculated measure would look like. We won't have a [Revenue] measure. For the first part of your comment, you would be able to have a separate rate using an Enterprise Custom Field for "Rate B" and you could add that as a measure on the cube (directly from the server pages).
For the second part, regarding Enterprise Resources, you will be able to set up on the server, the cost center and cost type but the rate will have to be entered on the desktop (you are able to set a custom rate for the resource even though it's an enterprise resource). Is this what you mean? I may not have understood your question.
Glad to hear about the 'weeks' dimension, it's a much requested feature here that led to quite a bit of custom development and considering other tools such as the Portfolio Analyzer Extender. Does this feature allow us to chose the date format for the header (i.e., the first and last date of the week displayed or just the first date of the week, etc.)?
William - I am glad the Week's level will help you guys :) We are currently just showing the week of the year which will be something like "Week 22" but this is a good point, "Week 22" may not be that interesting or meaningful to a lot of customers. What is the format that you guys are using? I'd be interested to hear about your solution. Thanks for the post! -Lidiane
Hey Lidiane, hope all is well. Quick question (I am sure you covered it somewhere during the conference but my mind was wandering (again!)).... what happens to the OLAP extensions we had to build in P2003 when P2007 comes around? For that matter the question applies I guess to any other custom OLAP extensions not built by MS..Are the staging tables, cubes and data preserved? Gracias!
Hi - I am doing pretty well :) We won't ship any staging tables out of the box but since we have a dedicated Reporting SQL database, you will be able to create views that match what your solution needs as a data source. We've made a huge effort to keep the same naming convention in the cubes in P12 compared to the one in P11. We will also releasing documentation with some recommendations. -Lidiane
Excellent news!! I wish I could have got to the briefing but couldn't make it. I've just gone through a lot of pain to give a customer a facility to define task location within each project and then interrogate the database to get total expenditure per location across all projects. The other job is to coordinate visits to the location by staff from different projects in order to minimise travel costs and optimise use of technical resources. Also modified cube to do fiscal year reports. This new one sounds a lot easier. Denis
Hi Denis - Yes, you will be able to do a lot of what you need with using Custom fields to customize the cubes and that will be very easy do to with P12. We will release a DVD set from the Project Conference soon, so you will be able to see the session that talked about how to do this in P12. -Lidiane
This sound very interesting and a major face lift to the current model. However, can you please give more details on the security aspect of the service? What I need to know if the security model that governs the visibility on projects and resource information in EPM is also carried forward to the cube and the portfolio analyzer views. In EPM 2003, anyone who has access to the portfolio analyzer views will have access to all projects and resources information in the organization similar to being member of the My Organization category. The only way to enforce security on the cube is to set access roles programmatically.
[Lidiane] The security model with respect to accessing the data is still the same in EPM 2007 as it was in EPM 2003. If the user is granted the permission to view OLAP data they will be able to see all of it. We do not use OLAP roles in this release. If your users will only use the Portfolio Analyzer (now called Data Analysis) to access the data, you can restrain access to the views through category permissions though this won't prevent the users from accessing the data through another application, such as Excel, to read other projects' data.
Now that Project 2007 has gone RTM, when will there be a good configuration guide available for interaction/with SQL 2005/SQL 2005AS?
So far, my best source of information on how to get this to work has been through a PS2K3-->SQL2K5 workaround located here http://support.microsoft.com/kb/921116
PLEASE tell me I'm just looking in the wrong spot for this information.
I'm also trying to get started using project server 2007 analysis services. I've been thru the sdk docs and done the usual googling, msdn searchs etc. What should i read FIRST? Where is the documentation that explains what is going on with the cube build settings page (CubeGenAdmin)?
We are trying to customize the Cube: "MSP_Portfolio_Analyzer" and add couple more measures to it viz., FTE_HOURS_MONTH, FTE_HOURS_QTR etc. These are calculated values based on MSP ResourceByDay values. I am encountering some issue when I am trying to add it using AMO programming. Could you please direct me to any documentation on how to customize cube by adding custom Measures...
Thank you for your help in advance.
I want to add a new field MDX in the Timesheet Cube (2007)
The formula will add
Actual Work Billable
Actual Work Non Billable
Actual Overtime Billable
With this total I will see if my time sheet are completed for any given Timesheet period.
Is there a way to do this?
Do you know about some tutorial about cube-building configuration? I installed MOPS 2007, single server, but there's no way to configure cube generation.
I'm having trouble installing Analysis Services with the SQL Express edition that came with MOPS.
Thanks in advantage, and sorry for my English!
I meant "in advance", not in advantage! :S