Order / Order Item: Accumulating Snapshot vs. transaktional

Hi everybody

1.
Which is the better approach to design a workflow (i. e. for a production process)? An accumulation snapshot facttable or a transaktional facttable?
I think the answer is: it depends. But it depends on what? I can imagine both options but i quite don't know what are the dis/advantages of each approach.

Other questions which are linked to this topic:
2.
Using a accumulation SS table, how is it possible to change the model when a new production step is is added?

3.
I have read that it's not common to have an OrderDimension and a OrderFacttable.
Does this mean that it's better to include the information which are stored in the OrderDimension in the OrderFacttable or to create other dimensions (maybe by adding attributes to a junk dimension).


Thanks for your information.


Markus

The difference between an accumulating snapshot and a transactional fact table is the transactional fact can easily calculate the magnitude of change over time across all dimensions. This is very difficult to do in an accumulating snapshot, particularly if dimensional changes occur.

Queries to report a specific state are simpler in a snapshot as a transactional fact requires summing rows up to the desired point in time. The transactional fact also tends to be larger. So, if magnitude of change is not a requirement, a snapshot is a better choice.

On #2, why would you need to change the model?

On #3, usually attributes of the order are assumed as dimension in the order fact table. Customer, product, dates are obvious. Left over attributes, like status, usually wind up in junk. Order number and line are usually degenerate dimensions.

Thanks for your reply.

1.
Okay, I think I understand what you mean. If a dataset of an accumulation snapshot isn't completed and a SCD type II is changed I can not link the current (new) Dimension FK to the existing but not completed accumulation snapshot dataset (easily).

2.
I have to change the model when a new production step (status/station) is added or removed.
But I thought about it and I think I found a solution:
I just have to add a new "productionstep date FK" and a timestamp attribute (date FK + timestamp attribute = representation of an production step) to the accumulating snapshot. For orders which are produced before the production step was added, I just have link the FK to an surrogat dataset like "during production of the order the production step wasn't already implemented" and set the timestamp attribute at 00:00-9999.01.01
Further information: I use the timestamp just for the daytime information.

3.
Thanks, that was my suspicion.

I still don't get #2. Are you saying you have separate columns for each production step?

Not a good idea.

Production step should be a dimension of the fact, each step being its own row in the table. Production changes then become a matter of changing data, not the model.