Convert Power BI datasets to Analysis Services

Converting your Power BI data model to Analysis Services can resolve some of your dataset challenges. Got a large data model (> 1GB)? Or need refreshes more than 8x per day? Using Analysis Services to host your data model can help! In this post I’ll show you a quick way to evaluate what things would look like if you moved your model to Azure Analysis Services (AAS), the cloud version of Analysis Services. If you have Analysis Services (AS) on-prem instead, the method described in this post should still work. Contemplating the licensing of a dedicated Analysis Services server? Read here for the latest on SQL Server pricing.

So first, you’ll need an Azure Subscription, an AAS server, and a tool called Tabular Editor. If you have Analysis Services on-premises, you won’t need an Azure subscription or AAS.

Next, create a Power BI template from your Power BI file (or PBIX). This is required because Tabular Editor can only open Power BI template files at the moment. That will change once Tabular Editor is integrated into the Desktop tool, but for now, you’ll need a PBIT file (not a PBIX) to open the model in Tabular Editor and then deploy it to either AAS or AS. Also important, make sure to disable auto-time intelligence before you do this.

So once you have Tabular Editor and the PBIT, you’re all set. Let’s deploy this to AAS (or AS if that’s what you have):

1. Open the PBIT file in Tabular Editor, File > Open > From File…
2. In the left explorer pane, expand the Data Sources folder and check that each data source has the appropriate credentials set.
3. Go to Model > Deploy
   1. Enter Server as asazure://azure_region.asazure.windows.net/aas_serverename (or your server name/ instance name if it’s AS on-premises)
   2. Change authentication type to “Username and Password” and enter in your authorized AAD or AD account to publish this new AAS model.
   3. Click Next
   4. Uncheck Deploy Roles
   5. Click Next
   6. Deploy

Model deployed! Now you need to process your new database, or “tabular model”. This populates your model with data as it is currently empty. This is the same as refreshing your Power BI dataset.

If you’re using AAS, I suggest using Powershell. This will help with scheduling refreshes later, or in Analysis Services terminology, processing the tabular model.

For those with Analysis Services on-premises, read here.

Process the Tabular Model

Picking up tips from Christian Wade’s blog on how to process AAS with Powershell, here’s the process.

In elevated Powershell console, install the following modules:

Install-Module -Name Az.AnalysisServices

Install-Module -Name SqlServer

Then executed the following command to login to Azure:

Login-AzureAsAccount

A login screen will pop up asking you to login. Use the same Azure Active Directory account you used previously to deploy the model.

Invoke-ProcessCube -Server "asazure://region.asazure.windows.net/server" -Name "myCube" -Database "Db1" -ProcessType "ProcessDefault"

Alternatively, you can process a particular table of the model. Analysis Services has all sorts of ways you can refresh data in your model.

Invoke-ProcessTable -Server "asazure://region.asazure.windows.net/server" -TableName "your_table" -Database "your_database" -RefreshType Full

Follow up

If you like what you see here and need to learn more, leave a comment below so I can address your questions. I know I did not address cost considerations or scheduling model refreshes in Azure. If you’d like to see that, just leave me a note and I will work on that.

I wrote this post mostly because I was so impressed with how easy it was to work with data models in Tabular Editor. I’ve used Tabular Editor in the past and really liked it for developing AS Tabular models. I’m happy to see that it works just as easy with Power BI files and AAS.

Thanks for reading!