Status - SCD Type 2 or dimension on the fact

View previous topic View next topic Go down

Status - SCD Type 2 or dimension on the fact

Post  jryan on Mon Mar 25, 2013 12:02 pm

Hi

I have a fact table that models purchase orders by Date, Product, Supplier, Department etc. The fact table is at the line level. Fairly simple.

I have a dimension included called Purchase Order Details. This holds the PO Header Number, the PO Line and the PO Status. In a sense its a junk dim. I could have split these attributes out onto the fact, but I chose to put them together as they're so related.

I now need to track the PO status along time. Is it normal, for this sort of status field, to track the change in status as a Type 2 SCD, or is it better that I put this directly on the fact?

Thank you

jryan

Posts : 33
Join date : 2010-09-27

View user profile

Back to top Go down

Re: Status - SCD Type 2 or dimension on the fact

Post  BoxesAndLines on Mon Mar 25, 2013 8:23 pm

Statuses make good dimensions. Most often PO statuses are fixed by the time you load them up unless you are doing pipeline analysis. As such, I'd make it a type 1. Also, you junk dim isn't very junky. That is, the distinct combination of values will be too high since you are including PO Header Number. Here's what I would do. Kill the junk dimension. Create a status dimension. Put the PO Header and line number on the fact as degenerate dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Status - SCD Type 2 or dimension on the fact

Post  jryan on Tue Mar 26, 2013 4:54 am

Thanks for the reply.

I have a snapshot fact that will be interacting with these dims, so yes you'll be able to see a PO change from status A to status B, whether its a type 2 or its own dimension. Sp that's one vote for a seperate Status dimension - that's what I'll go for.

jryan

Posts : 33
Join date : 2010-09-27

View user profile

Back to top Go down

Re: Status - SCD Type 2 or dimension on the fact

Post  M. Khan on Thu Mar 28, 2013 10:27 pm

What you said snapshot, do you meant accummulating fact ..... where you have a seperate datetime column for each status to indicate when PO moves from one status to next, e.g. Open_Datetime, InProcess_Datetime, Closed_Datetime for three statuses Open, Inprocess and close. The Open_Datetime column is populated when PO is open, and when PO is moved to in process then InProcess_Datetime is updated, and finally Closed_Datetimeis updated when request is comleted. It is very simple example that still requires PO Status dimension.

M. Khan

Posts : 11
Join date : 2012-07-24

View user profile

Back to top Go down

Re: Status - SCD Type 2 or dimension on the fact

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