SSAS CUBE 2 fact tables and 1 dimension

Question

I have 2 fact tables and 1 dimension.

Fact1 foriegn key is directly pointing to DIM primary key.

Fact1 and Fact2 have common column say e.g., "orgkey"

Fact2 is not connected to DIM as it hasn't got any common column.

But I want to connect Fact2 to DIM.

So as Fact1 and Fact2 got common column I connected Fact1 and Fact2 in the DSV (CUBE building).

Problem is Fact2 is not able to reference DIM table as I am not getting any data when I select DIM attributes.

I am doubting of relationships.

Could you please help me on this?

Answer 1

You need to add a connection to Fact2 in the dimension usage.

The connection is a many to many via Fact1 (If I understood your schema right).

The thing is that the connection must be made in a specific order.

You need to connect the Fact1 to Fact2 prior to connecting Dim to Fact2 so that when you chose the many to many relationship between Dim and Fact2 the drop down list will be populated with Fact1

I hope this helps.