Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to avoid aggregations for the measure? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 14, 2009 9:04 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 23, 2013 6:48 AM Points: 93, Visits: 125
 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.
Post #771016
 Posted Monday, August 17, 2009 6:54 AM
 Say Hey Kid Group: General Forum Members Last Login: Thursday, December 05, 2013 10:03 AM Points: 674, Visits: 730
 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.
Post #771862
 Posted Monday, August 17, 2009 9:11 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 23, 2013 6:48 AM Points: 93, Visits: 125
 Thank you very much for excellent explanation!
Post #771989
 Posted Monday, June 20, 2011 9:59 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, November 25, 2013 2:02 PM Points: 107, Visits: 69
 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 :)
Post #1128361
 Posted Monday, June 20, 2011 11:31 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, August 23, 2013 6:48 AM Points: 93, Visits: 125
Here it is.

Post Attachments
 dim mapping.doc (21 views, 178.50 KB)
Post #1128421
 Posted Monday, June 20, 2011 5:53 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, November 25, 2013 2:02 PM Points: 107, Visits: 69
 Thanks Vitik
Post #1128661

 Permissions