An interesting SQL Interface issue

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.

 

Technorati Tags: Essbase SQL Interface,Date bug,Essbase,SQL,Date issue

Essbase Studio Recursive build with Attributes

I’ve used Essbase Studio a little as many of you might know and I like the tool, but once in a while I get perplexed on how to do something I think should be simple. The other day I had a rush requirement to build an attribute dimension based on a table the used a recursive hierarchy in an ASO cube I was developing for a client. Pretty simple I thought, I’ll just add it in like I normally do and it will just work. I should know better. I spent a day and a half trying to figure out what was going on. To start out I had defined my Hierarchy as:

Parent
	Child
ISICP
	Child

and in 11.1.2.2 it gave me the very nice warning that it could affect existing Essbase Models. I bravely went on and after doing a resync of the hierarchy (god I love that the Studio team added that) , I went into my Essbase Model and added the Attribute dimension. I then tried to deploy the cube. What I got back was the error:

Failed to deploy Essbase cube.

Caused by: Cannot end incremental build. Essbase Error(1060053): Outline has errors

\\Outline verification errors:

\\Attribute Dimension ISISP is not associated to the base dimension

\\Record #2 - Member name (BalanceSheet) already used

BalanceSheet Assets

\\Record #4 - Member name (CurAsset) already used

CurAsset CashMktSec

\\Record #6 - Member name (TotCash) already used

TotCash CashPetty

\\Record #7 - Member name (TotCash) already used

TotCash CashatBank

In variations of testing, I also got:

Failed to deploy Essbase cube.

Caused by: Failed to build Essbase cube dimension: (Scenario) .

Caused by: Cannot end stream build. Essbase Error(1007083): Dimension build failed. Error code [1060246]. Check the server log file and the dimension build error file for possible additional info.

\\Error Updating Dimension Acct_W_ICP

\\Error Initializing Rule File Information

At this point I was befuddled, I spent a few more hours making sure the view I was using only had the Attribute value on the level zero child members and tried variations creating the hierarchy different ways. I tried filters to limit the members, an outer join to another view that just had the level zero members, Adding user created members as the parent values all with no luck. If I took out the attribute Hierarchy, the build worked fine.

With deadlines looming and no hope in sight, I opened an SR with Oracle to see if I could get some help. I was able to get in touch with Lyudmila the QA manager for the Essbase Studio team. Her help was invaluable and I owe her a debt of gratitude. She was able to replicate my issue based on info I sent her. The first issue she was able to determine was not with my problem dimension but with my Scenario dimension. Remember the error:

Caused by: Failed to build Essbase cube dimension: (Scenario) .

Caused by: Cannot end stream build. Essbase Error(1007083): Dimension build failed. Error code [1060246]. Check the server log file and the dimension build error file for possible additional info.

\\Error Updating Dimension Acct_W_ICP

My Scenario dimension was made up of a single user defined member “Actual”. This is a place holder for later. It turns out with this combination the scenario dimension followed by a dimension building attributes, you get errors. She was nice enough to open a bug report on this.

But I was still having problems and she was not. Hmm, I started to look at the screen shots she was sending me of the cube she built and realized that she built a BSO cube and I was working on an ASO cube. I was trying to build the attribute on the Accounts dimension. Essbase Studio was nice enough to let me know you are not allowed to have attributes on the compression dimension, so I had turned that off. The error I was getting looked like:

Failed to deploy Essbase cube.

Caused by: Cannot end incremental build. Essbase Error(1060053): Outline has errors

\\Outline verification errors:

\\Attribute Dimension ISICP is not associated to the base dimension

\\Error Associating Dimension "ISICP" to "Acct_W_ICP"

\\Error Initializing Rule File Information

\\Record #8476 - Error in association transaction [410100.10.COGSDir] to [N] (3362)

410100.10.COGSDir N

\\Record #8477 - Error in association transaction [410100.30.COGSDir] to [N] (3362)

410100.30.COGSDir N

\\Record #8478 - Error in association transaction [410100.50.COGSDir] to [N] (3362)

I started looking through the documentation and found a single line in the comparison of ASO to BSO that said you are not allowed to have attributes on the dimension tagged as accounts. Bummer. Not a Studio, but is is making me revisit how I build the cube.

I’m in the process of converting the Accounts dim to not be tagged as accounts, take my Time balance tags and change them into UDAs and use the code from Gary Crisci’s blog entry to add the required formulas in my view dimension to add time balance functionality.

In the near future I’ll also have a blog entry on some interesting things I’ve seen on drill through with recursive hierarchies.

Book Review, New Oracle Aces and A changing on the Hyperion SIG

For those who read my blog, here is the directors cut of a review I wrote for the ODTUG Journal coming out soon. You get the first look at it and the extended features(Just a little extra text)

When I started out in the Essbase world; aside from the database administrators guide (DBAG) there were virtually no other books on Essbase. Since then, there have been a number of books produced on the topic. I have had the pleasure and agony of reviewing some of these Hyperion books. The latest book to be released is Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals edited by Cameron Lackpour and written by Cameron and twelve other authors many of whom are well known in the Essbase community.

 

In every review I have written, I have given a disclaimer: this review will be no exception. I work for a company (interRel Consulting) that has written eight books on Hyperion products. I have edited a few of them and I’m the author of one on Essbase Studio. I am an advocate for the professionals in the Hyperion field and if you have read any of my prior reviews realize I don’t hold back or sugar coat what I think. I am friends with many of the authors of this book and acquaintances of others. When I agreed to do the review of the book, it was with the understanding that I would to be blunt and honest. With all this, I was still asked to do it.

With so many books in what many consider a niche software space, is this book necessary? I think so. I love the Look Smarter books and recommend them to people who are starting out in Essbase. Once you have mastered building a cube, you need more in depth information. This book supplies that extra information but, in most cases, not going into step-by-step detail of how to accomplish it.

I won’t keep the authors in suspense; overall, I really liked this book and it more than fulfills its purpose. The topics are intermediate to advanced and widely varied. While I didn’t always agree with what the chapter author was saying, I understood their rationale behind it enough to accept what they wrote. Almost every chapter had information that would be of interest to an intermediate to advanced developer.

This book is written by 13 different authors, and it is like reading 13 different stories which is good and bad. The writing styles of the authors differ greatly and in some cases it was enjoyable and others it was not. I don’t recommend trying to read this book cover to cover. Find the section(s) you are interested in and read them, then read the other chapters at your leisure to see what tidbits you may have missed over the years.

First, if for nothing else, buy this book for the chapter “How ASO Works and How to Design for Performance.” The content is amazing. I’ll admit, I’ve read the chapter twice now and still don’t understand everything I’m being told. For some this will be overkill, but you can read the highlights and bypass details when your eyes glaze over. This is truly an advanced chapter and opens up the internals of ASO like nowhere else I’ve seen. I’ve already used some of what I’ve read to tune some ASO databases I created.

Ok, no book (especially mine) is perfect and this book is no exception: the chapter on Advanced Smart View wasn’t as advanced as the title might lead you to believe. I felt it was more basic than advanced and did not incorporate the Smart View toolkit as it should have. This chapter could have been the definitive advanced Smart View guide; alas it was far from it.

Now that I have discussed the extremes, the rest of the book is pretty solid. I don’t have the space to discuss everything, so I’ve selected a few tidbits. Most chapters had niggling annoyances that prevented them from being great. For example; I liked the chapter on preventing bad data. It gives a good perspective on how to prevent it, but the chapter is a bit verbose and rambling.

The chapter on the JAPI was good; It was aimed more at beginners using it. I think this is a good idea since intermediate Essbase developers may not have any experience with Java development. A description of development environments and how to include the Essbase classes would have completed it for me. I found the chapter on infrastructure hard to read, maybe because I’m not an infrastructure guy; it was more tables and lists than a description of what you need to do to install or pitfalls you might encounter.

Note, these comments will not come as surprises to the authors: I have discussed it with them.

I enjoyed (well enjoyed is the wrong word), I appreciated, ah better, the chapter on managing a project. There is a good discussion on things a project manager running their first project should be mindful of. The chapter on Groovy was interesting, but I’m still not convinced that Groovy is the way to go.(sorry Joe). Perhaps I’m too square to be groovy.

In summary, I believe once you have gotten past the basics of trying to figure out how to build your first cubes, you should purchase this book, read it, put it on your shelf or Kindle as a reference and thank the authors for their hard work. The book is truly worth the time to read it!

Talking about Cameron Lackpour, the author of Editor if the book, I would be remiss if I did not congratulate him on becoming an Oracle Ace Director in the EPM space.  He is truly a proponent for the Hyperion community and I’m sure he will continue to support it well. It is a well deserved recognition. Another recent recipient is an Oracle Ace honor is John Booth. He is one of the few infrastructure guys out there that is willing to share his knowledge and expertise.  He has worked hard at getting cloud instances for the masses and is active answering questions and speaking. Like Cameron he is well deserving. They should be applauded for their efforts.  Remember an Oracle Ace or Ace Director recipient does not lobby for the award, but is recognized by his peers for the work he/she does in the community.

Finally, A changing of the guard is occurring. I have been on the ODTUG Hyperion SIG for a while now and it was time for me to roll off. The SIG bylaws require that board members serve no longer than a three year term and have to wait a year prior to being reelected. Each year three board members are elected with the restriction that the number of client/customers exceed the vendor/partners. Elections were recently held and three deserving individuals were elected to a three year term.

Eric Helmer
Vice President of Infrastructure IT Services, Linium
Michael LaBarge
IT lead for Hyperion, Cessna
Deanna Sunde
Senior Director, EPM Planning and Essbase Practice, Hackett Group

I’m sure they will do great expanding on what the SIG started. Give them your support and ideas on how to make the SIG better.