## How to avoid aggregations for the measure?

 Author Message Vitik SSC-Enthusiastic Group: General Forum Members Points: 121 Visits: 162 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:Month Year Unit Sales PF1 2009 Store1 20 1.21 2009 Store2 25 1.51 2009 Store3 17 1.11 2009 Branch1 0 1.41 2009 Branch2 0 2.31 2009 Region 0 1.9Region dimension:KeyID StoreID Store BranchID Branch RegionID Region1 11 Store1 21 Branch1 31 Region2 12 Store2 21 Branch1 31 Region3 13 Store3 21 Branch1 31 Region4 14 Store4 22 Branch2 31 Region5 15 Store5 22 Branch2 31 RegionWhen cube is created aggregations for Sales are correct: Branch1.Sales = Store1.Sales + Store2.Sales + Store3.Sales, Region = Branch1 + Branch2The 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. Ells UDP Broadcaster Group: General Forum Members Points: 1464 Visits: 931 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. Vitik SSC-Enthusiastic Group: General Forum Members Points: 121 Visits: 162 Thank you very much for excellent explanation! :-) Basit SSC-Enthusiastic Group: General Forum Members Points: 114 Visits: 77 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 Vitik SSC-Enthusiastic Group: General Forum Members Points: 121 Visits: 162 Here it is. Attachments dim mapping.doc (47 views, 178.00 KB) Basit SSC-Enthusiastic Group: General Forum Members Points: 114 Visits: 77 Thanks Vitik :-)