Hi,
I want to join three tables A,B and C Where A is fact table and B & C are dimensions tables. All three have ITEM column.
The confusion is, what should be the strategy to join the tables if I want to join i) B & C ii) B & A and iii) C & A
Table A is Fact Table
Table B is Dimension
Table C is Dimension
Table A has many entries for ITEM
Table B has only single entry for ITEM and
Table C has many entries for ITEM
I want to join tables based on ITEM
There is no loop is getting generated , I need an idea to join the tables and what all the Cardinality we should use to create a join between these tables.
Please see attachment for clear idea.
Thanks
Judging from the diagram and what you said, B & C are in one dimension and B has another, additional role by itself. You want to create an alias for B and move either the relationship between it and A or the relationship between it and C to the alias.
This will disambiguate the query.
The cardinalities of the relationships should reflect the cardinalities of the entities.