I have a retail sales system and want to create a data warehouse using Dimensional Modeling by ralph kimball.
I have a simple order fact table that measures order quantity and order dollar amount. From what I read in the book and internet, the order number is a degenerate dimension that sits on the fact table.
The order fact table then has a status that initially I thought it's a flow, so accumulated snapshot fact table is come to my mind. It was all fine until I realize the status is actually not a flow, it's a label so the order can change status from 'a' to 'b' then to 'a' again. My case is even worse because the order fact table is now has 3 types of status that its changes need to be tracked. So I think accumulated snapshot fact table is not working here.
My attempt is I create 4 fact tables, order order status a order status b order status c.
Every new order creates a row in order table as well as one row for initial status in each order status. Then every change to the status is done by creating a new row in order status.
Since the order status table is related to the order table, I need these 3 order status tables to have reference to the order table. How to do it? is using the same order number (degenerate dimension) suffice? I think of conformed dimension also can solve this, but these dimension rows will grow as much as order table. Any thought on this?
