locked
Same measure from multiple measuregroups RRS feed

  • Question

  • Hi ,


    I get data for a particluar measure at different levels.

    i.e to say Let the measure be profit I get Profit at state level and product level in a single tables and profit at country level and product combination in a different table.

    I have created a calculated memeber called profit which selects the Profit from the base measure group based on the level chosen.

    For example when i chose US and Bikes it goes to the second table.

    Is there any other better approach.


    Monday, July 27, 2009 7:33 AM

Answers

  • Seems to me like the first fact table should already be able to answer the questions that you are trying to produce in the second fact table.  The geography dimension should already be able to roll-up the data to the country level and you can create groups of products with either a dimension attribute, many-to-many table setup, or creating aggregate members in the cube.

    In your example Bikes would be a category of products, so that would simply be a level in the hierarchy and US would be a level in the geography dimension.  You should be able to handle all of these questions off a single fact table since the first one is already at a lower grain.  Just need sure that the dimensions are properly setup.  Take a look at the Adventure Works DW sample on CodePlex to review their dimenisonal model - http://msftdbprodsamples.codeplex.com/.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Monday, July 27, 2009 11:06 AM
  • Then it sounds like you would want to implement a SCOPE statement in the calculations tab of the cube to evaluate which members (or level) are being selected so that you utilize the appropriate calculation.  I am assuming this is what you are already doing.  If you want someone to review your calculation and logic go ahead and post it to the thread so that we can provide feedback.


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Monday, July 27, 2009 4:34 PM

All replies

  • Seems to me like the first fact table should already be able to answer the questions that you are trying to produce in the second fact table.  The geography dimension should already be able to roll-up the data to the country level and you can create groups of products with either a dimension attribute, many-to-many table setup, or creating aggregate members in the cube.

    In your example Bikes would be a category of products, so that would simply be a level in the hierarchy and US would be a level in the geography dimension.  You should be able to handle all of these questions off a single fact table since the first one is already at a lower grain.  Just need sure that the dimensions are properly setup.  Take a look at the Adventure Works DW sample on CodePlex to review their dimenisonal model - http://msftdbprodsamples.codeplex.com/.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Monday, July 27, 2009 11:06 AM
  • I understand what you are trying to tell me Dan.

    But in my requirement thet say the data is not aggregatble. That is the country level profit is just not a roll up of all the states hence data is coming in from different levels.

    I understand the requirement is strange but i have to deal with it so please let me know if you have any solution.
    Monday, July 27, 2009 11:49 AM
  • Then it sounds like you would want to implement a SCOPE statement in the calculations tab of the cube to evaluate which members (or level) are being selected so that you utilize the appropriate calculation.  I am assuming this is what you are already doing.  If you want someone to review your calculation and logic go ahead and post it to the thread so that we can provide feedback.


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Monday, July 27, 2009 4:34 PM