Slowly altering dimension (SCD) is a knowledge warehousing idea coined by the wonderful Ralph Kimball. The SCD idea offers with shifting a selected set of knowledge from one state to a different. Think about a human assets (HR) system having an Worker desk. As the next picture reveals, Stephen Jiang is a Gross sales Supervisor having ten gross sales representatives in his staff:
At present, Stephen Jiang received his promotion to the Vice President of Gross sales position, so his staff has grown in dimension from 10 to 17. Stephen is similar particular person, however his position is now modified, as proven within the following picture:
One other instance is when a buyer’s handle adjustments in a gross sales system. Once more, the shopper is similar, however their handle is now completely different. From a knowledge warehousing standpoint, now we have completely different choices to cope with the information relying on the enterprise necessities, main us to several types of SDCs. It’s essential to notice that the information adjustments within the transactional supply programs (in our examples, the HR system or a gross sales system). We transfer and rework the information from the transactional programs through ETL (Extract, Transform, and Load) processes and land it in a knowledge warehouse, the place the SCD idea kicks in. SCD is about how adjustments within the supply programs replicate the information within the information warehouse. These sorts of adjustments within the supply system don’t occur fairly often therefore the time period slowly altering. Many SCD varieties have been developed through the years, which is out of the scope of this submit, however to your reference, we cowl the primary three varieties as follows.
SCD sort zero (SCD 0)
With this sort of SCD, we ignore all adjustments in a dimension. So, when an individual’s residential handle adjustments within the supply system (an HR system, in our instance), we don’t change the touchdown dimension in our information warehouse. In different phrases, we ignore the adjustments throughout the information supply. SCD 0 is additionally known as fastened dimensions.
SCD sort 1 (SCD 1)
With an SCD 1 sort, we overwrite the outdated information with the brand new. A wonderful instance of an SCD 1 sort is when the enterprise doesn’t want the shopper’s outdated handle and solely must hold the shopper’s present handle.
SCD sort 2 (SCD 2)
With this sort of SCD, we hold the historical past of knowledge adjustments within the information warehouse when the enterprise must hold the outdated and present information. In an SCD 2 state of affairs, we have to preserve the historic information, so we insert a brand new row of knowledge into the information warehouse at any time when a transactional system adjustments. A change within the transactional system is likely one of the following:
- Insertion: When a brand new row inserted into the desk
- Updating: When an current row of knowledge is up to date with new information
- Deletion: When a row of knowledge is faraway from the desk
Let’s proceed with our earlier instance of a Human Useful resource system and the Worker desk. Inserting a brand new row of knowledge into the Worker dimension within the information warehouse for each change throughout the supply system causes information duplications within the Worker dimensions within the information warehouse. Subsequently we can’t use the EmployeeKey column as the first key of the dimension. Therefore, we have to introduce a brand new set of columns to ensure the distinctiveness of each row of the information, as follows:
- A brand new key column that ensures rows’ uniqueness within the Worker dimension. This new key column is solely an index representing every row of knowledge saved in a knowledge warehouse dimension. The brand new secret is a so-called surrogate key. Whereas the Surrogate Key ensures every row within the dimension is exclusive, we nonetheless want to keep up the supply system’s main key. By definition, the supply system’s main keys at the moment are referred to as enterprise keys or alternate keys within the information warehousing world.
- A Begin Date and an Finish Date column symbolize the timeframe throughout which a row of knowledge is in its present state.
- One other column reveals the standing of every row of knowledge.
SCD 2 is probably the most frequent sort of SCD. After we create the required columns
Let’s revisit our state of affairs when Stephen Jiang was promoted from Gross sales Supervisor to Vice President of Gross sales. The next screenshot reveals the information within the Worker dimensions within the information warehouse earlier than Stephen received the promotion:
The EmployeeKey column is the Surrogate Key of the dimension, and the EmployeeBusinessKey column is the Enterprise Key (the first key of the shopper within the supply system); the Begin Date column reveals the date Stephen Jiang began his job as North American Gross sales Supervisor, the Finish Date column has been left clean (null), and the Standing column reveals Present. Now, let’s take a look on the information after Stephen will get the promotion, which is illustrated within the following screenshot:
Because the above picture reveals, Stephan Jiang began his new position as Vice President of Gross sales on 13/10/2012 and completed his job as North American Gross sales Supervisor on 12/10/2012. So, the information is reworked whereas shifting from the supply system into the information warehouse. As you see, dealing with SCDs is likely one of the most vital duties within the ETL processes.
Let’s see what SCD 2 means on the subject of information modeling in Energy BI. The primary query is: Can we implement SCD 2 instantly in Energy BI Desktop with out having a knowledge warehouse? To reply this query, we should do not forget that we all the time put together the information earlier than loading it into the mannequin. Then again, we create a semantic layer when constructing a knowledge mannequin in Energy BI. In a earlier submit, I defined the completely different elements of a BI resolution, together with the ETL and the semantic layer. However I repeat it right here. In a Energy BI resolution, we deal with the ETL processes utilizing Energy Question, and the information mannequin is the semantic layer. The semantic layer, by definition, is a view of the supply information (often a knowledge warehouse), optimised for reporting and analytical functions. The semantic layer is to not exchange the information warehouse and isn’t one other model of the information warehouse both. So the reply is that we can’t implement the SCD 2 performance purely in Energy BI. We have to both have a knowledge warehouse maintaining the historic information, or the transactional system has a mechanism to help sustaining the historic information, reminiscent of a temporal mechanism. A temporal mechanism is a function that some relational database administration programs reminiscent of SQL Server provide to offer details about the information stored in a desk at any time as an alternative of maintaining the present information solely. To study extra about temporal tables in SQL Server, examine this out.
After we load the information into the information mannequin in Energy BI Desktop, now we have all present and historic information within the dimension tables. Subsequently, now we have to watch out when coping with SCDs. For example, the next screenshot reveals reseller gross sales for workers:
At a primary look, the numbers appear to be appropriate. Nicely, they might be proper; they might be fallacious. It relies on what the enterprise expects to see on a report. Take a look at Picture 4, which reveals Stephen’s adjustments. Stephen had some gross sales values when he was a North American Gross sales Supervisor (EmployeeKey 272). However after his promotion (EmployeeKey 277), he isn’t promoting anymore. We didn’t take into account SCD once we created the previous desk, which implies we take into account Stephen’s gross sales values (EmployeeKey 272). However is that this what the enterprise requires? Does the enterprise count on to see all workers’ gross sales with out contemplating their standing? For extra readability, let’s add the Standing column to the desk.
What if the enterprise must solely present gross sales values just for workers when their standing is Present? In that case, we must issue the SCD into the equation and filter out Stephen’s gross sales values. Relying on the enterprise necessities, we would want so as to add the Standing column as a filter within the visualizations, whereas in different instances, we would want to switch the measures by including the Begin Date, Finish Date, and Standing columns to filter the outcomes. The next screenshot reveals the outcomes once we use visible filters to take out Stephen’s gross sales:
Coping with SCDs is just not all the time so simple as this. Typically, we have to make some adjustments to our information mannequin.
So, do all of the above imply we can’t implement any kinds of SCDs in Energy BI? The reply, as all the time, is “it relies upon.” In some eventualities, we will implement an answer just like the SCD 1 performance, which I clarify in one other weblog submit. However we’re out of luck in implementing the SCD 2 performance purely in Energy BI.
Have you ever used SCDs in Energy BI, I’m curious to know in regards to the challenges you confronted. So please share you ideas within the feedback part beneath.
