Nested Aggregation Problem
I am using OBI 11g.
Nested aggregation is not supported in some forms in the BI Server (RPD), but appears to be possible by putting the second aggr rule in an Answers column formula or pivot view column. However, I cannot get this to work. Looks like can even be done in the RPD with aggregation based on dimensions, as long as there is a standard aggregation function on the outside of the expression.
The biggest problem with any of the above techniques is the BI Server does not push the outer aggregation rule to the DB engine (the generated SQL).
In my case, consider a Referral Fact with Customer Dim and Referral Dim. I need to get # of Referrals per customer, filter that with a case statement to "bin" 1 Referral and >1 Referral, and then get # of Customers in each bin. So the first measure aggregation looks like:
Other: COUNT (DISTINCT "Referral Key")
Customer: "SUM( CASE WHEN "Referral Key" = 1 THEN 1 ELSE 0 END )"
Or the logical measure just has the COUNT DISTINCT aggregation rule and an Answers column has the CASE statement with a SUM aggregation rule. Or use CASE WHEN "Referral Key" = 1 THEN "Customer Key" END and use COUNT DISTINCT instead of SUM.
All these appear to return correct results, but they all perform the outer aggregation in the BI Server or Pivot engine instead of pushing to the generated SQL (DB engine).
I can't find any problem in the DB Features. We are using SQL Server 2010.
Thanks in advance for help.