As of Strategy 9.2.x, there is no way to calculate subtotals for count distinct metrics. Subtotal is calculated by the Analytical Engine based on one level of result data retrieved by SQL. Distinct counts retrieved from the database cannot be re-aggregated to calculate a meaningful distinct count at a higher level – the higher level values must be calculated by performing another distinct count back against the source data.
In Strategy 9.3.x, there is a new feature called 'subtotal from base' which can solve this problem. Cube Subsetting Instruction (CSI) engine generates statements to calculate the subtotal from base. But when a metric limit is defined on the grid, it is calculated by the Analytical Engine after the CSI, so it cannot affect the result that has been calculated. The subtotal from base number seem wrong after the metric limit is applied. To prevent confusion, subtotal from base will return null value when the metric limit in the grid is enabled. This is explained below with an example from the Strategy Tutorial project.
Create an Intelligent Cube with objects Region, Customer and Cost. In Strategy Web 9.3.x, create a view report and put objects Region and Cost in the grid. Right mouse click on the attribute 'Customer' and insert the metric 'Count'. When the total is enabled, it is seen that the Total of the metric Count(Customer) indicates the number of distinct customers in all the regions and is not the sum of the numbers of distinct customers of different regions.
From the CSI, it is seen that the Total of the metric Count(Customer) is calculated by the statement in red. Pay attention that the first step of Analytical Engine calculation steps below point to the calculation of the total of Cost from the view.
Sample Code/Error
select @,
@,
count(distinct @)@{} as ,
sum()@{} as ,
count(distinct @) as
from Cube Year Region Customer Cost
If a metric limit such as 'Cost >5000000; is defined on the grid, only four regions are qualified. From the work flow, the CSI engine calculates the subtotal from base first, and then the Analytical Engine applies the metric limit. A NULL value is displayed for the Total of the metric 'Count(Customer)'. If not, the grid has four rows returned but the Total of Metric Count(Customer) is for eight rows, which could be incorrect.
Sample Code/Error
select @,
@,
count(distinct @)@{} as ,
sum()@{} as ,
Dynamic Null Function() as
from Cube Year Region Customer Cost
