Logical Key - Part 1

Logical Key

Remember seeing a symbol like this ? Where did you see this ? I was conducting few interviews and I was surprised to see that many candidates did not know about it.

What is a logical key?

A logical key represents any column in a table for which you have defined an attribute and in case if there are multiple attributes from the same hierarchy, the key is shown only for the lowest level attribute.

Here I have two attributes; however Director is a child attribute of VP. Hence only the Director is having the logical key symbol.  Once i remove the relationship, both the attributes gets a logical key symbol.

 What is the signifiance of the logical key ?

MSTR join tables based on the attributes that have a key.  Lets look at this example. I have two tables, a Dimension table and tact table. I have two attributes - C1 and C2 and a fact - sales. The table i used in this example is shown below

C1_ID C1_DESC C2_ID C2_DESC
1 1A 1 2C
1 1A 2 2D
2 1B 3 2W
2 1B 4 2E
3 1C 5 2Q

C1_ID C2_ID SALES

1 1 10
1 2 15
2 3 20
2 4 25
3 5 10
I created two attributes C1 and C2 from the first table as lookup. I did not create a relationship between these two attributes.  The logical table looks like below.    

Here both the attributes have a key symbol. Lets create a report with C1, C2 and sales metric. The SQL for this report is shown below. 

select a11.[C1_ID] AS C1_ID,

         a12.[C1_DESC] AS C1_DESC,
         a11.[C2_ID] AS C2_ID,
         a12.[C2_DESC] AS C2_DESC,
         a11.[SALES] AS WJXBFS1
from ([FACT_TABLE_JOIN2] a11
        inner join [TABLE_JOIN1] a12
        on (a11.[C1_ID] = a12.[C1_ID] and
        a11.[C2_ID] = a12.[C2_ID]))

Why both C1 and C2 are appearing in the join clause ? This is because MSTR joins table based on the attributes that has a key. You can also say that MSTR joins tables based on the lowest possible child attribute. This lowest possible child attribute always have a key symbol.   Now, I defined C2 as a child of C1. Look at the change in key symbol and the SQL that is generated.  

Only C2 is having this key symbol. This is because C2 is the child of C2.  The new SQL is shown below. Now the join happens only using the attribute C2.   select a11.[C1_ID] AS C1_ID,
          a12.[C1_DESC] AS C1_DESC,
          a11.[C2_ID] AS C2_ID,
          a12.[C2_DESC] AS C2_DESC,
          a11.[SALES] AS WJXBFS1
from ([FACT_TABLE_JOIN2] a11
          inner join [TABLE_JOIN1] a12
          on (a11.[C2_ID] = a12.[C2_ID]))