Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Could design of the model cause performance issues ?

Hey there, 

model.png

 

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!

9 REPLIES 9
Bibiano_Geraldo
Resolver I
Resolver I

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
Not applicable

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
Resolver II
Resolver II

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
Not applicable

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.. 

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
Super User
Super User

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?  

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.