Day: September 27, 2019

What you need

• Power BI
• Microsoft Flow  Or Logic Apps in Azure
• Cognitive Service. Text Analytics in Azure

I love the show Taskmaster and I really want to see how much other people love taskmaster and when people are tweeting about it.

I want to see everything happening real time in my Power BI report.

For this example, I am going to use a streaming data set. The data will be pushed into the Power BI service, but Power BI will only store the data in a temporary cache which expires. We can give it a window of time to look at.

There is no underlying database with a streaming data set so I wont be able to use my usual report visuals. I will need to use the Streaming visuals available in Power BI

There are other ways to do real time data such as Push Data sets and PubNub streaming datasets but for this example I want to concentrate on the Streaming data set

Log into the power BI Service https://powerbi.microsoft.com/en-us/landing/signin/

I’m going to do this simply in my Workspace

Click on Create in the right hand corner of the screen

Then click on Streaming dataset

Choose API to create an API endpoint

API “a set of functions and procedures that allow the creation of applications which access the features or data of an operating system, application, or other service.”

Our Streaming Dataset will contain these values.

Extra time dimension fields have been created to use drill/down features.

The outcome of the sentiment analysis is placed in the field ‘score’. From 0 to 1 Positive to Negative

Historical data set’ to ‘on’, if you want to save the data for analysis later in time.

Power BI makes a JSON like format (a REST Webservice on the background).

JSON – is an open-standard file format that uses human-readable text to transmit data objects consisting of attribute–value pairs and array data types

REST – ‘Representational State Transfer’. It is primarily used to build Web services that are lightweight, maintainable, and scalable. A service based on REST is called a RESTful service. REST is not dependent on any protocol, but almost every RESTful service uses HTTP as its underlying protocol.

Once you click done, you can see that we have the new data set in our datasets list.

Microsoft Flow / Azure Logic App

The next stop is to create a Microsoft Flow (You can use Azure Logic App if you prefer)

Microsoft Flow is a cloud-based software tool that allows employees to create and automate workflows across multiple applications and services without the need for developer help.

https://flow.microsoft.com.  And sign up for a new account if you need one

Go to My Flows and Automated Flow

Click on When a new tweet is posted

And Create

When you are in the Flow you create New Steps to build up the logic required for your data. The Flow will create the Twitter feed for your Reports.

The first step I have simply added #Taskmaster to the When a new tweet is posted

You will also need to log into your twitter account at this point to get access to tweets

Click on New Step and add an action

The next step is to add some sentiment Analysis. I have used Text Analytics to do this

I have searched for sentiment and can then click on Text Analytics – Detect Sentiment

I have already logged into my Azure Text Analytics

I used the Key, Name and Endpoint details from Azure

You may need to set up your own Cognitive Service in Azure before you do this section.

Next step is to search for Power BI

Select Power BI – Add Rows to a Dataset

Next, add all the information about the RealTimeData streaming dataset

Most of the details can be added from the Dynamic Content picker. As you can see Score comes from the Detect Sentiment group. All the twitter data comes from the very first step ‘When a new tweet is posted ’ Group

The following are the expressions you need to add for the Time fields

• Time formatDateTime(utcNow(),’MM/dd/yyyy HH:mm:ss’)
• Month formatDateTime(utcNow(),’MM’)
• Day dayOfMonth(utcNow())
• Hour formatDateTime(utcNow(),’HH’)
• Minute formatDateTime(utcNow(),’mm’)

Save your Workflow

Testing Service in Microsoft Flow

The real time analysis will be triggered by hashtags in Twitter

This service only allows you to get current tweets. No historical tweets can be generated at this point

The visualization tiles will only be kept for an hour so this is the kind of service you need to monitor fast paced information. 

In the Workflow you can save

Click Test and Create a tweet with one of the hashtags in the workflow.

The service will run and you should get pass or fail information.

We can now use this information to create a sentiment report in PowerBI

Back to Power BI Service

We already found the  dataset in the relevant workspace e.g. My Workspace

For this example, click on Create report against the dataset and add a table tile to the report pane. (The data may be refreshing at this level)

Leave like this for the time being. Save your report and pin table visual to dashboard by clicking the pin

I created the table simply to create a report and then a dashboard. Once at dashboard level Custom Streaming data tiles can be added. I am deleting the table when the dashboard is created for simplicity

Click on +Add Tile

Then choose the Custom Streaming data

At present, there isn’t many visualisations to choose from for this type of data

Click Next

The #Taskmaster dataset has been chosen

I am going to create a very simple bar chart to look at the last hours data and see what the sentiment score is based on location

Now I can see the sentiment score by Location.

Azure has provided the Cognitive Text services to analyse the data for a score.

It would be useful to understand what tweets are driving the sentiments by location.

I would also prefer to group on Positive neutral and negative Scores

In my next post I am going to look at how to create a sentiment description and add Description Name and Count into the Flow and streaming dataset