Partitioning dataset and Refreshing power BI

Amir_m 0

Hello, We had a dataset with 5 years data, then reduced the size to only 30 months data to improve performance. Now we want to automate Power BI refresh but getting an error: "This operation was canceled because there wasn't enough memory to finish running it." We have over 25 tables but only 2 of them are big in size (one with 10M rows and the other with 7M rows). I used Tabular editor for partitioning those 2 large tables with 1 year incremental period and 3 years RollingWindowPeriods (4 partitions in total, one for each year). Then, I tried refreshing each partition using PowerShell in Azure Runbooks. Part of the script is as follow: $Query ="{ ""refresh"": { ""type"": ""full"", ""objects"": [ { ""database"": ""Dataset_1"", ""table"": ""Orders"", ""partition"": ""2023"" } ] } }" However, when I refresh each partition, I still get memory capacity error. I changed partitioning from 1 year to monthly (30 partitions, each 1-month period) and I could refresh each partition separately. But it doesn't make sense because each partition (1 month) would take between 2 - 6 minutes to refresh and each monthly partition is only around 300,000 rows. *** I created a dataset spanning 12 months, which includes all tables without partitioning. The dataset refreshes successfully within 3 minutes without any errors, despite the fact that the largest table in the 12-month dataset contains around 3.5 million rows. But, when using yearly partitioning, I face a memory error in just one partition and for only one table. I don't know where I am going wrong. I really appreciate your ideas and comments on this.

Chris Webb 0 Reputation points Microsoft Employee

2024-02-22T16:45:50.45+00:00

Are you using Power Query to connect to your data source, and if so are you creating the filters for each partition by adding them in the M code for your partition? What data source are you using? One possible explanation is that the filters in your M code are not being pushed back to the source - "query folding" is not happening. If that's the case then all the data from your source is being returned to Analysis Services for each partition and the filtering is happening in the Power Query engine, which would then explain the poor performance and the memory spike.
Chris Webb 0 Reputation points Microsoft Employee

2024-02-22T16:48:46.0633333+00:00

Are you using Power Query to connect to your source, and if so did you implement the filters for each partition in the M code for each partition? What data source are you using? One possible explanation is that the filter in each partition is not being pushed back to the source - "query folding" is not happening. If that is the case then all the data from the source is being returned for each partition and the filtering is only happening inside Analysis Services engine, which would explain the poor performance and memory spike.
Amir_m 0 Reputation points

2024-02-22T17:34:11.52+00:00

Yes, we are using power query to connect to the data source and using SQL server (in Azure). For partitioning, I defined RangeStart and RangeEnd in Tabular Editor and copied the M-code for the particular table into Tabular Editor and added the RangeStart and RangeEnd in last step of the M-code. So basically I didn't make any changes in power query, I did it only in Tabular Editor. I published the dataset without any changes, then made all the changes in Tabular Editor. As a test, I defined RangeStart and RangeEnd in power query and updated the M-code and "View Native Query" was active, so I think query folding is happening. Also, I defined monthly partitions (30 partitions) and started refreshing each one in Azure Runbooks using PowerShell script. Almost 20 partitions refreshed successfully but the remaining ones failed due to memory capacity limit. Interestingly, some of the failed partitions were even smaller in terms of row count compared to the successfully refreshed ones. And even those that refreshed successfully took around 2-3 minutes to refresh (each partition around 250K rows). I'd like to add some additional information. In our database, we have two large tables that collectively account for 57% of its size. One of these tables is the parent, and the other is its child. Before implementing partitioning, I attempted to define RangeStart and RangeEnd in Power Query for the date column of the parent table. However, this caused the loading process from Power Query to Power BI Desktop to endlessly spin without completing. To resolve this issue, I applied the parameters to the date column in the specific child table (the second largest table), which allowed both tables to load successfully into Power BI Desktop. Thanks again for your help. Much appreciated.
Chris Webb 0 Reputation points Microsoft Employee

2024-02-22T18:23:37.64+00:00

Can you share the M code you're using? Are you using a SQL query as a data source or are you pointing direct to a table or view?

Amir_m 0

Yes, we are using SQL query as a data source.

//Parent table

let    

Source = Sql.Database("uh4c3ekhom.database.windows.net", "all-time"),    
dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_Orders,{"Medias", "Notes", "Responses", "Status"}),   
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([DataState] = "I")),    
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"Email", "Resolved", "Repeat", "Index", "Override", "Reminder", "Escalation", "CreateTimestamp", "ModifiedTimestamp"}),    
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each [CreatedOn] >= RangeStart and [CreatedOn] < RangeEnd)

in    

#"Filtered Rows"


//Child table
let
    Source = Sql.Database("uh4c3ekhom.database.windows.net", "all-time"),
    dbo_OrderDetails = Source{[Schema="dbo",Item="OrderDetails"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_OrderDetails,{"CreateTimestamp", "Id", "CreatedBy", "ModifiedBy", "ModifiedTimestamp"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [CreatedOn] >= RangeStart and [CreatedOn] < RangeEnd)
in
    #"Filtered Rows"

Amir_m 0

Yes, we are suing SQL query as a data source.

//Parent table

let    

Source = Sql.Database("uh4c3ekhom.database.windows.net", "all-time"),    
dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_Orders,{"Medias", "Notes", "Responses", "Status"}),   
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([DataState] = "I")),    
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"Email", "Resolved", "Repeat", "Index", "Override", "Reminder", "Escalation", "CreateTimestamp", "ModifiedTimestamp"}),    
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each [CreatedOn] >= RangeStart and [CreatedOn] < RangeEnd)

in    

#"Filtered Rows"

//Child table

let
    Source = Sql.Database("uh4c3ekhom.database.windows.net", "all-time"),
    dbo_OrderDetails = Source{[Schema="dbo",Item="OrderDetails"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_OrderDetails,{"CreateTimestamp", "Id", "CreatedBy", "ModifiedBy", "ModifiedTimestamp"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [CreatedOn] >= RangeStart and [CreatedOn] < RangeEnd)
in
    #"Filtered Rows"

Chris Webb 0 Reputation points Microsoft Employee

2024-02-23T10:11:29.4833333+00:00

That looks OK to me - there's nothing there that should stop query folding from happening as far as I can see. Can you look at the SQL queries that are being generated when a refresh runs and check that they include the filters in their WHERE clauses that you would expect, and that they perform well?
Amir_m 0 Reputation points

2024-02-26T18:55:56.9866667+00:00

I defined RangeStart and RangeEnd along with an incremental refresh policy in Tabular editor to create 4 partitions (each representing one year) for a specific table. I tried to refresh each partition using a PowerShell script. The Partition for 2024 refreshed (after 5 minutes) and I reviewed the generated SQL queries in server profiler and the last batch query included the WHERE clause specifying the range for the last partition (01-01-2024 until 01-01-2025). But, when I attempted to refresh the partition for 2023, it failed due to reaching the memory capacity limit. Also I tested refreshing a small table (without partitions) using powershell script in runbooks and the refresh time was around 7 minutes which doesn't make any sense. Could it be an issue with Runbooks powershell? I also tried to create custom partitions by applying a filter to a date column in a specific table and copying the M-code to the Tabular editor. After refreshing each partition using a PowerShell script, I faced another error indicating a duplicate value in the Id column. This error says that duplicate values are not permitted for columns on the one side of a many-to-one relationship or for columns used as the primary key of a table. But, I verified the database and PBI dataset and there is no duplicate values in there. Thanks again for your time. Much appreciated.
Amir_m 0 Reputation points

2024-02-27T04:03:59.56+00:00

Can I refresh each partition individually based on changes detected in the data, using the insights from this article? https://blog.crossjoin.co.uk/2022/07/31/custom-queries-for-detect-data-changes-in-power-bi-incremental-refresh/. For instance, I plan to automate the refresh process for each partition using Azure Runbooks and Logic Apps. If, for example, the partition for the year 2020 experiences any data changes, it will trigger a refresh; otherwise, it will remain unchanged?!
Chris Webb 0 Reputation points Microsoft Employee

2024-02-28T10:52:39.2633333+00:00

If the SQL queries that are generated have the WHERE clauses you would expect to see for each partition, but they are still slow, then the problem lies in the SQL Server database - you need to ask a DBA to tune the queries that are being generated.

1 answer

Lucas Sousa da Silva 75 Reputation points

2024-02-20T23:51:22.27+00:00

Check Memory Usage: Ensure that your environment has enough memory available to handle the refresh process, especially when dealing with large tables and partitions. You may need to allocate more memory or optimize the memory usage in your Power BI setup. Optimize Partitioning Strategy: Instead of monthly or yearly partitions, consider using a hybrid approach. For example, you could have quarterly partitions for the large tables and monthly partitions for smaller tables. This way, you balance the refresh performance and memory usage. Incremental Refresh: If your data allows it, consider using incremental refresh to load only the new or changed data into the dataset. This can significantly reduce the amount of data that needs to be refreshed, improving performance and reducing memory requirements. Check for Bottlenecks: Identify any potential bottlenecks in your refresh process, such as complex transformations or calculations, and try to optimize them to reduce memory usage. Error Handling: Implement robust error handling in your PowerShell script to handle memory capacity errors gracefully, such as retrying the refresh or logging the error for further investigation. Check for Updates: Ensure that you are using the latest versions of Power BI, Tabular Editor, and any other related tools, as updates often include performance improvements and bug fixes. Consider Parallelism: If your environment supports it, consider running multiple refresh operations in parallel for different partitions or tables to distribute the workload and reduce the risk of memory capacity errors.

By optimizing your partitioning strategy, memory usage, and error handling, you should be able to improve the performance and reliability of your Power BI refresh process.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment