API Documentation: get_myview

by

Purpose

The get.myview stored procedure API allows Power Applications to interact with the DataWarehouse by performing SELECT queries on specified DataMart views.

The results are returned as a JSON array.

Security

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

Parameters

Required

  • token: Paired value associated with the header value.
  • dataMart: DataMart name, e.g., Project, Employee, Customer, etc.
  • viewName: View name, e.g., details_plus.

Optional

  • version: Default is the last stable version. If ‘none’ is specified, the beta development version is provided.
  • viewSchema: Default is ‘mvw’. Can be switched to an alternative SQL schema.
  • whereClause: SQL statement to filter results.
  • selectFields: Default is ALL ('*'). Used to restrict fields returned, list fields separated by commas.
  • rowNum: Default is 30000. Used to limit rows for testing. If >0 is specified, all rows will be returned.
  • env: Default PRD. Used to change between Production and Development environments for UAT

Typical Use

URL

https://bmt-dwh-uks-app-api.azurewebsites.net/get_myview?
   token=<myToken>&
   datamart=project&
   viewname=details_plus&
   whereClause=STATUS NOT IN ('closed','cancelled')&
   selectFields=COMPANY,PROJECT_ID,CUSTOMER_ID
HTTP Header:
'Authorization: my.email@uk.bmt.org'

cURL Example:

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

$params = array(
    'token' => '<myToken>',
    'datamart' => 'project',
    'viewname' => 'details_plus',
    'whereClause' => "STATUS NOT IN ('closed','cancelled')",
    'selectFields' => 'COMPANY,PROJECT_ID,CUSTOMER_ID',
);

curl_setopt_array($curl, array(
  CURLOPT_POSTFIELDS => http_build_query($params),
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => '',
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 0,
  CURLOPT_FOLLOWLOCATION => true,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => 'GET',
  CURLOPT_HTTPHEADER => array(
    'Authorization: my.email@uk.bmt.org',
  ),
));

$response = curl_exec($curl);

curl_close($curl);
echo $response;

PowerBI:

= Json.Document(Web.Contents("
https://bmt-dwh-uks-app-api.azurewebsites.net/get_myview?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_myview?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