telemetry from sensors to Azure IotHub via StreamAnalystics to PowerBi visualization

[Wojtek]

Hi,

My target is to:

Display live telemetry data from sensros (connected to IoTHub & StreamAnalystics) on the same PowerBi report.

Solution for single sensor (working very well):

Sensor -> IotHub -> stream analystics --- (Query on stream analytics setting) --> PowerBi dataset -> report -> publish to web

I wish to do sam but for 3 sensors

Problem I have:

How to send data to PowerBi, so I can use telemetry from all 3 sensors on same report ?

Shall I send data from stream analystics to Data Lake/ etc and then to PowerBi  or all is about Query used in stream analystics ?

I am sending data from sensor as JSON file.

Thanks for any advices,

Wojtek

[v-yuezhe-msft]

@Wojtek,

It is not possible to combine these reports in same report if you have different Power BI dataset for each Sensor. However, you can create a dashboard, then pin visuals of these report to the dashboard and share the dashboard to users.

If you need to use all the data of three sensors in a single report, you can send data from stream analystics to Data Lake, and then use Power BI Desktop to connect to Data Lake to create report.

Regards,
Lydia

[Wojtek]

@v-yuezhe-msft

Thanks Lydia.

1st solution (dashboard pin) - working but It is NOT creating single dashboard with all sensors readings on it.

It is just diplaying all reports on one-page dashboard (need to scrool to see other reports connected to dashboard).

My target is to have one page with all sensors reading in small area (like we can do with single dataset and report).

2nd solution - I stongly believe this is solution for my needs.

Here I have lack of "know-how" knowledge.

My test message: (JSON) 1st device

brokers_out = {"messageId": msg_id, "deviceID": "deviceNameFirst", "temp": temp, "co2": co2,}

My test message: (JSON) 2nd device

brokers_out = {"messageId": msg_id, "deviceID": "deviceNameSecond", "pressure": pressure}

1. Streaming data from Stream Analytics to Data Lake

SELECT
       *
INTO
       datalakestoragegdansk
FROM
       iothub

2. Importing data from stream analytics into PowerBi

-> in PowerBi Desktop

--> GetData / Azure / Azure Data Lake Storage  --> same for URL and AL URI : Error "We cannot convert the value null to type Logical"

--> GetData / Blank Query /  Advanced Editor (https://community.powerbi.com/t5/Integrations-with-Files-and/Cannot-get-data-from-Data-Lake-due-to-e... -- using ADL URI/

Data are imported

Under Column "Content"

I am going to folder defined in Data lake Storage : datalakestorage/logs/{year}/{time}

In each date i have Binary files:

While selecting any I have only one Column and each message sent to IoTHub is in seperate row. 

But PowerBi "thigs" that all data are String like "pressure:1007.6875" , etc - what makes me problem in visualizating data.

 I strongly believe that is shall be : Column Name == pressure (from JSON file) , each row == float/int reading from JSON file.

How to import JSON file in same shape like it was sent to IoTHub/ Data Lake

or

How to convert file (in PowerBi) to do the job.

Thanks for any advices.

Wojtek

[v-yuezhe-msft]

@Wojtek,

Please click the highlighted button to expand the Content column.



Regards,
Lydia

[Wojtek]

Hi Lydia,

While selecting the marked button I can see single column with multiple raws. Each row represents telemetry from specific time.
The problem is that I have only one column.

Lydia,
1. I am sending from device to Azure IoTHub data in JSON format. Do I have to in Stream Analytics Output (setting as Data Leak) select JSON file as well ? I assume that than JSON received by Stream Analytics shall be push to PowerBi WITHOUT any changes or modifications. Am I right ?
2. Let's assume that point no 1 is correct and I have JSON file in PowerBi. How can I parse received file and devide it into more columns ?
While sending data from stream analytics directly to PowerBi (WITHOUT Data Leak) all is right (JSON has same "columns" and "values")

Thanks !

[v-yuezhe-msft]

@Wojtek,

I am not familiar about Stream Analytic. As long as you transfer data from Stream Analytics to Azure data lake store, you can connect to the Azure data lake store data source in Power BI Desktop. What is the data like after you expand the above Content column?

In addition, after you connect to JSON file in Power BI Desktop, you still need to expand columns to extract data.

Regards,
Lydia