OBIEE 11g and 12c: The Percentile Range Exceeds 1 When the Data Includes NULL

(Doc ID 2227447.1)

Last updated on JANUARY 30, 2017

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.7.150120 and later
Information in this document applies to any platform.

Symptoms

When the numeric column including null, the range of percentile rank may exceed 1 (e.g. 0.5 - 1.5)

 

This symptom can be reproduced with following steps:

1. create test table including a number column in database, and insert some data including null.

e.g.
create table percentile_test(idcol int, numcol int);

insert into percentile_test values(1,100);
insert into percentile_test values(2,200);
insert into percentile_test values(3,null);
insert into percentile_test values(4,400);

select * from percentile_test;

IDCOL    NUMCOL
---------- ----------
1            100
2            200
3
4            400

2. Import above table to repository, and set it appropriately in Physical, BMM and Presentation layer.

e.g.
a. Create a new Alias table for "percentile_test" as "f_percentile_test", and join them by using "idcol" column.
b. Drag these 2 physical tables to BMM Layer under Business Model: "Test" .
c. Drag this Business Model: "Test" to Presentation layer.

3. login to obiee.

4. Open Administration > Issue SQL , input the following sql, and then click "Issue SQL" button.

SELECT
"Test"."PERCENTILE_TEST"."IDCOL" s_1,
percentile("Test"."PERCENTILE_TEST"."NUMCOL") s_2
FROM "Test"

5. You will get the percentile range between 0.50 and 1.50.

s_1         s_2
double    double
1.00        0.50
2.00        1.00
3.00
4.00        1.50

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms