COUNT Improvements

As of March 23, 2017, MAQL provides two versions of the COUNT metric. The original COUNT remains the default. To activate improved COUNT you must do so via this API. Improved COUNT is NOT backward compatible, so consider this deciding whether to activate improved COUNT functionality.


Improved Use Cases

This section provides example use cases for how improved COUNT works with the following single-dataset model:

COUNT function - Better Slicing

You can slice COUNT(Stage) by other attributes from the dataset, for example: Is Won. Previously this was only possible to slice by using COUNT with two parameters.

COUNT function - Better Filtering

You can filter COUNT(Stage Name) by filter on an attribute, for example: Is Won. The COUNT is then computed in context of the Opportunity dataset. Previously this was only possible by using COUNT with two parameters.

New USING keyword for COUNT Context Resolution

The context for computation of count may be ambiguous if there are multiple fact tables which relate to a counted attribute. Imagine a model with fact datasets Purchase_Fact and Sales_Fact which are both connected to the Store and Product attributes.

A report that displays the count of products per store (What: “select COUNT(Product)”; How: Store) will not compute because it is ambiguous if the report displays the number of unique products that have been purchased by store or the number of unique products that have been sold by the store.

The USING keyword in the metric provides a hint for which context should be used. For example the report will show the number of unique purchased products per store if Purchase_Fact attribute is placed into the USING clause (What: “select COUNT(Product) USING Purchase_Fact”; How: Store).

The attribute in the USING clause does not need to be from the actual fact table, it can also be another attribute which uniquely determines the correct context (e.g. use of the Purchase Date attribute in the USING clause directs the use of the Purchase_Fact dataset to join Product with Store because Sales_Fact does not directly relate to Purchase Date).

Attribute Listing - better filtering

More filters are applied when you list attributes. For example, when listing the Stage Name attribute, the filter on the Is Won attribute is applied. This was previously possible only with the OVER … TO … construct (for information, see Advanced Data Permissions Use Cases).

Warning - Backward Compatibility

Backward incompatibility is caused by the changes in the filter application. You can continue to use use the old COUNT method but we encourage to experiment with new MAQL version. See Rollout Plan below.

Rollout Plan

You can enable new MAQL version on your projects (or domains) to check that you are satisfied with its behaviour. If you want to stick with old version of MAQL, you must explicitly set it through API on your projects (or domains).

Activating the new MAQL Version

You can use new MAQL version by calling our public Hierarchical Configuration API either on the project or domain level (using GET, PUT, DELETE):


Example for turning new xae_version for my_domain is:

PUT /gdc/domains/my_domain/config/xae_version HTTP/1.1
Content-Type: application/json
		"settingItem": {
		"key": "xae_version",
		"value": "2"

The valid values for xae_version are “1” for current (old) behaviour and “2” for new MAQL version with extended support of Count and Filtering. Note the use of Strings for xae_version value.

The xae_version is inherited and can be overwritten. You can set xae_version = 2 for all projects in some domain on /gdc/domains/<my_domain>/config/xae_version and for some particular problematic project you can enforce xae_version = 1 by calling /gdc/projects/<my_problematic_project>/config/xae_version.

All new projects created in a domain inherit the domain’s configuration (so when you have xae_version = 2 set on your domain, every new project created in this domain will leverage from new xae version features).

For more information, see Hierarchical Configuration.

In case of any question or issue with the new COUNT, contact GoodData Support.