question

CharlesElwood-6092 avatar image
0 Votes"
CharlesElwood-6092 asked HimanshuSinha-MSFT commented

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

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 :

Start Azure Percept
Start Stream Analytics Job
Start PowerBI Desktop and use Get Data -> PowerBI Datasets to connect to the dataset in PowerBI service
Create calendar table in PowerQuery
Connect the two tables together with a relationship from date field in calendar table to datetimestamp field in powerBI service dataset.
Popup asks if you want to convert to DirectQuery connection, say yes and then connect the two date fields.
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

@AmiraYousif-2257 @JussiNiemela-7425


azure-stream-analyticsazure-percept
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CharlesElwood-6092 - thanks for your question and for using Azure Percept. I'm looking into your question and will get back to you soon. While I do that, here are some other resources you might want to check out.

Power BI has an active community and might be able to help - https://community.powerbi.com/

Here is Tech Community for Azure Analytics - https://techcommunity.microsoft.com/t5/analytics-on-azure/bd-p/AnalyticsonAzureDiscussion

Here is an article about outputting Stream Analytics to Power BI - https://docs.microsoft.com/en-us/azure/stream-analytics/power-bi-output

Thanks! -Mike

0 Votes 0 ·

Hi @CharlesElwood-6092 ,

Thanks for the ask and using the Microsoft Q&A platform .

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 .

Thanks
Himanshu


1 Vote 1 ·

@HimanshuSinha-MSFT @mimcco Thanks for the code snippits.
I tried two flavors, one with the CONCAT and one with just the DATEPART for year and I am getting a syntax error for both.
!
115646-image.png


0 Votes 0 ·
image.png (128.0 KiB)

@mimcco @HimanshuSinha-MSFT @AmiraYoussef-8715

Update on this path. Himanshu based on your feedback that it might be possible to change the data type with a DirectQuery connection I tried the following.
1) Connect to PowerBI Service Dataset in my Workspace in PowerBI Desktop
2) I then added a DataSet in PowerQuery with a custom calendar built in M
3) This created a mixed data model which was shown at the bottom right
117519-image.png
4) The Data Type was greyed out in the table that had the DirectQuery connection to the PowerBI Dataset in the cloud.

117600-image.png

5) I was able to create a "new table" with VALUES(TestTable71321[DETECTION_TIMESTAMP]), to reference the data in the DirectQuery connection.

The nice thing now, is that the data type can now be changed to a date type so that I can build a relationship to the custom calendar.
117613-image.png


This doesn't seem like the best way to convert the data type however, as it creates a reference table route. I also need to bring in the other values from the Percept, ie the label, confidence, and bounding box into this new reference table.

0 Votes 0 ·
image.png (4.7 KiB)
image.png (39.4 KiB)
image.png (89.6 KiB)

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT commented

Hello @CharlesElwood-6092 ,
My apoloziges for the late reply on this . But i just tested the logic which a dummy data ( I do not have Percept knowledge or access )and it works for me . Not sure if this helps .

Query

select EventProcessedUtcTime , concat (DATEPART ( yyyy ,EventProcessedUtcTime),'-',DATEPART ( mm ,EventProcessedUtcTime),'-',DATEPART ( dd ,EventProcessedUtcTime)) as Date ,
concat (DATEPART ( hh ,EventProcessedUtcTime) ,':',DATEPART ( mi ,EventProcessedUtcTime),':',DATEPART ( ss ,EventProcessedUtcTime) ) as Time
from EHInput

119166-image.png



image.png (54.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@HimanshuSinha-MSFT Thanks for this. I tried the datepart command on system.timestamp and that is working now too. I think previously I was applying datepart() to timestamp in milliseconds that was being passed through a UDF which I think was causing the issue for me.

0 Votes 0 ·

Hello @CharlesElwood-6092 ,
It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

1 Vote 1 ·