How to check unknown Key in fact table

Check list of all posts

In data warehouse development, -1 (unknown) should be inserted in fact table in case when no value is referenced to dimension table. This document is to provide a stored procedure (usp_DWHealthCheck_Unknownkey)  to check the percentage of this unknown key.   It is written in SQL server 2008.

Stored procedure name:  usp_DWHealthCheck_Unknownkey
Sample:  execute usp_DWHealthCheck_Unknownkey 'AG_Fact_CRM_ContactActivity'
By given fact table name, you will see result below:

Code attached below

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DWHealthCheck_Unknownkey]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_DWHealthCheck_Unknownkey]
GO

CREATE PROCEDURE [dbo].[usp_DWHealthCheck_Unknownkey] (@FactTablename  varchar(50))
AS
BEGIN

DECLARE @ExecuteQry varchar(4000)


Create table #Temp (KeyName varchar(50), TotalRecords integer,NumberOfRecordsWithUnknownKey integer, UnknownKeyPercent integer)

DECLARE Cursor_ExecuteQry CURSOR LOCAL FAST_FORWARD
FOR
SELECT
REPLACE ( REPLACE (
'
insert into  #Temp (KeyName, TotalRecords ,NumberOfRecordsWithUnknownKey, UnknownKeyPercent)
select ''<@SurrogateKey>'',
count(*) as Total,
sum(case when <@SurrogateKey> = -1 then 1 else 0 end) as UnknownKey,  
convert(integer, ((sum(case when <@SurrogateKey> = -1 then 1 else 0 end)*1.0) /  count(*))*100) as  UnknownKeyPercent
from <@FactTablename>
'
, '<@SurrogateKey>',COLUMN_NAME),'<@FactTablename>',@FactTablename)

FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME=@FactTablename
AND right(COLUMN_NAME,3)='Key'


OPEN Cursor_ExecuteQry
FETCH NEXT FROM Cursor_ExecuteQry INTO @ExecuteQry
WHILE @@FETCH_STATUS = 0
BEGIN

exec (@ExecuteQry)

FETCH NEXT FROM Cursor_ExecuteQry INTO @ExecuteQry
END

CLOSE Cursor_ExecuteQry
DEALLOCATE Cursor_ExecuteQry


select * from #Temp

drop table #Temp

END