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
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
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