Weird Results When Calculating Percentages or Ratios…

While I was looking into a calculation issue today for a calculated percentage, let’s say the formula is ((b-a)/c)*100, the numbers just were not coming out correctly. Actually they were all coming out to 0! I know this was not right, so I investigated. I looked at my physical query and noticed OBI was creating that formula as FLOOR((b-a)/nullif(c,0))*100. Well, that explains it! Why was the BI Server adding this FLOOR function? It did not make sense to me. So how do I make or trick the BI Server to not do this. I asked my friend Google. He had no answers. I then went to my pal support.oracle.com. They did within Doc ID 1460809.1. As per Oracle, the cause was,

“There was a change in the calculation engine in OBIEE 11g pertaining to how integer division is performed. In OBIEE 10g, you could do integer division but it might not return an integer, you might get a decimal. In OBIEE 11g this is no longer true, it always returns an integer. As a result in DBFeatures in the repository, FLOOR_SUPPORTED is checked by default.”

So there are 2 solutions:

1) Turn off FLOOR_SUPPORTED database feature. This would be a global change and I think too drastic

2) Within the Answers report do the following, wrap a cast( …. As double) around the numerator like so: (CAST(b-a AS DOUBLE)/c). The results where that the FLOOR was replaced with the cast-> cast(b-a as DOUBLE PRECISION)/nullif(c,0))*100