Sentiment Analysis with Power BI

Patrick LeBlanc, 2015-12-09 (first published: 2015-12-02)

I was recently asked by a customer to help them do some Text analysis of course evaluation data. They wanted to perform sentiment analysis and key phrase extraction. My first thought was to develop an Experiment using Azure Machine Learning (Azure ML). However, after doing a little research I stumbled upon a Text Analytics API that accomplishes exactly what the customer asked. It is built using Azure ML and can be found in the Microsoft Azure Marketplace (https://datamarket.azure.com/dataset/amla/text-analytics). There are several subscription levels, I opted for the 10,000 free transactions a month.

 

After registering I clicked on the Interactive Demo link located in the bottom right corner of the Text Analytics subscription page. This page provided me we a great place to interactively test out the API. You can also find code samples for building console applications using C#. Now, typically I would have launched Visual Studio and put on my developer hat. However, after reading the documentation on the API, I realized that API is a suite of web services.

 

Hmmmm, a web service. Now what do I have in my tool belt that can extract data from a web service, Power BI Desktop. So I opened Power BI Desktop and selected Get Data from the ribbon. Then I selected the OData Feed from the Other data connections section. Next I used the following url:

https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text=hello+world

as the URL for the OData feed.

 

image

 

Then I clicked OK.  After clicking OK, you should be prompted to supply credentials for the OData feed.  Since the web service is hosted in the Azure Marketplace you will need to supply your Account Key.  Select Marketplace key in the left navigation section and enter your account key in the available textbox.

 

image

 

If you need to obtain your key, click the Get your Marketplace Account Key hyperlink and you will be directed to a webpage that contains the key.  You may need to log in.  Either way, copy the key and paste it in the Account Key text box.  Click the button labeled Connect.

 

Now, this is where the fun actually begins.  The Query Editor window opens and the sentiment score for the query string is displayed.

 

image

 

If you want to test further, locate the formula bar and change hello+world  to terrible world and press enter.  Then score should change drastically.  Note, the plus (+) sign is not required when issuing the web service call.  Pretty cool, if I may say so myself.  One more thing, in the top left corner click the Into table icon to convert the result set to a table.

 

If I only wanted to analyze one row of data at a time this would be a great solution, but anyone that works with data knows that it is never just a single row.  So I created a Blank Query in the Query Editor and used the following code:

let
    Sentiment = (inputtext) =>
let
    Source = OData.Feed("https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text="&inputtext),
    ToTable = Record.ToTable(Source)
in
    ToTable
in
    Sentiment

What I have done is parameterized the web service call using a function in the Query Editor.  By invoking the function you can manually provide your own text for analysis.  Instead of manually entering a single set of data, I created an excel spread that contains several rows of text and imported that data into Power BI.  The following is a sample set of the data that I used:

 

image

 

The first row is the header and all subsequent rows are the text that need to be analyzed.  After I imported the data I added a custom column to the new table.  I named the column SentimentScore and used the following as the custom column formula:

 

=Sentiment([text])

 

After that I click the expand icon located to the right of the newly created column and unchecked all boxes excepted the one located to the left of the Value column.  Then I clicked OK.

 

image

 

There you go, each row received a sentiment score.  The higher the score the better the sentiment.  Stay tuned for another blog explaining how to perform the key phrase extraction.

 

Patrick LeBlanc

Data Platform Solution Architect

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads