The creation of Azure Stream Analytics inputs and outputs and what they are should now be very clear to you. It makes sense that you would need to create an output for Power BI to send the transformed streamed data to it. The input, as shown in the top portion of the query in Exercise 7.5, is the location from which the data is streamed to the Azure Stream Analytics job. The input is named brainwaves, which is the event hub you sent the brain wave readings to; it is referenced using the FROM clause.
WITH BrainwaveResults AS (SELECTSystem.TimeStamp() AS IngestionTime,
PERCENTILE_CONT(0.5) OVER (ORDER BY brainwaves.ALPHA) AS medianAPLHA,
PERCENTILE_CONT(0.5) OVER (ORDER BY brainwaves.BETA_H) AS medianBETA_H,
PERCENTILE_CONT(0.5) OVER (ORDER BY brainwaves.BETA_L) AS medianBETA_L,
PERCENTILE_CONT(0.5) OVER (ORDER BY brainwaves.GAMMA) AS medianGAMMA,
PERCENTILE_CONT(0.5) OVER (ORDER BY brainwaves.THETA) AS medianTHETA
FROM brainwavesGROUP BY IngestionTime, TumblingWindow(second, 5)),
The query uses a tumbling window function with an interval of 5 seconds. This means the stream of brain wave readings are stored into memory for 5 seconds before the logic in the SELECT statement is performed. Once the first portion of the query is performed, the next portion is executed. Notice that the result of the first portion of the query is stored into a variable named BrainwaveResults, which is then used in the following query as the source of the data, accessed using the FROM clause:
ScenarioDetection AS (SELECT medianAPLHA, medianBETA_H, medianBETA_L, medianGAMMA, medianTHETA,CASE
WHEN medianAPLHA> 4.3924 AND medianAPLHA <= 5.0287 THEN ‘Meditation’ WHEN medianBETA_H> 1.2994 AND medianBETA_H <= 1.38 THEN ‘Meditation’ WHEN medianBETA_L> 2.0487 AND medianBETA_L <= 2.1775 THEN ‘Meditation’ WHEN medianGAMMA> 0.8675 AND medianGAMMA <= 0.9364 THEN ‘Meditation’ WHEN medianTHETA> 2.344 AND medianTHETA <= 5.1052 THEN ‘Meditation’
ELSE ‘Unknown’END AS ScenarioFROM BrainwaveResults)
This portion of the query checks whether the median values calculated in the previous portion fall within the ranges expected of the meditation scenario. If they do, Meditation is stored into the Scenario column; if not, Unknown is placed into it. Finally, the data sent to the Power BI workspace is selected from the output of the second portion of the query from the ScenarioDetection variable and placed into the powerBI output sink.
SELECT Scenario, medianAPLHA, medianBETA_H, medianBETA_L, medianGAMMA, median THETA
INTO powerBI FROM ScenarioDetection
The first time you run the brainjammer.exe application to send data to the Power BI workspace, you need to create the dataset into which the stream will flow. Without a dataset, you cannot add a tile to the dashboard. The csharpguitar‐brainjammer‐pow‐50.json file in the Chapter07/Ch07Ex05 directory contains fewer brain wave readings. Only one needs to be sent to Power BI to create the dataset. Once the dataset is created, you can access the Power BI workspace and create the dashboard and add the Custom Streaming Data tile. The value selected for the Time Window to Display setting matched the tumbling window time frame of 5 seconds. You could make it 1 second, but that would not be prudent because data is sent to the dashboard only every 5 seconds. It does make sense to update the dashboard stream values with a time frame that matches the time frame in which the stream source sends the data. You might have noticed the additional card tiles in Figure 7.23. These tiles represent the median brain wave reading value placed into the dataset and presented in the dashboard. There is one card tile for each of the five frequencies. There are other types of tiles you might find interesting. Feel free to explore the additional features available in Power BI. Finally, be sure to stop the Azure Stream Analytics job once you complete Exercise 7.5, as it incurs cost even when not used.
