Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.


Monday, April 4, 2016

Undocumented SQL Data Load Rule Change

Recently I had a client do an in place upgrade of Essbase from 11.1.2.2 to 11.1.2.4. The only thing that was upgrades was the EPM stack, The server and SQL server instance were not changed. Everything seemed to go smoothly except for one thing. We had a process where data is extracted from Essbase into SQL Server some data manipulation is done and the data is loaded back to Essbase. This worked great in 11.1.2.2 but in 11.1.2.4 the numbers in my SQL server table didn’t match what was being loaded into Essbase. Hmmm. What do I mean?

Well, I created this test sample for Oracle support. First I gave the then a table definition

CREATE TABLE [dbo].[test](

[Process] [nvarchar](50) NULL,

[Organization] [nvarchar](50) NULL,

[Produt] [varchar](50) NOT NULL,

[F_Code] [varchar](50) NOT NULL,

[Accounts] [varchar](50) NOT NULL,

[amt] [float] NOT NULL,

) ON [PRIMARY]

Next sql to populate the table with some test values.

insert into Test

values('F1','00N18','CP.T354','F.21N1','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N2','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N3','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N4','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N5','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N6','E795000',1467149.93)

insert into Test

values('F1','00N18','CP.T354','F.21N7','E795000',1467149.93)

When I retrieve from the table I showed them I got the following results:

clip_image001

Notice the last column amt matches what was loaded. Next I created a load rule and put in the following SQL statement

* From test

Since this was just a test, I didn’t bother to explicitly list the column names, but usually do and recommend doing that.

I retrieved the data and got:

clip_image002

I don’t know about you, but to me 1467149.93 does not match 1467149.875. If my math is correct it is off by .055. This would typically be no big deal, but these numbers were used in allocations and put the results out of balance by anywhere from 1 to 30 cents per allocation and the differences added up across the enterprise.

The response I got from Oracle surprised me. They told me it was not Essbase but the way SQL Server handled Floating point conversions. Huh? Sql Server had not changed in my environment and worked before. I wasn’t buying their response and rejected it completely. So they went back to development. I kept getting stalled with the similar answers about it not being Essbase but SQL Server and they kept pointing me to web links talking about floating point math.

https://msdn.microsoft.com/en-us/library/ms173773(v=sql.90).aspx https://msdn.microsoft.com/en-us/library/ms173773(v=sql.100).aspx https://msdn.microsoft.com/en-us/library/ms173773(v=sql.110).aspx.

Ok, I get it, I understand floating point math and how it is an approximation, but When SQL shows we the correct answer and Essbase load rules don’t, how can it be a SQL Server issue. I used the analogy with support that I’ve looked at a painting for years and it was clear as crystal. When I got a new pair of glasses, the painting got fuzzy, so that means the fault is in the painting and was not caused by perhaps an incorrect new prescription?

After literally months of going back and forth, I finally got an answer, I didn’t like it, but at least it made sense. I was told:

“A change was made in PS4 that explains the difference between the versions. Previously, all values were binded to SQL_C_CHAR type and the drivers were responsible of doing the conversion while displaying the values. In PS4 the values get binded according to their type (i.e. float is getting
binded to SQL_C_FLOAT). Therefore, the value is converted accordingly by Essbase and not by the driver. This behavior is considered correct.
You can see that the value is indeed stored correctly using a workaround
while creating the rule file: instead of selecting the amt column as is, use str(amt, 25, 2).
The syntax is: STR ( float_expression [ , length [ , decimal ] ] ). “

So now I have to go back and re-code all the SQL to convert the data type. It would have been nice if Oracle documented the change or even better, set an option to use the old coding. But at least I have an answer and so do you in case you are reading this because you did a google search because you are having a problem.

2 comments:

Mike Henderson said...

It gets worse. When HFMEA pushes HFM data to the fact table, it uses FLOAT for the data type in that table, not a decimal. So when you load values into Essbase through the SQL Interface, they can get mangled.

Daniel Willis said...

Gotta love SRs going to development. If nothing else i am happy you have enlightened that 'development' is not code for rubbish bin.

I avoid using floats but obviously you get what you're given on a job. Can see this biting many people who wont be so lucky to spot the issue.

I still dont really get why you get a different value now though. Is it because SQL Server approximates a float differently to Essbase? I'm on my phone so havent dug into the msdn links in the article