Introduction

Power BI is a business analytic service providing interactive visualizations. Supported data sources include a wide range of file types, databases, Microsoft Azure services, and many other third-party online services. A common flow of work in Power BI begins by connecting to data sources and building a report in Power BI Desktop. That report is then published from Desktop to Power BI service and shared so users in service and mobile can consume (view and interact with) the report. 

Power BI Desktop supports data sources including a wide range of file types, databases, Microsoft Azure services, and many other third-party online services. The supported data source is available under the ‘GetData’ Ribbon.

In case that data source is not available in the list, users may import data from well-known file formats (i.e. text/CSV, XML, JSON). The DataScope Select/ ThickHistory provide data in CSV format, so users can generate data in Web GUI, and then import the file to Power BI Desktop. Anyway, users can also develop “Query Editor” in Power Query Formula Language to connect to new data source. Microsoft also provides a Custom Connectors SDK for third parties to create their own data connectors and seamlessly plug them into Power Query.

In this article, we are going to demonstrate how to create a new custom data connector to connect to Datascope Select/Tick History product via DSS REST API. The new connector extracts TRTH ElektronTimeSeries and DSS IntradayPricing report template data via DSS REST API, so the basic understanding of DSS REST API is required. We would recommend reviewing the Connecting to Server and On Demand data extraction workflow tutorials first.

Quick Start

To help you understand how the connector works, below are the steps to use the custom connector.

  1. Install the Power Query SDK for Visual Studio.

To develop a Custom Connector, the Power Query SDK needs to be installed. The SDK is available in Visual Studio Market Place. It works with Visual Studio 2015 and 2017. The SDK will add a new Data Connector project template (.mproj).

The template contains the following files:

  • Connector definition file (.pq) – contain connector logic.
  • A Query test file (.query.pq) – implement query to test the connector.
  • A String resource file (resources.resx) – contain resources used by the connector (i.e. string description)
  • PNG files of various sizes used to create icons
  1. Install Power BI Desktop.
  2. Enable Custom data connectors in Options and settings -> Options menu.

  1. Change Security setting to allow an uncertified extension to load without validation or warning.

  1.  Build and deploy the connector

The sample code and projects file can be downloaded from this link. Once the package is unzipped, please open and build the project file (.mproj). The extension file (.mez) will be created in the “bin\Release” folder. Please copy the file to the “[Documents]\Power BI Desktop\Custom Connectors” folder and then restart the Power BI Desktop.

  1. In Power BI Desktop, select the “Get Data” -> “Other”. You will see that new extensions are added.
    • DSSDemoExtension-TRTH ElektronTimeSeries (Beta) - retrieve Time Series historical data for last 30 days via the TRTH ElektronTimeSeries template. The extension accepts a RIC string as input.
    • DSSDemoExtension-DSS IntradayPricing (Beta) – retrieve current market data via DSS IntradayPricing for three defined currency RICs: JPY=, EUR=, INR=.

  1. Select the TRTH ElektronTimeSeries which retrieve last 30 days of timeseries for a specific RIC. The extension is defined in Beta mode, so the following pop-up about under development is displayed. Select “Continue”.

Then, the following UI to input parameters for the connector will be displayed. The created connectors for TRTH TimeSeries data accept a parameter for RIC name.

After that, the Credential UI will be displayed to input the DSS Username and Password. Input the DSS Username and Password and click “Connect”. The username and password will be provided to TRTH customers and are valid both in the API and the GUI.

               Finally, the extracted data will successfully be loaded into Power BI Desktop. The preview data is below.                            

 

How to develop the connector

Next, we are going to provide an explanation on how this extension is implemented so that you are able to modify the request as needed.

Please note that this article will not describe the Connector Extension implementation and Power Query in detailed. If you need detailed information, please look into the Data Connector SDK and samples and Power Query reference document.

Define credential

An extension can support one or more kinds of Authentication (such as OAuth, Windows, etc). Once the extension is loaded, Authentication UI will be displayed in the format of Authentication kinds defined in the extension. DSS REST API normally uses DSS Username and Password to get a token for any API capability access, so we use the “UsernamePassword” kind. For more information about Authentication kinds, please see this link.  

// Data Source Kind description

DSSDemoExtension = [
    Authentication = [
        UsernamePassword = [
            UsernameLabel="DSS Username",
            PasswordLabel="DSS Password"]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

On Demand extraction

This extension follows the same On Demand extraction workflow of DSS REST API as other DSS REST API. The steps in the workflow are shown below.

  1. Request authentication token
  2. Request historical data from the server, using an on demand request
  3. Check the request status
  4. Retrieve the data

The main connector function is created following these steps.

First, it requests a token with Username, Password, and a current date and returns a token string. The reason for passing the current date will be explained later. We then use the token string for all API calls.

Next, in the ExtractTRTH_TimeSeries() function, the extension requests historical time series data using an on demand request. The function will also check the request status and finally returns the jobId string. Anyway, if the request is unsuccessful, the function will return the error object with detailed information.  

Finally, if the request is successful, the extension will call GetData function to load the data.

[DataSource.Kind="DSSDemoExtension", Publish="DSSDemoExtension.Publish1"]
shared DSSDemoExtension.TRTHEndOfDay = (DSSUserName as text,DSSPassword as text, Ricname as text) =>
    let 
    currentdate = Date.ToText(DateTime.Date(DateTime.LocalNow())),
    //1)	Request authentication token
    token = Login(DSSUserName,DSSPassword,currentdate),
    //2)	Request historical data from the server, using an on demand request
    //3)	Check the request status
    jobId = ExtractTRTH_TimeSeries(token,Ricname),
    //4)	Retrieve the data
    source = if Value.Is(jobId, type text) then
        GetData(token,jobId)
    else 
        jobId
    in
        source;

 

Login() function sends a token request to the RequestToken endpoint with DSS username and password. Any REST API request is sent using the Web.Contents function which supports both HTTP POST and GET. If the Content is defined, it will use POST.

According to this helpful blog, using Web.Contents, Power BI automatically caches HTTP response and then gets the response from the cache once the same request was made. This means that Power BI always reuse the token from the cache since the DSS Username and Password are rarely changed. However, the token is only valid for 24 hours. Reusing the token can cause “401” authorization errors. To avoid this issue, we need to add the current date in a header to make the request different from one in the cache.

// Request authentication token
Login = (user,pass,currenttime) =>
    let
        endpoint = "https://hosted.datascopeapi.reuters.com/RestApi/v1/Authentication/RequestToken",
        body = "{""Credentials"": {""Username"": """ & user & """,""Password"": """ & pass & """}}",
        response=Web.Contents(endpoint,[
                        Headers=[#"Content-Type"="application/json",
                                MyHeader=currenttime], 
                        Content=Text.ToBinary(body)]), 
        responseBody= Json.Document(response),
        value = responseBody[value]
    in
        "Token"&value;

ExtractTRTH_TimeSeries() function sends On Demand extraction request to ExtractRaw endpoint. The body is for TimeSeries data with 30Days ago condition. You can modify the request body as you want. For other report templates and samples, please see this tutorials and API Reference tree.

ExtractTRTH_TimeSeries = (token, ric) =>
    let
        endpoint = "https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/ExtractRaw",
        body =
        "{
            ""ExtractionRequest"":
            {
                ""@odata.type"": ""#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.ElektronTimeseriesExtractionRequest"",
                ""ContentFieldNames"":
                [
                    ""Instrument ID"",
                    ""Bid"",
                    ""Ask"",
                    ""High"",
                    ""Low"",
                    ""Last"",
                    ""Volume"",
                    ""Trade Date""
                ],
                ""IdentifierList"":
                {
                    ""@odata.type"": ""#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList"",
                    ""InstrumentIdentifiers"":
                    [
                        {
                            ""Identifier"":"""& ric &""",
                            ""IdentifierType"":""Ric""
                        }
                    ]
                },
                ""Condition"":
                {
                    ""ReportDateRangeType"":""Relative"",
                    ""RelativeEndDaysAgo"": 1,
                    ""RelativeStartDaysAgo"": 30
                }
            }
        }",
        response = Web.Contents(
                        endpoint,
                        [Headers=
                            [#"Authorization"=token,
                             #"Content-Type"="application/json",
                             #"Prefer"= "respond-async;wait=1"],
                         Content=Text.ToBinary(body),
                         ManualStatusHandling={400,202}]),
        status = Value.Metadata(response)[Response.Status],

        jobId=if status = 200 then Json.Document(response)[JobId]
        else if status = 202 then CheckStatus(token,Value.Metadata(response)[Headers][#"Location"])
        else Error.Record("HTTP Status",Text.From(status), Json.Document(response)[error][message])

    in
        jobId;

 

On Demand extraction requests are executed as soon as possible. There is no guarantee on the delivery time, it depends on the amount of requested data, and the server load.

In the request, we set a preference for an asynchronous response. We will get a response in 30 seconds (default wait time) or less. If the extraction complete within 30 seconds, the server will respond with 200 OK status. The connector will get “JobId” from response’s body.

Otherwise, it will return 202 Accepted status which means processing has not yet completed. The response’s header contains “Location” which is used to check request status. The connector will get value from “Location” header and repeatedly check status in the CheckStatus() function. Once the process is complete, the 200 OK status will be received with “JobId” in the response.

The Value.WaitFor is a helper function which is useful when making an asynchronous HTTP request, and you need to poll the server until the request is complete.

CheckStatus = (token,location) =>

    let
        jobId = Value.WaitFor(
        (iteration) =>
            let
                url = location,
                response = Web.Contents(url, [Headers=[Authorization=token, #"Content-Type"="application/json"],ManualStatusHandling={202}]),

                status = Value.Metadata(response)[Response.Status],

                jobId=if status = 200 then Json.Document(response)[JobId]
                else null
            in
                jobId,

        (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),5)
    in
        jobId;

It is also possible that a 400 Bad Request with an error message is received when the request is invalid. The connector will verify, and then return Error.Record instead of JobId string.

For the last step, if the request is valid and complete, the GetData() will get the extracted result using the JobId from the previous step. The result normally is CSV format compressed in GZIP. However Power BI automatically decompresses the result, and then the connector converts the string data to CSV format.

GetData = (token,jobId) =>
    let
        endpoint = "https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/RawExtractionResults('" & jobId & "')/$value",

        Source = Csv.Document(Web.Contents(
                endpoint,
                [Headers=
                    [Authorization=token,
                    #"Content-Type"="application/json"]
                ]),
                [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])
    in
        Source;

 

Troubleshooting

Running Power Query in Visual Studio with Power Query SDK

With the Power Query SDK, you can run the debugger for the connector development. The debugger will run on the ".query.pq" file extension which generally calls the connector extension. The output is similar to the result generated on the Power Query Desktop. Below is the DSSDemoExtension.query.pq file.

// Use this file to write queries to test your data connector

let
    Source = DSSDemoExtension.TRTHTimeSeries("JPY=")
in
    Source

Package sniffer

When I try to develop this connector and find an issue, Power Query SDK provides limited information. I use a network sniffer such as Fiddler tool to monitor requests and responses sent between the connector and server. With the sequence of requests/responses, you can also use them to replicate issue using Postman. Some useful information is provided in this blog.

Power BI Desktop cache

In some scenarios, cache management of Power BI Desktop may cause unexpected behavior. You may try to clear cache to see if the issue can be solved. Please follow the steps in this link for instructions

Summary

In this article, we demonstrate how to develop a connector extension for Power BI to connect to TRTH/DSS data. It uses DataScope Select REST API to connect, retrieve data using On Demand extraction method. This allows TRTH/DSS data integration in Power BI. You can add/modify the connector to extract other report templates to serve your requirements.

Reference

DataScope Select REST API Tutorialhttps://developers.refinitiv.com/datascope-select-dss/datascope-select-rest-api/learning

Tick History REST API Tutorialhttps://developers.refinitiv.com/thomson-reuters-tick-history-trth/thomson-reuters-tick-history-trth-rest-api/learning

Basic Power Query - https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query

Power Query M Reference - https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

Data Connector SDK and samples - https://github.com/Microsoft/DataConnectors