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

Michael:

Another approach I use in AS2000 will also work in AS2005. We have a
large cube with 3 significant non-measure dimensions: Time (at week
grain), Product, and Geography (Geos). We have one measure (ACV) that
is non-additive over Geos, additive over Product, and averaged over
Time. I had to develop a solution in order to get acceptable report
performance using this measure and several measures that are based on
it.

The solution is to build a 'normal' cube with regular dimensions. Using
a C# program (using OLE for OLAP that returns flattened rowsets), the
cube is queried with the ACV measure for Product at the leaf level,
Time at the Week level, and every Geos value. I write the dimension
labels and measure value out to a new table (about 43 million rows for
104 weeks). I create two new single-level dimensions: TimeWeek (with
just Week members) and GeosFlat (all Geos values in a 'flat' hierachy).
I create a new cube that has those dimensions and the shared Product
dimension. This cube only aggregates over Product and takes just a few
minutes to process. I join this cube with the 'normal' cube in a
virtual cube that uses the LookupCube function in a measure visible to
users.

This results in excellent performance for reports using this measure.
You may have an issue with the extract processing time (mine takes 40
hours per month for all 104 weeks). We restate the data every month.
Another issue may be designing a query that gives a rowset easy to
read. Except for the smallest cubes, stay away from ADOMD cellset
results - performance is bad. Right now, I'm experimenting with XMLA
but not getting good performance with large result sets.

Regards,

- David