Dimensional Hierarchy – Best Way to Capture Historical Changes

The standard way is to use a History Table pattern.

If you have a table like this:

create table foo (
  int foo_id primary key,
  name varchar(x) not null
);

You would have a corresponding table

create table foo_history (
  int foo_id,
  int version,
  name varchar(x),
  inserted_at timestamp not null default current_timestamp,
  operation_code char(1) not null, -- D=deleted, U=updated [, I=inserted]
  by_username varchar(x) not null,

  primary key (foo_id, version)
);

The data in this table would be filled by triggers on foo.

If you use Java, you could use Hibernate Envers to automate this for you.

My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy.

Here's an example:

CREATE TABLE dimGeography (
    id
    parent_id
    geographyType
    geographyCode
    geographyName
    PRIMARY KEY (id)
);

CREATE TABLE factDemographics (
    date
    geography_id
    monthlyIncome
    workingPopulationAge
    numberOfPeople
    PRIMARY KEY (date, geography_id)
    FOREIGN KEY (geography_id) REFERENCES dimGeography (id)
);

The advantages of this approach are:

• you're free to add or remove levels as you like over time without changing the data model
• you can connect facts (demographics) to each level
• no duplicate measure columns, i.e. just one column called "number of people"
• future-proof: some countries or regions may use different types of levels - for instance, not all countries use "state".

The disadvantages that I can think of are:

• you may have to redesign your existing (?) dimension or fact table
• your BI tool may not be able to handle parent-child dimensions
• more complex SQL queries when aggregating the facts on higher levels

I haven't the slightest idea if this is the "correct" solution according to Kimball, Inmon, etc. Instead, I would urge you to see if it applies to your particular needs. ;)