Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhenry
New Member

About the called "Link Table" and "Lookup Table"

I searched about all possible types of tables and I didn't understand 2 types, the "Link Table" and "Lookup Table".

Illustrations of each:

 

1) Link Table

 Link Table 1

 2) Another Link Table

Link Table (2)

 

3) "One lookup table for each attribute"

Lookup Table (2)

 

4) "One lookup table for all attributes"

Lookup Table (2)

 

 

So, someone understand this concepts? Someone can explain them for me?

 

Thanks a lot!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @jhenry,

 

For #1, it means use relation table to manage the relation between key/value table and fact table will be suitable.


#2 is a relation table which used to link 'many to many' relationship tables.(you can't direct create relationship between 'many to many' tables)

 

#3 is a dictionary table which merge from key/value tables, you can simply use it to quick transfer key(id) to value(description). It often used to reduce database table redundancy.

 

BTW, dictionary table not need to be a real table, it can be a virtual table which used to quick lookup.(use table name and key/value to dynamic combination the search query string)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @jhenry,

 

For #1, it means use relation table to manage the relation between key/value table and fact table will be suitable.


#2 is a relation table which used to link 'many to many' relationship tables.(you can't direct create relationship between 'many to many' tables)

 

#3 is a dictionary table which merge from key/value tables, you can simply use it to quick transfer key(id) to value(description). It often used to reduce database table redundancy.

 

BTW, dictionary table not need to be a real table, it can be a virtual table which used to quick lookup.(use table name and key/value to dynamic combination the search query string)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This is a big topic. None of what I see above looks wrong to me, even the one marked wrong. My general advice

1. Opt for star Schema rather than snowflake where possible

2. Only use a lookup table if it has 3+ columns

3. Avoid junk dimensions. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.