Categories
- All Categories
- Oracle Analytics Learning Hub
- 29 Oracle Analytics Sharing Center
- 18 Oracle Analytics Lounge
- 236 Oracle Analytics News
- 45 Oracle Analytics Videos
- 16K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 88 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
COUNT DISTINCT Shows Decimals in Reports - Logical Column Data Type Changed from INT to VARCHAR
After migrating from OBIEE 11g to OAS, We are experiencing an issue where numeric fields that display COUNT DISTINCT aggregations are showing decimal places in OAS reports, whereas they displayed as integers (no decimals) in OBIEE.
Key Findings:
- Both RPDs have identical aggregation rules: COUNT DISTINCT on a VARCHAR field
- The Logical SQL Query (SQL Issued section in Analytics Report: Advanced tab) appears identical in both environments
- In the RPD Logical Layer, the column properties show:
- OBIEE 11g: Data type displays as (INT) next to column name in General tab
- OAS: Data type displays as (VARCHAR) next to column name in General tab
- Column Source tab shows the same expression in both:
COUNT(DISTINCT View_Table.VARCHAR_FIELD)
Why does OAS infer the logical column data type as VARCHAR while OBIEE 11g correctly inferred it as INT for a COUNT DISTINCT aggregation? Is this a known change in OAS's type inference logic?
Is the recommended fix to explicitly CAST the result as INTEGER in the logical column source, or is there a configuration/setting that restores OBIEE's behavior?
Also there is an Idea which explains this problem:
Best Answer
-
Themis,
While you are on the Data Format tab, can you see a button called "Save as Default?" If yes, then follow these steps:
- Select the 'Override Default Data Format' checkbox.
- Change 'Decimal Places' to 0.
- Click Save as Default button to open a pop-up menu.
- Select 'Save as the system-wide default for "your column".'
1
Answers
-
Hi @Themis , I found this KM - Cast Function Is Adding Decimals To Measure Values After Migrating From OBIEE To OAS (Doc ID 2908866.1), it describes the similar issue that you mentioned and the system level fix is to set the OBIS_REDUCE_REPORT_AGGREGATE_TO_AGGREGATE_FOR_COUNT_DISTINCT variable to 0 under the COMPATIBILITY section in the NQSConfig.INI file.
Apply this at the report/analysis level first to confirm the fix works for your issue before making the system level change.
1. Edit the Analysis
2. Go to the Advanced tab
3. Enter the following in the Prefix field
SET VARIABLE OBIS_REDUCE_REPORT_AGGREGATE_TO_AGGREGATE_FOR_COUNT_DISTINCT = 0;4. Click [Apply SQL] and Save the Analysis
Hope it helps!
1 -
Hello @Aman Jain-Oracle
My problem is slightly different.
I do not use a CAST function. Just the COUNT DISTINCT in the formula.
In the report and in the 'Data Format' tab under column properties the decimal places are shown as 2 in OAS whereas in OBIEE 0
The method you proposed unfortunately didnt work in my case
0 -
Can you see if following steps work for you → Open Administration Tool → Load your RPD
- Navigate to:
Business Model and Mapping (BMM) → Locate your logical fact column
- Right-click the logical column → Properties
- In the General tab:
- Change Data Type → Integer
- (Optional) Ensure the “Logical Column Name” does not have an (incorrect) datatype suffix
- Press OK
- Save and Consistency Check
0 - Navigate to:
-
I tried the steps but I dont have any ption to change the datatype
Your proposed method seems to be working… Unfortunately i have to do for all affected fields
0 -
Themis,
In all my years using various versions of OBIEE or OAS, it has been my experience that Count(Distinct ) results show a decimal place, and that the default format had to be changed in this way. Perhaps someone had already set the default format for your columns in OBIEE 11g and now that you are in a new system, it has to be redone.
1 -
Thanks, Chere, for sharing this.
0
