Why use the filters parameter?

By default, the intersections export API returns all records that you have permission to view. This can be a very large dataset. To export a subset of the data, use the filters parameter.

The filters parameter is a query parameter that restricts which records are exported. Typically, it is used to export only the intersections whose dimension(s) match the given member(s).

If you’re familiar with MQL, then you can think of the filters parameter as an analogous notation for specifying dimensions and members in a data model.

Example

Here is how the filters parameter might look as a query parameter appended to a GET request for intersections:

https://us2.vena.io/api/public/v1/models/1234567891011121/intersections?filters=[{"field": "Account", "mx": {"bottomLevel": {"name":"Revenue"}}}]

The above filters parameter limits the intersection values to the bottom-level members underneath the Revenue member in the dimension Account.

Structure

The filters parameter is a JSON array that is composed of individual filter objects (or filters for short).

filters = [ FilterObject, ... ]

Conceptually, a filter object is evaluated against each record in the system. For any given record, either the filter matches or it doesn't. The export API returns only the records where all given filters match.

For example, consider an export request with the two filter objects below:

filters = [
  { "field": "Period", "eq": "Jan" },
  { "field": "Year", "eq": "2000" }
]

Since all filters must match for a record to be returned, a record with {Period: Jan, Year: 2000} would be returned, but one with {Period: Feb, Year: 2000} would not, and neither would a record with {Period: Jan, Year: 2001}.

Important notes on the filters parameter:

• A filters parameter can include up to a single filter object per unique field. For example, if your data model has 7 dimensions, the maximum number of filter objects that can be included in the JSON array is 7 (one for each filterable dimension). You cannot specify more than a single filter object for a dimension.
• The filter objects are case-sensitive. Make sure that the dimension names, member names, and operators are written with the correct case.

The full OpenAPI specification of the filters parameter and the notation of the JSON array can be downloaded below:

Filters

A filter is a JSON object. It generally has this format:

{ "field": "<field name>", "<operator>": <ValueExpression> }`

• field specifies the name of the field we are filtering on. For intersections export, this is currently always a dimension name. The value field does not yet support filtering.
• <operator> defines what kind of filter it is
• <ValueExpression> is the input value to the filter. A value expression can be a string, an array, or a member expression (see below). The exact type depends on the filter.

The following filters are supported in the Intersections endpoint:

Member Expressions

A member expression is a JSON object that evaluates to a set of members in the data model hierarchy. A member expression is not a filter on its own; instead it is a value expression that is used as an input to the mx filter or to another member expression. Note: A member expression is always evaluated in the context of a single dimension; the mx filter provides that context through the field.

Important notes on filters

• Use eq and in on bottom level and mx on parent members.
  • It is important to note that the eq and in filters will only return results when given bottom-level member names. This is because the intersections export API only returns records with bottom-level members, and these filters can only match the values in those records. This is different from the mx filter, which can accept a parent member as input through the bottomLevel member expression. For more information on the difference between bottom-level members and parent members, refer to the Terminology guide.
• Parent member aggregation isn’t supported.
  • Filters can only restrict the records that are returned by the API. They cannot make the API perform roll-up or aggregate computations. For example, if you use the eq filter expression on a parent member, you will simply not get any results. To roll-up or aggregate bottom-level members, you can combine the intersection values from the Intersections endpoint with the hierarchy data from the Hierarchy endpoint.
• You can specify a filter for every dimension in your data model, which is sufficient for most use cases. If you cannot get all the data you want using a filters query parameter in a single request, you can use multiple requests, each with its own filters query parameter. Another option is to fetch a superset of the data you need and then perform more fine-grained filtering in the downstream system after the data has been exported out of Vena. If your integration would benefit from more advanced filtering functionality, please reach out to Vena and we’ll consider your request for future releases.

How to use filters for your use case

Your use case can serve as a guide for the filter that you use. This section describes different use cases to help you understand which filter may be most appropriate. For an in-depth description of filters, reference the Definitions section above.

Use Case: Match a single member

If you want to match a single bottom-level member in a dimension, then you should use the eq filter.

Use Case: Match one of many members

If you want to match one of many bottom-level members in a dimension, then you can choose the in filter or the mx filter with bottomLevel member expression.

If the members you are interested in are categorized under a single parent member, then the mx filter may be appropriate.

If you are interested in a set of members that don’t neatly fall under a single parent member, or you only want a subset of the bottom-level members beneath a parent member, then the in filter may be appropriate.

MQL vs filters JSON examples

If you are familiar with Vena’s MQL language, then it may be convenient to learn the filters parameter through comparison to MQL. You can think of the filters parameter as modifying MQL into a JSON format and focusing on the filtering functionality most useful for exporting data. Both notations specify expressions to select members for a dimension.

Note: The filters parameter does not support all the same functions as MQL. The complete list of supported filters can be found in the previous section.

Find all intersections that have member “1200” in the dimension “Account”

MQL

dimension('Account':'1200')

Filters JSON

[ 
  { "field": "Account", "eq": "1200" }
]

Find all intersections that have member “2020”, “2021”, or “2022” in the dimension “Year”

MQL

dimension('Year': union('2020', '2021', '2022'))

Filters JSON

[ 
  { "field": "Year", "in": ["2020","2021","2022"] }
]

Find all intersections that have a bottom-level member beneath “Revenue” in the dimension “Account”

MQL

dimension('Account': bottomlevel('Revenue'))

Filters JSON

[
  {
    "field": "Account",
    "mx": {
      "bottomLevel": { "name": "Revenue" }
    }
  }
]

Find all intersections that have “2021”, “2022”, or “2023” in dimension “Year”, AND a bottom-level beneath “Actual” in the dimension “Scenario”, AND a bottom-level member beneath “Local” in the dimension “Currency”, AND a bottom-level member beneath “All Departments - Planned” in the dimension “Department”, AND a bottom-level member beneath “Undefined” in the dimension “Placeholder 3”

MQL

dimension('Year': union('2023','2022','2021') dimension('Scenario': bottomlevel('Actual')) dimension('Currency': bottomlevel('Local')) dimension('Department': bottomlevel('All Departments - Planned')) dimension('Placeholder 3': bottomlevel('Undefined'))

Filters JSON

[ 
  {
    "field": "Year",
    "in": ["2023", "2022", "2021"]
  }, 
  {
    "field": "Scenario",
    "mx": {
      "bottomLevel": { "name": "Actual" }
    }
  },
  {
    "field": "Currency",
    "mx": {
      "bottomLevel": { "name": "Local" }
    }
  },
  {
    "field": "Department",
    "mx": { 
      "bottomLevel": { "name": "All Departments - Planned" }
    }  
  },
  {
    "field": "Placeholder 3",
    "mx": {
      "bottomLevel": { "name": "Undefined" }
    }
  }
]