Recently, I was presenting my session about AI Capabilities for Power BI to make AI Accessible for Everyone for the Virtual Power BI Days Hamburg. A great event organized by Kathrin Borchert. Part of my session was about the Artificial Intelligence capabilities offered as part of Power BI Premium. A day later, Kathrin came up with a great idea how you can leverage these AI capabilities without the need for Power BI Premium.

I was directly enthusiastic about that idea since I thought about this in the past as well. Back then, there were some blockers which are sorted now. I asked Kathrin if she was open for co-authoring this blog and she immediately agreed.

AI Insights

The Artificial Intelligence Insights that are available in Power BI dataflows as well as the Power BI Query Editor in Power BI Desktop, require a Power BI Premium capacity. This premium feature is a great addition to your BI reporting and dashboard by enhancing your insights with more advanced insights.

These AI insights offer a bunch of different options. Below a short line-up of the options you have today:

• Sentiment Analysis, returning a number between 0 and 1 on how negative or positive a text string is, based on a Cognitive Services trained algorithm.
• Key Phrase Extraction, returning a list of key words representing the main message based on text string, based on a Cognitive Services trained algorithm.
• Language Detection, returns the language text string, based on the Azure Text Analytics API.
• Image Tagging, computer vision-based API that returns the objects seen on an image used as input for the Cognitive Services.

All the above options are running based on the power of Azure Cognitive Services. These Azure services are leveraged within Power BI by a few simple clicks and without any specific knowledge about Cognitive Services.

Next to the AI Insights offered via Cognitive Services, Power BI also has the option to use Auto ML and Azure Machine Learning custom build algorithms within Power BI. Find out more details about these different offerings in the Microsoft documentation.

Need for Power BI Premium, but not any longer!

All the before described services, need Power BI Premium dedicated capacity to leverage these enhanced insights. Although Power BI Premium offers a lot more than only these AI Insights, it is a very expensive way of enriching your BI reporting. The Power BI Premium offering is a monthly capacity-based pricing, that can be setup by an A or P SKU in different price ranges but is in every case a yearly commitment.

But Azure Cognitive Services can also be called by an API endpoint. In this case it is a tier-based pricing that is based on the amount of transactions you will do with the Cognitive Services API. Below an example of the pricing for the Text Analytics offering in Cognitive Services based on the Azure Pricing Calculator, for full detailed pricing, we advise you to run the pricing calculator based on your region and currency.

Calling the Cognitive Services API can be done in Power BI, by using a Power Query function. By doing that, you can run every row in your dataset through the Cognitive Services API and get the result back of your preferred Cognitive Services offering. More detail on how to do this will be described in the next chapter.

Incremental refresh to the rescue to save loads of money!

Calling the Cognitive Service API in Power Query almost looks like a solution. However, this can become very expensive! Every time you refresh your Power BI dataset, it reprocesses your whole dataset. As a result, the Cognitive Services API will be called for every single row in your dataset. Imagine that you have a dataset with millions of rows, this can become very expensive!

In order to avoid reprocessing your whole dataset, incremental refresh can help you out to only call the Cognitive Services API for the newly added rows. This will significantly lower the number of transactions on the API during reprocessing your dataset.

In the past, incremental refresh in Power BI was only limited to Power BI Premium. But as of the Power BI update in February 2020, incremental refresh is also available for Power BI Pro! This will help you to take the next step in effectively reprocessing your dataset and limiting the required resources to do so.

Building your Power BI solution with Azure Cognitive Services, but without Power BI Premium

The idea behind this was to find out how attendees rated our DIAD workshops. After another DIAD Event I was wondering how good our workshop was. I decided to build a little survey based on a Forms (Forms MSFT). After a couple of workshops, I see that many people answered the free text fields and I try to figure out how the attendees rated us. But this was not really easy to go to every line and look if the workshop was good or only okay or whatever. I thought about an easier solution for this and I find out that Power BI had an AI function called Text Analytics. After that I created a solution with this function to score every answer as number to get a better overview about our rating. Now I have the possibility to go to every survey I want to find out how good or bad our workshops are.

That is even a great solution for every company which is rated by free text to find out what the costumer liked and what they did not like.

The dataset we used in this solution is based on a table from a survey like mentioned before from a DIAD workshop. Below we will elaborate further step by step how you can set this up yourself.

For this solution you need (you can test this solution without using extra money for subscription or other things):

• A Power BI Pro License
  (you can also use a Free Trial of 60 Days to test this solution)
• A Account in the Azure Server (https://portal.azure.com/)
  (No subscription needed, because the basic version for text analytics is free)
• Depending on your data source, a Power BI Gateway (only for on Premises solutions – like an excel file on the local machine)

Setup the Cognitive Service in Azure

The Cognitive Services run in Azure. We first need to set this up before we can leverage it in Power BI.

1. Go to https://portal.azure.com/ and search for Cognitive Services
   
2. Create the Textanalysis Service
   
3. Click Create and give it a reasonable name.
   
4. After creation go to the Service and choose Quickstart
   
   
5. Go to API-Consol (V2) and choose your current location (in which location your environment is available, and resource is created at step 3). Write down the URL for the next steps.
   
6. Go back to your TextAnalysis resource in the Azure Portal and go to Key and Endpoint choose the first Key (write is down for next Steps)
   

Now we have everything setup in Azure and we have created the keys, the next step is to start leveraging this service in Power BI.

Create the Power Query function in the Query Editor

In Power BI, we will leverage a Power Query Function in order to run all the rows of data through the Cognitive Services in Azure. We will set this up in a few additional steps.

1. Load your preferred table which you like to analyze. In our case that is the Dashboard in a Day data Kathrin collected as feedback.
   
2. Create a blank query in the Query Editor. You can do this via the menu on top or a right click in the Query pane.
   
3. Open the Advanced Editor and copy the Power Query code below in the advanced editor.

   (text) => let
   
       apikey      = "<<Key 1>>",
       endpoint    = "https://<< Text Analytics API (V2.1)>>/text/analytics/v2.1/sentiment",
       jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
       jsonbody    = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
       bytesbody   = Text.ToBinary(jsonbody),
       headers     = [#"Ocp-Apim-Subscription-Key" = apikey],
       bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),
       jsonresp    = Json.Document(bytesresp),
       sentiment   = jsonresp[documents]{0}[score]
   
   in  sentiment

   Do not forget to adjust the apikey and the endpoint with the information you copied in earlier steps. You should have something like displayed below.
   
   Please be aware that you need to insert the general API Endpoint as listed the API-Consol in Step 5 and not the endpoint of your resource. To us this was really confusing and where we spend some time to get it working.

4. Go to your table with the column you like to analyze – choose under Add Column the Invoke Custom Function. 
   
5. Choose the Function query you build in Step 3 and choose the column you like to analyze.
   
6. After clicking OK, Power BI might ask you to authenticate. Please choose for Anonymous and click Connect.
   
7. Finally, the resulting column in Power BI shows up. This column now represents a number between 0 and 1, telling us how positive the inserted text is according to the Text Analytics API.
   

Incremental Refresh

The fact that we are able to leverage the Azure Cognitive Services without Power BI Premium is already pretty awesome! But you might think, this is going to cost me loads of money when I run this over a million-row dataset! But that does not have to be the case.

When you apply a full refresh to your dataset, every row in your dataset will be run through the Cognitive Services API. Since the pricing for Cognitive Services is based on the number of requests, this can be really expensive. But by leveraging Incremental Refresh in Power BI, we do not have to run every single row in our dataset through the Cognitive Services API every time.

Note: there are a few prerequisites to make Incremental refresh work. Please look at the documentation to see if it will work in your scenario. 

For incremental refresh, datasets are filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. These parameters are used to filter the data imported into Power BI Desktop, and to dynamically partition the data into ranges once published to the Power BI service. The parameter values are substituted by the service to filter for each partition. There is no need to set them in dataset settings in the service. Once published, the parameter values are overridden automatically by the Power BI service. Below description will help you to setup Incremental Refresh based on our sample dataset.

 

1. Choose in the Power Query Editor the Parameter Function
   
2. Create two parameters with
   RangeEnd -> Date/Time
   RangeStart -> Date/TimeAttention: User the exact wording for the parameters its necessary for the incremental refresh
   
3. Use the parameter for a date column and choose Custom Filter.
   
   Make sure you add this step before the TextAnalysis function in invoked. The filter steps need to be applied while Query Folding is still taking place. Therefor apply these steps as early as possible in the Power Query steps after importing the data.
4. Ensure rows are filtered where the column value is after or equal to RangeStart and before RangeEnd. Other filter combinations may result in double counting of rows.
   
5. Close & Apply the Power Query Editor and go to the 3 dots on the right site of the table in which the date with the parameter is stored.
   
6. Choose your preferred configuration for incremental refresh.The following example defines a refresh policy to store data for six months plus data for current date, and incrementally refresh ten days of data. The first refresh operation loads historical data. Subsequent refreshes are incremental, and (if scheduled to run daily) perform the following operations:
   • Add a new day of data.
   • Refresh ten days up to the current date.
   • Remove calendar month that are older than six months prior to the current date. For example, if the current date is January 1st 2019, all data back from the first of June 2018 will be removed.

   The first refresh in the Power BI service may take longer to import, depends on the amount of data you will load into the dataset. Subsequent refreshes may be finished in a fraction of the time.
   

7. After applying the Incremental Refresh policy, you can publish the report to the Power BI Service and schedule the refresh.The current date is based on the system date at the time of refresh. If scheduled refresh is enabled for the dataset in the Power BI service, the specified time zone will be taken into account when determining the current date. Both manually invoked and scheduled refreshes observe the time zone if available. For example, a refresh that occurs at 8 PM Pacific Time (US and Canada) with time zone specified will determine the current date based on Pacific Time, not GMT (which would otherwise be the next day).

   In the Pro License you can only refresh datasets eight times a day. Also, you might need a data gateway depending on the data source you are using.

To prove that this setup works, below a few facts on how we measured that only the newly added rows run through the Cognitive Services API.

• Incremental refresh is set to only refresh data in the last 10 days, according to the above described setup.
• The dataset includes only one row that is in the last 10 days.
  
• The Power BI refresh has run (on demand) after I added the row to the SQL database. This has triggered to run the incremental refresh.
  
• The Cognitive services in Azure is only triggered for the new row in the actual partition of the Power BI dataset.
  

Wrap-up and remarks

Enriching your dataset with Cognitive Services, such as Sentiment Analysis as we have done with the above sample, can add great value to your dataset. It easily transforms textual data into measurable numbers. Where this was first only limited to Power BI Premium, it is now also possible to setup in a cost-effective way with a Power BI Pro license.

Where the AI Insights features in Power BI Premium are part of the Premium pricing, the setup described still brings additional costs with it on top of your Power BI Pro License. Though, in most cases this is much cheaper than a Power BI Premium P1 capacity. The business case if it is actually cheaper in your scenario is easy to make, since the licensing for Cognitive Services licensing is based on the number of transactions.

In our example we described how you can run sentiment analysis. But of course, Azure Cognitive Services offers tons more of great features that can be leveraged in this way. Now it is up to you to enrich your dataset!

Links:

• Documentation about Text Analytics with Azure Cognitive Services.
• Documentation about Incremental Refresh in Power BI.
• Documentation about AI Insights with Power BI Premium.

---

Co-author

This blog is co-authored by Kathrin Borchert. Kathrin is a BI Consultant and Power Platform Enthusiast since day one. She works as BI Consultant und Trainer at pmOne AG in Hamburg, Germany. Kathrin is co-organizer of the Power BI Days Hamburg and blogs on her own website (YodaBI) as well about anything Power Platform related.