Performance issues with pushing data to a tabular object model dataset

[khushidahiya]

We have an embedded powerbi solution in a .NET web app that allows users to visualize their data on-demand. We process data and have an in-memory data table within the app. We used to push this data to a powerbi push dataset but wanted extended functionality like localization of metadata and measures in our dataset so we switched to using XMLA endpoint-based Tabular Object Model datasets. To push data to the dataset we have been hardcoding the string JSON rows in M queries and creating the partitions, this has been bad for performance since the data refresh is slow and increases with the number of partitions that we push. 

Was hoping to get some advice on how to get the data into TOM datasets such that it is available quickly / has better data loading performance, similar to how it is with push datasets when pushing rows using the PowerBI Rest API. 

M expression being used in partitions: 

let Source = Text.ToBinary(\"{json}\"), ParsedJson = Json.Document(Source), JsonToTable = Table.FromRecords(ParsedJson) 

in JsonToTable

[123abc]

It's great that you've migrated to using XMLA endpoint-based Tabular Object Model (TOM) datasets for extended functionality in your Power BI solution. However, the performance issues you're experiencing with slow data refresh times can be addressed. Here are some tips to improve data loading performance:

1. Batch Data Loading:

   • Instead of pushing each row individually as a partition, batch the data and push it in larger chunks. This reduces the overhead of creating and managing a large number of partitions. It's more efficient to load a few larger partitions than many smaller ones.

2. Optimize Data Transformation:

   • Ensure that your M expressions are optimized for performance. Avoid complex transformations and calculations in your M queries. Minimize the number of steps and transformations, and make sure they are as efficient as possible.

3. Data Compression:

   • Consider optimizing your data for better compression. Use data types that allow for efficient compression, such as integers instead of strings for numeric values. This can reduce the size of your dataset, leading to faster loading times.

4. Data Modeling:

   • Ensure that your data model in Power BI is well-designed. Make use of relationships and hierarchies, and avoid unnecessary calculated columns and measures that can slow down data processing.

5. Aggregation Tables:

   • If your data contains a lot of detailed records, consider creating aggregation tables to speed up query performance. Aggregation tables store pre-aggregated data, reducing the need for complex calculations during query execution.

6. Use Partitions Efficiently:

   • Partitions should be used to divide the data logically, not as a means to create many small pieces of data. Use partitions to divide your data into manageable chunks, but avoid creating too many partitions.

7. Parallel Data Loading:

   • If you have a large volume of data to load, consider parallelizing the data loading process. You can split the data into multiple threads or processes to load in parallel, taking advantage of multi-core processors.

8. Incremental Loading:

   • If possible, implement incremental data loading. Only load the data that has changed since the last refresh, rather than reloading the entire dataset. This can significantly reduce loading times.

9. Hardware and Resources:

   • Ensure that your server and infrastructure have sufficient resources, including CPU, memory, and disk speed, to handle the data loading efficiently. Bottlenecks in hardware can slow down the process.

10. Monitoring and Profiling:

    • Use tools like Performance Analyzer in Power BI to profile and identify performance bottlenecks. This can help you pinpoint specific areas that need improvement.

11. Consider Automation:

    • If your data refresh process involves a lot of manual steps, consider automating it. This can reduce human error and ensure consistency in the process.

By implementing these best practices, you should be able to significantly improve the data loading performance of your Tabular Object Model datasets in Power BI. It's important to continuously monitor and optimize your data loading process as your data and usage patterns evolve.