Post reply

How to avoid aggregations for the measure?

  • August 14, 2009 at 9:04 am

    #224756

    I have a problem designing a cube, probably it is simple for experienced people. To simplify idea, I am trying to create 2D cube. One dimension is Time: Month and Year. Second dimension is Region: Store, Branch, Region. Measures: Sales, Performance Factor (PF). PF is pre-calculated measure and comes from DB. So, Fact table looks like this:

    MonthYearUnitSalesPF

    12009Store1201.2

    12009Store2251.5

    12009Store3171.1

    12009Branch101.4

    12009Branch202.3

    12009Region01.9

    Region dimension:

    KeyIDStoreIDStoreBranchIDBranchRegionIDRegion

    111Store121Branch131Region

    212Store221Branch131Region

    313Store321Branch131Region

    414Store422Branch231Region

    515Store522Branch231Region

    When cube is created aggregations for Sales are correct: Branch1.Sales = Store1.Sales + Store2.Sales + Store3.Sales, Region = Branch1 + Branch2

    The problem is with PF. Branch1.PF should not be sum of Store1, Store2 and Store3, it should stay 1.4, and Region.PF should be always 1.9. My question is how better design to avoid calculations for the measure?

    Thank you.

  • August 17, 2009 at 6:54 am

    #1040143

    As the reply is quite long I have attached a word doc. This shows the creation of a dimension same as yours, the fact table presented slightly differently. There are sceen shots.

    The way to do it revolves arounf the dimensions tab on cube editor and the way you have joined the tables in the data source view.

    All the views have been hardcoded. You do not need to do this. I have just done this to give you a taster so that you can see what can be done.

    Hope this helps.

    😎

  • August 17, 2009 at 9:11 am

    #1040259

    Thank you very much for excellent explanation! 🙂

  • June 20, 2011 at 9:59 am

    #1341294

    HI, I am facing the same issue. But I guess Ells has removed the doc file.

    Can you please upload it again for my reference.

    Thanks 🙂

  • June 20, 2011 at 11:31 am

    #1341341

    Here it is.

  • June 20, 2011 at 5:53 pm

    #1341542

    Thanks Vitik 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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