API Documentation: get.myhistory

by

Purpose

The get.myhistory API and stored procedure facilitate interaction with the DataWarehouse, enabling users to retrieve historical views for analysis. Designed for PowerBI and Power Applications, it offers flexible parameters, customisable views, and outputs data in JSON format.

By combining the adaptability of SQL stored procedures with a RESTful API, get.myhistory delivers a robust solution for accessing and analysing historical data. The procedure leverages the Disaster Recovery Data and Snapshot Store, which is backed up every Sunday, ensuring the returned data reflects the last known value from each week ending on a Sunday.

API Features

Security

  • Authentication: Requires an email address in the HTTP header and a token in the URL.
  • Validation: Email is validated as an existing user in myBMT.

Parameters

  • Required:
    • token: Paired value associated with the header value.
    • datamart: Name of the data mart (e.g., project, employee, customer).
    • viewname: Name of the view to retrieve (e.g., details_plus).
  • Schedule
    • end_date: last week in series
    • months: Number months to be included
    • interval: weekly (all snapshots), monthly (Last snapshot in month)
  • Optional:
    • whereClause: SQL condition for filtering results.
    • rowNum: Limits rows returned (default: 30000).

Response

  • Data is returned as a JSON array.

Defaults

  • version: beta ONLY
  • rowNum: 30000 rows
  • end_date: Today()
  • months: 6
  • interval: weekly

Paging and Row Limits

The get.myhistory API does not currently support paging functionality. This means that the responsibility for ensuring manageable data retrieval lies with the URL author or operator. To prevent excessive data retrieval, the following constraints and options are in place:

  • Row Limit: The API enforces a maximum of 1,000,000 rows per request.
  • Interval Constraints:
    • Use the end_date parameter to specify the latest date in the series.
    • Use the months parameter to define the range of data (e.g., retrieve data from the last 6 months).
    • Use the interval parameter to choose between weekly (all snapshots) or monthly (last snapshot in a month).

By carefully configuring these parameters, you can tailor the query to retrieve only the data required, reducing unnecessary load on the system and ensuring efficient data handling.

Example Usage

URL

https://bmt-dwh-uks-app-api.azurewebsites.net/get_myhistory?
   token=<myToken>&
   datamart=project&
   viewname=details_plus&
   months=3
HTTP Header
'Authorization: my.email@uk.bmt.org'

cURL Example

$curl = curl_init('https://bmt-dwh-uks-app-api.azurewebsites.net/get_myhistory'); 

$params = array(
    'token' => '<myToken>',
    'datamart' => 'project',
    'viewname' => 'details_plus',
    'months'=3,
);

curl_setopt_array($curl, array(
  CURLOPT_POSTFIELDS => http_build_query($params),
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_CUSTOMREQUEST => 'GET',
  CURLOPT_HTTPHEADER => array(
    'Authorization: my.email@uk.bmt.org',
  ),
));

$response = curl_exec($curl);
curl_close($curl);
echo $response;

Power BI Integration

= Json.Document(Web.Contents("
https://bmt-dwh-uks-app-api.azurewebsites.net/get_myhistory?viewSchema=mvw&datamart=project&viewname=details_plus&env=prd&token=<myToken>"
, [Headers=[Authorization="my.email@uk.bmt.org"]]))

PowerShell

$url = "https://bmt-dwh-uks-app-api.azurewebsites.net/get_myhistory?viewSchema=mvw&datamart=project&viewname=details_plus&token=<myToken>"
$headers = @{
    "Authorization" = "my.email@uk.bmt.org"
}
$response = Invoke-RestMethod -Uri $url -Headers $headers -Method Get
$response | ConvertTo-Json

Leave a Comment