Count Distinct and Summaries

When you have data items with aggregate type set to sum and rollup aggregate set to sum and you include summary footer in your report the SQL Cognos generates would be something like:

select sum(Column 3) over(partition by column 1, column2), sum(column 3) over (partition by column 1).

But a count distinct over partition is not supported by the DBs and if your requirement is to display count distincts for the detail records as well as the summary footer then Cognos generates 2 SQLs like :

select T0.c1,
T0.c2,
T0.c3,
T1. c2

from
(
select
column 1 c1, column 2 c2,
count(distinct column 3) c3
group by 1,2
) T0,
(
select
column 1 c1
count(distinct column 3) c2
group by 1,2
) T1......

Now the disadvantage is if your T0 hits huge fact tables then you would be hitting those fact tables twice. The above scenario is at least a little acceptable but imagine a scenario wherein you have union clauses. I have a report that has 5 union clauses so the count distinct actually causes the 5 union queries to be executed twice which impacts performance.

Solution was to include 1 more union to get the summarized data with dummy data items thrown in to sort and get them to align after the detailed records and then conditionally format the report to make the summarized row appear as a footer.