Latest Enterprise DNA Initiatives


Let SQL Server do the heavy lifting!

Data Model
#1

I have Power BI report that takes more than 10 seconds to fully load each time it is filtered via slicers. The report has a table visual with many columns each using Measures.

My understanding is that an SSAS cube could help because all the calculations are then done in SQL Server and a Power BI report would simply need to access a resultant table or stored procedure allowing SQL Server to do all the heavy calculations.

My thought on this is that by replicating the Power BI data model in SQL Server, I could create a stored procedure with appropriate parameters, which would allow for the dynamic filtering. The challenge is that DAX cannot retrieve this stored procedure each time a user changes the slicer options.

I need some help in solving this challenge, hope you can help!

#2

@powerbideveloper Are you using DirectQuery? In this case you need to optimize your SQL Server for the queries received by Power BI. For example, creating appropriate indexes, or using aggregated tables in SQL Server.

#3

@AntrikshSharma,

I have not used DirectQuery before. My preference is to have the data model in Power BI with just the necessary heavy calculation done by the database, but I’m not sure what that dataflow would look like? I have heard that Power BI is now able to have composite models.

I don’t think the report sluggishness comes from calculating overall aggregations. It looks like it comes from listing all items in the table visual, at its most granular level while doing calculations over each record at that level. So, you have many records where each column has its own calculation for each record.

#4

Bumping this post for more visibility.

#5

Hi @powerbideveloper! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!