In this blog post I am looking at how to load or reload historical data in AAS and PPU and compare the differences.

It should already be noted that I am only going to compare tables where I have partitions created and enabled. The reason being for dimension tables it is typically quick and easy to reload the data by re-processing the data for the table.

Here are the previous 4 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

4- Incremental Refreshing – Part 4 – Migrating AAS to PPU – Reporting/Analytics Made easy with FourMoo and Power BI

When having a lot of data over a period it requires more thoughts and effort to get this completed.

Whilst there are multiple ways to complete this, I am going to compare reloading historical data using SQL Server Management Studio (SSMS) to compare AAS vs PPU

Loading Historical data in AAS

When doing this in AAS, this can be done via SQL Server Management Studio, or via the Asynchronous REST API (Which could be automated via Power Automate, if I don’t mind the pun!)

  • The first thing I did was to connect to my AAS Cube using SSMS
  • I then went to the table where I had already configured my Partitions
    • I right clicked the table and selected Partitions
    • I could then see my partitions as shown below
      • NOTE: In the example above, I have not processed them yet, that is why there are zero rows and the last processed date is NEVER
    • I then went and processed the partition by clicking on the Process button.
      • I like to script this out to SSMS, so that I can see what is happening during the processing. If there are any errors, it is easier to view.
      • I clicked on the Script button.
      • I could then see the data in the messages section in SSMS
      • And once completed I could then see it successfully processed the data.
    • I could then process other partitions or multiple partitions at once.
    • This allows me to then load or re-process historical data in AAS

If you are interested in loading AAS data via the Asynchronous REST API here is the link: Asynchronous refresh for Azure Analysis Services models | Microsoft Docs

Loading Historical data in PPU

When loading or re-loading historical data for PPU, this can be done via SSMS or Tabular Editor.

  • The first thing I did was to connect to my AAS Cube using SSMS
  • I then went to the table where I had already configured my Partitions
    • I right clicked the table and selected Partitions
    • I could then see my partitions as shown below
      • NOTE: In the example above the partitions look different because this is managed by the PBI Service
    • I then went and processed the partition by clicking on the Process button.
      • I like to script this out to SSMS, so that I can see what is happening during the processing. If there are any errors, it is easier to view.
      • I clicked on the Script button.
      • I could then see the data in the messages section in SSMS
      • And once completed I could then see it successfully processed the data.
    • I could then process other partitions or multiple partitions at once.
    • This allows me to then load or re-process historical data in PPU

Please note at this point it time it is not possible to use the Asynchronous REST API for PPU, but it is on the roadmap and will be completed sooner than later which is awesome news!

Summary

In this blog post I have shown how to historically load data for your partitioned tables.

As you can see, they work in a very similar way and when comparing AAS to PPU. This is great to see as it means that there is less friction or challenges to deal with when moving from AAS to PPU

Thanks for reading and all comments are welcome!