Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Table Relation Performance: ID (with lookupvalue) vs Text

I would like to know what your guys oppinions on a best practice model are? If I have two tables, say fact and dim that I want to create a relationship with, which one of the two is better:   a) c...
Show and Tell
Tips and Tricks
sayaliredij's avatar
sayaliredij
Icon for Solution Sage rankSolution Sage
5 years ago

It depends on number of rows in your model.

 

If you are dealing with small model , then joining text columns should be fine

But look up will create more complexity in the model and I think it will not gain any performance benefits. 

 

It is always better to use integers as relationship keys. which you can either try to create in power query or if you are using any database system then there. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for your answer. My question is for a large dataset, lets say millions of rows and multiple fact tables potentially.
    Power query isnt a fitting option since joining the dim to the fact based on a text column is usually very slow and not feasible. Say you have 3 dim tables, it doesnt make sense to join all these into fact just for numeric key.
    Does that mean using lookupvalue to get id for relationship is never a good option with big models?