Dimensional model

Hey All

In a Star schema, can a Fact table in one data mart be a Dimensional table in another data mart or can we connect two fact tables or what can we do if we need to connect a column (primary key) from one fact table to another fact table?

Thanks in advance

Different facts are joined through a common dimension. I can't imagine that a dimension table in the same form could replace a fact table or vice versa. I have created factless fact tables that look similar to some of my dimensions, e.g. Customer.

A dimension contains attributes and a fact table contains measures and the foreign keys to the different dimensions that give context to that fact table.

Based on this, using a fact table (without attributes) as a dimension is... let put it this way, strange? You will not be able neither to add any fact table attribute to your selected column list nor to add filters on that "dimension".

In many cases this scenario means that the second fact table has a different granularity than the first fact table (the one you want to dimensionalise). To resolve this situation, include the dimensions of the first fact table to the second one. Then you will be able to report on a superset of dimensions that will include all dimensions of your first fact table plus specific dimensions for the second fact table.

Please let me know if this makes sense in the scenario you are facing.

Best regards,