Parameterized measure type - 2 different approaches

  • Hi colleagues,

    I need again your priceless time to discuss the following situation:

    I would like to parametetized the type of measure, or better, the type o calculation applied to a specific measure, i.e., I have only two base measure, Net Sales, and Gross Sales, and then I have different calculations like YTD, Currency Adjusted, Currency Adjusted Last Year, EUR Value, EUR Value LY, etc...

    Instead of create a big set of calculated members, I would like to create a measure type dimension instead, and then the user can select want he wants to see. I figured out two different ways:

    1) In the view which feeds the cube, create a data set for every measure type and flag it with the correspondent type and then apply an Union All to join the sets. The big disadvantage for me is, I will multiply the size of this measure group by the number of different measure types that I have.

    2) Inside the calculated member formula, use a switch of if-else statement to determine which formula should be apply. My concerns here are related to the performance of this approach.

    Any Idea or comment would be highly appreciated.

    Kind Regards,

    Paul Hernández
  • From what you are describing here, it doesn't seem like you are really solving the problem by creating a dimension for all your different types of calculations. You will still need to create calculated measures, so why not just do that to begin with?

  • Hi Martin,

    thanks for your answer.

    The goal is not save time in development, actually the cube and calculations become more complex.

    The question behind is what is most intuitive for the cube users and report developers. We have 5 fact tables, all of them with NetSales, GrossSales and Quantity, and all of them with YTD, QTD, MTD and Periodic measure plus different fx rates conversions. I try to simplify the final layout of the cube and the goal is to let the users select which base measures they want to see and then with a filter specify which time frame and fx rate type.

    I almost find the solution. I will share it here when I am done.

    Kind Regards,

    Paul Hernández

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply