MS SQL Server OLAP - Product Dimension

Author

Product Dimension

duvinrouge@NOSPAMservihoo.com

2006-03-23, 7:43 am

I have a parent-child product dimension.
It is parent-child because it is unbalanced - some products have more
levels than others.
This dimension has the product members for the entire Group.
However, because different products have different relevant dimensions
I have a number of cubes - i.e. I don't want to put all measures & all
dimensions of all products into just one cube.
If I were to use this product dimension in all cubes I have two
problems:
1. The end user sees numerous 'irrelevant' product members when
exploring the dimension
2. More seriously, because the seperate cubes will only have facts for
the appropriate products the aggregation up to 'All' (Group) will be
misleading.

It would seem that I should have a centrally maintained product table &
from this create a number of subset product dimensions.

Is this the best approach?

Many thanks.

Deepak Puri

2006-03-24, 8:26 pm

If you're using AS 2005, it might be better to incorporate the data in a
single cube (with multiple measure groups, if necessary). The fact that
you need to aggregate the values for the different product groups
suggests that they're not viewed as totally independent by end-users. In
terms of end-users browsing the products dimension, maybe the
auto-exists feature of AS 2005 can help, for example:
[color=darkred]
select {[Measures].[Order Quantity]} on 0,
[Product].[Product].[Product].Members on 1
from [Adventure Works]
where [Product].[Color].&& #91;Red][color=darkr
ed]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links