About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Wednesday, September 11, 2013

An auto generating SQL Merge stored procedure for Type 2 SCD


This could shave off some time in generating SQL Merge statement by hand. This helpful stored proc. will auto generate your merge statement on tables that use Type 0,1, and 2 SCD.  The dim table just needs to be setup to use extended properties.

He uses the schema dim and etl to represent the dimension table and staging table, respectfully. The field IsRowCurrent is used to represent the current updated row.

http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/
http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd/



Code Snippet
  1. /***************************************************************
  2. *                                                              *
  3. *   Script for use with blog post                              *
  4. *     "Automating T-SQL Merge to load Dimensions (SCD)"        *
  5. *     http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd
  6. *                                                              *   
  7. *   Posted: 6th April 2012                                     *
  8. *                                                              *
  9. *   By: Alex Whittles - Purple Frog Business Intelligence      *
  10. *       www.PurpleFrogSystems.com                              *
  11. *                                                              *
  12. *   All code samples are provided “AS IS” without warranty of  *
  13. *   any kind, either express or implied, including but not     *
  14. *   limited to the implied warranties of merchantability       *
  15. *   and/or fitness for a particular purpose.                   *
  16. *                                                              *
  17. ***************************************************************/
  18.  
  19. --The generate merge proc
  20. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateMerge]') AND type in (N'P', N'PC'))
  21. DROP PROCEDURE [dbo].[GenerateMerge]
  22. GO
  23.  
  24. CREATE PROCEDURE [dbo].[GenerateMerge]
  25.     @Dimension  varchar(50),
  26.     @Schema     varchar(50)='dim',
  27.     @ETLSchema  varchar(50)='etl',
  28.     @Execute    bit=0  --Should the resulting merge be returned or executed
  29. AS
  30. BEGIN
  31.     SET NOCOUNT ON;
  32.  
  33.     --Create Carriage return variable to help format the resulting query
  34.     DECLARE @crlf char(2)
  35.     SET @crlf = CHAR(13)
  36.  
  37.  
  38.     --Find out which Audit fields are used
  39.     DECLARE @UseIsInferred bit
  40.     DECLARE @UseValidFrom bit
  41.     DECLARE @UseValidTo bit
  42.     DECLARE @UseIsRowCurrent bit
  43.     DECLARE @UseLastUpdated bit
  44.     
  45.     SET @UseIsInferred = ISNULL((SELECT MAX(1)
  46.     FROM sys.columns c
  47.             INNER JOIN sys.tables t on c.object_id = t.object_id
  48.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  49.     WHERE       s.name = @Schema
  50.             AND t.name = @Dimension
  51.             AND c.name = 'IsInferred'
  52.     ),0)
  53.  
  54.     SET @UseValidFrom= ISNULL((SELECT MAX(1)
  55.     FROM sys.columns c
  56.             INNER JOIN sys.tables t on c.object_id = t.object_id
  57.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  58.     WHERE       s.name = @Schema
  59.             AND t.name = @Dimension
  60.             AND c.name = 'ValidFrom'
  61.     ),0)
  62.  
  63.     SET @UseValidTo = ISNULL((SELECT MAX(1)
  64.     FROM sys.columns c
  65.             INNER JOIN sys.tables t on c.object_id = t.object_id
  66.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  67.     WHERE       s.name = @Schema
  68.             AND t.name = @Dimension
  69.             AND c.name = 'ValidTo'
  70.     ),0)
  71.  
  72.     SET @UseIsRowCurrent = ISNULL((SELECT MAX(1)
  73.     FROM sys.columns c
  74.             INNER JOIN sys.tables t on c.object_id = t.object_id
  75.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  76.     WHERE       s.name = @Schema
  77.             AND t.name = @Dimension
  78.             AND c.name = 'IsRowCurrent'
  79.     ),0)
  80.  
  81.     SET @UseLastUpdated = ISNULL((SELECT MAX(1)
  82.     FROM sys.columns c
  83.             INNER JOIN sys.tables t on c.object_id = t.object_id
  84.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  85.     WHERE       s.name = @Schema
  86.             AND t.name = @Dimension
  87.             AND c.name = 'LastUpdated'
  88.     ),0)
  89.  
  90.  
  91.     --Identify the business key column(s)
  92.     --Also define what the null replacement should be
  93.     DECLARE myCurBK Cursor FOR
  94.     SELECT c.name
  95.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
  96.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
  97.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
  98.             ELSE 'NULL' END AS NullRep
  99.     FROM sys.columns c
  100.         INNER JOIN sys.tables t on c.object_id = t.object_id
  101.         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  102.         INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  103.         INNER JOIN sys.extended_properties ep
  104.              ON t.object_id=ep.major_id
  105.             AND c.column_id=ep.minor_id
  106.             AND ep.class=1
  107.             AND ep.name='SCD'
  108.     WHERE s.name = @Schema
  109.         AND t.name = @Dimension
  110.         AND ep.value = 'BK'
  111.     ORDER BY c.column_id
  112.     
  113.     --Identify all fields to be merged (Exclude Type 0)
  114.     DECLARE myCurType1 Cursor
  115.     FOR SELECT c.name
  116.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
  117.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
  118.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
  119.             ELSE 'NULL' END AS NullRep
  120.         FROM sys.columns c
  121.             INNER JOIN sys.tables t on c.object_id = t.object_id
  122.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  123.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  124.             LEFT JOIN sys.extended_properties ep
  125.                  ON t.object_id=ep.major_id
  126.                 AND c.column_id=ep.minor_id
  127.                 AND ep.class=1
  128.                 AND ep.name='SCD'
  129.         WHERE s.name = @Schema
  130.             AND t.name = @Dimension
  131.             AND c.is_identity=0
  132.             AND ISNULL(ep.value,'1') NOT IN ('0', 'Audit', 'BK')
  133.         ORDER BY c.column_id ASC
  134.  
  135.     --Identify all fields for insert
  136.     DECLARE myCurAll Cursor
  137.     FOR SELECT c.name
  138.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
  139.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
  140.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
  141.             ELSE 'NULL' END AS NullRep
  142.         FROM sys.columns c
  143.             INNER JOIN sys.tables t on c.object_id = t.object_id
  144.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  145.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  146.         WHERE s.name = @Schema
  147.             AND t.name = @Dimension
  148.             AND c.name NOT IN ('LastUpdated', 'IsInferred', 'ValidFrom', 'ValidTo', 'IsRowCurrent')
  149.             AND c.is_identity=0
  150.         ORDER BY c.column_id ASC
  151.  
  152.     DECLARE @Field varchar(255)
  153.     DECLARE @NullRep varchar(20)
  154.     
  155.     DECLARE @SQL varchar(max)
  156.     DECLARE @SQL2 varchar(max)
  157.     DECLARE @SQL3 varchar(max)
  158.  
  159.     --Now start building up the dynamic SQL
  160.     
  161.     SET @SQL =                'MERGE      [' + @Schema + '].[' + @Dimension + '] AS Target'
  162.     SET @SQL = @SQL + @crlf + '    USING [' + @ETLSchema + '].[' + @Dimension + '] AS Source'
  163.     
  164.     OPEN myCurBK
  165.     FETCH NEXT FROM myCurBK INTO @Field, @NullRep
  166.     IF (@@FETCH_STATUS>=0)
  167.         BEGIN
  168.             SET @SQL = @SQL + @crlf + '       ON Target.' + @Field + ' = Source.' + @Field
  169.             FETCH NEXT FROM myCurBK INTO @Field, @NullRep
  170.         END
  171.     WHILE (@@FETCH_STATUS<>-1)
  172.     BEGIN
  173.         IF (@@FETCH_STATUS<>-2)
  174.             SET @SQL = @SQL + @crlf + '       AND Target.' + @Field + ' = Source.' + @Field
  175.         FETCH NEXT FROM myCurBK INTO @Field, @NullRep
  176.     END
  177.     CLOSE myCurBK
  178.  
  179.     IF @UseIsRowCurrent>0   SET @SQL = @SQL + @crlf + '       AND Target.IsRowCurrent=1'
  180.     
  181.     SET @SQL = @SQL + @crlf + '    WHEN MATCHED'
  182.     
  183.     OPEN myCurType1
  184.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  185.     
  186.     IF (@@FETCH_STATUS>=0)
  187.         BEGIN
  188.             SET @SQL = @SQL + @crlf + '       AND (ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
  189.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  190.         END
  191.     WHILE (@@FETCH_STATUS<>-1)
  192.     BEGIN
  193.         IF (@@FETCH_STATUS<>-2)
  194.             SET @SQL = @SQL + @crlf + '       OR ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
  195.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  196.     END
  197.     CLOSE myCurType1
  198.     
  199.     SET @SQL = @SQL + @crlf + '      )'
  200.     SET @SQL2 = '    THEN UPDATE SET'
  201.  
  202.     OPEN myCurType1
  203.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  204.     IF (@@FETCH_STATUS>=0)
  205.         BEGIN
  206.             SET @SQL2 = @SQL2 + @crlf + '       ' + @Field + ' = Source.' + @Field
  207.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  208.         END
  209.     WHILE (@@FETCH_STATUS<>-1)
  210.     BEGIN
  211.         IF (@@FETCH_STATUS<>-2)
  212.             SET @SQL2 = @SQL2 + @crlf + '      ,' + @Field + ' = Source.' + @Field
  213.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
  214.     END
  215.     CLOSE myCurType1
  216.     
  217.     IF @UseLastUpdated>0   SET @SQL2 = @SQL2 + @crlf + '      ,LastUpdated = GetDate()'
  218.     
  219.     SET @SQL3 = '    WHEN NOT MATCHED THEN'
  220.     SET @SQL3 = @SQL3 + @crlf + '         INSERT ('
  221.     
  222.     OPEN myCurAll
  223.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  224.     IF (@@FETCH_STATUS>=0)
  225.         BEGIN
  226.             SET @SQL3 = @SQL3 + @crlf + '           ' + @Field
  227.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  228.         END
  229.     WHILE (@@FETCH_STATUS<>-1)
  230.     BEGIN
  231.         IF (@@FETCH_STATUS<>-2)
  232.             SET @SQL3 = @SQL3 + @crlf + '           ,' + @Field
  233.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  234.     END
  235.     CLOSE myCurAll
  236.     
  237.      
  238.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,IsInferred'
  239.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,ValidFrom'
  240.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,ValidTo'
  241.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,IsRowCurrent'
  242.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,LastUpdated'
  243.     SET @SQL3 = @SQL3 + @crlf + '         ) VALUES ('
  244.     
  245.     OPEN myCurAll
  246.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  247.     IF (@@FETCH_STATUS>=0)
  248.         BEGIN
  249.             SET @SQL3 = @SQL3 + @crlf + '            Source.' + @Field
  250.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  251.         END
  252.     WHILE (@@FETCH_STATUS<>-1)
  253.     BEGIN
  254.         IF (@@FETCH_STATUS<>-2)
  255.             SET @SQL3 = @SQL3 + @crlf + '           ,Source.' + @Field
  256.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
  257.     END
  258.     CLOSE myCurAll    
  259.     
  260.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,0'
  261.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
  262.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,NULL'
  263.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,1'
  264.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
  265.     SET @SQL3 = @SQL3 + @crlf + '         );'
  266.     
  267.     --clean up
  268.     DEALLOCATE myCurType1
  269.     DEALLOCATE myCurAll
  270.     DEALLOCATE myCurBK
  271.     
  272.     IF @Execute = 1
  273.     BEGIN
  274.         EXEC(@SQL + @SQL2 + @SQL3)           
  275.     END
  276.     ELSE
  277.     BEGIN    
  278.         PRINT @SQL
  279.         PRINT @SQL2
  280.         PRINT @SQL3
  281.     END
  282.     
  283. END
  284.  
  285. GO


An example of the extended properties being set through sql statement:
Code Snippet
  1. EXEC sys.sp_addextendedproperty @level2name=N'CustomerKey',  @value=N'PK' ,    
  2.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  3.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  4. EXEC sys.sp_addextendedproperty @level2name=N'Email',        @value=N'BK' ,    
  5.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  6.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  7. EXEC sys.sp_addextendedproperty @level2name=N'FirstName',    @value=N'1' ,     
  8.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  9.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  10. EXEC sys.sp_addextendedproperty @level2name=N'LastName',     @value=N'1' ,     
  11.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  12.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  13. EXEC sys.sp_addextendedproperty @level2name=N'DoB',          @value=N'1' ,     
  14.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  15.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  16. EXEC sys.sp_addextendedproperty @level2name=N'Sex',          @value=N'1' ,     
  17.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  18.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  19. EXEC sys.sp_addextendedproperty @level2name=N'MaritalStatus',@value=N'1' ,     
  20.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  21.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  22. EXEC sys.sp_addextendedproperty @level2name=N'FirstCreated', @value=N'1' ,     
  23.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  24.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  25. EXEC sys.sp_addextendedproperty @level2name=N'ValidFrom',    @value=N'Audit' ,
  26.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  27.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  28. EXEC sys.sp_addextendedproperty @level2name=N'ValidTo',      @value=N'Audit' ,
  29.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  30.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  31. EXEC sys.sp_addextendedproperty @level2name=N'IsRowCurrent', @value=N'Audit' ,
  32.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  33.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
  34. EXEC sys.sp_addextendedproperty @level2name=N'LastUpdated',  @value=N'Audit' ,
  35.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
  36.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
An example of printing the script:
Code Snippet
  1. EXEC dbo.GenerateMerge
  2.     @Dimension = 'Customer',
  3.     @Schema = 'dim',
  4.     @ETLSchema = 'etl',
  5.     @Execute = 0

1 comment:

Sqiar BI said...

data analysis reporting services
SQIAR (http://www.sqiar.com/solutions/technology/tableau) is a leading Business Intelligence company.Sqiar Consultants Provide Tableau Software Consultancy To small and Medium size of organization.