topic Re: Data Modelling Issue (Many to Many between Fact and Dimension) in Developer

Data Modelling Issue (Many to Many between Fact and Dimension)

RRaj_293 — Sat, 27 Jan 2024 17:25:05 GMT

Hello ,

Please help with the below data modelling question-

I have a fact table called purchase Order and few other dimensions connected in star schema . Now I have brought in a connection to smartsheet and the columns in both the fact and smart sheet table is as shown-

Purchase Order Fact Table
Purchase Order	Line Number	Ordered Units
ABC1	1	4
ABC1	2	5
ABC1	3	2
PQR2	1	13
PQR2	2	24
PQR2	3	5
PQR2	4	1
XYZ3	1	3
XYZ3	2	8
XYZ3	3	9

Smart Sheet Table-

Program Code	Program Name	Purchase Order
123	united pgm	ABC1
345	Exclusive pgm	ABC1
678	Rep Pgm	PQR
123	united pgm	XYZ3
678	Rep Pgm	XYZ3

- Purchase Order is the only common field(joining field) between both the fact and smart sheet table.

- One purchase order in Smart sheet table could belong to more than 1 program and hence can have more than one program code. This results in a M-M relationship as shown in table above(Multiple records of purchase order).

- In the Power BI report , Purchase order Number is used for display from Fact table and only 'Program Code' and 'Program Name' is used from Smartsheet table.

Problem - I tried single and bi-directional filter for the M-M relationship between these two tables they are not yielding right results. Even if I create a bridging table it would have one purchase order occuring twice because of the program codes.

Please advise how fix this data model issue.

Re: Data Modelling Issue (Many to Many between Fact and Dimension)

Burhan_Jiru — Mon, 29 Jan 2024 13:46:45 GMT

Hi,

try creating a bridge/factless fact table it will solve your many to many relationship problem.
if your don't know about bridge/factless fact table you can search on google that how to build one and what are the use cases of it.

I hope this is helpfull

thank you

Re: Data Modelling Issue (Many to Many between Fact and Dimension)

lbendlin — Mon, 29 Jan 2024 21:57:52 GMT

That is not a Purchase Order Fact Table. It is a PO Line Item fact table. Add a PO dimension table in between.

Re: Data Modelling Issue (Many to Many between Fact and Dimension)

RRaj_293 — Tue, 06 Feb 2024 23:25:08 GMT

Hi , Thank you all for responding. A bridge table did not resolve it. I went with a M-M bi-directional join and enabled the 'show items with no data' so this gave me the expected results of a left outer join by bringing in only the program codes of the Purchase order used from fact.