CODEWITHSUNDEEP
sql-serverdata-warehouse
BoNDoK
BoNDoK

Reputation: 145

DW Design (PO and Invoices)

I have to build a DW to store PO and Invoice data:

How is the recommended way to design this in star schema?

Upvotes: 0

Views: 112

Answers (3)

Ron Dunn
Ron Dunn

Reputation: 3078

You have a problem in that you are modelling data, not process.

Star schemas are based on a business process, not an entity relationship.

What are you trying to model? What is the grain of the model?

I'll go out on a limb, and say that you're probably modelling sales. Have one fact: Sale. If you need order-specific information, consider whether it is part of an Order dimension, or if it should be carried as degenerate dimensions and/or measures in the Sale fact.

Upvotes: 1

d_luffy_de
d_luffy_de

Reputation: 967

Designing a DW involves understanding multiple aspects before having a model.

  1. What is the frequency of data refresh.
  2. What is the volume of data.
  3. Which columns need to be indexed. Also, which index will help you better.
  4. The queries written on the tables. Are the queries aggregates? or are they straight select statements.
  5. What is your history preservation strategy.
  6. The data types of every column you need. You need to think about cross platform query executions...

So on and so forth..

You will need to deep dive into it. Just creating tables with FK will help now, but over the time when data volume increases it will be a bottleneck.

Upvotes: 4

Abhay Chauhan
Abhay Chauhan

Reputation: 414

  • Create a Invoice_Header_Fact and a Invoice_LineItem_Fact. (This can be denormalized and merged in one table too)
  • Use Order_Key from Header Fact in LineItem Fact to associate it to lineitems
  • Create a PO_Header_Fact and a PO_LineItem_Fact.
  • Use PO_Key from Header Fact in LineItem Fact to associate it to lineitems
  • Create a bridge/xref table to maintain many to many relationship between PO and Invoices.

Hope this helps!

Upvotes: 0

Related Questions