As of 23rd March 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
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:
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.
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:
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 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
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).
In case of any question or issue with the new COUNT please contact firstname.lastname@example.org