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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

data partitioning in SQL server using XMLA end points

Not sure if this is the correct forum to ask this question.

I have data model published in the premium workspace. It has around 10 million rows of data and some reports are build on this and published to the premium workspace.

 

Some of the visuals are working fine but some are taking more time to refresh than expected. My workaround is to manually partition the data using the SQL Server.

I am using XMLA endpoints to connect to my dataset in the SQL server. This is the first time I am trying to do the partitioning so need help from the experts on how to do it and if its the best way to reduce the latency time in my reports.

 

Please guide me to any resource if you have any.

Thanks

1 REPLY 1
MattCalderwood
Responsive Resident
Responsive Resident

Hi @Anonymous 

It is my understanding that the main benefit of partitioning an in-memory Power BI model via XMLA - is to allow partial refreshes of data (faster refreshes where you only refresh the partitions you need). I am not aware of any performance benefits that would be made by partitioning your tables in this way (I could be mistaken though).

If you are struggling with a slow visual, then it is probably worth focusing on the query that is being run.
Accessing the Performance Analyzer will let you see the query being run. https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

I would then recommend putting the query though the excellent DAX Studio tool:  https://daxstudio.org/

It takes a bit of time to learn what DAX Studio is telling you - but it will highlight number of rows read, cache hits, and excessive CPU usage - and might lead you to where the underlying problem is.

 

Hope this helps, Matt

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.