January 6, 2014 at 8:05 am
Hi, I'm given the task of creating a bus matrix on all the Fact & Dimension tables within our DW. I'm not real SQL knowledgeable. Is there a way to query all the facts & dimensions and show the relationships?
Thanks in advance for you help.
January 6, 2014 at 9:47 am
Is there a way to query all the facts & dimensions and show the relationships?
It's good to have a bus matrix although more typical to develop it to assist and guide design rather than to build after some of the architecture is in place. 🙂
Here's how I'd approach the task you have.
1. Identify each fact table and the business process it's capturing.
2. Identify each dimension table and the high level attribute being capture there.
3. Identify each of the dimension keys in each fact table.
4. Build the bus matrix.
Step One is manual--hopefully documented already somewhere. Since you're building what is essentially a design document, you may also want to capture information like the grain of the table, too. Step Two is also manual. For Step Three, if your warehouse design uses %ID fields for the keys--or some other regular naming convention--you can quickly identify those fields in the fact tables. That can be manual or using SQL along the line of what's below.
Step Three will give you what dimensions are used by the fact table, the basic information identified in the bus matrix for that process/dimension set. With that information in hand, you can build the bus matrix.
Declare @tabName varchar(50);
Declare @schema varchar(30);
Set @tabName = '';--Fact Table Name
Set @schema = '';--Fact Table Schema
With tab
As
(
select object_id
from sys.tables
where name = @tabName and schema_id = schema_id(@schema)
)
Select
c.name
From
sys.columns c
Inner join
tab t
On
c.object_id = t.object_id
Where
c.name like '%ID%'--Whatever foreign key pattern is used in naming the dimension table keys
;
January 6, 2014 at 2:28 pm
Thanks for your reply.
I ended up going with the following select statment and it works well. It gives me all the dimension keys for all the fact tables.
SELECT t.name AS table_name, c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name LIKE 'fact%'
and c.name like '%key'
ORDER BY table_name, column_name;
January 6, 2014 at 2:32 pm
Glad you were able to get the information--helpful naming conventions! 🙂
January 7, 2014 at 9:51 am
Hi KL25, In the example you provided above, is there a way to also pull in the dimensional table names on the same query?
Thanks,
January 7, 2014 at 11:23 am
I'm not sure this will capture everything, particularly if you have multi-column keys, but it should help point you in the right direction. There are probably more complete & elegant ways to try to capture this information. For what you're trying to do, you might want to read more about system views in Books Online. These views are quite helpful when you need information about your database objects.
/*
Assumptions:
Fact Table naming convention is 'Fact%'
Dimension tables have defined primary key constraints
The naming convention for the Dimension keys is '%ID'
Fact table schema needs to be defined for the query
*/
Declare @schema varchar(30);
Set @schema = '';
Select
t.name as FactName,
ccu.Table_Name as DimensionTableName,
c.name as KeyName
From
sys.tables t
Inner join
sys.columns c
On
t.object_id = c.object_id
Inner join
information_schema.constraint_column_usage ccu
On
c.name = ccu.column_name
Where
t.name like 'Fact%' and
t.schema_id = schema_id(@schema) and
c.name like '%ID' and
--Exclude the fact table from the dimension table list
ccu.Table_Name not like 'Fact%'
;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply