CODEWITHSUNDEEP
data-modelingdata-warehousedimensional-modelingstar-schemastar-schema-datawarehouse
variable
variable

Reputation: 9714

Should sales order number and purchase order number be placed in FactOrder table or should it have its own dimension?

I have FactOrders with date,saleid,productid,salesorderno,purchaseorderno,qty,price

There are date and product dimension that link to FactOrders on date and productid respectively.

Should sales order number and purchase order number be placed in FactOrder table or should I place them into a new dimension?

Upvotes: 0

Views: 46

Answers (2)

Joe S
Joe S

Reputation: 346

The Sales Order Number, if it is useful to the business, would simply live on the FactOrder table. In that case, the Sales Order Number creates a degenerate dimension containing that descriptive value.

The Purchase Order Number can live there as well, but usually only the transaction control number for that fact table would be present. The Purchase Order Number would live on its own FactPurchaseOrder, if one is present.

Upvotes: 0

NickW
NickW

Reputation: 9798

It's entirely up to you. If there are just these two attributes, you know they will never change and there will never be additional "sales dimension" attributes then it probably makes sense to create them as degenerate dimensions (i.e. add them to the fact table) rather than create a dimension table to hold them.

There is no right or wrong answer to your question - only the most appropriate solution for your specific circumstances

Upvotes: 0

Related Questions