User Review
( votes)Power BI pros share their tips on swapping GUIDs to surrogate keys, adding tooltips, displaying dates and changing queries to functions.
Changing out GUIDs to surrogate keys
Microsoft MVP Matt Allington, writing on the Excelerator BI blog recommended fellow users swap GUIDs out in-favor of surrogate keys. GUID is short for Globally Unique Identifier and refers to a hexadecimal number equivalent to a decimal digit with 39 digits. As a general rule, GUIDs are not the best way to join tables. Power BI supports a one-to-many relationship as a fundamental part of its underlying database structure. He wrote:
The Power BI engine (Vertipaq) materialises and stores these relationships into the database and then uses them to rapidly propagate filters from one table to another (via the 1 to many relationship). It is very common (even desirable) that the relationship logic is loaded in to fast L1 or L2 cache memory on your PC chip so that it can do its job super fast. If the relationship doesn’t fit in cache memory, then the whole process is going to be slower. Things that can drive up the size of the relationship are the number of unique values in the columns used in the relationship, but also the data type used in the relationship. Suffice to say, a GUID is way way less efficient that an integer value as a key column in Power BI.
Instead, users can substitute surrogates for the original key column, ideally built using a SQL Server back-end by an in-house IT team. However, a simpler approach involves Power Query. Users can connect to raw dimension tables, add an integer ID column as the surrogate and re-join the tables, wiping out the GUID with the merge.
Adding tooltips to column headers with Power Query Editor
FREE Membership Required to View Full Content:
Learn more about us here
