Search code examples
ssasssas-2008

SSAS m2m relationship without bridge table

Example:

We have 2 fact tables:

  1. Fact table with pk that include column1 and column2. Also we have measure1.
  2. Second fact table have pk just with column2 and one measure2.

Which type of relationship should we use for dimensions in ssas, that have no regular key for relationship with measure2? Should it be m2m? If we use regular type it will multiple measure2 and show wrong information. In fact it is not many to many relationship, case for one row from fact table 1 there is just one row from fact table 2. Could you please explain me?

Solution

  • If your dimension table has a 2-column PK, then your fact table must have both of those columns to have a relationship to that dimension.

    In the 2nd fact table, you can either add the missing column to the table, or use a view to add it, or even add it in the Data Source View in SSAS as a last resort.