Sale plan, should it be fact or dimension table?

[DreamingFighter]

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?

[LAndrews]

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/

[hkandpal]

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

[DreamingFighter]

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

[hkandpal]

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

[ngalemmo]

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.

[DreamingFighter]

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.

[ngalemmo]

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

[DreamingFighter]

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

[ngalemmo]

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.

[Sponsored content]