Home > Analysis Services > Percentage Aggregations in Analysis Services

Percentage Aggregations in Analysis Services


Aggregations, as we all know, are what gives cubes an advantage at query time. But while summing the lower levels of data to produce an aggregate is fine for a lot of measures , some measures are semi-additive. Now obviously there are options in analysis services for semi-additive measures , such as Min, Max and Distinct. What about percentage aggregations ? ( Or average values )

Click on the Cube Structure tab , select a measure and you can set this behaviour in the properties. Thing is , if you have percentage data in your cube, you want the aggregate at the next level up to be an average of the child level. There is an option in the properties called AverageOfChildren, but be warned , this doesn’t always work like you think it would !! If you’re expecting it to return an equivalent of a simple AVG() with a GROUP BY as in SQL, it doesn’t. It seems to return the average ONLY against the time dimension !

So what to do ? Well, we need to implement a calculated member that does a simple sum/count. It will apply this at all levels and give us the behaviour that we require. Lets use good old AdventureWorksDw. I didn’t find any measures with percentage values, so I’ve decided to create a fact table of my own, using the following query :

SELECT
    SUM(FactRS.SalesAmount) AS SalesAmount,
    SUM(FactRS.TaxAmt) AS TaxAmt,
    SUM(FactRS.OrderQuantity) AS OrderQty,
    MIN(FactRS.SalesTerritoryKey) AS SalesTerrirityKey,
    MIN(DimTime.TimeKey) AS TimeKey,
    FactRS.EmployeeKey,
    DimTime.CalendarQuarter,
    DimTime.CalendarYear,
    FactSQ.SalesAmountQuota,
    SUM(FactRS.SalesAmount) / FactSQ.SalesAmountQuota * 100 AS PercTargetAchieved
FROM dbo.FactResellerSales AS FactRS
INNER JOIN dbo.DimTime
    ON FactRS.OrderDateKey = DimTime.TimeKey
INNER JOIN dbo.FactSalesQuota AS FactSQ
    ON FactSQ.CalendarYear = DimTime.CalendarYear
    AND FactSQ.CalendarQuarter = DimTime.CalendarQuarter
    AND FactSQ.EmployeeKey = FactRS.EmployeeKey
GROUP BY FactRS.EmployeeKey,
    DimTime.CalendarQuarter,
    DimTime.CalendarYear,
    FactSQ.SalesAmountQuota

I’ve created a simple cube linking this fact table to the SalesTerritory, Employee and Time Dimensions.

Now one of the nicer things about Analysis Services is that it always tries to do things for you – it will normally create a measure called the same as your Fact table + “Count”, in this case Fact Sales Count. If it doesn’t create it you can create it manually by including a running value field and setting that fields aggregation to sum.

Now we need to add the calculated member. Click on the “Calculations” tab, then on the little calculator icon to start. Name your calculated member, select Measures as the parent hierarchy , and in the expression box enter the calculation. This would be :

Sum([Measures].[PercentageMeasure])/Sum([Measures].[FactCount])

In other words the sum of the percentage field divided by the sum of the Count field. This should do it , process and browse the cube and verify the results.


So there you go , a bit of work but it seems to do the trick. Now if only we could get a built in function to do this for us….

Update 24/01/2009 : As pointed out to me by a colleague, doing a sum/ count of a percentage value won’t always realize the correct answer that’s needed. What you have to do in that case is bring in the base data and write a calculated member that will return a percentage value at all levels while performing the calculation of the base measures.

Advertisements
Categories: Analysis Services
  1. Carl Thompson
    August 15, 2012 at 11:17 am

    Hi,

    I think I may be experiencing a similar problem as described in your last paragraph??

    I am trying to create a calculated measure using 2 existing measures which provide whole numbers. However, I would like the calculated measure to be a percentage. Therefore, my calculation looks as follows:

    [Measures].[Measure A] / [Measures].[Measure B]

    I am then returning on the ROWS column a time Hierarchy consisting of [Hour], [Half Hour] and [Quarter Hour] ([Time] has not been included as it is not needed).

    When returning the dataset into my report and displaying all of the returned TIME members I get the correct results. The results show the percentage at the [Quarter Hour] level.

    However, when I group by [Hour] and remove the [Half Hour] and [Quarter Hour] from the tablix the percentage calculation still only calculates at the [Quarter Hour] level.

    I have attempted the following but stil get the same result:

    (([Dialler Time].[Time Labels].currentmember, [Measures].[Measure A] )
    /
    ([Dialler Time].[Time Labels].currentmember,[Measures].[Measure B]) * 100)

    How do I get my Tablix to provide the percentage at the appropriate grouping level? In my case, [Hour].

    Any help would be greatly appreciated.

    Thanks

    Carl

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: