Welcome to the fourth instalment of my series on migrating AAS to PPU.

In this blog post I am going to cover the differences, and pros and cons to incremental refreshing when migrating from AAS to PPU.

The reason for incremental refreshing is that I do not have to reload the entire fact table every day when new data arrives.

This also allows for new data to be updated quicker.

And finally, it also uses less resources (memory and CPU) when refreshing the datasets.

Here are the previous 3 blog posts that I have completed in the series.

1 – Query Performance – Part 1 Migrating Azure Analysis Services to Power BI Premium Per User – Reporting/Analytics Made easy with FourMoo and Power BI

2 – Scalability – Part 2 Migrating AAS to PPU – Reporting/Analytics Made easy with FourMoo and Power BI

3 – Data Loading – Part 3 | Migrating AAS to PPU – Reporting/Analytics Made easy with FourMoo and Power BI

How to get Incremental Refreshing working in AAS

When working with AAS, if I wanted to create a table with incremental refreshing this had to be done with some type of automation.

Fortunately, the amazing helpful Christian Wade created AsPartitionProcessing

Now for me to get AsPartitionProcessing working it still required the following steps highlighted below.

• Configuration of the Application, database, and deployment of the EXE
• Configure the tables for the Model, Tables, and Incremental processing policies.
• Testing to make sure the incremental refresh works as expected.
• Scheduling to update the incremental refresh policy.
  • In my customers environments I have done this using Azure Functions.

A working example would be the following to configure the Incremental Refresh for AAS

• Keep data for the past 2 years.
  • To do this I had to configure 2 Partitions.
    • The one partition is for the past 29 Months.
    • 
    • The second partition is for the current month, to enable the data to be processed daily, which is for the past 2 days’ worth of data.
    • 
• I then had to also configure a process to merge the daily partitions to monthly at the end of each month.
  • This had to be managed with multiple TSQL Scripts to update the tables.
• And finally, I had to schedule multiple Azure Functions to run at the right time to process either the daily data, or to merge the monthly data partitions.

Processing historical partitions

Whilst it is not often required, there are times when historical partitions need to be reprocessed.

When doing this via AAS, there is the capability to use the REST API with an asynchronous process which allows the refresh to continue without having a continuous process.

More details on this can be found here: Asynchronous refresh for Azure Analysis Services models | Microsoft Docs

I have configured this using Power Automate to allow users to an HTTP URL to refresh the partitions they require without any intervention from myself or something that someone must do manually.

By configuring the processing of historical partitions via an HTTP URL, it allowed me to automate this process allowing me to put in some parameters and let it refresh all the partitions I required.

Pros/Cons using AsPartitionProcessing

As with everything there are pros and cons as listed below for AsPartitionProcessing.

• Pros
  • Flexibility to configure the incremental refreshing any which way is required by the organization.
  • Can automate the updating of partitions to keep using TSQL.
  • The capability to use the REST APIs to refresh specific partitions.
• Cons
  • Significant effort and potential complexity to set up the configuration.
  • Setting up a process to schedule running the EXE multiple times for different configurations.
  • Monitoring of processes to know when they are failing.

Incremental refresh configuration in PPU

To configure the incremental refresh in PPU, this is done in Power BI Desktop or can also be altered using Tabular Editor (v2 or v3)

For me to configure this all I need to do is the following shown below

As shown above this is significantly easier and really simple to configure.

All the complexity is managed by the Power BI team, how cool is that!

Pros/Cons using PPU

As with everything there are pros and cons as listed below for using PPU.

• Pros
  • Easy to use and understand how to configure the refresh policy.
  • Use a GUI to put in the incremental refresh policy.
• Cons
  • Currently not possible to update programmatically (Has to be done via SSMS or Tabular Editor)
  • Currently cannot refresh a partition via API (But this is coming, but not currently available at the time of this blog post June-2021)

Summary

In summary this shows that when migrating from AAS to PPU it is significantly easier to manage and look after the incremental processing.

Yes, there are a few gaps but those will be closed soon enough.

This is another example why moving to PPU will ensure less overhead and management of your Power BI datasets.