How to access data via Cube.JS

Introduction

Cube.JS is a third-party open-source software layer embedded in the ArchFX platform to address the following needs:

• It provides a unified interface to access ArchFX data.
• It behaves as a semantic layer. Cube.JS hides complex data architecture and exposes business-oriented data. That makes it easier for Arch customers to query the data.
• It provides a caching system that speeds up requests and decreases overall load on ArchFX infrastructure.

Cube.JS uses the following terms that are common in the Business Intelligence (BI) world:

• Measures refer to quantitative data such as number of units sold, number of unique visits, profit, and so on. They can be aggregated (a count, an average, … etc.)
• Dimensions refer to categorical data, such as state, gender, product name, or units of time (e.g., day, week, month). They can be used to aggregate measures “by” (group by). If no measure is selected, dimensions just return all the values.
• Segments are a predefined filter.

Cube.JS provides two kind of interfaces to query the data:

• REST API
• SQL interface

The Cube.JS configuration is managed by Arch engineers. Cube.JS must be used by customers to get data. Customers shouldn’t use the ArchFX Cloud API directly.

ℹ️ When accessing data via Cube.JS, data access rules are applied so that queries only return data the current user is allowed to access (e.g. only a single organization, only a few sites, etc.).

How to use the REST API

The Cube.JS REST API is documented by the Cube.JS team here: https://cube.dev/docs/product/apis-integrations/rest-api

In ArchFX Cloud, the Cube.JS REST API endpoint is exposed behind the /cubejs-api address.

Authentication

To use Cube.JS, you need to authenticate by providing a JWT token. This JWT token is the same as the one you would use to query the ArchFX Cloud API. To get your JWT token, you need to use the ArchFX Cloud REST API and do either of the following things:

• Log in within your browser and go to GET /api/v1/auth/token/. In the returned data in JSON format, the jwt field contains your token.
• Use the POST /api/v1/auth/api-jwt-auth/ API providing your username and password in the body request. In the returned response (in JSON format), the access field contains your JWT access token.

This JWT token will expire after a few days, but you can refresh it infinitely with the refresh token (also returned as part of these data) via the POST /api/v1/auth/api-jwt-refresh/ endpoint.

Examples

1.  Get your JWT token in a shell script:

$ curl \ 
-X POST \ 
-H "Content-Type: application/json" \ 
--data '{"username": "user1", "password": "123456"}' \ 
https://customer.archfx.io/api/v1/auth/api-jwt-auth/ 
{ 
"access": "JWT_ACCESS_TOKEN" 
"refresh": "JWT_REFRESH_TOKEN", 
[...] }

or get the JWT token in Python:

input requests

HEADERS = { "Content-Type": "application/json", 
} 
URL = "https://customer.archfx.io/api/v1/auth/api-jwt-auth/" 
REQUEST_PAYLOAD = { 
"username": "user1", 
"password": "123456", 
}
response = requests.post(URL, headers=HEADERS, json=REQUEST_PAYLOAD) 
data = response.json() 
print(data) 
# Actual JWT access token is available in the "access" key, as a string 
print(data["access"])

2.  Query the Cube.JS API and provide my JWT token in Authorization header:

$ curl \
-X POST \
-H "Content-Type: application/json" \
-H "Authorization: JWT_ACCESS_TOKEN" \
--data '{"query": {"measures":["site.count"]}}' \
https://customer.archfx.io/cubejs-api/v1/load


{
 [...]
 "data": [ 
   { 
     "site.count": "45" 
   } 
 ], 
[...] 
}

Or in Python:

import requests 

HEADERS = {
 "Authorization": "JWT_ACCESS_TOKEN",
 "Content-Type": "application/json", 
} 
URL = "https://customer.archfx.io/cubejs-api/v1/load"
REQUEST_PAYLOAD = {
 "query": {
    "measures": ["site.count"], 
 } 
} 
response = requests.post(URL, headers=HEADERS, json=REQUEST_PAYLOAD) 
data = response.json() 
print(data) 
# Actual data are available in the "data" key, as a list 
print(data["data"])

Notes

• We have added a custom keyword __all__ for the filters of type contains on fields id or slug. This keyword will be replaced by a list of all possible values for this field. This is useful for BI tools that are not able to remove a part of a query when all values are selected.
• You can get the list of all the entities we have defined in Cube.JS by going to https://customer.archfx.io/cubejs-api/v1/meta (as a GET request, with your JWT token, just as for any other Cube.JS API call as shown above). The response is a JSON describing all the “cubes” that are available, with extra technical details (see the official documentation page about that).
• If your request takes too much time to compute, Cube.JS may return a “200 - Continue waiting” response (see https://cube.dev/docs/product/apis-integrations/rest-api#continue-wait). If it happens, you just need to wait a bit and resend the exact same query. Behind the scene, Cube.JS is still processing your query. It is its way not to time out.
• Foreign keys on models are hidden in favor of related model primary key instead. For example, if you want the shift_id of react_downtime, you instead need to query the related react_shift.id . Query payload would for instance look like this then:

{
 "query": {
    "measures": ["react_downtime.id", "react_shift.id"]
 } 
} // This query will return the list of React downtime ids, with the related 
// React shift id for each downtime. This is possible because the relationship 
// between React downtimes and React shifts have been configured in Cube.JS 
// directly.

Examples

Here is an example of a script requesting some React data via Cube.JS. This script handles the polling in case of a request that takes too much time to compute. It also writes some logging information so that if an error occurs, it's easier to investigate. To use this script, you just need to update the variables in uppercase and run it with Python3.

get_react_data.py

Some typical queries

• OEE Availability and Performance, per hour, per line, for last week

  {
    "measures": [
      "metrics_react.availability",
      "metrics_react.performance"
    ],
    "dimensions": [
      "line.slug",
      "line.name"
    ],
    "timeDimensions": [
      {
        "dimension": "hourly_react_job.time",
        "granularity": "hour",
        "dateRange": "Last week"
      }
    ],
    "order": [
      [
        "hourly_react_job.time",
        "asc"
      ],
      [
        "line.slug",
        "asc"
      ]
    ],
    "limit": 5000
  }
  

• OEE Quality, per hour, per line, for last month

  {
    "measures": [
      "metrics_react.quality"
    ],
    "dimensions": [
      "line.slug",
      "line.name"
    ],
    "timeDimensions": [
      {
        "dimension": "inspection.time",
        "granularity": "hour",
        "dateRange": "Last month"
      }
    ],
    "order": [
      [
        "inspection.time",
        "asc"
      ],
      [
        "line.slug",
        "asc"
      ]
    ]
  }
  

• OEE Availability, per factory day, per area, for custom time period

  {
    "measures": [
      "metrics_react.availability"
    ],
    "dimensions": [
      "area.slug",
      "area.name"
    ],
    "timeDimensions": [
      {
        "dimension": "react_shift.local_factory_day",
        "granularity": "day",
        "dateRange": [
          "2024-01-01",
          "2024-01-19"
        ]
      }
    ],
    "order": [
      [
        "react_shift.local_factory_day",
        "asc"
      ],
      [
        "area.slug",
        "asc"
      ]
    ]
  }
  

• OEE Performance, per shift, per line (with area), for a given site on a given week

  {
    "measures": [
      "metrics_react.performance"
    ],
    "dimensions": [
      "area.name",
      "line.name",
      "react_shift.start",
      "react_shift.end"
    ],
    "order": [
      [
        "react_shift.start",
        "asc"
      ],
      [
        "area.name",
        "asc"
      ],
      [
        "line.name",
        "asc"
      ]
    ],
    "filters": [
      {
        "member": "site.name",
        "operator": "equals",
        "values": [
          "Site Name"
        ]
      },
      {
        "member": "react_shift.start",
        "operator": "inDateRange",
        "values": [
          "2024-01-01",
          "2024-01-08"
        ]
      }
    ]
  }
  

• OEE Quality, per job name, for last week

  {
    "measures": [
      "metrics_react.quality"
    ],
    "dimensions": [
      "react_job.name"
    ],
    "timeDimensions": [
      {
        "dimension": "inspection.time",
        "dateRange": "Last week"
      }
    ],
    "order": [
      [
        "react_job.name",
        "asc"
      ]
    ]
  }
  

• Number of non-minor downtime occurrences per category/reason, per shift, for yesterday’s shifts, in a given site:

  {
    "measures": [
      "react_downtime.count"
    ],
    "dimensions": [,
      "react_shift.name",
      "react_downtime.category",
      "react_downtime.reason"
    ],
    "timeDimensions": [
      {
        "dimension": "react_shift.local_factory_day",
        "dateRange": "Yesterday"
      }
    ],
    "segments": [
      "react_downtime.not_soft_deleted",
      "react_downtime.non_minor"
    ],
    "filters": [
      {
        "member": "site.name",
        "operator": "equals",
        "values": [
          "Site Name"
        ]
      }
    ],
    "order": [
      [
        "react_shift.name",
        "asc"
      ],
      [
        "react_downtime.count",
        "desc"
      ]
    ]
  }

Exposed data

Action Manager

Factory Map

Metrics

ArchFX React

Signals