Optimizing text search in DAX
HTML-код
- Опубликовано: 7 сен 2024
- How to optimize a text search operation in DAX that uses the contains condition in the filter pane or the filter mode of the Smart Filter Pro custom visual.
Article and download: sql.bi/797173?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
Thank you for another great video and article! I have a question: I followed the method described and it seems working fine when I do always the same search. However, as soon as I do a different search criteria it takes always several seconds. Does that mean the index is not being applied?
It depends, can you post a repro in a comment to the article on www.sqlbi.com/articles/optimizing-text-search-in-dax/ ?
@@SQLBI I think I already understood what is happening.
Just to give you some context: I have a requirement to have a Search functionality similar to a Google Search where the user should be able to search keywords through several fields.
To do that, I have a huge text field where I'm concatenating several different fields. Taking the Contoso Model as an example, I'm concatenating the Order Number, Order Line, Customer Key, Customer Name, Customer Address, Customer City, Customer Zip Code, Product Key, Product Name, Product Manufacturer, Store Key, Store Name and Store State in the Sales table (doing merges in Power Query) and after that I'm applying the transformation to have just the ASCII characters.
Then I have done a test using the Contoso 100k DB and the index is created and works regardless the search criteria I'm using. However when I use the Contoso 1M DB, the index creation fails due to the 25s limit.
Unfortunately my table has around 1,2M rows, so I won't be able to create the index. For the moment I'm going to try to implement the report in a way that the user needs to do some filter (like country) first before do the search which seems guarantees a better performance.
Awesome video as always.
Will this optimization work with the normal power bi slicer as well which has a dropdown and shows the list of values?
Not sure which advantage you look for with a regular Power BI slicer - if internally it generates DAX code such as CONTAINSSTRING yes, but it's not clear the scenario you are describing.
Thanks for the explanation, have a question, would this be applicable on import models only. How about text searches on direct query? Would it be applicable or this kind of optimization is not suitable for direct query/live connections
It's only for import models. For DirectQuery, you should look to SQL optimization on your server.
Is the index unique to each user? You mention one solution might be to go in and run a query after the refresh. If I go into the Pbi service and run a query. Do the column index for all users? Is this the same way for other chached data or am I not understanding. Thanks for the video
No, the index is shared, security is guaranteed by RLS.
Can I create a search engine in Powerbi?
I believe, report cache will be refreshed every hour in Power BI. So running the first query after dataset refresh will not help always. By the way, is there a way to check if the column is ASCII based or not? So that, it can be handled in the transformation layer. Thank you 👍
Report cache does not interact with storage engine cache, they are completely unrelated.
@@SQLBI Agree, they're independent. So, the first query has to be made available in SE cache instead of having in the report cache.