Sale plan, should it be fact or dimension table?

View previous topic View next topic Go down

Sale plan, should it be fact or dimension table?

Post  DreamingFighter on Mon Oct 22, 2012 4:40 am

Hi all,
I just encounter a headache issue about modeling sale data warehouse. That is, normally I would put Sale Plan data fact table (because the size, composite key, number attribute and all), but in this case in operation system, power user can change sale plan of a saler (happen every month), and that change should be tracked.

In this case, I'm thinking about creating a series of dimension table, each table have sale plan data for one month (the plan is one-month basis).

What's your suggestion on this case?
avatar
DreamingFighter

Posts : 6
Join date : 2012-10-22

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  LAndrews on Mon Oct 22, 2012 8:22 pm


Sales plan data should still be its own fact.

Each time the plan is updated, effectively a new "version" of the plan is created.

The fact table will have effective dates in order to retain versions, for each change the delta is captured in the fact.

There are a number of places to read up on this approach, look for "budget" or "plan" designs.

I think Christopher Adamson has a good section in his book on this (related to P&L if I recall).

http://www.amazon.com/exec/obidos/ASIN/0471777099/ref=nosim/datawarehousec0e/


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  hkandpal on Tue Oct 23, 2012 9:14 am

Hi,

how many rows are there in the plan table ? WHat would be the frequency of changes in the data will it be hourly, daily, weekly....



thanks

HImanshu

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  DreamingFighter on Wed Oct 24, 2012 6:04 am

The table have around 20 millions rows every month, and would be inserted every 20th.
The frequency is around 300k per month.
avatar
DreamingFighter

Posts : 6
Join date : 2012-10-22

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  hkandpal on Wed Oct 24, 2012 1:30 pm

Hi ,

looking at the size it looks big, is this table referening any dimension table, and will there be a case where you may not receive any update/changes for a particular sale plan.

thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  ngalemmo on Wed Oct 24, 2012 3:55 pm

It is a fact table as LAndrews has indicated. Size has nothing to do with it.

The design question should be how to represent the plan. Should the facts be deltas or snapshots?

Assuming your dimension includes the period the plan is for, you could consider a delta table. In such a table, you only load changes and the measures reflect the amount of change (i.e. net change). You would include a dimension that identifies when the change was applied. A particular plan for a particular period would be the sum of the measures. Delta facts also allow you restate the plan for any time in the past by filtering on the change applied dates.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  DreamingFighter on Wed Oct 24, 2012 9:59 pm

Thank you, I would like to clarify the problem and solution, please correct me if I'm wrong:

1. I have a plan table with these fields: start_period (first day of month), end_period (last day of month), staff_code, product_code, amount, is_active (1 = active, 0 = disable). This table have 20 millions rows and can be updated (by updating the is_active attribute and insert a new row) with frequency is 300k record per month. I want to model this table.

2. My solution (after suggestion from this forum) is to build a fact table with these fields: bi_prd_id (start_period in raw table), bi_end_prd_id, bi_staff_id, bi_product_id, amount, updated_time (insert time), is_active. Every time a plan is update, I update is_active column in old record and insert a new one.
There is another solution for this: I don't update old record but use updated_time column to identify newest record, but that solution cost too much resource on querying time so I would not do this.
avatar
DreamingFighter

Posts : 6
Join date : 2012-10-22

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  ngalemmo on Thu Oct 25, 2012 5:45 pm

You are describing an accumulating snapshot. That's fine. It's one way to do it.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  DreamingFighter on Fri Oct 26, 2012 4:44 am

Is there another? I really don't wanna update on my fact table. The way I'm describe above is exactly like the way you treat a type 2 dimension
avatar
DreamingFighter

Posts : 6
Join date : 2012-10-22

View user profile

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

Post  ngalemmo on Fri Oct 26, 2012 11:46 am

As I mentioned earlier, you can load net changes. It involves some manipulation in the ETL, but the result is insert only into the fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Sale plan, should it be fact or dimension table?

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