Custom SSMS Shortcuts for ETL Developer. Part 2: Extended Properties

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    Comments posted to this topic are about the item Custom SSMS Shortcuts for ETL Developer. Part 2: Extended Properties

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • hr_sn

    Ten Centuries

    Points: 1062

    Hi Olga,

    Nice article and I really like the way you have used those excel files. Something new to play with for next couple of days. 😀

    Cheers!

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    Thank you, I'm glad you find it useful!

    I use Kimball's spreadsheet all the time as a data modeling tool and like to have at hand the extended properties that spreadsheet generates, as a reference where the data is coming from and what transformation rules are imposed.

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • shawn1272

    Grasshopper

    Points: 20

    Neat article. I used it to automate a check for duplicates.

    CREATE PROCEDURE DBO.SP_DUPLICATE_CHECK

    @TABLE NVARCHAR(255),

    @CLM NVARCHAR(255)

    AS

    BEGIN

    DECLARE @CMD NVARCHAR(255)

    SET @CMD =

    'SELECT ' +

    @CLM +

    ',COUNT(*) AS CNT' +

    ' FROM ' + @TABLE +

    ' GROUP BY ' + @CLM +

    ' HAVING COUNT(*) > 1'

    EXEC SP_EXECUTESQL @CMD

    END

    GO

    EXEC DBO.SP_DUPLICATE_CHECK 'TABLE_NAME','COLUMN1, COLUMN2, ETC.'

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    Great addition to Shortcut library: SP_DUPLICATE_CHECK – elegant solution. I can see its benefits for data profiling.

    Thank you for sharing!

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • sneumersky

    SSCertifiable

    Points: 7667

    Have you given any thought towards putting together a post on driving the SCD Transform using the "SCD Type" column from the MSDWH Toolkit's spreadsheet. That would be really cool 🙂

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply