Slowly Changing Dimension Vs Fact

Question

I am rookie to DW. I have a Customer table with basic columns that rarely change like Name, JoinedOn etc. And another set of Columns that can change over time like "Status","CustomerType","PublishStatus","BusinessStatus","CurrentOwner" etc. At the moment there is no history. In the DW I would like to track when the following columns change "Status","CustomerType","PublishStatus","BusinessStatus","CurrentOwner". I feel it would be better if I create another table to track these, the table will have the following columns:

"CustomerId", "Status","CustomerType","PublishStatus","BusinessStatus","CurrentOwner", "ExpiredOn","IsCurrent"

Is this the right approach? And if yes then is this new table a fact or a slowly changing dimension? I would like to run queries like when did the CustomerType change from A to B? When was it Published? When the BusinessStatus changed who was the owner?

Answer 1

Which way of modeling the customers is better really depends on how are you going to use the corresponding dimension. If you would want, for example, to summarize some sales associated with the "Customer" dimension by "CustomerType" at the time of the sale, you could do it only if you keep the historical details as a part of slowly changing dimension.

You probably can run a lot of customer reports right on that table that represents a slowly changing "Customer" dimension. But if the number of your customers get into the millions, you'd be better off creating a separate fact table (or tables) for customer status changes.

So, to summarize: start off with a slowly changing dimension. If the number of customers grows too large and reports on customer statust changes become too slow, add a fact table for them and don't worry about duplicating data.