I thought i'd put rogether a quick post on Cube customisation, as this is something that I am constantly asked about. Now I am not an MDX expert at all, but there are some useful results you get from some pretty simple MDX expressions.
Firstly, custom measures are done in the OLAP Cube Database Management section of the Project Web App Server Settings page. Hightlight the cube you want to customise and then click Configure.
Once in this section you need to scroll down to the bottom and decide which cube you want to add the measure to in the OLAP database. Generally in most scenarios, the Portfolio Analyser cube is the best target, depending on the data you wish to interrogate. Once the cube is selected, click Insert.
S-Curves
OK, so you've got a decent OLAP report (in excel, PerformancePoint Services or the like) showing data by month, but you been asked for a nice S-curve report showing Baseline, Work and Actual Work on a cumulative curve. As with all useful things, its short but complex query that is needed.
This example would give you a cumulative WORK calculation, specifically from the earliest date possible.
> sum(periodstodate([time].[(all)]),[measures].[Work])
This example is a little more simple and would do the same but only over this year
> sum(periodstodate([time].[Year]),[measures].[Work])
This example is the associated Actual Work S-Curve but allows for there to be NULL values where no actual work exists - in the future - rather than the S-Curve flatlining after the last reporting period.
> iif([actual work]=0,NULL,sum(periodstodate([time].[(all)]),[measures].[actual work]))
Capacity Heatmap (Remaining capacity percentage)
A nice simple one for Excel Services conditional formatting, calculating the % of capacity that is remaining based on the Work values.
> ([Capacity]-[work])/[capacity]
Filtering a measure based on a dimension member to derive custom cube measures
imagine the scenario. On your report you want to put the following onto a stacked column chart
- Actual Work spent on Project Priority 1 projects
- Actual Work spent on Project Priority 2-4
- Resource Capacity (as a line)
note: Your priority field is a flat Lookup Table that doesnt have this "P2-4" grouping built in
In this scenario to easily derive these values:
1 - Create a measure for Actual Work spent on P1 projects:
ActWorkP1 ([measures].[actual work],[Project Priority_Project].[Priority 1])
2 - Create a measure for Actual Work spent on "everything else" besides P1 Projects
ActWorkP2-4 [Actual Work]-[ActWorkP1]
This may seem like an edge case and you can of course use a pivot table to cross analyse and filter for these values, but what this DOES give you is an interesting OLAP based source for KPI's....
Next target: I am currently looking at how I can derive a custom measure of the actual work done on one "Administrative" task across any given timesheet period....
More to follow!
Firstly, custom measures are done in the OLAP Cube Database Management section of the Project Web App Server Settings page. Hightlight the cube you want to customise and then click Configure.
Once in this section you need to scroll down to the bottom and decide which cube you want to add the measure to in the OLAP database. Generally in most scenarios, the Portfolio Analyser cube is the best target, depending on the data you wish to interrogate. Once the cube is selected, click Insert.
S-Curves
OK, so you've got a decent OLAP report (in excel, PerformancePoint Services or the like) showing data by month, but you been asked for a nice S-curve report showing Baseline, Work and Actual Work on a cumulative curve. As with all useful things, its short but complex query that is needed.
This example would give you a cumulative WORK calculation, specifically from the earliest date possible.
> sum(periodstodate([time].[(all)]),[measures].[Work])
This example is a little more simple and would do the same but only over this year
> sum(periodstodate([time].[Year]),[measures].[Work])
This example is the associated Actual Work S-Curve but allows for there to be NULL values where no actual work exists - in the future - rather than the S-Curve flatlining after the last reporting period.
> iif([actual work]=0,NULL,sum(periodstodate([time].[(all)]),[measures].[actual work]))
Capacity Heatmap (Remaining capacity percentage)
A nice simple one for Excel Services conditional formatting, calculating the % of capacity that is remaining based on the Work values.
> ([Capacity]-[work])/[capacity]
Filtering a measure based on a dimension member to derive custom cube measures
imagine the scenario. On your report you want to put the following onto a stacked column chart
- Actual Work spent on Project Priority 1 projects
- Actual Work spent on Project Priority 2-4
- Resource Capacity (as a line)
note: Your priority field is a flat Lookup Table that doesnt have this "P2-4" grouping built in
In this scenario to easily derive these values:
1 - Create a measure for Actual Work spent on P1 projects:
ActWorkP1 ([measures].[actual work],[Project Priority_Project].[Priority 1])
2 - Create a measure for Actual Work spent on "everything else" besides P1 Projects
ActWorkP2-4 [Actual Work]-[ActWorkP1]
This may seem like an edge case and you can of course use a pivot table to cross analyse and filter for these values, but what this DOES give you is an interesting OLAP based source for KPI's....
Next target: I am currently looking at how I can derive a custom measure of the actual work done on one "Administrative" task across any given timesheet period....
More to follow!
Comments
Post a Comment