Integrating Microsoft Flows, Azure, with Power BI
When should I use it?
Some time ago I had heard of Microsoft Flows and the advantages provided as a tool.
After a cycle of research and performing various tests, I found a very good opportunity to try a deployment in Power BI Dashboard.
Basically, and to give some context, Microsoft Flows can automate processes across a variety of important information sources and systems. In turn it comes with a number of useful templates such as:
- When a new item arrives to a SharePoint list, automatically send an email.
- Automatically post to Yammer tweets containing specific hashtag.
- If a document is approved in a SharePoint library it automatically moves it to another library.
- When a new object is created in SalesForce, it creates a SQL table row.
- Send automatic mobile notification when an item is assigned to me in Visual Studio.
- Receive a mobile notification when a new video is posted on the Youtube channel of my company
One case I found interesting to make a complete cycle using Microsoft Flows, Azure and Power BI was the following example.
Flow: Tweets capture – Save to SQL Azure then Create with Power BI Report
In the following example I will show how to mount everything to use Microsoft Flows and define a flow with Azure to host data in a database and Power BI to consume information from that database. This will allow us to display live information from one or more hashtags of certain topic on Twitter and then generate reports with this information from Power BI.
Creating Database in Microsoft Azure
First we have an account of Microsoft Azure to create a new database.
Once we are in the Azure Portal, go to the option with the + symbol on our left area of the screen and then use the option of Data + Storage. Below is a picture of this step:
Once this step is done, define the settings that will assign to our database. In this case we define the settings that will give the BD and will use to save the tweets that we will be following:
At the end of this stage we have the information server, and access to the DB and then we connect to this database using SQL 2016 On Premises.
The next step is simply to open SQL Management Studio (‘ll be using the version of SQL 2016) and connect to SQL Server Azure. The purpose here is to generate a table to capture the data coming from Twitter.
The connection on SQL 2016 On Premises we will do as follows:
As seen in the picture above, you must define the Server Name, taken from Azure, and authentication is added as SQL Server authentication, the user name and password (this information also from Azure) .
Once connected from SQL Management Studio to our server in Azure will create a table where we will stay the information coming from the tweets. The fields are obviously suggested, you can add others if you want to track other information. Below the image of our new table named Twitter:
Fields: ID (Primary Key), RetweetCount, TweetText, TweetedBy, CreatedAt, TweetID, Search Term: The following fields are added in the table.
So far we perform the procedure to generate a database on our account Azure will help us to keep live all tweets that go to make up.
Setting new Flow from Microsoft Flows
As I mentioned in the beginning of this post, this new platform allows us to define flows and automate processes in a very diverse network of systems and platforms.
To start working we will enter the site of Microsoft Flows . Once we enter we will have to use our personal or corporate account to perform the login.
First we generate all the connections that we will use in our flows. To do this we will click on My Connections option in the upper right section of the screen below our user profile. Once we entered, Create Connection. In this case we create connections with Twitter and SQL.
In this case we are adding the connection to Twitter where we specify the user of Twitter and the corresponding password.
In the next connection information must specify the SQL Server information:
Take note that all information requested and incorporated in the Portal Azure and SQL Management Studio when running the connection between our server and Azure On Premises.
Once we finish this process we will be able to create our new flow. In the upper area of the screen we go to My Flows. In this screen use the option Create from Blank.
In the steps that will define our flow go ahead with two main actions: Take the tweets with a particular hashtag, in our case will define the hashtag #PowerBI, and use the connection to our Twitter account defined a couple of steps before .
Once selected as a first step the flow is going to listen about a certain hashtag tweets issued, the next step will be to define a new action as the next step.
In this new action we will look for the option of SQL Server, where we have a variety of actions to be executed. In our case we use the Insert Row option as we look to add the information for each tweet with the hashtag PowerBI in our SQL Server table.
By selecting this step that SQL Server will give us for the information of the table on which to add information, and then select the fields that will complete our Tweet in various fields of the table. Take note that the fields were created in order that we do a simple mapping between fields and fields of Twitter we have in the SQL Server table.
Upon completion of this step we define in the upper screen area a name that identifies our flow: in this case I defined for example PowerBI Get hashtag to SQL Azure.
When the flow is completed and begins to get the data from each tweet posted and where the hashtag PowerBI were used.
I recommend to add two or three more Flows replicating this process to get other hashtags in which we are interested in making up (maybe SharePoint, Azure, Office365).
Creating Report on Power BI Tweets
The generation of our Dashboard can perform both using the Power BI Desktop and Power BI Web. In this case I will use Power BI Web as would show the direct ability of the web version of our platform to develop a report and dashboard from scratch in the cloud.
We will connect to portal Power BI Web with our account. Once we agree we will click on the lower left area where we will have the option to Get Data:
You get the option to use the Data connection to SQL Azure Data Warehouse where we request information server and the database. This is the same information that we had entered in Azure Portal and the SQL Management Studio console:
When generating the connection, we will have a new DataSet that allow us to have our Table fields, which are already getting all live data from Twitter with the hashtag defined.
With simple native tools of our Power BI Report generation to provide us with information Tweets cast (number of Tweets and Retweets), an slicer filtering by topics or hashtags, and users who have generated more tweets.