10/22/2018

Connect to Azure Analysis Services Tabular Model from Uploaded PBIX file

Decided to explore Azure Analysis Services today.

To summarize steps to connect to Azure Analysis Service, we: 


  • Connected to Azure
  • Created a Resource Group
  • Created Azure Analysis Service
  • Pointed to Power BI PBIX file (uploaded)
  • The Power BI file contained the sample Model
  • Downloaded drivers, to connect to Azure Analysis Model from on-premise
  • Added user as Admin to the AAS model
  • Set Firewall rule to allow user's IP Address to tunnel through  


Here's the steps screenshots:

Create single resource group:




Resource Group Added:




Add Analysis Services, select from different pricing options:




Azure Analysis Services added:




Attempt to add Model using Power BI pbix file:




Error, must be higher version file / model:





Fix this by grabbing newer version of Power BI Desktop.  So, Downloaded latest version of Power BI Desktop & Install...



Installing Power BI Desktop:





Installing...




Success...




We opened the Contoso downloaded PBIX file with newer version of Power BI Desktop, then saved file, no other changes...




Attempted upload again into Azure Analysis Services, it accepted the PBIX file this time, and loaded its Model, shown here...









Added table, created a diagram...




Online editor allows the model to be viewed in variety of tools, show here... Visual Studio Projet, Power BI Desktop or Excel...




Can view the table Relationships in the online editor...



And here we see the saved latest Model, after file was uploaded from Power BI Desktop, into Azure Analysis Services...






The model is functional...




We selected a few measures, we see actual data, in the browser...




We can view/see the underlying DAX code in online editor as well...




From the Azure portal, we can see the Azure Analysis Service info...





In order to connect from on-premise, we need to download the MSOLAP drivers...




Installed drivers...




Now we need to add a user from our local Active Directory in Azure Analysis Services.  We type in user email, it auto generates an email to the person.  User simply clicks on the button, gets added to Azure Analysis Services Admin...




We need to add a Firewall rule to allow on-premise user ability to tunnel through and see the model in Azure, added my local user…




We grab the URL Connection from Azure, place in SQL Server Management Studio 2017 (SSMS), and connect to Azure Analysis Services.  Here you can see the Model, Connections, and Tables...




Now, we can connect to Azure Model from Power BI Desktop.  Created new report, added same Azure Connection here...





We didn't specify exact database as its optional...




Can see the Model in Azure using Power BI Desktop...


Added a few measures and row(s), to build a quick report:  





Basically its pulling from Azure Analysis Services, from on-premise Power Bi Desktop, connected using AD account which got added to Azure model, after confirmation, and opening firewall for specific IP address...

And the nice thing about creating a single Resource Group, we can blow all the artifacts away in single swipe...




And that completes our Azure Analysis POC to 


  • Upload a Power BI sample Model to Azure Analysis Tabular Model, 
  • Create model using online tool
  • Add user to connect from on-premise
  • Set firewall rule to open specific IP Address
  • Create connection(s) from online applications: SSMS and Power BI

It should be able to connect from SSRS as well using the same connection and user info.  Why would we do this?

One reason is to "productionalize" a useful Power BI report to the Enterprise, maybe the report has useful information, vetted calculations, lock down users and row level security, leverage the power of Azure Analysis Services Tabular format, which extends the DAX language, for easy consumption, from variety of applications either in the Cloud or On-Premise.

And of course, you can follow similar steps to connect to Azure Analysis Services that were not built from Power BI Desktop PBIX files as well.

Thanks for reading~! 



Reference URLs:


Tutorial: Add a sample model from the portal
https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-create-sample-model



Download Power BI Desktop:
https://www.microsoft.com/en-us/download/details.aspx?id=45331



Download sample PBIX file ( ContosoSalesForPowerBI) :
https://www.microsoft.com/en-us/download/details.aspx?id=46801



MSOLAP Data Providers:
https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers