Perfomance issue while using Bookmark in Power BI Service

[JustinDoh1]

I am having a performance issue while I am using Bookmark (especially when I try running the report in PowerBI service).

This is related to my previous post.

I have four hierarchies of data that I am trying to present. The Fact table has little over a million row of data.

When it was three hierarchies, there was no issue with performance.

But, when it hits the fourth hierarchy and when I added a filter to visual, it is creating performance issue.

And then, when I clicked a Bookmark at PowerBI service for this visual (Matrix with four level of hierarchy with a a filter), visual crashes.

Bottom is screen shot of the error message:

I am trying to find a solution so that I could apply one more filter logic.

Here are my quetions:

1) Regards to using Bookmark, is there any difference of performance when Bookmark if it is used or not in PowerBI Service?

2) I am using Azure SQL on the backend (querying data from SQL View) pulling over 1 million rows of data. Should I consider using other database like Azure Databricks or some other storage? Actually, this is not something I could change as it is management decision 😞

3) Is it better to use one table with duplicate rows of data to aggregate data or use a separate dim table (for data like person's name & its ID)?

4) I tried Power Query to aggregate data. But, I ended up Grouping all columns we had before Grouping because I need to show all columns in order to leave the relationships with other tables that I had earlier. So, there is no point as all count is 1 (after trying Grouping all columns).

Is there any advice?

[JustinDoh1]

@lbendlin  

I tried using DAX studio and ran the query after watching few YouTubes, but I am not sure how to make the the performance better.

Also, how do I go about fixing/evaluating the "cardinality of the hierarchy levels" ?

Thanks. 

[JustinDoh1]

@lbendlin Just want to follow up on my questions:

I tried using DAX studio and ran the query after watching few YouTubes, but I am not sure how to make the the performance better.

Also, how do I go about fixing/evaluating the "cardinality of the hierarchy levels" ?

Thanks. 

[lbendlin · Accepted solution]

Did you watch the "How to optimize your DAX queries" videos?  They go through the process in detail, and show you alternative code that pre-filters your data more efficiently.  Reducing cardinality as early as possible is the key to success.

[JustinDoh1]

@lbendlin I have a question. Does Bookmark's report have any issue with performance (meaning if the query comes from Bookmark, is it usually slower than other visual (not on Bookmark))? 

[lbendlin]

The answer is a definite maybe.  If your bookmark calls up a poorly behaving visual and/or a bad filter combination then of course it will have an impact.

Bookmarks are only memories - they record your meta data choice at a point in time.  They do neither participate in the rendering nor in the caching.

[v-mdharahman]

Hi @JustinDoh1,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like able to work on your issue with the solution provided by @lbendlin. If it solves your issue then please mark the helpful reply and accept it as the solution. 

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[JustinDoh1]

@v-mdharahman I have not gotten solution yet. I have been waiting for his response on my latest question. Thank you.

[JustinDoh1]

@lbendlin  Thank you so much. I will take a look deeper and let you know if I have quesiton(s).

[lbendlin]

Use DAX Studio to examine the query that is created each time. What's the cardinality of the hierarchy levels?

[JustinDoh1]

@lbendlin  Thank you for help.

- I am not sure how to 'examine the query that is created each time'?

I have a SQL View and all four hierarchies come from same table.

- How do I find the "cardinality of the hierarchy levels"? Sorry. I am new to this concept. 

Thanks.

[lbendlin]

This should get you going

DAX Query Plans

Look out for excessive number of records. Those indicate likely full cartesians, stuff that usually leads to exceeding the resources.