Forum Discussion

ttrail's avatar
ttrail
Frequent Visitor
9 years ago

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

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 unde...
ttrail's avatar
ttrail
Frequent Visitor
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      ttrail
      Frequent Visitor
      9 years ago

      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.