OBIEE 11.1.1 - Count(Distinct) with multiple Logical Table Sources
By Nicolas Barasz-Oracle on Jan 23, 2012
Usually, when a metric is based on aggregation rule « Count(distinct) », it can be calculated only in the main fact table. In aggregate tables the IDs required to perform the « distinct » are not available anymore. But sometimes the IDs required are available on the aggregate table and you want to map the measure not only on the main fact table but also on the aggregate table. Unfortunately, even if you map the column properly on all logical table sources, the aggregate table is still never used for this measure.
For instance, in SampleApp, create on Fact “F0 Sales Base Measures” a measure “# distinct Products”. This column is mapped not only on the main fact table, but also on aggregate tables:
When use other measures, the aggregate table works fine. So when run the following query, it is expected that an aggregate table will be used.
SELECT 0 s_0, “A – Sample Sales”.”Base Facts”.”# Distinct Products” s_1 FROM “A – Sample Sales” ORDER BY 1
But instead I get the following physical SQL:
WITH SAWITH0 AS (
select distinct count(distinct T42433.Prod_Key) as c1 from SAMP_REVENUE_F T42433 /* F10 Billed Rev. */)
Select distinct 0 as c1, D1.c1 as c2 From SAWITH0 D1
This happens because BI Server never executes Count(distinct) on aggregate tables. The principle is that the distinct clause can be applied only on the fact table at the lowest level of aggregation. Fortunately it is possible to override this default behavior.
On the column, go to aggregation tab, and override the main aggregation rule for each logical table source. Note that this option is available only if you select Count(distinct) as the main aggregation rule.
Save the modifications, purge/disable BI Server cache, and run the same query again. Now get the right physical SQL:
WITH SAWITH0 AS (
select distinct count(distinct T42442.Prod_Key) as c1 from SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */)
Select distinct 0 as c1, D1.c1 as c2 from SAWITH0 D1