Oracle Communities

Oracle Analytics and AI

Child Item

OAS Classic Subject Area and DV Dataset calcs use floating point (DOUBLE)

This behaviour may be expected in OAS but it is a bit frustrating when trying to filter out 0. Basically Combine a Subject Area with a Dataset by joining in DV workbook, go into Classic and create an analysis. Example Expenses = 100 and income = -100 from subject area, but, creating a calculation on the subject area measures only it appears to force OAS to use double arithmetic and then we can't just simply filter out 0 as the total is not true 0. We have to do abs and coalesce etc on the amount in a filter to get it to true 0. See Screenshot with a calculation that has this exact issue. Is this just expected. It seems a bit of a unpleasing workaround?

Find more posts tagged with

Analytics

OAS

Accepted answers

Gianni Ceresa

Thank Tim for the post and the analysis.

Reading this thread, what you are observing, sounds to me like an issue. The RPD is expected to be the one defining what columns are (datatype), while it isn't the case anymore apparently.

If you were using a column based on some calculation between the two sources RPD and dataset, I could be open to "accept" a datatype change (as long as it is driven by some logical reason).

But you aren't doing that: you are just using 2 RPD columns, that have a defined datatype in the RPD, that should be considered that datatype and nothing else. And what you are describing is also that the calculation happen at the BI Server level, while the logic (and history of how the tool works) is that this calculation should be pushed down to the data source used in the RPD, because both columns come from there.

I would very much appreciate if somebody with internal view on the BI Server behaviour could explain why it is happening like that. Because, based on what I read, it means that when you extended a subject area with a dataset, the BI Server take over and the BI Server fails at doing the job in the most optimal way (pushing the calculation down to the database). The BI Server has always been quite good at pruning unneeded sources and just query things in the most optimal way, pushing calculation down to the database as much as possible, while now the opposite is happening.

I hope some PM will notice this and be able to explain what and why is happening.

Chere-Oracle

Tim,

My only guess is, perhaps the join to your DV workbook is causing duplication of some rows from the subject area, so that the decimal places become more significant than without the join. If the decimal places are of no significance in OBIEE but are more significant due to the join in DV, this may be why your SUM=0 filter works fine in OBIEE but not in DV.

I have no other idea as to the reason.

All comments

Chere-Oracle

Hi Tim,

I am guessing that Expenses or Income, or both, is not an Integer, so one or both of the values is not truly 100. My suggestion is to convert them to Integer data type for your calculation, like this:

CAST(Expenses AS Integer) + CAST(Income AS Integer)

Otherwise, you can change the calculation to round the sum, like this:

ROUND(Expenses + Income, 0)

RVohra

I believe it is documented here

You can't represent some numbers exactly with binary floating point data types such as FLOAT and DOUBLE.

When converting decimal numbers to and from binary floating point representations, often there are rounding errors because of the representational limitations of binary floating point formats. For example, a decimal number such as 1.365 might be represented as 1.364999999999999 when converted to the DOUBLE type. When this number is rounded to 3 digits after the decimal point, the result is 1.365. However, if the number is rounded to 2 decimal digits, then the result is 1.36 and not 1.37.

To avoid the limitations of the FLOAT and DOUBLE types, Oracle suggests that you update the FLOAT and DOUBLE data types to the NUMERIC type. There is no workaround to fix the inherent limitations with binary floating point data types, other than switching to the NUMERIC data type.

Hope it helps!

Tim Dickinson UK

Thanks @Chere-Oracle and @RVohra for your responses. Certainly @Chere-Oracle casting to INT and then setting filter <> 0 works ok. But the reason for the question is more the mechanics of why BI server appears to force the double floating point type for RPD columns that are 100% number in the RPD, simply because there is a dataset joined to the Subject area in a workbook which is then used to write a combined classic analysis of the Subject area and Dataset. If you don't include the DV dataset in the analysis you don't get this behaviour. I could perhaps understand it if an attributized measure actually came from the joined Dataset, but in the example given expense and income do not and are RPD subject area columns. It means that if you are extending a subject area by joining a dataset say to get some other attribute info thats not in the subject area then you get these minute differences as BI server seems to have somehow in memory changed the datatype to floating point without being asked! is there a way to force the calculation in the database?

Gianni Ceresa

Thank Tim for the post and the analysis.

Reading this thread, what you are observing, sounds to me like an issue. The RPD is expected to be the one defining what columns are (datatype), while it isn't the case anymore apparently.

If you were using a column based on some calculation between the two sources RPD and dataset, I could be open to "accept" a datatype change (as long as it is driven by some logical reason).

I hope some PM will notice this and be able to explain what and why is happening.

Chere-Oracle

Tim,

You write as though the two fact columns are Integer data types to begin with, but you have not actually verified it. The calculation may appear to be returning Integer when the sum is actually a rounded double precision or floating point. Have you verified that the facts have an Integer data type?

It could be that the join to your DV workbook is causing duplication of some rows from the subject area, so that the decimal places become more significant than without the join.

Tim Dickinson UK

hi @Chere-Oracle the two fact columns are NUMBER(18,2) in Db and come into front end OAS as number in subject area? does that help?

Chere-Oracle

Tim,

So, if you are not converting them to Integer during the calculation, or converting the sum to an Integer, they are definitely returning a non-Integer sum. It may look like an Integer, but it has unseen decimal places.

Tim Dickinson UK

hi @Chere-Oracle i think i get that you are saying, but, why does BI server seemingly move calc to in memory and adding the double float and no longer calculating on the DB when the measures in the RPD come from there? What am is asking is why do i need to do this cast to int on those raw columns when i pull in a dataset? I don't need to cast to int or add anything to the calculation when I calc the exact same columns without the dataset it just A + B . But with dataset included for absolute zero it needs to be cast(A + B as int)? This is on OAS 2025

Chere-Oracle

Tim,

I have no other idea as to the reason.