How to avoid aggregations for the measure?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » How to avoid aggregations for the measure?

How to avoid aggregations for the measure?

Rate Topic

Display Mode

Topic Options

Author

Message

Vitik

Posted Friday, August 14, 2009 9:04 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:21 AM
Points: 90, Visits: 118

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

Ells

Posted Monday, August 17, 2009 6:54 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:56 AM
Points: 379, Visits: 675

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