Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am importing date from a datamart from a Snowflake datamart in a Power BI dataset and facing a problem:
The foreign keys in the Snowflake datamart are 18 digit hash keys.
Power Query imports these columns as decimal datatype, which has a max digits of 15.
So I am losing the last 3 digits, which makes my keys incorrect.
I know that underlying problem is that snowflake creates all integer datatypes internally as Number(38,0)
In Power Query the whole number datatype can store max 19 digits, so there would be no issue when I will be able to import the key columns as whole number.
The current workaround is to cast the keys in Snowflake to a text datatype, which is not preferred as the keys are of course used in the dataset as relations to the dimension tables and it is not a best practice to have keys and relationships based on text values.
Another option would be to make smaller hashkeys, but I am told that there will be risk of collisions
Is there any way to force Power BI to import the data as a whole number? (so before the last 3 digits are lost)
Any other thoughts?
Solved! Go to Solution.
Hi @PieterM - please read the following Power BI data types in relationships - Does it matter? (maxwikstrom.se)
It should be okay to convert to Text.
But here is the thing, I would look to change Snowflake. IMHO the larger key values especially in low cardinality columns are just going to consume more Compute time and take up more storage. Therefore cost $$$ more money.
Thanks @Daryl-Lynch-Bzy Interesting reply and link. I will test this too.
(And yes, assuming whole numbers are the best perfomance-wise because of it is broadly seen as best practice :-))
Hi @PieterM - please read the following Power BI data types in relationships - Does it matter? (maxwikstrom.se)
It should be okay to convert to Text.
But here is the thing, I would look to change Snowflake. IMHO the larger key values especially in low cardinality columns are just going to consume more Compute time and take up more storage. Therefore cost $$$ more money.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |