Data Profiling med SQL Server

By | maj 22, 2015
0 Comment

Vi som arbetar med Business Intelligence behöver då och då läsa in nya typer av extraktfiler. Det är bara ett av många tillfällen då vi har nytta av Data Profiling.

Data Profiling är användbart för:

  • Att få koll på innehållet i nya extraktfiler
  • Att felsöka om något har hänt med befintliga extraktfiler
  • När vi läser direkt från andra databaser (ej via filer) och vill få ut mer information än bara tabellstrukturen

I SQL Server Integration Services (SSIS) finns ett inbyggt verktyg för Data Profiling som heter ”Data Profiling Task”. Detta verktyg har sina fördelar och nackdelar, vilket gör att det inte alltid är det man helst använder.

Ett alternativ jag tänker beskriva här är hur man enkelt med hjälp av SQL Server Management Studio och ett T-SQL script kan få en snabb överblick av sin källdata. Resultatet kan se ut så här:

Data Profiling med SQL Server

Så här kan den användas:

  • Längden (MaxLen och MinLen) är väldigt användbar för att se till att vi får rätt längd och typ på våra textfält.
  • HasBlanks talar om för oss om ett fält alltid är ifyllt eller inte.
  • AlwaysBlank talar om för oss om ett fält inte används överhuvudtaget.
  • AlwaysInt betyder att ett fält alltid är ett heltal (eller blankt).
  • AlwaysDecimal anger att ett fält alltid motsvarar ett decimaltal (eller blankt).
  • AlwaysDateTime112 betyder att ett fält alltid innehåller giltiga datum (enligt format 112). Man kan naturligtvis kontrollera mot andra datumformat om man vill.

Steg 1 – få in din data i SQL Server

En första förutsättning (vilket är samma som för SSIS Data Profiling Task) är att man får in sin källdata i en tabell i SQL Server. Enklaste sättet är ofta att använda Import Table wizard i SQL Server Management Studio.

SQL Server Management Studio Import Data Wizard

Där väljer man ”Flat File Source” om man skall profilera en fil. Annars går det naturligtvis bra att välja en annan typ av källa också.

SQL Server Management Studio Import Data Wizard choose Data Source

Se där till att göra alla dina fält till string eller ”Unicode string”. Se också till att längden är så hög att du garanterat får plats med all data.

Import Data Wizard advanced

Klicka färdigt igenom alla steg i Wizarden och låt SQL Server importera all data åt dig.

Steg 2 – Profilera

Jag har skrivit ett T-SQL script som gör jobbet åt dig. Scriptet kan du naturligtvis modifiera och lägga till den information just du vill ha.

-- Data Profiling T-SQL script
-- By Johan Åhén 2015

-- This variable will contain the T-SQL code to perform the Data Profiling
DECLARE @Sql NVARCHAR(MAX); 

WITH Config AS
(
	SELECT
		'YourSchema' AS SchemaName, -- Enter your schema here (for example dbo)
		'YourTable' AS TableName    -- Enter your table name here
),
ColumnNames AS
(
	SELECT
		ORDINAL_POSITION,
		COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS AS t1
	INNER JOIN Config AS t2
	ON t1.TABLE_SCHEMA=t2.SchemaName AND t1.TABLE_NAME=t2.TableName
),
SelectsSql AS
(
	SELECT
		ORDINAL_POSITION,
		'SELECT ''' + COLUMN_NAME + ''' AS ColumnName' +
		', MIN(LEN(' + COLUMN_NAME + ')) AS MinLen' +
		', MAX(LEN(' + COLUMN_NAME + ')) AS MaxLen' +
		', MAX(IIF(' + COLUMN_NAME + '='''', 1, 0)) AS HasBlanks' +
		', MIN(IIF(' + COLUMN_NAME + '='''', 1, 0)) AS AlwaysBlank' +
		', MIN(IIF(TRY_CONVERT(INT, ' + COLUMN_NAME + ') IS NOT NULL, 1, 0)) AS AlwaysInt' +
		', MIN(IIF(TRY_CONVERT(DECIMAL(18,8), ' + COLUMN_NAME + ') IS NOT NULL, 1, 0)) AS AlwaysDecimal' +
		', MIN(IIF(TRY_CONVERT(DATETIME2(0), ' + COLUMN_NAME + ', 112) IS NOT NULL, 1, 0)) AS AlwaysDateTime112' +
		' FROM ' + Config.SchemaName + '.' + Config.TableName
		AS [Sql]
	FROM ColumnNames
	CROSS JOIN Config
),
UnionAllSql AS
(
	SELECT
		ORDINAL_POSITION,
		CAST([Sql] AS NVARCHAR(MAX)) AS [Sql]
	FROM SelectsSql
	WHERE ORDINAL_POSITION=1
	UNION ALL
	SELECT
		SelectsSql.ORDINAL_POSITION,
		CAST(UnionAllSql.[Sql] + ' UNION ALL ' + SelectsSql.[Sql] AS NVARCHAR(MAX))
	FROM UnionAllSql
	INNER JOIN SelectsSql ON SelectsSql.ORDINAL_POSITION=UnionAllSql.ORDINAL_POSITION+1
)
SELECT TOP 1 @Sql = [Sql]
FROM UnionAllSql
ORDER BY ORDINAL_POSITION DESC;

-- Execute the Data Profiling
EXEC sp_executesql @Sql;

En förutsättning för att scriptet skall fungera är att alla kolumner är av strängtyp (varchar, nvarchar, etc). Du kan dock enkelt anpassa koden under SelectsSql om du behöver hantera andra datatyper.

Lycka till!

Related Posts