Comparing Results using T-SQL

Usually, in data migration or ETL projects there is a need for comparing source data with that of the destination once the data is loaded. The challenge is in comparing each field from the source with its counterpart on the destination, for each record – and you can have a whole lot of fields.

The query that you would write to do the comparison, would be a large tangle of comparison operators – even for tables of a few columns, increasing in complexity as the number of columns grow.

A simpler solution would be to first convert the source and the destination into tables of just three fields: The Key field, the Field field and the Value field. (In case you have a multi-column key, you would obviously have more than three fields – but the principle is the same)

Consider the following source:

Table 1: Source

Seq_code	Product_Name	Color	Unit_Price	Category_Name	Weight
C01	Crystal parrot	None	130.00	ORNAMENT	.75
C02	Silver pirate	Silver	35.00	ORNAMENT	1
C03	Pearl necklace	White	220.00	JEWELRY	.35
C05	Ceramic dog	Blue	15.00	ORNAMENT	.35

and then consider the following destination (assuming that data is already loaded through some sort of ETL process):

Table 2: Destination

Code	Name	Color	Price	Category	Weight
C01	Crystal parrot	None	130.00	Ornament	75
C02	Silver pirate	Silver	35.00	Ornament	1
C03	Pearl necklace	White	220.00	Jewelry	35
C05	Ceramic dog	Blue	15.00	Ornament	35

The Need

You need to compare these two, in order to pull out any discrepancies.

The Plan

You first mold each of these (i.e. the source and destination) to look like this:

Table 3: Pivoted Destination

Key	Field	Value
C01	Name	Crystal parrot
C01	Color	None
C01	Price	130.00
C01	Category	Ornament
C01	Weight	75
C02	Name	Silver pirate
…	…	…
C05	Weight	35

Here, you have the Key field (Code) of each record repeated for each field of that record along with the name of the field and the value of the field.

Before you do the molding you need to ensure that the field names in the original tables are the same. I.e. Seq_code in the source and Code in the destination should be changed to have the same name: Code, maybe.

Hence, the molded source should look like this:

Table 4: Pivoted Source

Key	Field	Value
C01	Name	Crystal parrot
C01	Color	None
C01	Price	130.00
C01	Category	ORNAMENT
C01	Weight	.75
C02	Name	Silver pirate
…	…	…
C05	Weight	.35

You now do the comparison using INNER JOIN on the Key + Field columns, and a WHERE to filter out the values that do not match. The molding can be done using UNPIVOT, so that there is no need to create any intermediate tables.

The Code

WITH
-- Pivot the source data
SourcePivot
AS
(
	SELECT	[Code],
			[Field],
			[Value]
	FROM	(
				SELECT	[Code] =		[Seq_Code],
						[Name] =		[Product_Name],
						[Color],
						[Price] =		CAST([Unit_Price] AS VARCHAR(20)),	-- Data type conversion since all
						[Category] =	[Category_Name],					-- fields have to be of the same
						[Weight] =		CAST([Weight] AS VARCHAR(20))		-- data type.
				FROM	Source
			) AS Source
			UNPIVOT
			(
				[Value] FOR [Field] IN ([Name], [Color], [Price], [Category], [Weight])
			) AS UnPvt
),

-- Pivot the destination data
DestinationPivot
AS
(
	SELECT	[Code],
			[Field],
			[Value]
	FROM	(
				SELECT	[Code],
						[Name],
						[Color],
						[Price] =	CAST([Price] AS VARCHAR(20)),
						[Category],
						[Weight] =	CAST(CAST([Weight] AS DECIMAL(8, 2)) AS VARCHAR(20))
				FROM	Destination
			) AS Destination
			UNPIVOT
			(
				[Value] FOR [Field] IN ([Name], [Color], [Price], [Category], [Weight])
			) AS UnPvt
)

-- Query for discrepancies
SELECT	SP.[Code],
		SP.[Field],
		SP.[Value] AS [SourceValue],
		DP.[Value] AS [DestinationValue]
FROM	SourcePivot SP
		INNER JOIN DestinationPivot DP
			ON SP.[Code] = DP.[Code]
			AND SP.[Field] = DP.[Field]
WHERE	SP.[Value] <> DP.[Value] COLLATE SQL_Latin1_General_CP1_CS_AS -- Use a case-sensitive collation on case insensitive
																	  -- tables/datbase if case difference is a concern.

The Result

Table 5: Comparison Result

Code	Field	SourceValue	DestinationValue
C01	Category	ORNAMENT	Ornament
C01	Weight	0.75	75
C02	Category	ORNAMENT	Ornament
C03	Category	JEWELRY	Jewelry
C03	Weight	0.35	35
C05	Category	ORNAMENT	Ornament
C05	Weight	0.35	35

<<Download sample code>>

To Key or Not To Key – That is the Question

Forgive the cliché up there, but I could not resist it!

In data warehousing (and data marting), we usually use the popular dimensional model (or the star schema) to design the data warehouse (or data mart). And it is a common conclusion that your fact tables are going to be huge. And would be updated on a scheduled basis. Could be monthly, could be daily and from recent times, even hourly or sometimes (God forbid) on the minute!

Now, there have been arguments in the past as to whether we actually need to create primary key constraints, and especially if foreign key constraints need to be created between the fact table and the related dimension tables. Do we need to create indexes on the different columns.

Some may be shocked by such a question. I mean is this something that need to be doubted? Of course you need keys. Where else some others may be quite liberal and say, No! Of course you don’t need these keys and indexes. There are valid points to counter-argue each approach. There can also be a middle path.

In order to fuel this fire I have a question going on in the Beyond Relational BI Quiz 2011. This is a great place where you can prove your point of what you think. Points will be rewarded based on how well your arguments are presented. And then you get a chance to win an Apple iPad if your aggregate of the whole quiz is at the top.

The question goes as follows:

You are designing a data mart that results in a star schema on an SQL Server 2008 R2 server. A fact table with 7 dimensions, initially containing records in the region of 2,000,000. You expect the fact table to grow increasing by around 50,000 each month. Data loads happen on a weekly basis, and sometimes on an ad-hoc basis twice or thrice a month. Would you or would you not design primary key constraints, foreign key constrains and indexes for the data mart? If so how and why would you proceed, or why would you not? Discuss.

Like I said, points would be awarded based on how well you present your arguments.  Click Here to Submit an Answer. Good luck!

Now Everyone Can BI

If you had flown AirAsia, you would probably know where I stole the title to this post from. Microsoft did the same thing too. Heh heh! No, not the stealing bit, but providing for (almost) every small organization to use BI – Something which only the big bucks could afford.