data partitioning in SQL server using XMLA end points

[Anonymous]

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

[MattCalderwood]

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