Floemdata
Data & ETL

Slowly Changing Dimensions: Type 1 vs Type 2

When a customer's region changes, past reports must not shift. Type 1 versus Type 2 for slowly changing dimensions, how to choose, and Floemdata's status.

Floemdata · · 5 min read
Two three-drawer cabinets: the left holds one dot and a dashed ghost ring, the right has a dot in every drawer

A customer moved from the Aegean region to the Marmara region. If reports read the customer's region from master data, last year's Aegean sales shrink overnight and Marmara's grow. Nobody changed the sales; the customer's current address was applied to the past. The regions and figures in this post are fictional.

The problem: dimensions are slow, but they change

Dimension fields such as customer, product group and sales representative do not change often, but they do change. Which value a report should show for a past period is a design decision; if nobody decides, whichever value was loaded last wins.

Type 1: overwrite

  • What it does: deletes the old value and writes the new one. Past reports show the new value too.
  • When it fits: when the change is a correction (a misspelled name, a wrong region) and you want the past corrected as well.
  • The cost: past figures shift; the report of an earlier period comes out different today.

Type 2: open a new row

  • What it does: on a change it closes the old record and opens a new row with the new value. Each row has a validity start and end date.
  • When it fits: when the change is a real event (the customer moved, the representative changed) and past sales should appear with the value of that day.
  • The cost: attaching a sale to the right row needs a join that looks at the document date; the dimension table grows and one customer has several rows.

How to choose

  • Decide field by field: a name correction is Type 1, region and representative are often Type 2.
  • Write the choice and the reason next to each field, so the answer is ready when someone asks about a figure later.
  • With Type 2, a wrongly built join can match a sale to two rows and duplicate it; test that validity ranges do not overlap.

Where Floemdata stands

In the SAP Business One extraction catalogue in Floemdata's product documents, business partner master data (OCRD, with its region and group fields) is taken by a daily full reload and stored in Bronze under its natural key plus the load date; so in the raw layer, a customer's states on different days can remain. We have not verified in the documents that Silver or Gold has a Type 2 customer dimension with validity dates, so this post does not claim whether past reports show the customer's current region or the region of that day.

To start, pick the dimension you use most (for example customer region), count how many customers changed in the last year, and write down which value the report should show. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides