Order / Order Item: Accumulating Snapshot vs. transaktional

View previous topic View next topic Go down

Order / Order Item: Accumulating Snapshot vs. transaktional

Post  Markus627 on Mon Aug 29, 2011 9:30 am

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

Markus627

Posts : 14
Join date : 2011-06-20

View user profile

Back to top Go down

Re: Order / Order Item: Accumulating Snapshot vs. transaktional

Post  ngalemmo on Mon Aug 29, 2011 10:59 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Order / Order Item: Accumulating Snapshot vs. transaktional

Post  Markus627 on Tue Aug 30, 2011 4:04 am

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.

Markus627

Posts : 14
Join date : 2011-06-20

View user profile

Back to top Go down

Re: Order / Order Item: Accumulating Snapshot vs. transaktional

Post  ngalemmo on Tue Aug 30, 2011 12:01 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Order / Order Item: Accumulating Snapshot vs. transaktional

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