Feeds:
Posts
Comments

Archive for February, 2013

This is a common problem new OBIEE developers can run into.

Below analysis is showing Brand, Product, Revenue and revenue share by product with in Brand.

result0

 

Below is the formula used to calculate revenue percentage by product with in brand.

example

 

Problem: Percentages with in Brand total to 100%, which is correct.

However, grand total is showing 274.5%. I am expecting to see 100% in grand total row.

 

Root Cause:

query0

 

OBIEE is receiving SUM by product from database. All report context specific calculations ( SUM (REVENUE BY BRAND)) are being handled in BI server. So, OBIEE was able to show 100% at Brand level. However, BI server was not properly calculating results in grand total row. I don’t understand the exact reason, but it is failing to perform proper calculation.

 

 

Solution:

 

Change Percentage calculated column aggregation rule to “Server Complex Aggregate”

setting1

 

Result:

result1

 

query1

 

OBIEE is sending sum(revenue by Brand) calculation to database. See sum(d1.c1) over (partition by d1.c2) in above screenshot.

Also you can notice additional sum(revenue) sql statement sent to database for grand total row calculation.

Advertisements

Read Full Post »

OBIEE allows analysis based on physical query.

Example:

physical sql

I have tried to use results from this analysis in a filter inside another analysis.

filter

filter 1

error

With its good intentions Oracle OBIEE tries to build a single query with analysis in filter as sub-query. Since analysis used in sub-query is a physical query it unable to process syntax properly.

It is possible that I’m not doing this correctly.  Has anyone able to use physical sql in sub-query? If you have could you please post details?

I couldn’t find any useful information online.

I’m going assume worse and it doesn’t work that way I’m trying to use. If that’s the case there is a good case to be made for adding this feature down the road. It would be nice to have nested logical and physical query capability to solve tough requirements.

Read Full Post »