More Insights with Azure Time Series Insights’ Power BI Connector

Published Feb 10 2021 09:56 AM 1,827 Views
Microsoft

The Azure Time Series Insights Power BI Connector allows you to integrate both Azure Time Series Insights and Power BI allowing you to augment the powerful analytics of TSI with the data visualization and easy sharing capabilities of Power BI.  

 

The Time Series Insights team is regularly adding new features to its service and we wanted to share some details on the latest release of the Power BI Connectothat for users working with IoT data to create reports and dashboards of metrics in Power BI.   

 

If you’re new to Time Series Insights, check out the IoT Show episode showing how Time Series Insights can be used for industrial IoT analytics and explore the Time Series Insights Gen2 demo environment 

 

New Features in Time Series Insights Power BI Connector

Cold Store Support

The Power BI Connector now supports querying data across both Warm and Cold stores so you can export both your recent and historical data out to Power BI!

 

Multitenancy Support

The Power BI connector can now be used by any user with access to the Time Series Insights environment, regardless of the Azure AD Tenant they belong to. Read more about our access policies to learn how to provide access to a Time Series Insights environment.

 

Increased Raw Data Limit

When exporting raw data out from Time Series Insights, users can now take up to 250,000 raw events.

 

Power BI Connector Custom Query

The Time Series Insights Explorer generates a custom query for the Power BI Connector to use. See more about how to use the Power BI Connector here.

 

PBIScreenshot.png
[View of Time Series Insights Explorer]

 

The Explorer now allows you to modify your query by changing the type of data exported, the  storage layer (Warm or Cold) to run your query against, and the time range of your query. Choosing the “fixed” time range means the data in the search span you've charted will be exported to Power BI. Choosing the latest time range means that Power BI will grab the latest data with the search span you've chosen (e.g. If you chart 1 hour of data and choose the "latest" setting, Power BI Connector will always make queries for the latest 1 hour of data from the current time.)

 

The custom query generated by the explorer now includes a parameter called “storeType”, in addition to “isSearchSpanRelative”. These parameters change based on your selection of store type and time range in the Explorer, respectively.

 

Users can add or modify the individual GetEvents or AggregateSeries queries within the “queries” parameter in a text editor, as well, before exporting to Power BI! Read more about the Time Series Insights query APIs and the Time Series Expression (TSX) syntax.

 

Other parameters in the query such as the “clientDataType” and “environmentFqdn” parameters help the Power BI Connector know which environment to query and which API version to use. We don’t recommend modifying these parameters.

 

{
    "storeType": "WarmStore",
    "isSearchSpanRelative": true,
    "clientDataType": "RDX_20200713_Q",
    "environmentFqdn": "00000000-0000-0000-0000-000000000000.env.timeseries.azure.com",
    "queries": [
        {
            "aggregateSeries": {
                "searchSpan": {
                    "from": "2020-12-09T00:03:16.245Z",
                    "to": "2020-12-22T16:56:58.000Z"
                },
                "timeSeriesId": [
                    "Device_1210101_AV_101"
                ],
                "interval": "PT2H",
                "inlineVariables": {
                    "PresentValue": {
                        "kind": "numeric",
                        "value": {
                            "tsx": "$event['PresentValue'].Double"
                        },
                        "filter": null,
                        "aggregation": {
                            "tsx": "avg($value)"
                        }
                    }
                },
                "projectedVariables": [
                    "PresentValue"
                ]
            }
        },
        ...
    ]
}

 

 

The Time Series Insights Explorer requires that you download the latest version (December 2020 or 2.88.321.0) of Power BI Desktop to use the new features listed above.

 

Any existing reports using queries generated by the previous versions of the Power BI Connector and Time Series Insights Explorer will continue to be supported by the latest version of the Power BI Connector.

 

Pricing

Customers are charged $0.01 per GB scanned by each Cold Store query (see pricing details for your region) and Warm Store queries are free.

 

Each query made by the Time Series Insights Power BI Connector to Cold Store will incur charges, as well.

 

Next Steps

 

 
 
 
 
Co-Authors
Version history
Last update:
‎Feb 10 2021 10:00 AM