Modeling deeply related dimension

Given the following condition:

- A subscriber entity always belongs to one revendor and a customized product always belongs to one revendor.
- There is business need to filter the fact table to revendor specific data
- There may be business need to quickly lookup, if a certain subscriber entity or customized product belongs to a revendor.
- A subscription fact table that shows, which of our products are sold in which customization over which revendor to which customer. There won't be entries for some revendor -> subscriber and revendor -> customized product combinations.

What is the best model? If Revendor would only be related to subscriber entity OR customized product, I would certainly flatten the hierarchy, but this way I am in doubt? I tend to do the following modeling:

- A Revendor dimension consisting of the attributes "Revendor Code", "Revendor name" and others...
- A Subscriber entity dimension resembling the "Revendor Code", "Revendor name" to flatten the hierarchy.
- A customized product dimension resembling the "Revendor Code", "Revendor name" to flatten the hierarchy.

But couldn't the extra Revendor dimension be neglected then? Or would it be better to not resemble the Revendor identifiers in subscriber entity and customized product but implement this relations in two factless fact tables itself.

Best regards,

Bergtroll

"A subscriber entity always belongs to one revendor"

Would "A subscription always belongs to one revendor" also be a true statement? And, is a customize product be the object of the subscription?

Where I am going with this is the basis of a dimensional model is to tie context to actions (business events). Subscriber, re-vendor, and product are all context. The subscription is the event. Rather than worry about relationships between contexts, worry about context relating to events. The events would imply the relationship between contexts.

The only issue is such relations will only occur if a subscription occurs. If that is not a problem then there is no need for a factless fact.

Hi and thank your for answering one of my questions again ngalemmo,

Yes you are right, the subscription is always one subscriber entity that has QUANTITY (the measure) subscriptions for one customized product, delivered by exactly one revendor. Unfortunately our source systems do not track the subscribers with one unique natural key on our side. Instead we are using the revendor code and subscriber code used by revendor as a compound identifier. Same is for customized product, the revendor code and product identifier is used as compound natural key. This leads to what at least I fear may be a problem, that the dimensions are always implying the coupling with the revendor, but my current understanding is, that this is not desirable and each dimension should be independent from the others? But If I just omit the revendor code information there may be trouble, that two distinct revendors casually use the same subscriber identifier or more realistic the same customized product identifier and name, due to the fact that several now split revendors started as a single company.

Don't let source system natural keys drive the design of the analytic model. You have a subscriber dimension table whose natural key is the re-vendor number and some subscriber number provided by the re-vendor. Ok, fine. Compound natural keys are not unusual. It does not mean you need to design your dimensional model so that the subscriber is dependent on the re-vendor. It just means when loading facts, you need both bits of information to find the proper subscriber row.

In the analytic (dimensional) model, the compound natural key doesn't imply anything other than a means to find rows when loading facts or maintaining the dimension row. That is one of the reasons why surrogate keys are used in the analytic model: to remove such implications.

Thank you a lot ngalemmo, that was exactly the clarification I needed :-).