locked
ASA - outputs RRS feed

  • Question

  • Hello,

    I have a few questions related to ASA outputs:

    1) In my ASA Job I want two outputs:
    - Azure Table Storage (ATS) and
    - Power BI (PBI)

    I wrote the following query (using the field I provide for timestamp):

    WITH Events AS (
    SELECT
        deviceId,
        System.TimeStamp AS [timestamp],
        sensor_type1,
        MAX(value1) as value1,
        unit1
    FROM
        input (= Event Hub)
        TIMESTAMP BY [timestamp]
     GROUP BY deviceId, sensor1, unit1, TumblingWindow(second, 60)
    )

    SELECT * INTO outputpbi FROM Events;
    SELECT * INTO outputats FROM Events

    In ATS I would like in my events table a record as follows (what results from SELECT in the query):

    PartitionKey -> deviceId
    RowKey -> [timestamp]
    Timestamp -> provided by ATS
    sensor_type1
    value1
    unit1

    In Power BI this gives of course the same result. So this results in an Y-axis title "value1" and X-axis title datetime. Instead, in PBI, I would prefer labels "Temp" (instead of value1) and "Time" (instead of datetime). How can I achieve this? Or can I change the titles/labels in PBI?

    2) When creating the output for Table storage I have to enter PartitionKey and Rowkey they are respective the deviceId and [timestamp]. However when trying to omit the [timestamp] in my query my table remains empty. When having the [timestamp] (as in my query above) I have the same [timestamp] twice: as rowkey and as a separate column?

    3) The timestamps in my outputs are my time (Belgian time) - 2 hours? However, when looking in Event Hubs the time is correct.

    Many thanks for any help.
    Guy

    Wednesday, September 30, 2015 3:17 PM

Answers

  • Hi Guy,

    Both Neils and I were suggesting the same thing. You can use alias only for the powerBI output, and keep Azure Table storage output as is.

    Below is an example

    WITH Events AS (
    SELECT
        deviceId,
        System.TimeStamp AS [timestamp],
        sensor_type1,
        MAX(value1) as value1,
        unit1
    FROM
        input (= Event Hub)
        TIMESTAMP BY [timestamp]
     GROUP BY deviceId, sensor1, unit1, TumblingWindow(second, 60)
    )

    SELECT [deviceId], [timestamp] as [time], [value1] as [temp] INTO outputpbi FROM Events;
    SELECT * INTO outputats FROM Events

    • Proposed as answer by nielsbMVP Friday, October 9, 2015 6:32 AM
    • Marked as answer by Guy Dillen Sunday, October 18, 2015 9:00 AM
    Monday, October 5, 2015 5:53 PM

All replies

  • Hi,

    1. You can rename the columns in the Select query if you want different names in PBI. For example, you can do below

    select  deviceId, [timestamp] as  [time], [value1] as [Temp] into outputpbi from Events

    2. Since you have set RowKey = TimeStamp, you will have to include it in the select query. Azure table has a timestamp column for all the rows by default, this property cannot be removed.


    • Proposed as answer by nielsbMVP Friday, October 9, 2015 6:32 AM
    Wednesday, September 30, 2015 9:27 PM
  • Thanks. I know i can use an 'alias' new name. The problem is in table storage i want value1, ... in my table but in PBI i want temp. Is there i way to achieve this in 1 job and query? Guy
    Wednesday, September 30, 2015 9:48 PM
  • Thanks. I know i can use an 'alias' new name. The problem is in table storage i want value1, ... in my table but in PBI i want temp. Is there i way to achieve this in 1 job and query? Guy

    Can't you instead of doing SELECT * INTO ... instead do SELECT someColumnName AS value1 etc?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    • Proposed as answer by nielsbMVP Friday, October 9, 2015 6:32 AM
    Thursday, October 1, 2015 12:59 PM
  • Hi Guy,

    Both Neils and I were suggesting the same thing. You can use alias only for the powerBI output, and keep Azure Table storage output as is.

    Below is an example

    WITH Events AS (
    SELECT
        deviceId,
        System.TimeStamp AS [timestamp],
        sensor_type1,
        MAX(value1) as value1,
        unit1
    FROM
        input (= Event Hub)
        TIMESTAMP BY [timestamp]
     GROUP BY deviceId, sensor1, unit1, TumblingWindow(second, 60)
    )

    SELECT [deviceId], [timestamp] as [time], [value1] as [temp] INTO outputpbi FROM Events;
    SELECT * INTO outputats FROM Events

    • Proposed as answer by nielsbMVP Friday, October 9, 2015 6:32 AM
    • Marked as answer by Guy Dillen Sunday, October 18, 2015 9:00 AM
    Monday, October 5, 2015 5:53 PM