Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to avoid aggregations for the measure? Expand / Collapse
Author
Message
Posted Friday, August 14, 2009 9:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:27 AM
Points: 93, Visits: 139
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 PF
1 2009 Store1 20 1.2
1 2009 Store2 25 1.5
1 2009 Store3 17 1.1
1 2009 Branch1 0 1.4
1 2009 Branch2 0 2.3
1 2009 Region 0 1.9

Region dimension:
KeyID StoreID Store BranchID Branch RegionID Region
1 11 Store1 21 Branch1 31 Region
2 12 Store2 21 Branch1 31 Region
3 13 Store3 21 Branch1 31 Region
4 14 Store4 22 Branch2 31 Region
5 15 Store5 22 Branch2 31 Region


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.
Post #771016
Posted Monday, August 17, 2009 6:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:23 AM
Points: 739, Visits: 848
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:27 AM
Points: 93, Visits: 139
Thank you very much for excellent explanation!
Post #771989
Posted Monday, June 20, 2011 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:15 PM
Points: 107, Visits: 71
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:27 AM
Points: 93, Visits: 139
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:15 PM
Points: 107, Visits: 71
Thanks Vitik
Post #1128661
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse