In Chapter 12 of my book, I published detailed walkthroughs that cover Translator Text API, Sentiment Analysis, Key Phrase extraction and the detection of languages using Cognitive Services. As I worked on the chapter, I realized that it will make sense to prepare a template that will streamline all of these APIs together to address business scenarios of global organizations that need to analyze a wide range of languages. The solution was too complex to fit the scope of a book, so I decided to publish it today.
This template takes you through the analysis of multi-lingual text feeds from the detection of languages, translation of unsupported languages, and the implementation of sentiment analysis and key phrase extraction. After a long holiday, I finally made the time to refine it, and publish it as an external template.
Do you need to run sentiment analysis and extract key phrases from textual feeds using Power BI and Microsoft Cognitive Services? This template is for you if you have multiple or unsupported languages.
This Power BI Template takes advantage of Microsoft Cognitive Services to perform Text analytics, including: Language detection, Text translation, Sentiment analysis and key phrase extraction. In many business scenarios, your textual feeds include mixed or unsupported languages that Microsoft service doesn’t support. To overcome this barrier, this solution performs the following steps:
- Checks which languages are currently supported by Microsoft Cognitive Services Text Analytics Sentiment Analysis and Key Phrase Extraction APIs (according to this website).
- Detects the languages that are used in the input text feed (using Microsoft Text Analytics detect languages API). You can change the input text feed using Power Query Editor.
- For unsupported languages, the template translates all input feeds to the desired supported language (Using the Translator Text API).
- Then, the template sends all the text for sentiment analysis and key phrase extraction APIs (in bulks of 1000 messages per API call).
- Sentiment scores and phrases are loaded to the report which includes few basic visuals to show negative sentiment by key phrases.
- Once purchased, load the ZIP file and extract the .pbit file.
- Open the .pbit file using Power BI Desktop.
- In the following dialog box you enter provide the following parameters:
- Support Preview Languages: Enter TRUE or FALSE. Microsoft Cognitive Services supports languages in a gradual manner. Some languages are in preview mode before they are officially released. By stating TRUE, you will rely on languages that are in preview and not yet officially released. Use TRUE only in non-production scenarios.
- Text Analytics API Key: You can copy this key from your Azure Text Analytics resource. You will need to subscribe to Azure Cognitive Services Text Analytics to gain such a key. I recommend reading Chapter 12 of my book, before you proceed, or find the relevant instructions here.
- Translate Key: You can copy this key from your Azure Translator Text API. I recommend reading Chapter 12 of my book, before you proceed, or find the relevant instructions here.
- Report Language: This is a two-characters code for the language you want to use for your report. It is recommended to use a supported code (For a list of supported codes go here). The template will translate all unsupported languages to this language, and will run sentiment analysis and key phrase extraction on the translated language.
- DataCenter: This is the data center you used in the Text Analytics Azure resource (e.g. westus).
- Click Load.
- In the Privacy Levels dialog box, select Ignore Privacy Levels as shown in this screenshot, and click Save.
- When the report will be loaded, in Home tab of Power BI Desktop, select Edit Queries to replace the default “Hello World” textual feed with your own.
- In the Queries pane, select the Text query and replace it with your own text feed. Make sure that your feed includes the columns text and id. The latter should include unique values. The template will remove duplicate id values.
In this screenshot you can see the Query Dependencies dialog box, with the pipeline from raw text to text with sentiment scores and key phrases.
Before you purchase this template, here are few disclaimers:
- This solution will not support schedule refresh on Power BI service. You can only refresh it using Power BI Desktop. For a refreshed version using a custom connector, contact me.
- This template was written for learning purposes and should not be used on production environments, unless you know what you are doing 🙂
- You are not allowed to sell this solution to others without my written approval.
- The Translator Text API may fail to refresh if you use large numbers of text messages (Above 10,000). Supporting larger number of messages may requires additional work, and is better implemented outside of Power BI.
- You can order one hour of my time to walk you through the implementation and troubleshoot your own data. Contact me at [email protected] if you have any questions.