M-M Relation in Purchase Order and Receivings Fact

Hi

I am working on my first BI/DWH projects, I have come across Purchase Order and Receiving business processes and modelled as follows, please correct and guide me on the following points based on the below modelling example

a. Is it OK to create a PO or RCV dimension in DWH or should it be handled in the BI app, or should it not be created at all
b. Creating Factless Fact Table to join two facts (based on my understanding that the PO dimension and fact will have the same no. of rows hence a PO dimension key will always refer to the correct row in the PO fact table, see example). Now should I join two star schema with fact less fact tables or should I include the foreign key of PO dimension in RCV fact?
c. Is multiple lines in PO Dimension is OK or should I use distinct in populating the dimension.

DIM_PurchaseOrder (mutiple lines of a single PO)
1. PO_Id (Surrogate Primary Key)
2. PO_Number
3. PO_Line_Remarks

FCT_PurchaseOrder
1. PO_Detail_Id (Surrogate Primary Key)
2. PO_Id (FK from PO Dimension)
3. Vendor_Id (FK from Vendor Dimension)
4. PO_Date_Id (FK from Time Dimension)
5. Item_Id (FK from Item Dimension)
6. Quantity
7. Unit_Price
8. Amount

DIM_Recieving
1. RCV_Id (Surrogate Primary Key)
2. RCV_Number
3. RCV_Line_Remarks

FCT_Receivings
1. RCV_Detail_Id (Surrogate Primary Key)
2. RCV_Id (FK from PO Dimension)
3. RCV_Date_Id (FK from Time Dimension)
5. Item_Id (FK from Item Dimension)
6. Quantity

FLF_PurchaseOrder_Receiving
1. Id
2. PO_Id (PO Dimension Foreign key)
3. RCV_Id (RCV Dimension Foreign key)

PO and Receipts are not a M-M relationship. Recipts are performed against PO lines, so a receipt line can only reference back to a single PO line... one PO line, one or more receipts... 1-M.

You usually do not have either a PO dimension or a Receiver dimension, they are usually degenerate dimensions in the fact.

You don't need the FLF... table. The receipt line should carry the po line reference.

Thanks for the comments, they are really helpful.

If you look at the the PO Dimension, I have marked PO_Line_Remarks means the remarks mentioned at every line item of the PO, my client wants to see almost every info. marked at PO alongside the analysis or drill downs, do u still suggest I should take this in a degenerate dimension. What do you suggest about the Lookups or statuses usually marked on PO Header.

Have a line remarks dimension and create a row for each unique remark. PO Number goes in the fact as a degenerate dimension.

Well, my confusion has increased

You means to say that every information besides the measures and the dimension references should got into the PO fact and fields like LineRemarks, POStatuses should go into to a separate single dimension (do we call this a Junk dimension). It will carry the same no. of records as the fact would have.

second thing is referencing a PO line in the Receipt Fact means that we are relating a fact with a fact since you said that we should not create a separate PO or Receipt dimension but leave the PO/Receipt No in the respective facts, or do you mean we create the PO DD in the tool and make its relationships with PO and Receipt facts

waiting anxiously for the reply