Purchase orders Fact Table Design

View previous topic View next topic Go down

Purchase orders Fact Table Design

Post  a.hajjat on 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

View user profile

Back to top Go down

Re: Purchase orders Fact Table Design

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Purchase orders Fact Table Design

Post  BoxesAndLines on Tue Apr 23, 2013 9:04 am

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

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

View user profile

Back to top Go down

Re: Purchase orders Fact Table Design

Post  JoaoLains on 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:



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

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

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

Back to top Go down

Re: Purchase orders Fact Table Design

Post  a.hajjat on 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

View user profile

Back to top Go down

Re: Purchase orders Fact Table Design

Post  JoaoLains on 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.
avatar
JoaoLains

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

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

Back to top Go down

Re: Purchase orders Fact Table Design

Post  a.hajjat on 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

View user profile

Back to top Go down

Re: Purchase orders Fact Table Design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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