Status - SCD Type 2 or dimension on the fact

[jryan]

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

[BoxesAndLines]

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.

[jryan]

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.

[M. Khan]

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.

[Sponsored content]