Data Warehousing Tip: Using T-SQL vs SSIS for Slowly Changing Dimension (SCD) Types 1 and 2

Here’s an example of using T-SQL to process a Slowly Changing Type 1&2 Dimension . Note:  SSIS has an SCD transform ,however, it does not process large dimensional datasets very quickly.
…eh, I’m putting it too nicely, the SSIS SCD transform is painfully slow!  This T-SQL performs 20X quicker than the SSIS SCD transform.
USE [SomeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_UpsertDimGuest] Script Date: 9/11/2013 6:09:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpsertDimGuest]
AS
BEGIN
–DROP Constraints
–SCD 2 Historical Attribute Tracking via Upsert
INSERT INTO [dbo].[DimGuest]
( [GuestContactSrgKey],
[SourceSystem]
,[GuestIDAlternateKey]
,[GuestAcct]
,[GuestFirstName]
,[GuestLastName]
,[ETLLoadID]
,[EffStartDt]
,[EffEndDt]
,[IsCurrent]
)
SELECT
[GuestContactSrgKey]
,[SourceSystem]
,[GuestIDAlternateKey]
,[GuestAcct]
,[GuestFirstName]
,[GuestLastName]
,[ETLLoadID]
,GETDATE()
,’1/1/3000′
,’true’
FROM (
MERGE GTDW.dbo.DimGuest AS [Target]
USING GTDW_Staging.dbo.DimGuestStagingFinal AS [Source]
ON Target.GuestIDAlternateKey = Source.GuestIDAlternateKey
AND Target.IsCurrent = 1
WHEN MATCHED AND
(Target.[GuestFirstName] <> Source.[GuestFirstName]
OR Target.[GuestLastName] <> Source.[GuestLastName]
)
THEN UPDATE SET
IsCurrent = 0
,EffEndDt = GETDATE()
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[GuestContactSrgKey]
,[SourceSystem]
,[GuestIDAlternateKey]
,[GuestAcct]
,[GuestFirstName]
,[GuestLastName]
,[ETLLoadID]
,[EffStartDt]
,[EffEndDt]
,[IsCurrent]
)
VALUES (
Source.[GuestContactSrgKey]
,Source.[SourceSystem]
,Source.[GuestIDAlternateKey]
,Source.[GuestAcct]
,Source.[GuestFirstName]
,Source.[GuestLastName]
,Source.[ETLLoadID]
,GETDATE()
,’1/1/3000′
,’true’
)
WHEN NOT MATCHED BY SOURCE AND Target.IsCurrent = 1
THEN UPDATE SET
IsCurrent = 0
,EffEndDt = GETDATE()
OUTPUT $action AS Action
,[Source].*
) AS MergeOutput
WHERE MergeOutput.Action = ‘UPDATE’
AND GuestIDAlternateKey IS NOT NULL
;
–Changing Attributes – history is not recorded
UPDATE DimG
SET
[GuestContactSrgKey] = DSF.[GuestContactSrgKey]
,[GuestAcct] = DSF.[GuestAcct]
,[ETLLoadID] = DSF.[ETLLoadID]
FROM DimGuest DimG
INNER JOIN [GTDW_Staging].[dbo].[DimGuestStagingFinal] DSF
ON DimG.GuestIDAlternateKey = DSF.GuestIDAlternateKey
AND DimG.IsCurrent = 1 –Optional
AND (
DimG.[GuestAcct] <> DSF.[GuestAcct]
)
END

Data Warehousing Tip: T-SQL Script to Create DEFAULT Constraints by Data Type


There is an ongoing discussion/debate regarding the handling of NULLS in a data warehouse, however, the following article by Michelle Poolet summarizes it beautifully – “consider the data warehouse business requirements” (see http://sqlmag.com/database-administration/null-data-warehouse).


Beyond consideration, there are some data types that should get the DEFAULT treatment (bit, char, nchar, nvarchar, and varchar) and the following script can assist in creating these across every applicable column in your database.


SELECT Object_schema_name(T.[object_id], Db_id()) AS [Schema], 
       T.[name]  AS [table_name], 
       AC.[name] AS [column_name], 
       TY.[name] AS system_data_type, 
       AC.[max_length], 
       AC.[precision], 
       AC.[scale], 
       AC.[is_nullable], 
       AC.[is_ansi_padded], 
       'ALTER TABLE dbo.' + T.[name] 
       + ' ADD CONSTRAINT ' + AC.[name] + ' DEFAULT(' 
       + CASE TY.[name] 
            --WHEN 'int' THEN '0' 
          --WHEN 'bigint' THEN '0' 
          WHEN 'varchar' THEN '' 
          WHEN 'bit' THEN '' 
          WHEN 'char' THEN '' 
          WHEN 'smalldatetime' THEN '' 
          WHEN 'datetime' THEN ''
          --WHEN 'tinyint' THEN '0'
          --WHEN 'smallint' THEN '0' 
          WHEN 'nvarchar' THEN '' 
          --WHEN 'money' THEN '0.00'
          ELSE '' 
       END +') FOR '+ac.name AS sqlstring 
   FROM   sys.[tables] AS T 
       INNER JOIN sys.[all_columns] AC 
               ON T.[object_id] = AC.[object_id] 
       INNER JOIN sys.[types] TY 
               ON AC.[system_type_id] = TY.[system_type_id] 
                  AND AC.[user_type_id] = TY.[user_type_id] 
WHERE  T.[is_ms_shipped] = 0 
       AND T.[name] LIKE ('DIM%') AND AC.[name] NOT LIKE ( '%SrgKey%' )  AND 
TY.[name] in ('varchar', 'bit' , 'char', 'smalldatetime', 'datetime' , 'nvarchar')

I’ve taken the approach of defaulting values for all columns (less those affected by the WHERE clause) in my STAGING Database.  The thought is that the non-NULL values inserted into my staging tables will persist to the TARGET database and I won’t experience one of a variety of issues (OLAP, T-SQL, or otherwise) that a development and/or user team will encounter due to having NULL values in the data warehouse.

 

Data Warehousing Tip: Surrogate Key Generation

If IDENTITY insert is disabled or simply not available in a particular database or a database architecture, you’ll want to do a table based surrogate key assignment or use INSERT w ROW_NUMBER() with the OVER clause. I prefer using ROW_NUMBER().

USE [SomeDB]

CREATE TABLE [dbo].[testTgtTable](
[srgKey] [int] NULL,
[colA] [nchar](10) NULL,
[colB] [nchar](10) NULL
)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[testSrcTable](
[colA] [nchar](10) NULL,
[colB] [nchar](10) NULL
)
GO

INSERT INTO testSrcTable
SELECT ‘hear’, ‘no evil’ UNION ALL
SELECT ‘see’, ‘no evil’ UNION ALL
SELECT ‘speak’, ‘no evil’

DECLARE @someIntVar int
SET @someIntVar = (SELECT ISNULL(MAX(SrgKey),0) SK
FROM dbo.testTgtTable)

INSERT INTO testTgtTable (SrgKey, colA, colB)
SELECT
ROW_NUMBER() OVER(ORDER BY [colA]) + @someIntVar SK
, [colA]
, [colB]
FROM
testSrcTable

Visio 2010 Workaround: Application Hangs when opening ‘Database Model Diagram’ template

I received the following error today when attempting to use Visio’s ‘Database Model Diagram’ template under category ‘Software and Database’.

The program VISIO.EXE version 14.0.6122.5000 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Action Center control panel.  Process ID: 3fa8  Start Time: 01cdad7f36f7bba9  Termination Time: 0  Application Path: D:\Program Files\Microsoft Office\Office14\VISIO.EXE  Report Id: 9c9eef0a-1972-11e2-9a12-005056995c65

Sure enough, per the seemingly improbable suggestion made in the post below, Visio did not hang after changing the default printer to ‘Microsoft XPS Document Writer’. Don’t make the i mistake I did and change the default printer to ‘Microsoft XPS Document Writer (redirected 7)’.

http://answers.microsoft.com/en-us/office/forum/office_2010-visio/visio-2010-not-responding-when-opening-database/64b2b582-53f0-41f2-bb47-d3a3f36aeb82

As it turns out,  Remote Desktop (RDP) assigns remotely-mounted printers which can cause issues with Visio’s templates.

Below is similar to what you should see once you’ve assigned the correct default printer.

%d bloggers like this: