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

Question

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

Olga Klimova

SSC Enthusiast

Points: 171
More actions
September 26, 2011 at 11:34 pm

#245422

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

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

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

hr_sn Ten Centuries Points: 1062 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 🙂