It seems to be my time to find bugs. First a little background for you. I’m working on Essbase and Studio 11.1.2.2 . I’m on a project using Essbase Studio to talk to EBS to pull GL data and drill through data. We have a view that uses a function that was written that passes the date off of a table to do a currency conversion. The query we are running pulls from a view that used the function. When we ran the Query in toad, the data we expected was returned. When it was run from an Essbase Studio Data Load SQL the wrong numbers were being returned for currency converted values but not the local (Functional) currency values.
There were a number of things tried to narrow down the problem. First, we cast the output as a Varchar2 to make sure there was no issue with numbers(This is a common problem and I actually had to do that on a drill through report to get the numbers right). I tried pulling the unconverted amount and it was correct. I tried to pull the currency rate and it was coming back wrong. Hmmm, a starting point to the problem. Looking at the Function, it converted the amount based on the transaction date that was being passed to the function. If the date was invalid or missing, it went into an exception routine that picked up the prior month rate. This was the rate we were getting back, so it was evident that we were going into the exception routine.
To test further, I narrowed down the SQL with a where clause to process a single row of data and ran it in both Toad and from a load rule. Again differences. We added the date field to both queries and noticed the format of the date was returned differently from Toad and Essbase. Upon further investigation, it seemed the date format of the date field being passed to the function changed format if it was being called from Essbase even though the specific query itself did not reference the column at all, it was just used in the view itself. The data processing within Oracle was getting the wrong date format.
We worked around it by modifying the date passed to the query to be in a specific date format, but followed up with Oracle support with an SR. Oracle was able to replicate the issue and has created as bug for this. (or course the answer was it will be fixed in a future version). I’ve not tried this on any other version, so I don’t know how long the bug has been around. Oracle certainly didn’t know about it. I’m posting this in hope it might save one or more of you the time I spent trying to debug this issue.
As a side note, I got an email today from ODTUG reminding everyone that session submissions for KScope13 in New Orleans are due by 10/15/12. It seems like the conference just ended and already I have to think of new content for next year. If you have interesting information to share, submit an abstract yourself. If selected you get a free pass to the conference. If you don’t want to submit one yourself but have an interesting idea for a session or a specific topic you want discussed, let me know and I’ll consider submitting it myself(of get someone who know about the topic to). Why do I bring this up in this post, because I’m thinking about doing a tips and tricks for Essbase Studio presentation and want to get your feedback if you would like a session like that. If you want tips for getting an abstract accepted, have a look at the ODTUG Content page
I think my next post will be on recursive Drill through tables and interesting things I’ve discovered working with them. Stay tuned.
Posts
No comments:
Post a Comment