locked
Sending data to powerBI RRS feed

  • Question

  • Hey guys, 

    This is the situation: 

    I am sending data from <g class="gr_ gr_59 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="59" id="59">azure</g> to power bi through a stream analytics job. I was able to plot one single point vs time in every event I sent from azure to <g class="gr_ gr_60 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="60" id="60">powerbi</g>. The thing now is that I want to plot several points in one single event from azure to <g class="gr_ gr_61 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="61" id="61">powerbi</g>, Could that be possible in some way?

    This is the data I send to powerbi:

    [
    {
    "V1": 93.57827534934525,
    "V2": 34.91722801668005,
    "V3": 42.54934529271297,
    "timestamp": 1527848201,
    "EventProcessedUtcTime": "2018-06-01T10:17:05.3485519Z",
    "PartitionId": 1,
    "EventEnqueuedUtcTime": "2018-06-01T10:16:49.2130000Z",
    "IoTHub": {
    "MessageId": null,
    "CorrelationId": null,
    "ConnectionDeviceId": "NetPI",
    "ConnectionDeviceGenerationId": "636625164167937534",
    "EnqueuedTime": "2018-06-01T10:16:49.1730000Z",
    "StreamId": null
    }
    },
    {
    "V1": 83.86097741057826,
    "V2": 20.011153004817082,
    "V3": 35.73305430565414,
    "timestamp": 1527848202,
    "EventProcessedUtcTime": "2018-06-01T10:17:05.3485519Z",
    "PartitionId": 1,
    "EventEnqueuedUtcTime": "2018-06-01T10:16:49.2130000Z",
    "IoTHub": {
    "MessageId": null,
    "CorrelationId": null,
    "ConnectionDeviceId": "NetPI",
    "ConnectionDeviceGenerationId": "636625164167937534",
    "EnqueuedTime": "2018-06-01T10:16:49.1730000Z",
    "StreamId": null
    }
    }, .... more here --> this is an array of 10 object elements that is sent every input event

    As you can see I have several objects in the array but this array is sent in a single event and the <g class="gr_ gr_240 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="240" id="240">PowerBi</g> can only recognize the data within the first object. I would like to know if I can do something in the job query to be able to send several outputs from one single input event. Let's say I want to separate every object that comes in the input event and send each of them in X number of outputs

     

    Thank you in advance

    Regards, Alex


    Tuesday, June 5, 2018 8:57 PM

All replies

  • Sorry for the weird characters... don't know what happened
    Tuesday, June 5, 2018 9:17 PM
  • Can you give an example what your output would look like?
    Tuesday, June 5, 2018 11:37 PM
  • Let's say I have this input into the ASA job:

    [
      {
        "messageId": 1,
        "deviceId": "NetPi Test",
        "temperature": 10.5,
        "humidity": 44.5
      },
      {
        "messageId": 2,
        "deviceId": "NetPi Test",
        "temperature": 20.5,
        "humidity": 50.5
      }
    ]

    It is a <g class="gr_ gr_185 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" data-gr-id="185" id="185">json</g> with two objects containing some data

    What I want is to send these objects separately but to the same output, which is powerBI. The problem is that powerBi does not support multiple object data in one single event, so it can only recognise the data in object 1 (data in messageId 1). Then, I would need to send this objects at different times to the same powerBi output.

    So the output would be:

    output 'event' 1

    {
        "messageId": 1,
        "deviceId": "NetPi Test",
        "temperature": 10.5,
        "humidity": 44.5
      }

    output 'event' 2

    {
        "messageId": 2,
        "deviceId": "NetPi Test",
        "temperature": 20.5,
        "humidity": 50.5
      }

    Regards, Alex


    • Edited by AFdezRol Wednesday, June 6, 2018 9:01 AM Weird characters appearing
    Wednesday, June 6, 2018 8:59 AM
  • I see your output and i think that it is missing the DATETime Field in general but maybe this might help

    
    SELECT
            messageid    AS [Message Id]
        ,   deviceid                AS SensorName
        ,   temperature           AS Temperature
    
        , humidity
    
    INTO   powerbioutput
    FROM   input 


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). http://sqldataside.blogspot.ca/ (SQL Tabular + PowerShell)

    Saturday, June 9, 2018 7:56 PM
  • What I want is to send these objects separately but to the same output, which is powerBI. The problem is that powerBi does not support multiple object data in one single event, so it can only recognise the data in object 1 (data in messageId 1). Then, I would need to send this objects at different times to the same powerBi output.

    Not sure about that but maybe i am missing something

    by using a simple file like your i have generated a power Bi that can handel 2 different Device 

    i used the exact query like the mentioned and its working

    SELECT
            System.Timestamp    AS OutputTime
        ,   dspl                AS SensorName
        ,   Avg(temp)           AS AvgTemperature
    INTO
      powerbioutput
    FROM
      blobinput TIMESTAMP BY time
    GROUP BY TumblingWindow(minute,1),dspl
    HAVING Avg(temp)>120

    good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). http://sqldataside.blogspot.ca/ (SQL Tabular + PowerShell)

    Saturday, June 9, 2018 8:00 PM