1

How often dimensions change in a slowly changing scenario?

I'm looking at SQL Server Temporal tables for Slowly Changing Dimensions (Type 2). Some of the dimension tables update quite frequently (daily! In that sense, they are not truly 'slowly changing'. They are not truly dimensions in strict sense).

Is it common for a dimension to change daily?

RaviLobo
  • 447
  • 2
  • 10
  • 29
  • I think you see the problem yourself, when you write They are not truly dimensions in strict sense. Dimensions are slow when business processes do care about previous values and the history of the change. But if they change daily, it is definitely not slow, and probably not dimension at all. – Michael Entin Feb 16 '20 at 21:16
  • @MichaelEntin Thank you. I thought the same thing. – RaviLobo Feb 17 '20 at 12:23

1 Answers1

2

"Speed" of dimension change should be considered relatively to the speed of change in fact tables.

If a dimension changes daily, but fact tables change every minute, it might be fine.

If a dimension changes daily and fact tables change daily, most likely it's a design mistake. What you currently have is not normal and you need to fix the design.

Often the root of the problem is a confusion between dimension and fact - if a designer models facts as dimensional attributes, the dimension will change too fast. For example, if you add Price to Product dimension, and prices change daily, you will convert "slow change" into a "rapid change". A solution here is to separate prices into a fact table that contains price history.

In less obvious cases, a common solution is to separate fast-changing attributes into a "mini-dimension". For example, if some customer attributes change much faster than the rest, split the table into "Customer" and "Customer Profile" dimensions. Such "mini-dimensions" are also called "Type 4" change in the dimensional design.

You can see an example here:

SCD Type 4, a Solution for Rapidly Changing Dimension

RADO
  • 7,733
  • 3
  • 19
  • 33