Could design of the model cause performance issues ?

[Anonymous]

Hey there, 

This is the model I'm working on D for Dimensions and F for Facts, I was searching what could impact the performance
because in power bi we have 10 tabs and most of the tabs have visuals that takes 5-12 seconds response time.

we use AAS model and queries in ssms taking less than 4 minutes in general. I don't think we have an issue there, bu the model design itself could be re-designed ? or without making this HUGE change how can we improve the performance for end-users.

ask me any question 
thanks!

[Bibiano_Geraldo]

Yes, the chosen design can impact your performance. However, you can enhance it by following these recommendations:

1. Whenever possible, merge or append tables in Power Query instead of establishing relationships in Power BI. Then, disable the table load in Power Query for that dimension table. This practice will reduce your .pbix file size.

2. Minimize the use of calculated columns whenever feasible. Opt for measures instead, as calculated columns increase your .pbix file size and may contribute to performance issues.

3. Opt for explicit measures over implicit measures for a slight performance boost.

4. Streamline your report page visuals by utilizing parameters. For instance, if you have visuals displaying sales by country and city, consider creating parameters for country and city. Use a slicer to dynamically change these parameters, eliminating the need for separate visuals.

If these suggestions prove helpful, kindly mark this response as the solution.

[Anonymous]

I'm using AAS model, so we have pbit that is connected via direct query mode. This limits whatever its working for pbix file. I'm working to improve the performance for the first time so it's very confusing even the details you gave me : /

[khaledHabib90]

A few suggestions to improve performance:

1- if you have a premium workspace, publish there rather than "My workspace"

2- if it is already on a premium workspace, consider increasing its capacity of the report is too big, or consider removing old big reports if the capacity is crowded

3- decrease number of visuals on the page

4- decrease complexity of visuals

5- consider simplifying your measures and moving to calculated columns, for example, iterators like sumx and averagex are slower in processing, measures calculate on visuals load and with every interaction. But calculated columns are refreshed with the dataset. So the end user is not affected

If my reply helped you solve the problem please accept it as the solution 🙂

[Anonymous]

1) yes it is premium capacity, premium workspace
2) but we are having problem with only one report in the workspace, then what we should do ?
3) any guidence on that ? I take 3 measures from 3 different cards and put in one table, 2 of them are showing different records than in the cards ?
4) can you please elaborate that ?
5) meaning instead of using measures in the model, create these columns in the sql ?

I would like to accept as a solution, yet I need to see performance is improving.. 

[khaledHabib90]

for decreasing complexity of visuals:

decrease the measures in a visual, or decrease the granularity of the visual, for example if it is on an hourly basis, switch to a daily basis, or perhaps filter a smaller range.

of course the above solutions depend on your specific scenario and you might choose one or non based on your needs, I am just saying that they are ways to improve the performance of visual loading time

for point 5, I mean instead of creating a complex measure, create a calculated column, in the power BI model, using dax, you can also create them in SQL if thats easier for you, however doing it in DAX would be ok as well

If my reply helped you solve the problem please accept it as the solution 🙂

[vanessafvg]

that does look like a complicated model so yes that can definitely affect your performance

also the more visuals you have on a page the queries it will run.  when you say the queries in ssms is taking less than 4 minutes are you referring to query time or processing time?

it also depends on the dax queries you have. and if you have many to many or birectional relationships.   

have you run performance analyzer?  

[Anonymous]

yes, in ssms it's taking less than 4 minutes to run the query that creates the tables in the AAS Model.
Loading the model is automatically processed every hour, and I don't think it causing the problem. 
because you're right, I did performance analyzer and most of the pages have issues like visuals are responding 3 seconds 5 seconds 9 seconds, 13 seconds. That's why I'm trying to improve the performance

[vanessafvg]

can you share the details abut the report that is slow, what part of the model is it using, what are the measures being used,  in order to actually help you drill down on the issue you really need to provide more detailed information about where exactly you are seeing the biggest issues.

[Anonymous]

we are using AAS Tabular model, so it's connected to pbit file via direct query mode. That's why any changes is required to do in the visual studio not in power bi. As you see in the model design, 9 out of 10 pages are having multiple visuals are responding mostly 3-9 seconds some of them are 13-15, and one of them is 72. Im not sure what details you want but ask any questions as you please