4

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current).

As our daily load runs and I see the changes flowing in, I sometimes worry that it's creating new records when it shouldn't.

What kind of scripts can be run to compare each customer over it's respective history and make sure it's changing appropriately?

Also, and I think I'm touching on the gaps and islands problem, what strategies are there for verifying that the start dates and end dates are all contiguous?

I want to feel confident that the table is reporting correct data day to day without becoming a beast due to some null check causing a ton of records to be created each day.

Thanks for any help you can offer.

2
  • Can you expand on how your fact table is linked to your dimension surrogate key? How do you perform that lookup?
    – Tom V
    Sep 8, 2015 at 13:58
  • Most of the validation we are doing in this regards performs record hashing on key fields in both source and target and then comparing the hashes for matches. The hashes are compared based on unique keys and then a report is generated identifying the number of issues to be inspected. We also do a sanity check without the hash and do a value to value match in similar fashion. I'll dig into the procs a bit to see if there is anything I can share to get you going. Sep 8, 2015 at 14:43

1 Answer 1

4
+150

Checking whether the dates are contiguous

You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to check on values in the next or previous row.

So for instance, you could set up a window function which is partitioned by your business key/surrogate durable key (I'll just use the term durable key from here on out), and ordered by your start date (ascending). You could then use LEAD to check that the start date on the next row was equal to the end date + 1 on the current row. You can't use window functions in the WHERE clause, so perhaps you could create a case statement which flags up those rows which are for the same customer (i.e. have the same durable key), but where the next row's StartDate isn't equal this row's EndDate + 1. You could do the same in reverse to flag up the following row as well. An outer query could then only select the rows which need investigation.

Checking the SCD is working correctly

To some extent the checks you can do are going to depend on your particular situation, but I'll try to tackle this from a generic standpoint that might be helpful to you (if you still need help, 7 months later!), and which might help other people having similar issues. If you return and give more specific detail, let me know and I'll be happy to have a read through and improve my answer if possible.

One option might be to run some queries which select a few COUNT values on different aspects of the dimension. So for instance, you could group by the durable key, and get a count of how many rows exist for that durable key. If you don't have an understanding of how many changes are reasonable, you could take an average count and then look for customers which are over that average (or over that average by some percentage or set amount).

From there, having a dig through the data belonging to a handful of suspicious-looking customers might turn up obvious issues which are worth further investigation. Without specifics I don't know what issues you might be running into, but as an example lets say you notice a customer which has hundreds of rows, with a value which is changing back and forth between two values. From there, you could write up some queries to look for that problem in the rest of the table - for instance, coming up with a query which would let you look at the number of rows for each customer vs. the number of unique rows for each customer (looking only at the actual attribute columns, so not the dates or any other metadata columns you might have).

Checking on whether the customer is changing correctly over time will depend on what historic data you have held in your source(s) or staging area. If you do have historic data in one or both of those places, you might be able to compare the values with your dimension rows either directly by date (if said historic data includes dates), or perhaps by the order those changes happened in.


On a slight tangent, but worth mentioning: I'm guessing from the fact that you're not sure whether this dimension is behaving correctly that this is something you've perhaps inherited from another developer? If you're really worried about the size of this dimension and if you don't know the full reason for its current design, it might be worth checking whether all of the Type II SCD fields really should be Type II SCD.

Some data warehouse developers will tend to presume SCD II by default, which can really be problematic. Aside from the space issues, as the link goes into, the use of SCD II can lead to some unexpected results for the people consuming the data. This might not be relevant in your case (the SCD II attributes might be well-thought-out and suitable for your users' requirements), but if it is relevant it might be possible to alleviate your concern about the size of the dimension and also remove the need to do those data quality checks.

2
  • Thank you for the in depth response. As it turns out - with this kind of project - I will always be needing help. At least I can say 7 months later we have something in production being used. I think you nailed it right on the head in the fact that we had too many fields being tracked for SCD II. One happened to be membership in a particular promotion/club, which could trigger 50k changes over night. As it stands now, I had redesigned things a bit, and broke out the quickly changing records into a factless fact table and track certain attributes through that. Sep 9, 2015 at 18:00
  • I just ran a query now and out of the 3.3m distinct customers, there are only 400k changed records, which over the course of 7 months I feel is appropriate for our business. I'm going to try the methods you outlined for validating the data - I never thought to use lag/lead but it seems like it would do the trick. I'm going to mark this as the answer, though the bounty is not mine to award. Thanks! Sep 9, 2015 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.