I have issues

Were I Cameron Lackpour, I would call these stupid pet tricks, but since I’m not, I’ll say they are issues I’ve encountered. Luckily I’ve resolved them so perhaps I can save you the pain I went through.

The first issue I encountered when I tried to use a Custom Defined Function(CDF) that runs a SQL statement or stored procedure from within a calc script. This function was written by Toufic Walkim(Thanks you) and was given to me a while ago. I’ve used this a few times at different clients but on older versions of Essbase. In trying to get it to run on 11.1.2.2 I encountered a number of issues.

First, It could not find the correct ODBCJDBC driver. That was resolved by downloading the driver from Microsoft and changing the properties file to point to it (or so I thought).  Turns out there are two drivers downloaded. ODBCJDBC.DLL and ODBCJDBC4.DLL. After experimentation, I had put the  ODBCJDBC.DLL in the UDF directory and got an error that basically said, I need to use ODBCJDBC4.DLL. Adding it to the directory did not solve the issue even if I removed the ODBCJDBC.DLL. So thinking swiftly (Ok, I was pretty slow) , I renamed ODBCJDBC4.DLL to be ODBCJDBC.DLL. Voilà, now it recognized the driver and knew it was the correct one.

My next issue was that once connected, even if trying to run a simple SQL delete statement, the calc script would hang and I would have to kill the process. Thanks to help from Robb Salzmann narrowing  the issue down  , I was able to Google a few things and found a bug published by Sun that basically says the version of the JDK installed with 11.1.2.2 will hang on connections. I found a later version of the JDK (jdk160_43 to be exact), installed it in the Oracle\middleware directory and pointed the JVMModulelocation parameter in the Essbase.cfg to use it. Now my life is good and the CDF works fine. I did need to remember to bounce Essbase and it took me a while to remember what I needed to do to get Essbase to run in foreground so I could see the messages in the application window (But that is another story)

My next opportunity was with Essbase Studio(11.1.2.2). I was trying to build dimensions and got an error “Cannot get async process state”. I started investigating and found the errors were all with my Entity dimension. If I built without that dimension, everything worked fine.

I should mention I’m not the only one working on the model. My client has SQL developers working to create views and add content. So I looked further and did a refresh of the Entity View. Imagine my surprise when I found there were columns removed from the view I was using in one of my alias tables.The Studio table refresh would not let me update the view since it knew something was being used that it was trying to remove. I tried having the column added back to the view, but still could not get they refresh working. So I went through my Essbase  model properties and removed the alias table the column was in, then went into the Alias table and removed the column from there as well. I was now able to refresh the view with the column changes. Moral of the story, if you get the message, see if your data source changed.

I’ve been reading the blogs and readmes for 11.1.2.3 and like the new features added. While Essbase Studio really only got bug fixes and Essbase itself only got a few new changes, I like what I see and can’t wait to try ASO planning.