Cube Design Question

Hi

I am currently redesigning my WH in AS2005 and would like some advice. We
currently have an aggregated WH due to volumes : 180m+ atomic 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 two 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

Answer this question

Cube Design Question

Cube Design Question

Answer this question