No data in streaming dataset from Azure Stream Analytics to Power BI

[ttrail]

I have created a Azure Stream Analytics job that outputs to Power BI.    I successfully tested the input, query and output and started the job running.  In Power BI Service, the dataset shows up under "Streaming Datasets" and I can see & configure (set data type) the fields that are being sent, however, I cannot use those fields to build a visualization.

If I try to build a report, I always get "The visualization has unrecognized fields".  I believe it's because there's no data available.

streaming dataset fieldsfield has no data

Similarly, if I try to add a tile directly to the dashboard, it will not render with data.  After waiting over an hour, if I try the Q&A I finally can see hints related to this dataset, but any input starts a "This will take a few moments..." spinner that spins endlessly.

Demos I've seen imply the streaming dataset should be available immediately.  Yesterday, I had this working and generating a report, but then for some unknown reason this morning that report said the fields were "unavailable".  Does anyone know how I can troubleshoot or resolve this?

[Anonymous]

Hi @ttrail,

I think the issue may related to your datetime fields:

Perhaps you can format these fields at query and try again.

Reference links:

Stream Analytics Query Language Reference

Date and Time Functions (Azure Stream Analytics)

Regards,

Xiaoxin sheng

[ttrail]

Thanks for the reply @Anonymous, however I don't quite understand what you're implying.  Here's some more information to work with:  

Below is the Stream Analytics Query I'm using:

SELECT
  readingdate,
  CONCAT(readingdate, ' ', readingtime) AS datetime,
  avg(soilmoisture*.393701) as soilmoisture,
  name
INTO
  [my_output]
FROM
  [myinput]
GROUP BY TumblingWindow(minute, 3), readingdate, readingtime, name

with the results of the Query test:

readingdate,datetime,soilmoisture,name
2016-10-21,2016-10-21 11:30:00,12.061726198368001,SoilSensor_16in
2016-10-21,2016-10-21 11:00:00,12.088480149823,SoilSensor_16in
2016-10-21,2016-10-21 11:00:00,12.183128626130001,SoilSensor_28in

As you can see, "readingdate" is actually just a date, whereas "datetime" is actually a DateTime combination.  When I pull up this streaming dataset in Power BI Service, the only type options available to me are Text, Number or DateTime.  Since I use these two fields as my x-axis, I choose DateTime.

[Anonymous]

Hi @ttrail,

I'd like to suggest you use the date format function to format the value. In your query, you merge the value and try to convert it to datetime, it may cause some compatibility issue, you can try to use below date format function to format the date.

Function:

DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)

Example:
SELECT EntryTime, DATETIMEFROMPARTS(2014,9,10,12,DATEPART(minute,EntryTime)+10,00,00)
AS ExitTime
FROM Input TIMESTAMP BY EntryTime
WHERE Toll > 5

Referece link:

DATETIMEFROMPARTS (Azure Stream Analytics)

Regards,

Xiaoxin Sheng

[ttrail]

Thanks for the suggestion, @Anonymous.  I changed the query to replace the CONCAT which at least made my data accessible (although not real time -- see details below).  The revised, ridiculously complex query is now:

SELECT
readingdate,
DATETIMEFROMPARTS(DATEPART(year, readingdate), DATEPART(month, readingdate), DATEPART(day, readingdate),
DATEPART(hour, readingtime), DATEPART(minute, readingtime), DATEPART(second, readingtime), 0) AS datetime,
avg(soilmoisture*.393701) as soilmoisture,
name
INTO
[soilsensorpowerbi]
FROM
[collineariothub]
GROUP BY TumblingWindow(minute, 3), readingdate, readingtime, name

The weird thing is the difference though.  Now, rather than showing up in Power BI as a "Streaming dataset", it shows up under Datasets as a regular dataset.  This means the data in my reports does not automatically refresh real-time and I cannot add a tile to a dashboard since it is not recognized this as a streaming dataset.  Ugh!  Guess I need to create another separate post.