Slowly Changing Dimensions

Posts so far:

Data Warehouse Design Considerations

Getting Started - Picking a Dataset

Using an External table 

A Data Warehouse is a central repository of data that should be acting as the single source of truth for Business intelligence reporting throughout an organisation. In some places the Enterprise Data Warehouse is often the only place that some data sources have been integrated together and might be the easiest place for management to obtain the information they need to drive the business. 

One of the key choices the Warehouse and Business teams need to make is how are they going to cope with changing dimensional data. If Betty has an address of 28 Street X for orders before 201910 and then moves to 5 Street Y, you would want the orders in the relevant time frames associated with the correct address at the time, well probably. but what about date of birth? If this had been changed from 1972 to 1992 then this would make a difference to metrics where you have been aggregating by age ranges, would you want your facts to be summed by incorrect data just because that is how it was at the time or would you want it reflecting the new and most accurate data? 

There are different "types" of ways to cater for Slowly Changing Dimensions, SCD0, SCD1, SCD2 etc, they have different benefits and personally I have found using SCD2 processing with business modelling generating a latest view of the dimension a very flexible way to cater for this. Firstly lets discuss the different types and then I will explain the the use of SCD2 for the above example. 

Types of SCD processing:

• Type 0 – No change or fixed Dimension

• Dimension never changes, so Betty will always have been born in 1972 and live at the first address she reported. 

• Type 1 – Latest Only Dimension

• Historical Data is removed or update to reflect the latest, so Betty is always born in 1992 and lives at the last submitted address.

• Type 2 – Time framed dimension

• Track changes with to and from effective dates and often using current flag or latest flags with the active dates. 

There are other types that are frankly not worth going into. You can read more about them here. 

Why I rely on SCD2 processing:

Now I said above that I currently favour relying on SCD2 processing. This type has the advantage of allowing you to join on a surrogate key or time period (depending on what processing you are using) or pick the current version which allows you to get the Business Intelligence team greater flexibility when generating their reports and dash-boards, though it does come with risks. 

Source Data in the Data Lake from source System

As the data has come in we have 3 different rows in the slowly changing dimension "data lake" table. For simplicity I have used changes over a small time frame but the from and to dates could be years apart. Betty was a new customer in 201809, changed her address in 201811 and then her date of birth in 201901, note the last entry is shown as current and as such has a null to date. 

Latest Customer Dimension

From the source data we can create a dimension of the latest data, obviously this can change each month to reflect the latest data available and as such reporting would update accordingly. 

Customer Dimension by period

There are several techniques you can use to effectively generate the above, obviously you can use surrogate keys in the SCD2 table and use these in the fact table in place of the customer id. Equally an option shown here is to store the customer data as at every year month, if the table is partitioned on year month the joins can be incredibly efficient and performant reporting can take place. 

Simple Orders Fact

Some sample Fact Data for some order by Betty, linking to the customer dimension on the customer id and either the year month or the current flag. 

Using the above data it is then relatively simple to produce a report like the above. the problem with this report is that we know the age is incorrect in the first 2 entries, so lets switch and use the latest dimension. 

Now the issue becomes that we know that not all orders were placed at 5 Street Y, so instead we have to make sure our reporting team, through their business knowledge and use of the data dictionary, use the right fields for the right dimension. Note that in the modelling tool the age here would be called latest provided age or something like that. 

So here we have our correct report, all using SCD2 processing but modelling it in 2 different ways, once using the by period data and once using the latest. 

Design considerations - Enterprise data warehouse

I have been absent over the last few months for several different reason. Primarily because I knew there was a chance of a promotion at work. Where I work the jobs are advertised externally and internally to ensure the best person is found for the job. Given current COVID issues there are a lot of people looking for a job so there were over 30 high quality candidates and 7 reached an interview, including myself.

The role in question is a Lead Data Warehouse and BI Developer. Currently I am a Senior ETL Developer, the lead role works as a lead for a Data Feed and Subject Area within our Enterprise Data Warehouse. Happy to say that I was offered the role, once of the questions I asked to address and answer in the presentation stage was Top 5 Design Considerations when introducing a new data source into an Enterprise Data Warehouse. 

Any questions or comments then feel free to post, please don't try and nick this presentation and my notes as although I have juggled things around for this post it is still my work and it took a lot of thought in the first place and to get it into this post. 

 

Slide 2:

I thought the best way to address this question would be to start with my simplified view of what an Enterprise Data Warehouse is. The overview shown presents a simplistic view of multiple source systems, for example, transactional data or files such as from the ONS, finance systems etc., flowing into a landing or “data lake” area. From here an ETL engine will perform various transformations that will result in the production of data in a series of dimension and fact tables in star schemas. These dimension and fact tables are then presented to end users through a business intelligence tool, for example Oracle Analytics or ClickView.

So my first design consideration would be…

Slide 3:

Business or user reporting requirements and SLA’s. For a successful design we need to consider how are users going to access the data, what Facts and Dimensions do they need but also importantly can your reporting system cope with the demands. If you are unable to develop the dashboards or pixel perfect reports they want or they will take too long with the current set up then this needs to be highlighted early.

Slide 4:

Data profiling – This is the systematic analysis of the content of a data source, looking at the size of all the data and checking constraints and keys and whether the data can meet the high level goals of the data warehouse, such as is the data of high enough quality that we can actually use it?

An entity relationship diagram would be useful help identify relationships and other design considerations in the data profiling include: are you getting full extracts or delta’s? How does the data change?

Lastly, we also need to think about how this data is going to get into the Warehouse and whether you have the resources and scheduling available to handle the processing and volume of data in question with the other data sources that already exist.

Slide 5:

Can we integrate the data into our existing ecosystem. Some design issues that come to mind are:

1. Do we have an ETL tool that can handle the required transformations

2. Do we have the DB resources to perform the transformations in a performant way, at the desired frequency and without interrupting the existing processes.

Then my 4^th design factor to consider is integration of the modelling:

Can it be integrated into the existing repository e.g. can a tool such as the RPD work with integrating this model.

What Dimensions and Facts are needed within this model? What star schema’s are required? This relates back to point 1 on business requirements but also what conformed dimensions are already available within existing processes in the data warehouse and how will this fit together. And what hierarchies are required.

The previous points can all be linked together through the production of a conformance or bus matrix to visually represent the planned fact and dimensions along with the conformity.

Slide 6:

My final design consideration is security and data governance. 

When on-boarding a new dataset we need to consider, especially with highly confidential patient data, do we have robust enough security in place for the data transfers, data storage and data presentation.

You also need to consider which data within the dataset you require, with GDPR it is increasingly important that you only have and retain the data that you actually need. Patient names for example may need to be pseudonymised or even anonymised. If any new sensitive data is involved you may need to design the requirements around the guidelines of the information governance team.

Creating a year month dimension

I am kind of going to build my ETL from the ground up as a learning experience. As such I don't really have a specification and I am creating things on the fly. I decided to start by creating a year month dimension. 

I started as in all likelihood, no matter what data set I end up with, a dimension for year and month is bound to be useful. In my professional experience this is always required, everyone wants their data split by month, year or year to date etc. I don't need many columns here and it is something I can generating using just code. 

Next step after creating this will be to create the initial parts of the ETL. 1) The ETL engine to process the results of the view into a staging table and from there into a dimension table. Then 2) to create a control environment to ensure that the data I am putting in looks right, after all data without data quality is worthless.