Purchase orders Fact Table Design

Hi,
I have a question regarding purchase orders Fact table.
We have 2 source tables master purchase orders (weekly closed orders with 1 year history), and daily purchase orders with open purchase orders.
my requirments are to have 2 fact tables, one for open (daily) and one for close (master)
also the team want to see for each SKU the next 3 comming purchase orders qty and thier dates
is calculated fact via a view of open purchase orders fact table would be the best approach?
any better idea ?
Thanks
Alex

The closed/active thing is fine.

As far as the 3 pending purchases, it seems all you need is a detailed purchase order table. Then investigate windowing functions in your DBMS SQL Manual.

The thing is, it is too soon to talk about aggregates. If you can get acceptable performance using detailed data, why bother with the extra work? Run some tests.

If the two fact tables look the same, I would partition the table on open/close status.

According to what i have read theres very little information regarding what's the real connection between that "Weekly_Closed_Orders" and the "Daily_Open_Orders".

So after analyzing your post i would say something like this:



So with the SKU and the time you can join the facts by the lkps and get your pending purchases.

PS: assuming that SKU is a Stock Keeping Unit

Thank you guys
The Issue is the manager need to see


SKU QTY1 QTY2 QTY3 QTY1_due_Date QTY2_due_Date QTY3_due_Date
ABC 50 70 40 5/15 6/12 7/02


is the best way to have this aggregation with open orders is to have View based on open orders fact
instead of ETL to build other calculated fact table ?
any better Idea?

a.hajjat wrote:
SKU QTY1 QTY2 QTY3 QTY1_due_Date QTY2_due_Date QTY3_due_Date
ABC 50 70 40 5/15 6/12 7/02
is the best way to have this aggregation with open orders is to have View based on open orders fact
instead of ETL to build other calculated fact table ?

Well that is a big question... it actually depends a lot of the end use that the tables will have... the reporting tool over those tables should be able to handle that, without a view, but then you will have to analyse its behaviour and start to fine tune the tables, and then and only then if the reporting tool can't handle it or the performance is too bad even with tuning you should consider aggregating it, or else you will be aggregating tables every time there a new need in the business.

I have tow aproaches in my mind to solve this Requirment but not sure which one is more efficent
1- View
2- Rank Partitioning in ETL (adding row_rank Field) to fact table
which one sound better for you?
Thanks guys
Alex