Status - SCD Type 2 or dimension on the fact

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

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.

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.

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.