04 Oct Fun with Analysis Services!

<dry – again!>

Gee’d on by too many nights in hotels, I started briefly messing around with Analysis Services, and I started thinking that there must be so much you can do with this tool than the basics that you get out of the box. 

My main reason for this was that I wanted to get a view of availability in terms of days (rather than the default hours) for specific resources, and in particular, I was interested in people who are over allocated, i.e., have a negative availability.  So I’ll show you how to build this type of view, though once you get into it, you’ll learn a whole lot more, just as I have.

So, start off with a blank view using the MSP_Portfolio_Analyzer cube.

Then, making sure you have the tool bar showing, select the Calculation button and create a new calculated field.

The following formula calculates availability (you can put this as a calculated field in the cube too) and then divides it by 7 (the number of hours in each day as defined in our Enterprise calendars)  

([measures].[capacity] – [measures].[work])/7

Click Change and the value will appear in the data section of the Pivot Table.

To clear up the decimal points, click on the format tab and type in #,# in the Number field, this rounds the days to the nearest whole day.

and puts in the separator for the thousands…

Now, add the resources as row fields.  Obviously you may want to apply a filter, probably by RBS, or, if you’ve set one up, OBS or similar.

Adding a time dimension is required so that we can view over allocations over the coming weeks/months.

To highlight the resources with negative capacity, we can format the cells and also group them by sorting them in either ascending or descending order.

All over allocated resources are grouped on the pivot table (and consequently any pivot chart).

Further more, we can drill into the month and get weekly details, which can be graphed too, so we begin to see by how many days each resource is over allocated in each week.

Enjoy,  Ben.