How to assign combinations of members into specifc groups (for OLAP dimensions)

Question

I've got what I believe would be a fairly common scenario in OLAP but a really great way of addressing it isnt jumping out at me:

ive got say a sales transaction with multiple items, with different products for each one..

What I want to do is assign the product group combinations into a categories i.e. 'dog only', 'cat only', 'cat and dog'

So you'd end up with something like..

If possible I'm hoping to achieve this in Transact-SQL or SSIS, I found calculated members/measures to be slow, put I'm open to that solution if that is the best way to do it

Any help much appreciated

Answer 1

If you want to follow best practices for dimensional modeling (Kimball methodology), you should only have one dimension table for products which also contains all product groupings. In other words, you denormalize all product attributes in one dimension. This Product dimension would have a surrogate key, ProductKey (use an Identity column in SQL Server). This key which is the PK, would be the FK in your fact table which has all the transations. So based on your simple model you would have one fact table for your sales info which has all your measures (amount, qty) and one FK to your Product dimension (ProductKey). Your product dimension would have all product attributes including categories.

In SSAS simply bring the two tables in your DSV and create one dimension (product) and everyting is taken care of for you.