Understanding the filters parameter

Why use the filters parameter?

The filters parameter is used to specify which intersection values you would like to export out of your data model. The filters parameter accepts JSON as input, where the JSON is used to select the dimensions and members to filter the exported data on.

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.

How the filters parameter works

The filters parameter is one of the optional query parameters for the Intersections endpoint. An example of how the filters parameter is included in a GET request is shown below:

<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 parent member Revenue, in the dimension Account.

The filters parameter uses a specific notation to specify which data to filter on. As shown in the example above, the filters parameter expects a JSON array that is composed of individual filter objects. The high-level structure for the JSON array is shown below:

[ {“field”: “dimension 1”, “filter expression”: “expression”}, {“field”: “dimension 2”, “filter expression”: “expression”}, {“field”: “dimension 3”, “filter expression”: “expression”} ]

In this example, each filter object pertains to a particular dimension, specified by the key “field” and chooses which members to select within that dimension, using the expression provided in the “filter expression” key.

Important notes on the filters parameter:

• A filters parameter can include up to a single filter object per dimension. For example, if your data model has 7 dimensions, the maximum number of filter objects that can be included in a JSON array is 7. 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 filter expressions 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:

Filter expressions

Filter expressions are used to select specific members in a dimension. The following filter expressions are supported in the Intersections endpoint:

Important notes on filter expressions

• Use eq and in on bottom level and mx on parent members.
  • It is important to note that the eq and in filter expressions accept bottom-level members as input. This is different from the mx filter expression, which accepts a parent member as input. For more information on the difference between bottom-level members and parent members, refer to the Terminology guide.
• Parent member aggregation isn’t supported.
  • There is no filter expression to roll-up, or aggregate, intersection values. For example, if you use the eq filter expression on a parent member, you will receive an error. 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 expression for every dimension in your data model, which offers a high level of filtering precision. For more advanced filtering, we recommend using multiple requests, each with its own filters parameter JSON array. It is also advised to 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 filter expressions for your use case

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

Use Case: Select a single bottom-level member from a dimension

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

Use Case: Select multiple members from a dimension

If you want to select multiple bottom-level members in a dimension, then you can choose the in filter expression or the mx filter expression.

If the members you are interested in are categorized under a single parent member, then the mx filter expression 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 expression may be appropriate.

MQL to 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 filter expressions can be found in the previous section, Filter expressions.