Dynamic Info Matrix

Hi

I recently had to do an Info matrix for a DWH with no referential integrity - all that they had were neatly named tables and key columns (but no foreign keys). There were 45 Facts and 49 dimensions. Out of sheer laziness I decided to rather create it using code than manually go and fill in the ticks. so here's the code to do it (output it to text using SSMS, and paste into your spreadsheet tool of choice, split the text data and voila !!!)

if Object_ID ('tempdb..#IM') is not null
drop table #IM
if Object_ID ('tempdb..#MasterList') is not null
drop table #MasterList

if Object_ID ('tempdb..#Dim') is not null
drop table #Dim
Go
Set Nocount on
go

declare @FactTable_Convention varchar(200) -- How are fact tables named
,@DimSK_Convention varchar(200) -- How are dim_sk's indicated
,@Warehouse_DB varchar(200) -- what is the warehouse name
,@IM_UsageIndicator varchar(20) -- what value is used to indicate that
-- a table uses a dim

Select @FactTable_Convention = '%_fact'
,@DimSK_Convention = '%_id'
,@Warehouse_DB = 'Some_DWH'
,@IM_UsageIndicator = 'a' --Webdings makes this a tick mark

-- Internal variables
Declare @intRow int -- tracks the current row
,@intRows int -- tracks the number of rows
,@Query varchar(max)



Create table #MasterList
(
RowID int identity (1,1)
,FactName varchar(200)
,SKName varchar(200)
)


Select @Query = 'Insert into #MasterList (FactName, SKname)'
+ ' Select SO.Name, case when SC.Name like '
+ char(39) + '%Date%' + char(39)
+ 'then ' + char(39) + 'Date dimension' +char(39)
+ ' else SC.Name end '
+ 'from '
+ @WareHouse_DB
+ '..SysObjects SO join '
+ + @WareHouse_DB
+ '..sysColumns SC on SO.ID = SC.ID where SO.XType = '
+ char(39) + 'U' + char(39)
+ ' and SO.Name like '
+ char(39)
+ @FactTable_Convention
+ Char(39)
+ ' and (SC.Name like '
+ char(39)
+ replace(@DimSK_Convention,',',' or SC.Name like ')
+ Char(39) + ') and SC.TypeStat=0' --TypeStat indicates ID columns

--Print @Query
Exec (@Query)



Create table #IM
(
RowID int identity (1,1)
,FactName varchar(200)
,Fact varchar(200)
)

Insert into #IM (FactName,Fact)
Select distinct FactName,
Replace(FactName,replace(@FactTable_Convention,'%',''),'')
from #MasterList


Create table #Dim
(
DimID int identity (1,1)
,DimKeyName varchar(200)
,Dimension varchar(200)

)

Insert into #Dim
(
DimKeyName
,Dimension
)
Select distinct case
when SKName like '%Date%'
then 'Date dimension'
else SKName
end,
case
when Replace(SkName,replace(@DimSK_Convention,'%',''),'')like '%Date%'
then 'Date dimension'
else Replace(SkName,replace(@DimSK_Convention,'%',''),'')
end
from #MasterList

Select @intRow = min(DimID)
,@intRows = max(DimID)
from #Dim

While @intRow <= @intRows --iterate Dim columns
begin

select @Query = 'Alter table #IM add ['
+ Dimension
+ '] varchar(5)'
from #dim
Where DimID = @intRow

--Print @Query
exec (@Query)

-- now check in which tables it occurs
Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39)
+ @IM_UsageIndicator
+ char(39)
+ ' from #IM IM join (select FactName from #masterlist where SKName= '
+ char(39) + DimKeyName + char(39)
+ ') List on IM.FactName = List.FactName'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39) + char(39)
+ ' Where ['
+ Dimension
+ '] is null'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @intRow = min(DimID)
from #Dim
Where DimID > @intRow

End

Alter table #IM
drop Column RowID


Alter table #IM
drop Column FactName

select *
from #IM

----Print courtesy of http://my-pc-hates-me.blogspot.com