SSIS Junkie : data modelling

Data Warehouse modelling deliberations – foreign keys and unknown members

jamiet — Tue, 16 Apr 2013 21:36:00 GMT

Earlier today I posted the following question on Twitter:

Foreign keys in a data warehouse. Yes or no? This discussion is looming at work, i know my position but am interested in what others think.
(https://twitter.com/jamiet/status/324215986729385986)

Specifically, I wanted to know if people were in favour of creating foreign keys from their fact to to their dimension tables or not.

To say it prompted a good response is a bit of an understatement, 38 responses so far and counting. Here are some of those responses:

It certainly seems to be an emotive subject and its clear (to me) that there’s no correct answer, just lots of opinions. That’s a good thing. The majority of responders appeared to be of the opinion that a data warehouse should contain foreign keys and that is my position too. In this blog post I want to outline why I believe that one should create foreign keys from a fact table to its dimension tables:

Maintain integrity

Of course, this is the main reason why foreign keys exist – to protect the integrity of your data. I see no reason not to use them for this purpose in a data warehouse. The main argument that I see going against is that with a sufficiently robust ETL solution it shouldn’t be necessary. That is true but I would counter with “how do you know that your ETL solution is suitably robust?” I don’t think its possible to anticipate every eventuality that may arise and for that reason I like the safety net that foreign keys provide. I liked Devin Knight’s response here, foreign keys breed confidence.

Communicate business logic

When I join a project that has a database in place the first thing I do is try and understand the data model – to do that I go and look at the foreign keys in that database. Understanding the dependencies between entities is crucial in any data model and the best means of communicating those is via foreign keys. If I encounter a database that is bereft of foreign keys then my heart sinks a little.

Performance

Foreign keys can, in some circumstances, be beneficial in improving query performance. Take a read of A Check and Foreign Key Constraint Improves Query Performance by Sarvesh Singh or Do Foreign Key Constraints Help Performance? by Grant Fritchey.

Code Generation

I am a big fan of generating ETL code where possible and foreign keys can be invaluable when doing so.

As I said there is no correct answer here so if you have any opinions, either agreeing or disagreeing, I look forward to reading your thoughts in the comments below.

Mark Stacey’s comment prompted an interesting digression into talking about surrogate keys for denoting unknown members and this is something I have strong opinions on too:

In short, I don’t like the practice of using “–1” as the surrogate key for an unknown member. My reasoning is simple, I don’t like giving meaning to something that is supposed to be meaningless. How then should we indicate which is the unknown member? I propose a single-row table that includes a column for each dimension table, each with a foreign key to the unknown member in the respective dimension table.

Moreover I don’t like the practice of starting surrogate key counters from 1; the first value available for the integer datatype in SQL Server is –2147483648 so why not start from that?

I discuss both of these issues in much more depth at Considering surrogate keys for Unknown members.

Again if you have any thoughts on these subjects please put them in the comments. If nothing else I find it both fun and educational to debate this stuff.

@Jamiet

Update, Chris Adkin posted a comment below that contained a link to Microsoft's own guidance on building datawarehouses where it is stated:

"Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns."

Chris' take on this:

"So, assuming we are talking about a Kimball DW, there is most definitely value in using foreign key contstraints as this provides a fail safe for the heuristics getting it wrong."

Thanks Chris.

Twitter status id conundrum

jamiet — Sun, 18 Apr 2010 15:05:38 GMT

I have an interest, a slightly perverse one some might say, in using online services and trying to figure out what the underlying (logical) data model is and in this day and age Twitter is one that lends itself very well to scrutiny. Consider this recent tweet of mine:

The URL that enables you to see that tweet is http://twitter.com/jamiet/status/12154647354. We can interpret that URL to mean "a tweet by jamiet with an id of 12154647354" and hence we might further assume that the unique identifier for the tweet is {jamiet,12154647354}.

However, its well-known that Twitter gives each status a unique ID regardless of who tweeted it so we might expect we could reach that tweet just by using a URL of http://twitter.com/status/12154647354 however (at the time of writing) that only redirects to Twitter's homepage. That seems strange to me especially given that we can use Twitter's API to access information about that tweet using only the id of the status. Witness http://api.twitter.com/1/statuses/show/12154647354.xml:

[We can also access a JSON version of that information using http://api.twitter.com/1/statuses/show/12154647354.json]

I'm puzzled as to why a tweet can't be accessed using on the main twitter website using the id alone. Anyone have any suggestions?

@jamiet

Debunking Kimball Effective Dates

jamiet — Sat, 28 Nov 2009 15:59:00 GMT

Those who are familiar with Ralph Kimball’s theories on data warehousing modelling may be familiar with his assertion that a type 2 dimension member record should have a StartDate and an EndDate that defines the effective period of that record. He outlines this approach in his paper Slowly Changing Dimensions, Types 2 & 3 from the October 2008 edition of DMReview:

I recommend adding five additional fields to a dimension that is undergoing Type 2 processing… begin-effective-datetime & end-effective-datetime are full time stamps that that represent the span of time between when the change became effective and when the next change becomes effective. The end-effective-datetime of a Type 2 dimension record must be exactly equal to the begin-effective-datetime of the next change for that dimension member .The most current dimension record must have an end-effective-datetime equal to a fictitious datetime far in the future.

My opinion, which is based on experience of implementing this technique, is that it is a bad idea and in this blog post I’ll explain why. Let me clarify that I do not think the practise of managing dimension members as Type 2 slowly changing dimensions is a bad idea, only that maintaining an effective period using a start date and an end date is.

Framing the problem

Let’s look at an example where I am modelling customers as a type 2 dimension. Following Kimball’s advice we would implement a table something like this:

Note how we have two records for “Henry” because at some point he got married and had a child (attributes that we treat as type 2 changes) and how the [SCDEndDate] of the first record equals the [SCDStartDate] of the second record.

Can you spot the obvious problem here? We’ve got the same piece of information in more than one place; namely the date ‘27/11/2009’. What’s the point? We know that the [SCDEndDate] of a record equals the [SCDStartDate] of the record that supersedes it so using our old friend SQL we can easily work out the [SCDEndDate] of any record. I’ve seen too many examples of Kimball’s approach being used and I’ve hardly ever seen it not be a problem; just recently I worked on a data migration project and we had untold problems created by the use of [SCDStartDate] and [SCDEndDate] because the database could not constrain this relationship and neither, seemingly, did the code that had been written to manage the data.

At the root of this problem is the fact that Kimball’s method cannot be constrained in a relational database management system (RDBMS) without a complicated and debilitating set-based check constraint, it ignores the goodness of relational theory and normalisation that data professionals have had drummed into them for years.

A better way

Here follows how I would solve this problem; note that the syntax here is for SQL Server but this method would work perfectly well for any RDBMS.

Firstly, let’s create a table and put some data in it:

CREATE TABLE [Customer] (

   [Id]                INT             PRIMARY KEY

,  [NId]               NVARCHAR(50)

,  [Name]              NVARCHAR(50)

,  [HomeTown]          NVARCHAR(50)

,  [MaritalStatus]     CHAR(1)

,  [NumberOfChildren]  INT

,  [SCDStartDate]      DATETIME   --Note only [SCDStartDate], no [SCDEndDate]

,  CONSTRAINT  Customer_UK UNIQUE

   (

       [NId]

   ,   [SCDStartDate]

   )

);

INSERT [Customer] ([Id],[NId],[Name],[HomeTown],[MaritalStatus],[NumberOfChildren],[SCDStartDate])

VALUES (1,'Cust001','Henry','London','S',0,CONVERT(DATETIME,'20050324'))

,      (2,'Cust001','Henry','London','M',0,CONVERT(DATETIME,'20070726'))

,      (3,'Cust002','Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))

,      (4,'Cust001','Henry','London','M',1,CONVERT(DATETIME,'20091127'));,'M',2,CONVERT(DATETIME,'20060213'));

I’ve created three rows here for Henry for demo purposes. Over time we notice that Henry got married and then later had a child; note that there is no [SCDEndDate]:

Now let’s write a view that gets us the [SCDEndDate] for each record:

CREATE VIEW vCustomer  
AS

SELECT c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

,      c.[NumberOfChildren],c.[SCDStartDate]

,      COALESCE(MIN(c2.[ScdStartDate]),CONVERT(DATETIME,'99991231')) AS [SCDEndDate]

,      CONVERT(BIT,CASE    WHEN MIN(c2.[ScdStartDate]) IS NULL THEN 1 ELSE 0 END) AS [IsLatest]

FROM   [Customer] c

LEFT   OUTER JOIN [Customer] c2

ON     c.[Nid] = c2.[Nid]

AND        c.[SCDStartDate] < c2.[SCDStartDate]

GROUP  BY c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

,      c.[NumberOfChildren],c.[SCDStartDate];

Here is what we get if we select from that view:

Note that each row for Henry has an [SCDEndDate] that is guaranteed to be the same as the [SCDStartDate] of the record that supersedes it. Note also that we are guaranteed there will be no time period gaps since when Henry first became known to us. (Edit: Another advantage of this approach was pointed out to me by Phil Nolan in the comments below: we do not have to update a record when it gets superseded which we would have to do were we explicitly storing [SCDEndDate])

We have achieved this by joining the [Customer] table to itself and, for each row, finding the row that supersedes it and use the superseding row’s [SCDStartDate] as the current row’s [SCDEndDate]; if there is no superseding row we simply use a fictitious future date exactly as Kimball suggests. Moreover we can also derive an [IsLatest] field which is another field that Kimball recommend we use and also another field whose value cannot be constrained by the relational model. We have achieved the same as Kimball’s bunkum [SCDStartDate] & [SCDEndDate] table columns and still maintained the integrity of our data.

The counter argument to doing this is that Kimball’s method will be quicker because we don’t need to write an INNER JOIN to achieve the data that we want. Well that is true but ask yourself what is more important, querying speed or the integrity of your data? If your answer is '’querying speed’ then you should probably ask yourself why you’re bothering to use an RDBMS at all; data integrity is the reason that we build a relational data model. [That’s a pretty controversial opinion so I’ll look forward to debating it in the comments of this blog post :)]

If you absolutely need the [SCDEndDate] persisted somewhere then I’ll concede that the view resultset can be materialised into a table but really I don’t think there is that much need to do so; the main use of an [SCDEndDate] is to make your ETL easier and I’m sure its extremely rare that an ETL process cannot cope with the slightly increased querying time created by using the method I outline herein.

Conclusion

In this blog post I have outlined the problems that I believe are inherent in Kimball’s method of defining a time period for a slowly-changing-dimension record and also an alternative method that exhibits a purer method of achieving the same information. I welcome your thoughts in the comments below.

I have uploaded the script that I used herein so that you can try this technique for yourself. Download it from my Skydrive: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091128/DebunkingKimballMethodEffectiveDates.sql

@Jamiet

P.S. There are some great debates going on in the comments section of this blog post so if you’re reading this using a blog aggregator/feedreader click through to http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx to read more opinions on this.

UPDATE: The debate continued over on KimballGroup.com (where all respondents disagreed with me, unsurprisingly).