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: https://community.oracle.com/products/oracleanalytics/discussion/10324/oas-set-column-properties-data-format-decimal-places-to-zero

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".'

Oracle Communities

Oracle Analytics and AI

Child Item

COUNT DISTINCT Shows Decimals in Reports - Logical Column Data Type Changed from INT to VARCHAR

Themis

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:

https://community.oracle.com/products/oracleanalytics/discussion/10324/oas-set-column-properties-data-format-decimal-places-to-zero

Find more posts tagged with

Migration

data type

Decimals

aggregation rules

SQL Query

Accepted answers

Chere-Oracle

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".'

All comments

Aman Jain-Oracle

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!

Themis

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

Chere-Oracle

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".'

RVohra

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

Themis

@RVohra

I tried the steps but I dont have any ption to change the datatype

@Chere-Oracle

Your proposed method seems to be working… Unfortunately i have to do for all affected fields

Chere-Oracle

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.

RVohra

Thanks, Chere, for sharing this.