What to do about really long GUIDs?

[thisisausername]

We have long GUIDs in our system like 34613390-3bdc-e911-b076-00155d6c9e03

I'm guessing these columns are 'expensive' because they have high cardinality, but they're used in all our relationships in the model.

Is there any way around this? I read about removing such ID columns from your model but how does that work when these columns are all used in relationships?

[vanessafvg]

you could always create a lookup table and assign an integer index to them  and then just reference them through the int index via a table relationship if you have a very big transactional table.  How many are there?  There is best practice, but also has to weigh this against requirements.  

[thisisausername]

Tables are varying sizes - but our biggest with two GUIDs has about 1.5M rows. 

Do you have any documentation to read about that pattern? Tried to give it a quick google but couldn't find anything

[vanessafvg]

the positive about working in a tabular model which is what power bi is compression of duplicate values, so it may not be a huge issue, just depends on where your compute is coming from and how well resourced you are.    Obviously its good to follow best practice because your model isn't static, it just depends on what you planning on doing with it, it is a one off, are you going to productionise it, where will it be update from.   So many variables really.   But answers to those questions should help steer you.

[vanessafvg]

this might also be useful

https://www.element61.be/en/resource/microsoft-power-bi-tabular-model-optimization

[thisisausername]

Thanks Vanessa.

The 1.5M row table has duplicates of the GUID: for each GUID there are rows per each day.

I therefore had to create a composite ID because I needed a unique ID for the daily grain of the data, so I combined the GUID plus the day. This probably didn't help the overall size of the model but I couldn't think of another way to do it.