Data Query API Best Practices

The best practices for using Visier's Data Query API.

Making queries

Aggregate method

We recommend that you think of aggregate queries as on-demand queries. Each query execution has a context that addresses a specific set of questions or information needs. Put simply, aggregation queries are not intended for bulk extraction.

Rate limits

Visier's default rate limits are 100 calls per minute or 6000 calls per hour.

Form multiple queries in one POST request

The Data Query API doesn’t support batch queries in a single POST request. To form multiple queries, you can use the metrics option in the request body, or make multiple POST requests. If using metrics, all axes and filters defined in the query body apply to all metrics.

Define axes by named levels, not member sets

You can define axes by level or member set.

  • Level: Define the axis by the name of a level. This approach ensures that the query includes new members that may be added to the level in future data loads. If you want to include all level members, including levels added in the future, we recommend defining axes by level.

  • Member set: Define the axis by the set of members within a level. This approach creates a static query definition that doesn’t include new members that may be added to the same level or higher levels in future data loads. If you want to include specific members only, we recommend defining axes by member sets.

Easily convert date time values to true date types

By default, the display names of members on the date axis are suffixed by the ordinal of each member; for example, 2020-04-01T00:00:00.000Z - [1]. Use the option "memberDisplayMode": "COMPACT" to generate date time member values that exclude the ordinal suffix. Without the ordinal suffix, it's easier to convert date time values into true date types programmatically.

Define a time interval to query

If defining timeInterval in a list query or timeIntervals in an aggregate or snapshot query, the direction value impacts the meaning of the defined time value (fromDateTime and fromInstant).

  • When the direction is BACKWARD, the specified time value is excluded.
    • Events that occur on the specified date are excluded.
    • If the data is subject-based, data that ends on the specified date is included because the ending event is excluded.
  • When the direction is FORWARD, the specified time value is included.

The default direction is BACKWARD. This means the platform queries backwards from the specified time. You can define direction: FORWARD to query forwards from the specified time.

For aggregate query results, Visier returns the technical member name for time; for example, 2019-06-01T00:00:00.000Z - [0]. You can request "options": { "memberDisplayMode": "DISPLAY" } for aggregate queries to return time members with a display name matching the caller's locale; for example, May 31, 2019.

Only keep members in aggregate or leaf positions

Use "enableDescendingSpace": true to enable advanced optimizations in your query. This setting instructs the platform to avoid certain computations on a per-axis basis by only keeping members that are in aggregate positions or whose previous position was a leaf. This use case is most applicable to Planning API users.

Querying multiple metrics

Use "metrics" to query multiple metrics in the same set of axes and filters

If you want to query more than one metric that uses the same axes and filters, use metrics in the request body. This allows you to reduce the number of query definitions you maintain. Fewer queries reduces the risk of divergence, the complexity of making multiple calls, and the complex task of stitching together multiple result sets from multiple queries.

Note:  

  • The Accept header’s first value must be a flat format, such as text/csv.
  • Each metric must reference the same analytic object; for example, you cannot query the Headcount and Applicant Count metrics in the same query because one uses the Employee subject and the other uses the Applicant subject.

Use "formula" parameter to query multiple metrics in the same axes with additional filters

If you want to query more than one metric with the same set of axes and additional filters, use formula in the request body. In the metric formulas, you can specify different filters for each metric in their unique formula. Filters defined in the query body are applied to the formula in addition to the formula-specific filters.

Types of queries to avoid

Avoid extraction-style queries

Extraction-style queries retrieve detailed data from Visier for a general purpose and are typically large queries with many rows and columns as opposed to a specific and targeted query that drives a dashboard or chart.

Aggregate query executions enforce rigorous and complex data security rules that make aggregate queries a poor fit for extraction-style queries. Extraction-style queries are commonly run using a high-powered service account. Service accounts are not associated with specific individuals, making them unaffiliated with your organization’s supervisory hierarchy and its data security rules. Because these service accounts lack data security rules, we do not recommend running aggregate queries through service accounts.

Avoid queries with many axis members

Cell sets are the default response for aggregate queries. Each new axis that is defined in a query multiplies the size of the cell set response with the number of members on the new axis. Visier’s cell set limit is 500,000, which may be exceeded if many axis members multiply and result in an exponential number of cells.

To avoid queries that exceed the cell set limit, we recommend that you:

  • Limit the axes you include in a query if the axes each contain many members.
  • Run additional queries rather than one large query.

Response format

Always accept "application/json" to get error messages

If you want your query responses in a format other than JSON, such as CSV, always keep application/json as a secondary Accept type because Visier returns errors as JSON. If your query returns an error and it doesn't accept application/json, you will receive an HTTP 406 status code instead of the appropriate error response body.

Flatten the response to reduce complexity

The cell set is a complex response that expresses multi-dimensional cell values that represent positions on an axis. You can make it easier to understand the cell set response by asking the platform to flatten the cell set into a tabular form. To flatten the response, add text/csv or application/jsonlines to the Accept header.

Note: Always add application/json in the Accept header after the flat format; for example, Accept: text/csv, application/json. This allows Visier to successfully return errors in JSON format.

Add "axisVisibility": "VERBOSE" to get display names

The aggregate query response returns the path of an axis member; for example, an axis member's path might be "Canada", "BC", "Vancouver". To also return the display name of an axis member, use the option "axisVisibility": "VERBOSE" in your query; for example, an axis member's display name might be Vancouver.

Methods to handle zeros and nulls

Enable sparse results

If set to true, enableSparseResults tells Visier’s server that the response can be sparse. The server then decides whether or not the result should be sparse.

Note: In multi-dimension cell sets, a cell set is "sparse" if many of its member intersections from each axis lack a value. If your cell set is sparse, you should consider whether you can process sparse results with enableSparseResults. Some considerations include whether the overhead for managing a sparse cell set exceeds the cost of savings by using sparse mode.

Hide zeros and nulls

Use the options zeroVisibility and nullVisibility set to HIDE to remove all cells thats have a 0 value or no value from the result, provided a complete cell set is retained. The coordinates of other cells are updated accordingly. If a row or column contains at least one non-zero or non-N/A value then the row is not removed from the result.

Note: A dense (non-sparse) cell set has a cell in each possible member intersection. For example, a cell set with three axes can be thought of as a three-dimensional cube. If zeros and nulls are hidden, the response may return a smaller cube (cell set) depending on the location of the non-null and non-zero cells within the original cube. The smaller cube is still a complete cell set and the caller will save on the overall cell count.

Eliminate zeros and nulls

Use the options zeroVisibility and nullVisibility set to ELIMINATE to remove all cells that have a 0 value or no value from the cell set, regardless of whether a complete cell set is retained. You must be able to process sparse result sets.

Exclude zeros and nulls in sum aggregations

We recommend that you exclude 0 values and null values from your cell set to remove any results that don’t contribute to sum-aggregated values. If you’re running a large query that’s at risk of hitting the cell-count limit, excluding zeros and nulls reduces your cell count. If you’re calculating an average, zeros should be included.

References

Defining reverse references in a query

To define a reverse reference in a query, use the option qualifyingPath to define the path to the reference. For example, let's say that you want the employee ID of an employee's direct manager. The column definition would be defined as follows:

Copy 
{
    "columnDefinition": {
        "property": {
            "name": "Employee.EmployeeID",
            "qualifyingPath": "Employee.Direct_Manager"
        }
    }
}

We don't support using reverse references in list queries because it's difficult to understand the response. Instead, define list queries at the most granular level possible and use forward references to traverse the relationships in the Visier data model.

Reverse references change the granularity of the resulting table. For example, every manager's row is duplicated by the number of the manager's direct reports. If you add properties to the query through additional reverse references, that same manager row is duplicated by every combination of records that are produced by that manager through the reverse references.