Fix Display of Larger Oracle Id Values in Subject Areas for Analysis Reports

Brandon Stein

Organization Name

American Eagle Outfitters

Description

We're requesting Oracle to consider making an enhancement to OBIEE that will enable longer Oracle Id values (18 digits +) to be properly displayed when selecting them from the subject areas for analysis reports.  We also wanted to get this information out there for any other customers that could possibly experience the same issue and let them know what workaround we are using for now.

We experienced this issue with Period of Service Id, but we are thinking that the same limitation would apply to any Oracle Id fields in the subject areas.  As a note, the standard 15 character ids that are being displayed properly are ones that are generated when data is added via the UI.  The longer 18 character ids that we have are set when loading data via HDL from other systems.  This was done to be able to load external data using an ID range that the data entered through the UI should not overlap with.

Use Case and Business Need

We recently created analysis reports to retrieve the data needed for some mass loads of data via HDL. As part of this, we had to retrieve the Period of Service Id ("Work Relationship Details"."Period Of Service Id").  After running the HDL load, we had a significant amount of errors related to not finding the data that needed to be updated.

After investigating, we found that the Period of Service Ids returned by our analysis report were not always correct.  Some were being rounded off to the nearest thousand and it ultimately was related to how long the actual value of the period of service id is.

As an example, a value of 100001381469776 (15 digits) is being correctly returned as 100001381469776 by the analysis report.  A value of 110543919003715287 (18 digits) is being incorrectly returned as 110543919003715000 by the analysis report.  We were able to work around the issue by adding a custom column that casts the value as CHAR(30).  CAST("Work Relationship Details"."Period Of Service Id" AS CHAR(30))

More details

The workaround with casting the value as a CHAR(30) allowed us to proceed with retrieving the data that we needed.  However, we saw this as a bug and submitted a support request with Oracle.  Through the SR, we were told this is a limitation of OBIEE, as the values displayed are of datatype Double, which cannot support a number that is 18 digits long and that is why the number is being rounded.  We would like to see Oracle consider fixing this in the subject areas.

Original Idea Number: 4dd512aa59