Cube Design question

Last Post 24 Aug 2006 04:34 PM by SQLUSA. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ShreuderM
New Member
New Member

--
15 Aug 2006 12:08 AM
Hi

I am currently redesigning my WH in AS2005 and would like some advice. We
currently have an aggregated WH due to volumes : 180m+ rows per month. The
problem is that some of the data is not additive. Most of the messures are
normal additive or count measures, but a couple are not. And as we are not
storing the atomic data we need to pre-aggregate the data to the levels that
we require and try to represent it that way. We currently have a work around
in the cubes but it is not a good solution as it only allows us to see the
top and bottom levels of the data. As far as I can see there are tow options:

Option 1
Create a Parent Child Relationship of the Dimension that we are grouping on
and have all the levels of Aggregations in one table. We could then have no
aggregations on the affected measures and create a calculated column using
the .DataMember constraint to get to the data.
The problem I see with this is the complexity of admin on the Dimension
(Type 2) and no aggregations on the Parent child Dim.

Option 2
Create a standard Dimension and have a table for each level of the data.
Create a Measure Group per table joining to the different levels in the
Standard Dim. Create a Calculated Measure to pull the different measures
together at the different levels in the Dim.
This would allow for Aggregations to be created but creates more Measure
Groups and complexity.

Are there any other options that I have not thought of that could solve this
problem or thoughts on these two solutions?
Regards
Michael
SQLUSA
New Member
New Member

--
22 Aug 2006 02:05 AM
Can you explain more about the non-additive measures?

Can you list the fact table definition?

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
ShreuderM
New Member
New Member

--
23 Aug 2006 05:46 AM
Hi

Basically, we have got an Online Network, where players from different brands can play games against each other. If you do a straight count of the number of games per User, this will be inflated as you can have multiple users involved in a game. So you need to have the value aggregated to the next level up, ie the Brand, how many games occurred where members of this brand were involved, and then how many games occurred across the whole network.

As I said earlier, it is probably better to have atomic level data to allow you to calculate these things on the fly, however due to volumes (180m + rows/month) , that is not really feasible. This means that we are calculating the measure at each level of aggregation that is required, and need to store this. Either we create the dimension as a Parent-Child and bear the costs of no Aggregation storage on all the other additive measures, or the other option I can think of is to have a standard Dimension, have each level of data in it’s own fact, and join the data to the different levels in the dimension.

Thanks
Michael
SQLUSA
New Member
New Member

--
24 Aug 2006 04:34 PM
Is your table design for the fact table thin?

Are you using smalldatetime instead of datetime? etc....


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
ShreuderM
New Member
New Member

--
31 Aug 2006 06:31 AM
Hi

We are using Surrogate Keys for everything so making the fact as thin as possible.

Michael


Acceptable Use Policy
---