locked
Calculating percentage using aggregate data? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm new to SSAS/BI, but the data I'm working with is a great opportunity to get some experience with a data warehouse and being able to build my first cube. So please bear with me if the question doesn't make sense.

    I have a standard DimTable and a Fact Table that looks like this:

    Date          Hour     Failures     Attempts
20140201   14       5            10
20140201   15       6            10
20140201   16       2            8
20140201   17       4            8
20140201   18       7            28
20140201   19       2            8
20140201   20       7            10
20140201   21       6            20
20140201   22       1            5

    Let's say I want to calculate the fail rate percentage on a daily/weekly/monthly basis. The cube would include the aggregates of failures and attempts in N period, but is it possible that the cube include these percentages?

    So let's say I want to calculate the fail rate with the data above, then the percentage would be 40/107 or .37.

    Is it somehow possible to do this with the cube?

    Thanks.

    VM


    • Edited by rbhatup Monday, April 28, 2014 9:22 PM
    Monday, April 28, 2014 9:21 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi VM,

    In order to handle this you will have to create a calculated member named [fail rate percentage].

    Before doing that, from the above mentioned fact table, you will have to create 2 measures Sum(Failures) and Sum(Attempts), which I suppose you already have.

    Using these measures create a calculated member [Fail Rate Percentage]

    Expression : [Measures].[Failures]/[Measures].[Attempts]

    Format String : "Percent"

    Below link would give insights on how to create calculated member.

    http://technet.microsoft.com/en-us/library/ms166568.aspx

    Remember calculated members are not stored physical in the cubes, they are calculated on the fly while querying the cube. And that is suffice in your case.

    Saurabh Kamath

    • Proposed as answer by Butmah Tuesday, April 29, 2014 5:43 AM
    • Marked as answer by Charlie Liao Wednesday, May 7, 2014 2:20 AM
    Tuesday, April 29, 2014 1:51 AM