Azure Percept to Stream Analytics to PowerBI DirectQuery connection with PowerQuery calendar

Question

Problem Definition : I have Azure Percept data with a timestamp being fed through Azure Stream Analytics to a PowerBI report. I want to add a custom calendar table and tie it together with a relationship but the datetime data type is not allowing the relationship to work correctly between the two tables in PowerBI desktop.

Possible solutions : is there a way to change data type for a field in a DirectQuery connection or is there a way to define the data type as date and create a separate field for time in stream analytics?

Steps to reproduce :

1. Start Azure Percept
2. Start Stream Analytics Job
3. Start PowerBI Desktop and use Get Data -> PowerBI Datasets to connect to the dataset in PowerBI service
4. Create calendar table in PowerQuery
5. Connect the two tables together with a relationship from date field in calendar table to datetimestamp field in powerBI service dataset.
6. Popup asks if you want to convert to DirectQuery connection, say yes and then connect the two date fields.
7. But in the report view a slicer on the calendar table date field does not filter the other tables that are a directconnection.

What I have tried :

• Changed the format of the datetimestamp field from direct query to date format (no timestamp shown), but that doesn't work.
• Reviewed documentation for stream analytics, to try and cast to a date data type, but there doesn't seem to be a date data type supported.

The query code (for Stream Analytics) that I use to place the Percept data into PowerBI.

SELECT
    Percept.ArrayValue.label,
    Percept.ArrayValue.confidence,
    GetArrayElement(Percept.ArrayValue.bbox, 0) AS bbox0,
    GetArrayElement(Percept.ArrayValue.bbox, 1) AS bbox1,
    GetArrayElement(Percept.ArrayValue.bbox, 2) AS bbox2,
    GetArrayElement(Percept.ArrayValue.bbox, 3) AS bbox3,
    Percept.ArrayValue.bbox,
CAST (udf.main(Percept.ArrayValue.timestamp) as Datetime) as DETECTION_TIMESTAMP,
    Percept.ArrayValue.timestamp
INTO
    "PowerBI Output"
FROM
    "IoT HUB Input" as event
    CROSS APPLY GetArrayElements(event.Neural_Network) AS Percept
WHERE
    CAST(Percept.ArrayValue.confidence as Float) > 0.6

Answer 1

I think you can implement something like below on ASA side . The logic which I am trying to put around is read the 'Part" of the datetime and concatenating the same to construct the date and time field .

,CONCAT ( DATEPART ( YYYY, (udf.main(Percept.ArrayValue.timestamp)) , '-' , DATEPART ( mm ,(udf.main(Percept.ArrayValue.timestamp)), '-',DATEPART ( dd ,(udf.main(Percept.ArrayValue.timestamp) ) as 'Date-Only'

,CONCAT ( DATEPART ( hr, (udf.main(Percept.ArrayValue.timestamp)) , ':' , DATEPART ( mi ,(udf.main(Percept.ArrayValue.timestamp)), ':',DATEPART ( ss ,(udf.main(Percept.ArrayValue.timestamp) ) as 'time Only'

Also you mentioned that you are using Direct query to pull the data in PowerBI , I understand that you can implement simalar logic on the PowerBI side also

Do let me know how it goes .

Answer 2

If I understand correctly, you have a Power BI output in your ASA job, and not a table in a database.

If that's the case, what is created in Power BI to hold the data is a Streaming Dataset. One of the limitation of Streaming Datasets is that they can't be part of a model. I don't understand why Desktop is allowing you to create a relationship, it should not be possible.

If you need the relationship, you can either:

• Switch from a Power BI output to an Azure SQL output for ASA, targeting a newly created table. Then build your model on top of that table using Direct Query mode. It's less of a real time solution, but you get long term storage of your data in a SQL database.
• Switch from ASA to the newly released (and still under preview) Power BI Streaming Dataflows. Under the hood it's still ASA, but in addition to a development experience more consistent to Power Bi, you get the new output tables with hot and cold storage.