I recently came to extend some DTS packages that we have implemented for a client to include a LOT of new Date styled dimensions. You know the kind of thing… We have a date table with all of the possible dates over the last 400 years which we all figure should suffice. If I’m still coding beyond this date I’m not entirely sure that DTS will be the framework of choice, Hell its already VERY superceded even as we speak! What I did not appreciate was that date look ups would be problematical in DTS.
To state the problem we have the ‘DimCalendarDates’ Dimension table defined thus:-
- Sid (int) – Surrogate ID
- CalendarDate (date) – The date that this record pertains to.
- FirstDayInMonth (date) – The first date within this month.
- Year (int) – The year that this record appears within
For the purposes of this Blog we are only really interested in the CalendarDate which is the field that we join to and the SID field which is the surrogate ID field that we populate the Facts Table with instead of a date. Our example fact table ‘DataFacts’ is defined thus:-
- SID (int)- Surrogate ID Field
- DimStartDateFK (int) – Links to the DimCalendarDates table on the SID field
- DimEndDateFK (int) – Links to the DimCalendarDates table on the SID field
- DimArchiveDateFK (int) – Links to the DimCalendarDates table on the SID field
I tend to populate my tables in these easy to follow steps:-
- Load data from source tables into staging table.
- Populate dimension tables with new dimension data found in staging table.
- Load staging data into dataset.
- Using a Lookup DTS component look up the SIDs for each dimension field required and persist it in the dataset
- Update/Insert the facts table data.
OK, job 1 in this instance we are not bothered about as this is irrelevant to the blog, in addition as we are using a predefined and pre-populated calendar table we also need not worry about step 2. Step 3 is where we load our data so in the instance above we would use a Get Component as shown:-
And issue the following SQL statement within it:-
SELECT SID, StartDate, EndDate, ArchiveDate FROM DataStaging
Simple, Now we perform the lookup We configure the component thus:-
And then we merge this data into the dataset taking all of the data from the incoming Staged data and appending the lookup value as the image below shows:-
Our finished transformation looks a little like this:-
Brilliant, except for one small thing. It doesn’t work… At least not correctly Quite a few BUT crucially not all of the Look up matches will fail to find a corresponding date even though the value is in the underlying DimCalendarDates table. The problem is to do with Siberian winds I think, that is to say… I don’t know what the issue is and I care even less. Basically it seems that DTS lookups work best against textual data and so the answer is indeed simple, treat the date fields as string. But no-one wants to store dates fields as text right? So here’s the two fold approach I took:-
- Add a new formula field to the DimCalendarDates table to express the data as a string value
- Change the look up expression to convert the look up value to a string representation of the data
- In both instances I needed to take care of nulls as well (i have a NULL date within my DimCalendarDates table)
OK, So first off we create the formula to express the date field as a string value. I thus applied the following update to the DimCalendarTable:-
ALTER TABLE DimCalendarDates ADD [CalendarDateAsString] AS (CONVERT([nvarchar],[CalendarDate],(101))) PERSISTED
Nice and simple and for performance reasons I also persist the string field so that it is worked out only on updates not on ALL reads.
The conversion of the look ups are a little more involved:-
In essence what we have done is changed the query to compare the CalendarDateAsString formula expression to a textual representation of the date that we are looking up (represented in this instance by a question mark). Note that the Inner SQL query on the advanced tab must match exactly the source as defined on the Connection tab. Now the look ups should behave exactly as one would have expected.
You live and learn, well…. you live anyway.