Populate your dimensions with Unknown Members

Normally in every dimension load my first task is to insert the unknown value in the dimension table. This is tedious and time consuming task if dimension has many columns with different data types. And to make the script and put that script back into SSIS execute SQL task.

As usual then I will start googling and find the very Nice article Populate your dimensions with Unknown Members of Karl Beran. Which is dynamic in nature but I want to make it to dynamic from SSIS point of view. So simply I added the code which looking for the unknown value in dimension. If unknown is present then It by pass the code which populate the value into the dimension.

Note: @TableName is Parametrized variable.

And it’s enabling the identity insert before executing the insert statement and disabling the identity insert statement after the execute statement.

Simply put following code into the SSIS execute SQL task and do parameter mapping which contains the table name to pass this script. ALL Done for automated Unknown statement in SSIS.

—-Script which I love

—– Check if Unknown already Exists…..

DECLARE @sqlCommand nvarchar(1000)

DECLARE @counts int

Declare @TableName AS Varchar(500)

SET @TableName =   ?

SET @sqlCommand = ‘select @cnt=count(*) FROM ‘ + @TableName +  ‘ where id =-1′

EXECUTE sp_executesql @sqlCommand, N’@TableName varchar(500),@cnt int OUTPUT’, @TableName = @TableName, @cnt=@counts OUTPUT

Print @counts

—-Check if unknown is not available then insert unknown

IF @counts = 0

Begin

DECLARE @crlf CHAR(2)

SET @crlf = char(13) + char(10)

DECLARE @UnknownSurrogateKeyValue AS VARCHAR(255)

DECLARE @SurrogateKeyName AS VARCHAR(255)

DECLARE @DimensionTableNamePrefix AS VARCHAR(255)

DECLARE @SQL AS VARCHAR(5000)

–Unknown member surrogate key value:

SET @UnknownSurrogateKeyValue = ‘-1’

–String within dimension surrogate key column name

SET @SurrogateKeyName = ‘key’

–Prefix for Dimension table names

SET @DimensionTableNamePrefix = ‘Dim’

–Create Table for default values

DECLARE @dataTypes AS TABLE

(

DATA_TYPE     VARCHAR(50)

,MIN_DESC     VARCHAR(255)

,MAX_DESC     VARCHAR(255)

,DEFAULT_DESC VARCHAR(255)

)

INSERT INTO @dataTypes

(DATA_TYPE

,MIN_DESC

,MAX_DESC

,DEFAULT_DESC)

–Exact Numerics

SELECT  ‘bigint’

,’-9223372036854775808′

,’9223372036854775807′

,’-1′ UNION ALL

SELECT      ‘numeric’

,’0′

,’0′

,’0′  UNION ALL

SELECT      ‘bit’

,’0′

,’1′

,’1’UNION ALL

SELECT      ‘smallint’

,’-32768′

,’32767′

,’0’UNION ALL

SELECT      ‘decimal’

,’0′

,’0′

,’0’UNION ALL

SELECT      ‘smallmoney’

,’-214748.3648′

,’214748.3647′

,’0’UNION ALL

SELECT      ‘int’

,’-2147483648′

,’2147483647′

,’0’UNION ALL

SELECT      ‘tinyint’

,’0′

,’255′

,’0’UNION ALL

SELECT      ‘money ‘

,’-922337203685477.5808′

,’922337203685477.5807′

,’0’UNION ALL

–Approximate Numerics

SELECT      ‘float’

,’0′

,’0′

,’0’UNION ALL

SELECT      ‘real’

,’0′

,’0′

,’0’UNION ALL

–Binary Strings

SELECT      ‘binary’

,’0′

,’1′

,’0’UNION ALL

SELECT      ‘varbinary’

,’0′

,’1′

,’0’UNION ALL

–Other Data Types

SELECT      ‘sql_variant’

,’0′

,’1′

,’0’UNION ALL

–Date and Time

SELECT      ‘date’

,’0001-01-01′

,’9999-12-31′

,’1900-01-01’UNION ALL

SELECT      ‘datetimeoffset’

,’0001-01-01 00:00:00.0000000 +00:00′

,’9999-12-31 23:59:59.9999999 +00:00′

,’1900-01-01 00:00:00.0000000 +00:00’UNION ALL

SELECT      ‘datetime2′

,’0001-01-01 00:00:00.0000000′

,’9999-12-31 23:59:59.9999999’

,’1900-01-01 00:00:00.0000000’UNION ALL

SELECT      ‘smalldatetime’

,’1900-01-01 00:00:00′

,’2079-06-06 23:59:59′

,’1900-01-01 00:00:00’UNION ALL

SELECT      ‘datetime’

,’1753-01-01 00:00:00.000′

,’9999-12-31 23:59:59.997′

,’1900-01-01 00:00:00.000’UNION ALL

SELECT      ‘time’

,’00:00:00.0000000′

,’23:59:59.9999999′

,’00:00:00’UNION ALL

–Character Strings

SELECT      ‘char’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘varchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘text’

,’U’

,’Unknown’

,’UNK’ UNION ALL

–Unicode Character Strings

SELECT      ‘nchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘nvarchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘ntext’

,’U’

,’Unknown’

,’UNK’ UNION ALL

–Binary Strings

SELECT      ‘image’

,”

,”

,” UNION ALL

–Other Data Types

SELECT      ‘hierarchyid’

,’/’

,’/’

,’/’ UNION ALL

SELECT      ‘uniqueidentifier’

,’null’

,’null’

,’null’ UNION ALL

SELECT      ‘xml’

,”

,”

,”

— IDENTITY INSERT ON

SELECT @SQL = ‘SET  identity_INSERT ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME +’  ON’ + @CRLF+ @CRLF

FROM INFORMATION_SCHEMA.TABLES t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

SELECT @SQL= @SQL + ‘INSERT INTO ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME + ‘ (‘ + @crlf

–{ column_list }

+ replace(stuff(( SELECT ‘,’ + c.COLUMN_NAME FROM

INFORMATION_SCHEMA.columns c

WHERE c.COLUMN_NAME = COLUMN_NAME AND c.TABLE_NAME = t.TABLE_NAME ORDER

BY

c.ORDINAL_POSITION FOR XML PATH(”) ), 1, 1, ”), ‘,’, ‘,’) + ‘) ‘ +

@crlf +

‘VALUES ‘

+ @crlf + ‘(‘

–{ VALUES }

+ replace(stuff(( SELECT ‘,”’ + CASE WHEN c.COLUMN_NAME LIKE ‘%’ +

@SurrogateKeyName + ‘%’ THEN @UnknownSurrogateKeyValue WHEN

CHARACTER_MAXIMUM_LENGTH > len(dt.DEFAULT_DESC) THEN dt.MAX_DESC WHEN

CHARACTER_MAXIMUM_LENGTH < len(dt.DEFAULT_DESC) THEN dt.MIN_DESC ELSE

DT.DEFAULT_DESC END + ”” FROM INFORMATION_SCHEMA.columns c INNER JOIN

@dataTypes dt ON

c.DATA_TYPE = dt.DATA_TYPE WHERE c.COLUMN_NAME = COLUMN_NAME AND

c.TABLE_NAME =

t.TABLE_NAME ORDER BY c.ORDINAL_POSITION FOR XML PATH(”) ), 1, 1, ”),

‘,’, ‘,’

) + ‘ ); ‘   + CHAR(13) + CHAR(13)

FROM   INFORMATION_SCHEMA.tables t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

SELECT @SQL = @SQL +  ‘SET  identity_INSERT ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME +’  OFF’ + @CRLF+ @CRLF

FROM INFORMATION_SCHEMA.TABLES t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

PRINT @SQL

EXEC (@SQL)

END

Else

Print ‘Already Exists’

SSIS Screenshot:

Image

Advertisements

About Zaim Raza

7 Years of professional experience in Business intelligence using SQL Server and Microsoft SharePoint/.net Technologies. Experience in Data warehousing, Data Modeling, Data profiling, Data Integration, Data cleansing, consultations and enterprise reporting. I want to work for organization where I am able to utilize my skills, experience and knowledge to transform the technology into business value.
This entry was posted in SSIS, T-SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s