Skip to main content
To homepage To homepage

Introduction

It has been discussed in multiple topics here: the question how to deploy semantic layers from TimeXtender to Power BI. As of version 21, this is natively supported. But many organizations are still on version 20 and therefor use Azure Analysis Services (AAS) to deploy their datasets to. While this works fine, AAS is a very expensive service and if you already have Power BI Premium, you already pay for a similar technique called XMLA Endpoints.

Because the techniques behind AAS and XMLA Endpoints are similar, there is a way to directly deploy from TimeXtender 20.10.X to Power BI Premium. This guide explains how.

 

Disclaimer

Obviously this guide explains a trick. Deploying from TX 20.10.X to Power BI Premium is not supported by TimeXtender and some manual manipulations after deployment are involved. However, it can be used to save costs. In our customer case, we save around EUR 4500 on monthly AAS costs, which is worth the pain of some manual actions.

 

Ingredients

  • TimeXtender 20.10.X
  • Power BI Preimium per Capacity/User
  • Azure Service Principal (AppID + ClientSecret). Create one as described here.
  • Your Azure Tenant ID
  • Azure Analysis Services Data Drivers installed. Make sure you install version 15. As of TX 20.10.41, SQL Server 2022 is supported, which may enable the use of the latest driver versions as well.
  • Tabular Editor version 2 (optional, free)

 

Configure Azure Service Principal

First thing is to assign the Service Principal API Permissions to the Power BI Service. From the Azure Portal, go to your Active Directory, select your principal and choose API Permissions. Add these permissions, which is also described here:

 

Configure Power BI Service

First step is to allow Service Principals access to Power BI APIs. Go to the Power BI Admin portal and change the settings accordingly:

NB: our Service Principal is part of AAD group PowerBiSecurityPrincipals.

Please also see the general article on Power BI Endpoints.

Next, depending on if you are using Premium per User or per Capacity, set XMLA Endpoint to Read Write:

When using Premium per Capacity, make sure that the workspace you’re writing to is part of the capacity (under Workpace Settings):

 

Last, add your Service Principal (or AAD group) as admin on the Workspace

Configure TimeXtender Endpoint

Create a new Semantic Model of type Analysis Services Tabular

Configure the endpoint accordingly:

  • Server: powerbi://api.powerbi.com/v1.0/myorg/<NameOfYourWorkspace>
  • Database: provide a name of the dataset
  • Use authentication login
  • User: app:<ClientSecretID>@<Tenant ID>
  • Password: use Value that corresponds to the SecretID
  • Leave everything under Processing as it is. Do not check Process model offline

 

Deploy the endpoint.

NB: Execute won’t work yet. TX cannot deploy database credentials to Power BI Service. Immediate execute after deploy will result in an error. We need to manipulate some settings in the Power BI Service.

 

Manipulate the dataset in Power BI Service

Go to the Power BI Service and open your Workspace.

Find your dataset and go to the dataset Settings:

 

Press Take Over to take over the ownership of the data.

Next, under Data source credentials, click Edit credentials and enter the credentials to your TimeXtender MDW database.

 

This step is also explained here

Go back to TimeXtender and perform an execute. It should now finish successfully:

 

Done!

 

Some important notes

 

Roles and RLS

  •  
  • In case you are using Roles (eg. when using row-level security), you have to create the role in TimeXtender and make sure it’s associated with the Endpoint:
  • However, TimeXtender cannot deploy role members to Power BI. You have to assign the members in the Power BI Service

 

 

Deploy newer version

  • Please note that a new deploy from TimeXtender first DELETES the old dataset and then puts a NEW dataset in the Power BI Service
  • This delete action also involves the deletion of related dashboards from the service. Always save a copy of your pbix
  • The deletion also means that after each deploy, you have to reassign your role members for row-level security
  • At my customer, we use Tabular Editor to download a copy of the model and re-deploy it to a different workspace. Tabular Editor does not delete you existing dataset, but simply updates the structure.

 

Conclusion

While it is possible to deploy a dataset from TX 20.10.X, I would only recommend to use this hack in order to save some costs on Azure services. Considering the manual actions and risk of loosing dashboards, it might not be a sophisticated solution for your organization.

 

Please leave any comments, questions or experiences in the comment section.

 

Incredible solution @erik.van.mastrigt 👏👏👏 thank you for sharing!!!

@erik.van.mastrigt Can or should this work without the Tenant.ReadAll setting for the service principal? I’m at a client where access to everything under the tenant is prohibited.

Hi @rogier.helmus, I think it should work without. Frankly, I copied these permissions from Christians article, but apparently we haven’t set the Tenant.ReadAll at my customer either. So it’s worth trying.

Hi @rogier.helmus 

The tenant.all permission would only be necessary if your company had more than one tenant somehow. I am sure it will work without it, I definitely did not use this setting when I was testing Power BI Premium.

Reply


Terms and ConditionsCookie settings