Compose DWH modelling question

[bilzekek]

Hi,

How would below modelling in Compose act.

I have 3 tables/entities in my DWH layer, that should be joined to 1 dimension in the Data mart. The issue is that Table A (the main table) has a relationship to Table B on a column that is not the Key in Table B. Tried to picture it below.

I was wondering how you would design this - and if design below is used, how Compose will handle multiple rows in Table B with same value in the column use for the relationship;

[BHR]

I believe it will all be based on what you define as the root of your dimension in the data mart. Anything that is a "parent" of the root of the dimension should be available to include in the dimension but anything that is a "child" will not. In the case above - if you choose table A as the root for your dimension then I don't believe that table B would be available for inclusion.

You might possibly be able to setup table B as a factless fact, or a join table fact. Table B would then become the Face in the middle of the star schema while Table A and Table C could be 2 dimensions that relate to the Fact table.

[bilzekek · Accepted solution]

Hi,

Yes that is the issue and all tables should be part of 1 dimension - not fact. I was able to "fix" this, by creating a "translate" table between Table A and Table B, that only contained A_ID and B_ID, but where I made A_ID the key. Table A then relates to this new table, and the new table related to Table B.

Dont know if this is the best way to fix it though.