OLAP PivotTable Extensions – extending analysis in Excel

August 24, 2009

Anyone developing cubes who uses Excel as an analysis/debug tool may find there are a couple of limitations. One of these is knowing what MDX is generated by Excel to query your OLAP cubes.

Yes, you could run a SQL Profiler trace on the SSAS instance and find out that way but its easier to install the OLAP PivotTable Extensions. This will let you view the MDX directly in Excel amongst other things.

Im surprised i only just came to know of this today since this utility has been on Codeplex for some time. Better late than never!

Handling Divide by null in Cubes for PPS, a better way than IIF()

August 13, 2009

Today i needed to get rid of the “1.#INF” error you get from SSAS when there is a divide by zero or null. The reason for this was that i was building a scorecard in PPS that had a “% change from previous month” calculated member as one metric in the KPI. This was specified as (Current-Previous)/Previous with the appropriate format string. When there was no activity in the previous month Previous would be null and this would give the error. I wanted to produce null instead so i could use the PPS scorecard option to replace null with a custom string.. in my case just a hyphen (i.e. “-“)

I initially though of using IIF() to solve my problem but i have read many times how it should be used thoughtfully as it can affect performance in certain scenarios. I have also experienced this in practice. Unfortunately ive lost the original post i read but i will reproduce the method i found here.

The SSAS formula parser tries to performs certain optimizations and we can take advantage of that! ill simplify the example by using just two values A and B.

If our calculated member originally took the form:


we replace it with:

(B * (A/B)) / B

What does this do? Well if you do the simple maths, effectively it just multiplies the calc by 1.. but the advantage is that the parser will not evaluate the rest of the expression if B is null (N.B this does not work if B = 0) . I tested this behaviour with the following query:

with member a as 55

member b as null
member c as 0

member c1 as a/b
member c2 as a/c

member c3 as (b * (a/b))/b
member c4 as (c * (a/c))/c

select  {c1, c2, c3, c4} on 0




Its interesting that c4 returns “-1.#IND”. This (according to the IEEE 754 definition) means “Negative Indefinite”. Basically 0/0 is special case and is not the same as any other divide by zero.

Here is a related post on this topic.

SSAS: Comparing Actuals and Targets at different granularity when you cant use IsLeaf()

August 12, 2009

Disclaimer! : Ive purposely simplified a lot of the examples here for readability. It may seem as though i am not using best practice such as surrogate IDs in dimensions etc.. this is not the case.

Yesterday I inadvertantly come up with an interesting solution (in SSAS2005) to a common problem! For arguments sake say I have a simple cube containing two measure groups – one for Actuals and one for Targets. The Actuals fact contains a measure “Actual_Days_Elapsed” i.e. the number of days an event took. There are number of dimensions such as Time, Location, and Event_Group – all are related to the Actuals via regular relationships using the dimension key attribute.

The Event_Group dimension contains a hierarchy that is of particular interest in this example:


The Actuals fact is related to every dimension, however, the Targets fact is only related to the Event_Category dimension and at the “group Code” level i.e. We only have a Target_Days_Elapsed per event Category.

I dealt with this by relating the Event_Category dimension to the Targets fact using an attribute that was not the dimension key. This worked as expected.

Then things got interesting.. i was later asked to report on the percentage of facts that were within target. My first thought was to add a calculated member called “Within Target” along the lines of:

iif(Actual_Days_Elapsed <= Target_Days_Elapsed), TRUE, FALSE)

I quickly realised that this didnt work because it is performing the calc on the aggregation and at the leaf level. To be clear we want SSAS to perform the calc at the leaf level and then aggregte normally.

Some research led me to this informative post by Ajit Singh. This and a related thread on the MSDN forums led me a potential solution. I tried to implement the IsLeaf() solution which is quite elegant. However i got an error saying i coulnd tuse isleaf in my script because i had used a non-key attribute to link Event_Category to the Targets fact. This does makes sense.. but whoa.. what to do now?

After racking my brains for a little while i came up with the following solution:

  1. Alter the DSV and replace the Actuals fact with a named query
  2. The named query (simplified for the example) is:

    , CASE WHEN Fact_Actuals.Actual_Days_Elapsed <= Fact_Targets.Target_Days_Elapsed
    THEN 1 else 0 END AS Within_Target

    LEFT OUTER JOIN Fact_Target
    ON Fact_Actuals.Group_Code = Fact_Targets.Group_Code

  3. Add a new measure in the Actuals measure group on the new Within_Target which has a Sum aggregation type.
  4. Add a calculated member to do the percentage with target now that we have a measure giving us the number that were within target. Assumption here is that we have a default Count measure on the Actuals fact.

PeformancePoint – custom master pages

August 11, 2009

In some cases you will find that the default master page associated with the PPS dashbaord upon deployment is not suitable. Usually it is because you need additional screen  real estate to properly display the dashboard. All you need to do is create a new master page in Sharepoint Designer in your master catalog. I usually start with a copy of an existing master page.

Note when deploying to a subsite you need to ensure the master page you want to use has been copied to the master page gallery in the subsite.

Streaming Shoutcast to the PS3 with PS3 Media Server

August 10, 2009

Most PS3 owners will no no doubt be familiar with the media capabilities of the console. I was using the very capable TVersity media server for some time.. until recently! Problems i had with tversity were the fact that it needs to index media to keep in its internal catalog, the user interface is a littly flaky and that you need to restart the service if you reboot your computer otherwise the PS3 cannot see it (Windows Vista/7). Also i wasnt able to successfully use any shoutcast streams… kept on getting the famous “The data is corrupted” message.

So this weekend i did some research and discovered the Java based PS3 Media Server. I must say i was very impressed – the entire setup and configuration took me 5 minutes. Adding a shoutcast stream was a manual config file edit, but as easy as this.

This server reads the filesystem directly so there is no need to rescan the hard drives and update a catalog when you add new media. Shoutcast works great and the PS3 picks up the media server all the time.