As part of the Data Insights Summit I will be teaching the “Dev in a Day” Pre Con with Ted Pattison. In this lab we will be going through how to build Reports using REST End Points and parsing JSON, Embedding Reports into your Website, Creating Dashboards with Streaming Datasets (this Post), Embedding Visuals into your applications and finishing with one of Ted’s specialties building custom Visuals for Power BI.
Power BI real-time streaming enables you to create reports and dashboards that you can stream data and update in real-time/near real-time. While there are three types of real-time datasets (Push, Streaming and PubNub) this lab will focus on hybrid Push and Streaming Datasets.
(This lab assume you have the new Navigation turned on: In settings under the preview features)
1. Creating a Streaming Dataset
To create a Streaming In Power BI Dataset click the App Workspace you want to create the streaming dataset in and select “Create” > Streaming Data.
If you are doing this in your work account you may consider creating a new App Workspace. For example the Image below is using a new App Workspace called “MDIS”
2. Select the streaming dataset type
For this lab we will be focusing on the API based Streaming Datasets…Select this type
3. Specify the data columns and data types.
For this lab please create a dataset with at least three columns one of which is type number (called Price) and two are of type Text (called Product and OrderedBy).
You will also want to select the option “Historic data analysis”
4. Finish the Streaming dataset Creation
Before you finish the creation please copy the PowerShell for adding rows to your dataset
5. Open a PowerShell prompt and paste in the test above
If you do run it at this point, you may want to run it twice so you can see the difference between the real time tiles and reports based on this dataset
If you prefer to do this from C# below is the code that can run from almost any project type.
string row = “[“Price” :98.6 ]”; //Use the cURL view for the Dataset in Power BI to grab this JSON
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(“https://api.powerbi.com/beta/08a0b824-56f9-4983-ac8a-3fd373f68466/datasets/d8f32510-3796-4c3d-8738-2903b9a67fb1/rows?key=fq1qobhbdg4wo1e9fJuIwl%2BlOugcDWAkoY1S6o5NVszD%2Bqrtzg%2Bf3g9mqMFKwMHUxe9IZgYWqk7ynrvdkai2bw%3D%3D”); //Use the I icon in Power BI to grab the Push URL
request.Method = “POST”;
request.ContentType = “application/json”;
request.ContentLength = row.Length;
StreamWriter requestWriter = new StreamWriter(request.GetRequestStream(), System.Text.Encoding.ASCII);
WebResponse webResponse = request.GetResponse();
Stream webStream = webResponse.GetResponseStream();
StreamReader responseReader = new StreamReader(webStream);
6. Create a report based on the real time streaming dataset.
Selecting the column chart glyph under datasets will create a report based on that streaming dataset.
You can not create a Power BI alert from a real time streaming tile, in order to create an alert from a dataset you need to use a Power BI Card, KPI or Gauge visual based in a report.
7. Add a card in a report based on the Streaming Data Set
Select the card visual and drag it to the report design surface. Select the numeric column for the field.
Note the value displayed should be the sum of the number of times you ran the PowerShell script above.
Run the PowerShell again at this point and note the latency for updating the value
8. Pin the card Visual to a new Dashboard
In the upper mid left area of a visual in a report you will see the thumb tack glyph will allow you to pin that visual to either a new Dashboard or an existing Dashboard.
9. Pin a Streaming Data Tile to the Dashboard
10. Select the Dataset for you Streaming Data Tile
10.5 Select the number you want to display
11. Rerun the PowerShell or the C# application to input a new data value into the real-time dataset…Why are they different?
Create a new real-time Data set In Power BI something like the following.
4. Create the Flow that triggers and collects the Twitter information.
To do this navigate to http://flow.microsoft.com, login and click on My flows.
Luckily for us there are a couple of templates that does much of what we want like the “Save Tweets to Google Sheet”, “Save Tweets to SharePoint List” or the “Save Tweets to Excel File”.
In this case i have opted to start with the “Email myself new Tweets about a certain keyword” as it also retrieves the user details.
6. Modify the Flow to include the Power BI activity, that inserts the data into the Power BI data set.
To do this is click on “Add an action”, search for “Power BI” and select the Power BI Add Rows to a dataset activity.
7. Map the Twitter output to the Power BI Activity that inserts the data into a Power BI Row.
As you can see from the screen shot below. The Power BI activity prompts you for all the data needed to make this happen.
In the screen shot below I have deleted the email activity and attempted to save the Flow before adding the hashtag for the tweets i wanted.
(Don’t include any spaces like i am showing for the hashtag and you should probably rename the Flow since it is no longer sending email!)
8. Create a Power report that shows the Twitter Data
Throwing a couple of visuals on the report i found it interesting the ESRI map understands Twitter locations (mixed up ordering, incomplete etc)but the Bing maps don’t.
(I will also demo this in my session)