Month: January 2022

Multilingual Reports in Power BI

Power BI Guy8 Comments

Creating Power BI reports in Switzerland can be challenging when it comes to the question: In which language should we display the report? The reason behind is that Switzerland has 4 official languages and if we consider English, it’s even 5. Therefore it’s not easy to fulfill this requirement with one report – or is it? Let me walk you through different scenarios how you can create multilingual reports with Power BI.

Possible Solutions

As in most scenarios in Power BI there are different ways to achieve the same goal. Therefore let me list some solutions:

  • Create a separate report for each language
  • Create one report and translate data through a service (like Azure Cognitive Services)
  • Create a Datasets for each language
  • Create a report, use a “translation table”, and work with bookmarks
  • Create one report and a “translation table” to display the right language

Of course each scenario has his pros and cons so let me go through some of them quickly and focus afterwards on the main solutions I’m going to present in this post.

Create a separate report for each language

Creating a separate report for each language is from my point of view a maintenance nightmare. Imagine having 5 different languages, therefore having 5 “different” reports (which all look the same but using only a different language), and now you have to customize a visual. Not only you have to do the work multiple times, you have to do it every time a modification is required! Besides that you have to keep an overview which users has access to which report making sure they have access to the right language. Therefore I wouldn’t recommend to go this path and look into other ways to fulfill the multilingual report requirement.

Create one report and translate data through a service

Using a Service to translate your data is a nice and handy solution. The good side of this approach is that you do not have to create your own translation for each word and sentence for each language. This can be done automatically through an API. On the other hand the translation itself could lead to unexpected results, sentences could be translated in a wrong or misleading way which can cause other, unexpected issues which could be avoided in the first place. Therefore it’s not a perfect solution but for sure one worth trying if you do not have a “translation table” which can be used. If you’re interested in how to call an API to translate your data let me know and I’ll be happy to post about it in future.

Create a Datasets for each language

Chris Webb wrote a great article (see https://blog.crossjoin.co.uk/2021/02/21/implementing-data-as-well-as-metadata-translations-in-power-bi) how you can create multiple Datasets – one for each language – and use the DirectQuery Mode (not Live Connection! 😉 ) to connect to the needed Dataset. This approach has also his benefits as the data and meta data like column name and title can be translated. On the other hand it’s again a maintenance effort for all the different Datasets and PBIX files.

Create a report, use a “translation table”, and work with bookmarks

I saw a post from Greg Philips how you can handle Multilingual Reports with a translation table and multiple pages and bookmarks. Everything is described here: https://blog.enterprisedna.co/create-a-multilingual-power-bi-report/

This solution gives you the full flexibility to translate data, columns, titles, etc. in your report. On the other hand you would need to create a separate page for each language and maintain the bookmarks on top of it. If changes will occur you would need to change it on every page. Still better than having multiple reports, but could lead to a lot of maintenance hours. Nevertheless it’s a really nice solution to work with different languages in one report.

Create one report and a “translation table” to display the right language

Another approach is to have a “translation table” within Power BI which is used to translate the needed data in the right language. Let me walk you through how this could look like in Power BI.

As an example imagine we have a table with some fruits, numbers, and an ID for each fruit. Further we have a second table with the fruit ID, display language, and the translated value.

Once in Power BI imported you can create a relationship between the two tables through the ID if that’s not already automatically done during the import process.

As a next step you can add a Slicer Visual using the Language column from the Language table and add a Table Visual with the Number Column from the Fruits table and Value Column from the Language table.

If you select now a language in the slicer the Table Visual will filter it to the selected language. You can even force a selection through the slicer in the settings by turning on “Single select”.

To enhance this solution and apply automatically the correct language you can even build a third table matching the language with an user and use RLS to apply the right language. In my simple solution I created following table.

Tip: I blogged about RLS a while ago and if you’re interested in how to implement it check it out here: https://pbi-guy.com/tag/row-level-security/

As you can see the table contains the language, name and UPN of each user. Once in Power BI imported create a relationship between the language column from the User Language table and the Language column from the Language table. Our whole relationship model looks now as following.

Our next step is to create a role and set a rule so users can only see their specific language. We’re going to user the USERPRINCIPALNAME() to identify which user is logged in and match the mail address with the User Language table.

Once this is set up the Slicer Visual is not needed anymore as through RLS the data is already filtered. I just added a card visual showing the current language and the USERPRINCIPALNAME (I created a DAX measure for this). If we test now RLS on behalf of another user – let’s take Luke – we see following.

If we change it to Chewbacca the language will apply automatically.

This approach is very good if you do not have a lot of data and languages. As you can imagine if you add a new fruit your language table will need three more lines for the three languages in this case. If you add a new language your language table will increase even more which leads to a bigger data model size in both cases. Further performance can decrease due to the implemented RLS rule. On the other side user experience can increase as the language will automatically apply but users can’t change it on their own if RLS is in place (e.g. in my personal case I prefer to display everything in English even if I’m based in the German-speaking part of Switzerland).

Besides data translation Power BI offers ways to translate field and table names automatically as well. Let me show you how to add different languages into your model.

Translate Power BI field and table names

To be able to add multiple languages into your model you’ll need to work with third-party tools. This could be Visual Studio, Tabular Editor, or anything else that can handle and configure a tabular model. In my case I like to work with Tabular Editor because as soon as you install it you’ll have it integrated in Power BI Desktop to open it directly from the ribbon connected to your model.

Once opened you’ll find a “Translations” folder in your model. Right-click on it and hit New Translation.

A new window will pop-up where you can choose which culture you wish to add. In my case I select de-DE and hit OK. This means once translations are implemented in de-DE language Power BI will recognize the Browser or Power BI Desktop Language Settings and display automatically the translation of my tables and columns in this language.

You’ll notice how the Translation folder has now two different languages. In my case en-US and de-DE. Now I can expand the Tables folder, select a table or column, scroll down to Translated Names Properties and add for each language a translation. As you can see in my example below I choose the table Fruits and translated it to Früchte in German.

To make the translation easier Chris Webb has a very useful and easy-to-use tool which is available for free here: https://www.sqlbi.com/tools/ssas-tabular-translator. To be able to use it export the translations as json file and import it afterwards by right-clicking on the Translations folder in your model.

Once everything is translated as wished save your model and publish the report to Power BI Service. In there you have to change the browser language or choose through Power BI settings your preferred language and your table and column names will be display accordingly. If you wish to test in in Power BI Desktop keep in mind that since March 2021 release Power BI Desktop will not reflect the model translations so you would need to have an earlier version if you wish to do so.

Edit: Further keep in mind that this works only with Premium. Therefore you’ll need Power BI Premium, Premium per User, or an Embedded capacity.

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the files used in this blog check out my GitHub repo https://github.com/PBI-Guy/blog

Refresh a Power BI Dataset with Python

Power BI Guy9 Comments

As you probably know Power BI Datasets can be refreshed manually, on a scheduled base or through the Power BI REST API. Depending on the data source you will require a Power BI Gateway to be able to update your dataset. Manually refreshing a dataset can be handy if you need an instant refresh but usually you will at least schedule it to automate this process. But in few scenarios you probably don’t want to wait till a certain, scheduled time or you wish to trigger a refresh immediately but programmatically after your ETL / ELT process has finished. In this blog post I’m going to show how to trigger a refresh with Python.

JFYI: You can also use Power Automate, PowerShell or anything else that can call and authenticate against the Power BI REST API to trigger a dataset refresh and there are plenty good blog posts out there explaining how to do so.

Prerequisites

  • Power BI Dataset in Power BI Service
  • basic Python know-how
  • a registered Azure App
  • optional: Azure Synapse Analytics

How to

First of all we need a Service Principal on which behalf we can trigger the Dataset refresh programmatically. You could also use your user and password to log in and call the Power BI REST API (so-called Master User Authentication) but I wouldn’t recommend this approach due to some obvious reasons: Your password can change, what happens to the program if you leave the company, probably too much rights, etc. Therefore we’re going to register an App and give just the needed permissions. A very well how-to-guide can be found here: https://www.sqlshack.com/how-to-access-power-bi-rest-apis-programmatically/.

Checking the doc (https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history-in-group & https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history-in-group) we see that in our case we need at least the Dataset.ReadWrite.All or Dataset.Read.All to be able to check the Refresh History, and Dataset.ReadWrite.All to be able to trigger a refresh.

Before we’re going to refresh a Dataset we want to make sure that no refresh is happening right now. That’s the reason why we need the Refresh History API.

Once the app is register you’ll get an App ID and Secret. Store this information somewhere securely as we’re going to need it later on.

To be able to access the Dataset our Service Principal will need sufficient permission on the dataset itself. In my case I’m just going to add the user as admin in my workspace.

As a next step we’re going to write our Python Code, authenticate against Power BI and call the needed REST APIs. You can choose whatever environment suits you best to write Python. In my case I’m going to use Azure Synapse and create a Jupyter Notebook to run my Python Code.

If you’re interested in how to start with Azure Synapse feel free to check out this link: https://docs.microsoft.com/en-us/azure/synapse-analytics/get-started

I created a folder called PBI Guy and create a new Notebook with the name Power BI Dataset Refresh.

As first step we need to have some code and assign afterwards a Spark Pool to be able to run it. I like to start with importing my needed libraries. Because we need to authenticate against Microsoft Azure Active Directory we’re going to use the MSAL library (https://docs.microsoft.com/en-us/azure/active-directory/develop/msal-overview). Further we will call the Power BI REST API. For this purpose I’m going to use the requests library (https://docs.python-requests.org/en/latest/). Afterwards we would need to convert the output into a JSON format to make it easier to process the data. Therefore we’re importing the JSON library as well (https://docs.python.org/3/library/json.html). Last but not least I’m going to use the Pandas library (https://pandas.pydata.org/) just because it’s so simple to transform and extract the necessary data. Luckily with Python importing libraries is straight forward and our first code snippet looks as following.

I’m sure there are multiple, probably easier ways to achieve the same goal triggering a Power BI Dataset refresh but thinking about future improvements of my code (probably store the refresh history, compare it to other data, etc.) I’ll make it nice and clean from the beginning.

Our next goal is to set up all the needed parameters – and there are a few! We will need:

  • Client ID
  • Client Secret
  • Tenant Name
  • Workspace ID
  • Dataset ID

After we registered our App / Service Principal we got the Client ID and Secret. If you’re not sure what’s your tenant name just login to your M365 Admin Center, go to Setup, click on Domains, and see your domain name ending – this is your M365 tenant name. The easiest way to get your Workspace and Dataset ID is to head over to Power BI Service, click on your Dataset and check your URL. The red part is the Workspace ID, the blue part shows you the Dataset ID.

Besides that we specify the Authority URL which is needed to authenticate with the MSAL library. Basically it points Python to the right Microsoft Tenant. Further we also need to specify the scope (Power BI in this case). Lastly we also need our Power BI REST API URL. Because we’re interested just in the last refresh history status we filter it in the query to Top 1. With all these information we create following code snippet.

Now it’s time to authenticate and grab an access token which can be used to call the Power BI REST API and get the Refresh History of the Dataset. Once authenticated and an access token is available, we’re using it to provide it to our header of our request statement. After we set up the header correctly we call the API through a GET request providing the URL from above and the header we just created including the access token. As a next step we’re converting the result directly into a JSON format which is used to format it again to a Pandas Dataframe with the columns requestId, id, refreshType, startTime, endTime, status. All these information are provided through the Power BI REST API. Lastly we set the Dataframe Index as the id column. Our code should look now as following.

Let us know check if the code runs successfully. We’re going to execute the whole code to import all libraries, set the parameters, authenticate, and call the Power BI REST API to get the refresh history of our dataset. If everything works fine we should see a result like the one below.

Perfect, this means we got now the Power BI Dataset refresh history and see the last refresh has been executed on demand (meaning manually) and it’s completed. The status in our case is very important because if the refresh is running we can’t trigger a new one and it will fail. From the documentation we can see there are four different status:

StatusDescription
UnknownState is unknown or refresh is in progress
CompletedRefresh completed successfully
FailedRefresh was unsuccessful
DisabledRefresh is disabled by a selective refresh

So to trigger our refresh we want to make sure the status is not unknown. Further we can also specify what should happen if the last refresh status is failed or disabled (e.g. enable it through another API – see https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/update-refresh-schedule-in-group). Due to demo purpose I’m just going to print out a different message based on the status. This means our last code snippet checks the status and depending on it executes a different code.

I added an else section as well if for some reason a new status code will appear so I’m handling every situation separately.

If we now execute the last bit of our code we see the right message has been printed out – so far so good.

But let’s also check the Power BI Dataset Refresh History. If we switch now to Power BI Service, select our Dataset, move on to Settings and check the Refresh History we see following – our Dataset has been refreshed and triggered via API!

Imagine what you can do now – not only calling the Dataset Refresh REST API but as well any other API Power BI offers via Python! Because I’m in Azure Synapse I can even integrate my Python script in Azure Data Factory and trigger a refresh at the end of my ETL / ELT pipeline, and many more! I’m really excited about this opportunity and will definitely elaborate more possibilities in near future.

Keep in mind that the limitation of your licensing can’t be eliminated. This means if you’re using the Power BI Free license you can programmatically refresh a dataset once a day. With a Pro License it’s eight times a day, and with Premium or Premium per User it’s 48 (see https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#data-refresh).

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the files used in this blog check out my GitHub repo https://github.com/PBI-Guy/blog