For someone like me dealing with a pbix file with 8 facts taking in more than a million rows per day and with 25 dims and watch the size of the model grow each day wondering when it will be large enough not to fit in memory, these tips are Priceless..
Really great video ! I use this at work ! However, I did the same operation on a decimal column that’s encoded in HASH. After this operator, the column is encoded in HASH one more time. I don’t really understand. I ask myself if someone has an explanation or theory about this. Thanks a lot
Great as always! One thing I want to add is that I do a step further after deleting the unnecessary columns. If you do that from the Power BI interface might introduce duplicate rows on the fact table. So I prefer to do it in Power Query and group by so I guarantee that I don't have duplicate rows. So in the end I might have a even more compressed model. But the main reason I do that is because of context transition problem when you call it over a table that contains duplicate rows. Especially with the new composite models, because you can't always control how other people will create measures!
Primary Key in a Fact Table in a Tabular model is bad. If you are worried about the duplicate values in case of context transition, then just remember never initiate Context Transition on a Fact Table. :)
Hi Marco, This is brilliant as usual! Does disabling the MDX property on all foreign keys and Dimkeys have any side effects, as these keys mostly will not be used in measures/filters?
Great video, Marco. Will there be a video also for for sorting fact table, segmentation (DefaultSegmentRowCount) for maximizing data compression instead of the dictionary?
It's too specific for very large databases (> 10GB) and usually it does not worth the effort. I don't see much value for that, it's hard to replicate in small models.
Hi Marco, I have changed this column encoding to Value from HASH with help of tabular editor, but this result surprised me , tha data size no getting double while comparing with previous size.
If we had already selected data type as fixed decimals already then would the VertiPaq have chosen the correct encoding mode (Value type) for these columns?
It depends on data distribution. Integer and fixed decimals number can be compressed using hash encoding if the algorithm establishes that it would be convenient. Because the algorithm decides based on the first rows of the table, it might be wrong. Encoding hints could be useful in that case.
Thank you for this valuable insight! I tried to apply this tips on a high cardinality column with hash encoding. I set the column to fixed decimal and hint to value. Then I refresh data. But after refreshing DAX Studio, I still have hash encoding. I guess the engine still thinks it’s better but how to know when hint will work or not without trying it for each column? Is there a way to force value encoding in order to see compression and performance impacts? Numerical values shouldn’t always been encoded with value? Thanks for any reply.
A hint is a hint, not an enforcement. For example, if a column (int or currency) has values that cannot fit in 32 bit, the encoding is always hash. For floating point, if the number cannot be represented in a 32-bit integer applying simple transformations applying power of 10, you get always hash encoding.
Hi, Marco, want to second this comment after trying out in my data model. I am just wondering if there’s any safe way to enforce the encoding to be VALUE? Thanks! 🙏🏻
The column in the data model is not getting refreshed. You will have to close the pbix and then reopen it for it to work. This is a bug in power bi which came with the march update.
@@SQLBI Please load any new table into the existing pbix and go to the Data model tab, you will see that the table is not present in the data model until you close and open the pbix once again. Please check.
@@SQLBI In SSAS tabular, I got an error when changing this property for a column that is used to sort another column. Error massage is: Column in table has the SortByColumn property set to an invalid column ID.
Would it have helped/worked, if instead of changing the Unit/Net Price to Fixed Decimal Number we keep it as Decimal Number only and reduce it to 2 decimal places?
Yes, the result should be similar in terms of granularity, but if you need to remove the dictionary you are usually more effective by using the Fixed Decimal Number.
It depends. Fixed Decimal Numbers have a limited range compared to Decimal Number. If the behavior of Fixed Decimal Number fits your requirements, of course you should use them. But pay attention to the limitations. See this: www.sqlbi.com/articles/choosing-numeric-data-types-in-dax
For someone like me dealing with a pbix file with 8 facts taking in more than a million rows per day and with 25 dims and watch the size of the model grow each day wondering when it will be large enough not to fit in memory, these tips are Priceless..
You just completely blew my mind, Marco. Outstanding work, as usual!
Gold! Thanks Marco. Excellent content and demo.
Cool, already knew about the fixed decimal (also from sqlbi), but now we have added insights about tabular editor and the hierarchy sizes.
Awesome stuff Marco. Love these sessions.
The 5 minutes to wow seems to far away. As always, great content and explained so well. Thanks.
Really great video !
I use this at work !
However, I did the same operation on a decimal column that’s encoded in HASH.
After this operator, the column is encoded in HASH one more time.
I don’t really understand.
I ask myself if someone has an explanation or theory about this.
Thanks a lot
Amazing explanation and approach. Congrats :)
Awesome! Thanks, Marco!
Great as always!
One thing I want to add is that I do a step further after deleting the unnecessary columns. If you do that from the Power BI interface might introduce duplicate rows on the fact table.
So I prefer to do it in Power Query and group by so I guarantee that I don't have duplicate rows.
So in the end I might have a even more compressed model. But the main reason I do that is because of context transition problem when you call it over a table that contains duplicate rows.
Especially with the new composite models, because you can't always control how other people will create measures!
Primary Key in a Fact Table in a Tabular model is bad. If you are worried about the duplicate values in case of context transition, then just remember never initiate Context Transition on a Fact Table. :)
This is great! Thanks for your books and videos.
Hi Marco,
This is brilliant as usual!
Does disabling the MDX property on all foreign keys and Dimkeys have any side effects, as these keys mostly will not be used in measures/filters?
There should be no side effects, as long as you don't use them to filter data (filters on other columns of the same dimensions are not affected).
@@SQLBI Will this affect the performance of crossfiltering though relationships tho ?
no, no effects on cross filter.
Great video, Marco. Will there be a video also for for sorting fact table, segmentation (DefaultSegmentRowCount) for maximizing data compression instead of the dictionary?
It's too specific for very large databases (> 10GB) and usually it does not worth the effort. I don't see much value for that, it's hard to replicate in small models.
Hi Marco,
I have changed this column encoding to Value from HASH with help of tabular editor,
but this result surprised me , tha data size no getting double while comparing with previous size.
Indeed, the engine usually does the best choice!
Valuable lesson Marco. Thanks
If we had already selected data type as fixed decimals already then would the VertiPaq have chosen the correct encoding mode (Value type) for these columns?
It depends on data distribution. Integer and fixed decimals number can be compressed using hash encoding if the algorithm establishes that it would be convenient. Because the algorithm decides based on the first rows of the table, it might be wrong. Encoding hints could be useful in that case.
Excellent Marco!
Minute 2 of the video: Is there any way to identify which columns are not used in the model so that I can remove them?
What about the columns with string data type , can we change them from hash to value
You cannot, string must be hash.
Great info Marco.
Thank you for this valuable insight! I tried to apply this tips on a high cardinality column with hash encoding. I set the column to fixed decimal and hint to value. Then I refresh data. But after refreshing DAX Studio, I still have hash encoding. I guess the engine still thinks it’s better but how to know when hint will work or not without trying it for each column? Is there a way to force value encoding in order to see compression and performance impacts? Numerical values shouldn’t always been encoded with value?
Thanks for any reply.
A hint is a hint, not an enforcement. For example, if a column (int or currency) has values that cannot fit in 32 bit, the encoding is always hash. For floating point, if the number cannot be represented in a 32-bit integer applying simple transformations applying power of 10, you get always hash encoding.
Hi, Marco, want to second this comment after trying out in my data model. I am just wondering if there’s any safe way to enforce the encoding to be VALUE? Thanks! 🙏🏻
The column in the data model is not getting refreshed. You will have to close the pbix and then reopen it for it to work. This is a bug in power bi which came with the march update.
Which column? Did you try with April 2021 update?
@@SQLBI Please load any new table into the existing pbix and go to the Data model tab, you will see that the table is not present in the data model until you close and open the pbix once again. Please check.
Can the property availableInMDX = false, be used for sort by columns?
What do you mean? Please, can you clarify the question?
@@SQLBI In SSAS tabular, I got an error when changing this property for a column that is used to sort another column.
Error massage is: Column in table has the SortByColumn property set to an invalid column ID.
Thanks for the excellent video 👏
Would it have helped/worked, if instead of changing the Unit/Net Price to Fixed Decimal Number we keep it as Decimal Number only and reduce it to 2 decimal places?
Yes, the result should be similar in terms of granularity, but if you need to remove the dictionary you are usually more effective by using the Fixed Decimal Number.
@@SQLBI in that case would you suggest to always use Fixed Decimal for all numeric value columns or only currency columns?
It depends. Fixed Decimal Numbers have a limited range compared to Decimal Number. If the behavior of Fixed Decimal Number fits your requirements, of course you should use them. But pay attention to the limitations. See this: www.sqlbi.com/articles/choosing-numeric-data-types-in-dax
Encoding Hint optiion not available when connected to Analisys Services.
It depends on the version of SSAS but it has been there for a while. What is the version you are using?
@@SQLBI standard edition, version 13
It's AS2016, you need at least AS2017 (version 14).
I see. Thanks
Immortal...!!! Ex..