Search code examples
ssasmdx

Is there any ways to dynamic cumulative measure in MDX?


All of the measure that I want to cumulative has the same formula. So, is there any way to use the thing like function or any thing in calculate measure to resolve this issue?


Solution

  • There are two ways to achieve your aim:

    1- the first solution is based on using the business intelligence wizard to add time intelligence to your solution.

    The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:

    1. List item

    2. Period to date.

    3. Period over period growth.

    4. Moving averages.

    5. Parallel period comparisons.

    The wizard will let you chose the calculations and measures you want to apply.

    Visit : https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/define-time-intelligence-calculations-using-the-business-intelligence-wizard

    Visit : http://www.ssas-info.com/analysis-services-articles/62-design/2465-ssas-time-intelligence-wizard

    2- Use a dimension table to calculate your calculations, this solution is more complicated, but very powerful and one of the best practices.

    The first step is to create a new physical dimension, with real members for each of the calculations we're going to need. We don't actually need to create a table in our data warehouse for this purpose, we can do this with an SQL view like this

      CREATE VIEW DateTool AS    SELECT ID_Calc = 1, Calc = 'Real Value'    UNION ALL    SELECT ID_Calc = 2, Calc = 'Year To Date' 
    

    Next, we need to add this view to our DSV and create a dimension based on it. The dimension must have one hierarchy and this hierarchy must have its IsAggregatable property set to False. The DefaultMember property of this hierarchy should then be set to the Real Value member. Giving this dimension a name can be quite difficult, as it should be something that helps the users understand what it does – here we've called it Date Tool. It needs no relationship to any measure group at all to work.

    Our next task is to overwrite the value returned by each member so that they return the calculations we want. We can do this using a simple SCOPE statement in the MDX Script of the cube:

    this code let you create the YEAR-TO-DATE aggregation for all your measures.

      SCOPE ([Date Tool].[Calculation].[Year To Date]);     THIS = AGGREGATE (            YTD ([Date Order].[Calendar].CurrentMember),            [Date Tool].[Calculation].[Real Value]); END SCOPE;
    

    Visit:https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781849689908/6/ch06lvl1sec35/calculation-dimensions