SSAS many to many dimension hierarchy

1
votes

First of all, this is very, very simple data warehouse that I made only to ask following, specific question.

Scenario:

I have one fact table FactSales, and 2 dimensions: DimShop and DimProduct, and they are both separated from each other and directly connected to the fact table. some shops can sell selected products and vice versa, some products can be selled in specific shops. This give us many to many relationship. The problem is when I try to slice my cube i get all combinations between shops and products.

Question:

How can I create hierarchy between two separated dimensions in SSAS with many to many relationship? i tried to use brigde table but i was unable to configure hierarchy in SSAS. Is it even possible?

2
many-to-manyssasdata-warehouse
I don't understand your question. What exactly do you want to show in your report? As a complete guess, you're trying to report on what products can be sold (or are stocked in) each shop, but I really don't know. – Pondlife
Oh i forgot to mention the whole idea, stupid me. Yes, you guessed it right, I'd like to show shops, and list every product which can be selled in each shop. I know that it can be achieved via hierarchy. It is easy to create such hierarchy, when shops and products' data is contained in one dimension, but I'd like to use separate dimensions for product and shops. I assume i need M:N relationship between them in a bridge table, but i dont know how to build such hierachy, best in product dimension if possible. – shq

2 Answers

1
votes

If you're trying to report on "what can happen" rather than "what did happen", you need a separate fact table & cube to represent the relationship between products and the shops that can sell the products. It's not really a hierarchy since it's many to many.

A simple cross reference fact should be fine:

FACT_PRODUCT_SHOP
ProductID
ShopID

Then when doing reports that want to see what products are allowed to be sold in what stores, you can use this fact table. The sales fact only shows "what actually happens".

You can even modify this fact to be your Inventory fact table, just adding a date and "In Stock amount" and "On order amount" etc..

1
votes

It is possible to implement such a design but it may not perform well. Basically instead of product and shop key in the fact table, you need an alternative key. This key will be the unique combination of products and shops. That needs to be prepared in the ETL. In a new dimension named "Shops and Products", on top of this key, you can create 2 hierarchies Product and Shop in the same dimension.

Additionaly, you can also create an unnatural hierarchy as you requested. But since it is an unnatural hierarchy, it may not perform well. So in addition to Product and Shop hierarchies, you can provide following unnatural hierarchies: Shop -> Product, Product -> Shop.