Cube Building Services Overview
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
8. Timesheet
9. Issues
10. Risks
11. Deliverables
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
Cube Customization
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.
Comments
Anonymous
February 16, 2006
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. -LidianeAnonymous
February 17, 2006
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! -LidianeAnonymous
February 20, 2006
The comment has been removedAnonymous
February 21, 2006
The comment has been removedAnonymous
February 21, 2006
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. -LidianeAnonymous
March 07, 2006
Lidiane, you asked which header we currently use for the 'Weeks' when we present Project reports. We primarily use the short date format of '03/05/06' to show the date the week begins. Very occasionally we show the encompassing dates '03/05 - 03/11'. Would be great to see at least one option other than 'Week 22' since most of us can only guestimate when that occurs without counting weeks on a calendar.Anonymous
March 14, 2006
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.Anonymous
July 21, 2006
Hi Lidiane,
I have a few questions to clarify the new features of EPM 07. First, I understand from my readings that the standard cube functionality will allow for inclusion of all custom Project, Task and Resource fields into the cube?
Also, will custom cost fields be time phased as are default cost fields to allow for the allocation of cost at a daily level instead of simply at the end of the range?
Thank you very much! WendyAnonymous
July 21, 2006
Sorry, one more question...with respect to fields, will there be fields that support more than 250 characters and can/will all of those characters be displayed on reports? If the allowed characters is more than 250, is there a limit and what is it? (ok not one question). Thank you!!Anonymous
December 06, 2006
Hi Lidiane, 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. Best Regards, -RSAnonymous
December 11, 2006
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)? iraAnonymous
July 01, 2007
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. KishoreAnonymous
March 08, 2008
Hi, 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 Actual Work Non 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? ThanksAnonymous
April 27, 2008
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. Any idea? Thanks in advantage, and sorry for my English!Anonymous
May 15, 2008
I meant "in advance", not in advantage! :SAnonymous
May 20, 2008
How to get schedule variance report in project server 2007. For this actually I need "Actual Start" and "Actual Finish" for this which field i need to refer in Cube.Anonymous
May 28, 2008
I am running into confusion on Data View content. I am using PS 2007 and when I use a data view to look at the Timesheet cube data I get a actual worked billable hours of 296.75. When I use the MSP_Project_Timesheet cube data I get a value of 73.5 for the same time period. Any idea of what is going on? Thanks for the assistance. DougAnonymous
June 24, 2008
Is there a way (and if so, can you point me in the right direction) to add a custom field to EPM Timesheet cube? This cube has a hook into Resource List. We have created a custom field for resources called Team Name, which I would like to bring into this reporting cube, if possible. When I goto Server Settings, Cube Configuration... step 4 from the instructions on how to add custom fields to your cube is below... On the Cube Configuration page, in the Cube Dimensions section, go to the Cube list and select the cube for which you want to specify dimensions. My problem is, I DON"T SEE THE EPM TIMESHEET CUBE in the list of cubes for Cube Dimensions... all I see in the drop down list is Project, Resource, Task, Assignment I DON"T SEE THE EPM TIMESHEET CUBE in the Cube Measures...all I see is project, resource, task, assignment. Now in CALCULATED MEASURES, I DO SEE EPM Timesheet.... what gives??? My custom field, uses a lookup table...so...any help out there?Anonymous
July 25, 2008
Hello, i'm trying to add a Member to the cube MSP_Portfolio_Analyzer. The name is "Availability" and the formula is Availability = [Capacity] - [Work], but when I type the MDX Expression shows an error message about wrong sintax (or somenthing like that...) I´m using PWA in Spanish, so i guess the names of the fields should be in that language but I still get the same result. ¿Could you people help me?Anonymous
August 21, 2008
I am trying to build cube in 2007...I have 1 project with resources assigned...I have a demand vs. capacity data analysis view built...my time period is 2008, 2009 and 2010, but my capacity only shows for 1 year...I have gone into server settings and changed resource capacity settings to 24, but I still cannot see further data???? also, can anyone explain when one needs to use each of the cubes...i am not following the need for the multiple cubes and need a primer on differentiating between the various ones. thanks.Anonymous
August 21, 2008
ok, doesn't look like my query got posted, so here I go again. I am building a cube in 2007. I have a view that looks at demand (work) and capacity...I want to see 24 months into the future...but when I build the cube I only see 12 months of capacity. I went to additional server settings and increased resource capacity settings from 12 to 24. I repbulished, rebuilt cube, but still see only 12 months...I verify capacity for the resources are for a much longer time period. Also where can one learn what and when do we use each of the various cubes...it seems everyone can list what they are, but no documentation is listed that provides a laymans description and use of each cube. it shouldn't be this hard to find this info. Thanks for your columns and posts. very helpful insights. TAnonymous
November 06, 2008
Tombzi, I have seen the same thing. I only get resource capacity to show for 12 months in the future and 12 months in the past no matter what time period I use for the cube. I guess I would like to know if this is expected behavior or an issue, because I have not seen any documentation on this.Anonymous
December 01, 2008
Hi, Im New for this, Could you give me a tutorial or links for an overview in each cube. Thanks!Anonymous
December 09, 2008
Customized Project, Resource and Task enterprise fields, even though available in the field list for the MSP_Project_Timesheet, actaully do not have any numeric values associated with them and therefore are not available in building Data Analysis views using MSP_PROJECT_TIMESHEET. How can we fix this? All the documentations shows it should work, but it does not...it is a bit frustating to see that I cannot organize not only my project specific work reported on timesheets, but all my admin time. I want to use several task and resource attributes to organize my timesheet data for cost accounting purposes. TAnonymous
March 20, 2009
I have a cube report that displays a different date than the date for same Project task. I've tried changing the date in Project. I even tried to manipulate the date in the cube report. Any suggestions/ideas?Anonymous
March 03, 2010
Was there ever a response posted regarding bringing in a custom field into the EPM_Timesheet data analysis views. (similar to what Oleksandr posted) I've create a custom task level field that I'd like to use to filter/group data similar to seeing hours per project, but instead hours posted to a task code across projects.