Key Table: Store data relationships between primary key values

[intapiuser]

Analytical Need 

A key table (click the link to download a demo Data Model) is a central table that stores all the relationships between the primary key values.

Modeling Challenge

The main advantages of using Key Table:

• It helps to tidy up a schema that has tables all over the place

• It resolved data modeling issues like Many to Many, tables circular joins, Slowly Changing Dimensions, etc.

• It allows better security control

• There are too many tables that have multiple fields connecting to each other (each table has a different number of key fields)
• Tables are connected to one another with a different level of granularity

The goal is to create a star (and sometimes snowflake) schema with dimension and fact tables connected to a central key table. Unlike in classical data warehousing, the central table doesn't often have the measures - they generally stay in the fact tables.

Each table in the Schema, with some exceptions, is linked to the central key table and to the Key Table only.

The exceptions are generally some lookup tables that are not really relevant to have associated with other tables. For example, in the schema below, we have dim_ProductSubcategory linked to dim_Product. It is not really appropriate or necessary to bring the ProductSubcategoryID into the Key Table (although it is possible) so we can leave it out and linked to dim_Product.

When all the tables are linked via the Key Table alone, this is generally called a "Star Schema". When we have other tables that remain linked off the main dimension tables (such as Division or Category), is often called a "Snowflake Schema".

Note: This table causes an added join between the facts & the dimensions. Since this table can get quite large (depending on the different key combinations in the data set), please perform performance tests to the dashboards that rely on this cube in order to make sure that performance is not impacted.

Image 1. Key Table schema (end result)

Solution

Step 1 - Make sure all the primary keys of the Dimension tables are unique

If a table doesn't have one unique key, derive one using a function like Rank.

Here is a simple example of how to test primary key uniqueness using custom SQL statement:

SELECT count(ProductID) AS [count] , Distinct_Count(ProductID) AS [Distinct_Count]
FROM dim_Product

Uniqueness is validated when the number of the distinct values to be equal to the number of rows within the dimension table:

Image 2. Uniqueness test

 Step 2 - Planning is caring!

Create a reference spreadsheet that maps all the Primary and Foreign keys that exist in the schema:

Step 3 - Create custom “Key” field in every Fact table.

Make sure to separate the foreign keys with +‘|’+ delimiter and replace the missing fields with ‘_’.

For example - "Key" field in fact_Orders :

IfString(isnull(tostring(VendorID)),'_',tostring(VendorID)) 
+'|'+ 
IfString(isnull(tostring((EmployeeID))),'_',tostring(EmployeeID)) 
+'|'+ 
'_' //CustomerID 
+'|'+ 
'_' //SalesPersonID 
+'|'+ 
IfString(isnull(tostring((ProductID))),'_',tostring(ProductID)) 
+'|'+ 
IfString(isnull(tostring((getyear(OrderDate)*10000+getmonth(OrderDate)*100
+getday(Orderdate)))),'_',tostring((getyear(OrderDate)*10000+
getmonth(OrderDate)*100+getday(Orderdate))))

Step 4 - Create the KeyTable that is uniquely identified by the “Key” field

Use a mixture of Concatenate and Join to generate the Key table using the existing data.

For example:

SELECT DISTINCT
Key, VendorID, EmployeeID, toint(null) AS CustomerID, toint(null) AS SalesPersonID,
ProductID, OrderDate AS Date
FROM [fact_Orders]
WHERE NOT isnull(Key)

UNION

SELECT DISTINCT
Key, toint(null) AS VendorID, toint(null) AS EmployeeID, CustomerID, SalesPersonID,
ProductID, OrderDate AS Date
FROM [fact_Sales]
WHERE NOT isnull(Key)

UNION

SELECT DISTINCT
Key, toint(null) AS VendorID, toint(null) AS EmployeeID, 
toint(null) AS CustomerID, SalesPersonID, toint(null) AS ProductID, Month AS Date
FROM [MonthlyTarget] 
WHERE NOT isnull(Key)

Step 5 - Make sure the the Key field of the Key table is unique

Image 3. Key Table uniqueness test

Step 6 - Create the relationships

• Link all the dimension tables’ primary keys to the associated foreign keys within the Key table

• Link the “Key” field within the Key Table to the fact tables’ “Key” field

Attachments

 - 9.8 MB - Key.ecdata