Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Purchase orders Fact Table Design

4 posters

Go down

Purchase orders Fact Table Design Empty Purchase orders Fact Table Design

Post  a.hajjat Mon Apr 22, 2013 10:50 pm

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

a.hajjat

Posts : 4
Join date : 2013-04-22

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  ngalemmo Tue Apr 23, 2013 4:38 am

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  BoxesAndLines Tue Apr 23, 2013 9:04 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  JoaoLains Tue Apr 23, 2013 10:06 am

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:

Purchase orders Fact Table Design Model10

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
JoaoLains
JoaoLains

Posts : 3
Join date : 2012-07-10
Age : 40
Location : Portugal

http://www.bimaven.com/main_page.asp?lang=uk

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  a.hajjat Tue Apr 23, 2013 10:35 am

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

Posts : 4
Join date : 2013-04-22

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  JoaoLains Tue Apr 23, 2013 10:45 am

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.
JoaoLains
JoaoLains

Posts : 3
Join date : 2012-07-10
Age : 40
Location : Portugal

http://www.bimaven.com/main_page.asp?lang=uk

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  a.hajjat Tue Apr 23, 2013 10:29 pm

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

a.hajjat

Posts : 4
Join date : 2013-04-22

Back to top Go down

Purchase orders Fact Table Design Empty Re: Purchase orders Fact Table Design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum