How to avoid aggregations for the measure?

  • 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.

  • 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.

    😎

  • Thank you very much for excellent explanation! 🙂

  • 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 🙂

  • Here it is.

  • Thanks Vitik 🙂

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

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