Kaleidoscope changes

For those of you are my groupies (I guess the whole post is for Cameron) and signed up for all my presentations at Kaleidoscope, I have a change for you. Edward Roske and I switched a session. I will now be presenting "Essbase Studio Tips and tricks" on Wed at 9:45 and Edward will be presenting "Configure it out" on Thursday at 9:45. All of you Roske groupies can change your schedule to listen to his dry sense of humor. The switch made sense to us, I'm almost done putting together the presentation and I think it has some good content. A word of warning, if you read the abstract on the the Kaleidoscope website you will get a little confused. They did a good job of updating who the presenters are, but in the abstract is still says Edward is presenting.

Wow, after my initial posting, I found that I've been added to another session. This time with Edward Roske and Tracy McMullen. It is on Tuesday at 9:45 and is called "Ask interRel's gurus, More fun that a barrel of monkeys".So for this session you don't have to choose between Edward and me. Actually if it works like normal, Tracy will answer the questions and Edward and I will make snide or off topic comments. What I want to know is how fun is a barrel of monkeys? One would think they would get rather annoyed being packed together. I do think it will be interesting, we never know what will happen. I typically end up learning new things at these sessions from the audience.

Cameron on his blog has posted the sunday symposium and it looks like the topics are interesting and meaningful. I am anxiously waiting to see what is new, exciting and on the roadmap.  You can find the agenda here at http://camerons-blog-for-essbase-hackers.blogspot.com/2010/05/kalidoscope-2010-symposium-agenda-is_18.html

If you are not signed up to go, what are you waiting for? If you miss this conference, you miss so many technical presentations that you will fall behind. I would say this is where all the cool kids will be, but to be more accurate, this is where all the geeks  and nerds will be. (I'm proud to be a geek or is it a nerd?). All of the Oracle Aces in BI except for John Goodwin will be there presenting. I'm still hoping John comes out. If he does, he will get very drunk as a lot of us owe him drinks for help in one thing or another.

I'm not sure anyone ever notices it, but I think the coolest thing on my blog is the useless knowledge box that appears on the lower right hand side, you might need to scroll down to see it. I come to my blog just to see the trivia. Of course some people say my whole blog is useless knowledge, but that is a different story

ODTUG Hyperion SIG and Essbase Studio 11.1.2 features.

Are you interesting in helping you fellow Hyperionites (I made that word up). If so, consider running for the ODTUG Hyperion SIG board. There are three openings this year. The bylaws require at least 50% of the board be from end users and not consultants. This is so end user need are met and the board does not become a consulting advertisement. This is a hard working group and if you looked at the agenda for the 2010 kaleidoscope conference, you will see they have put together a great program. They work all year to prepare for this one week. If you are interested  in becoming a board member, please contact Natalie at Nadelemar@yahoo.com to submit your intent to run. After a year’s absence on the board, I have decided to run again. If you don’t plan on running, plan on voting for me.



In my last post, I was talking about issues with Essbase Studio and my hope that many things would be better in 11.1.2. With that version now out, I thought I would highlight some of the improvements that I think are significant. First, you can now install Studio as a service, why that was left out in the prior version I have no idea. You could do it yourself, but it was a pain to set up.

Next is support for ODBC DSNs and OCI connections. There were times I needed to specify a particular connection and was not able to, this resolves it.

Here is one of the big improvements. Enhanced Data source sync. You can now delete tables (as long as nothing is using them, makes sense) You can refresh them for changes in columns or data types. YEAH. I don’t know how many times I’ve had to rebuild because of the lack of this functionality. It will make me a lot more productive.

You can now create text lists from Studio. While I have not done a lot of implementations with Text lists (maybe because I couldn’t create them). This is a piece of functionality that was missing.

Another big change is in Essbase Models. With the prior version(s) if you made changes to hierarchies, you had to rebuild the Essbase model every time. Now there are a number of conditions in which you don’t have to rebuild. Things like renaming or moving members or changing the bindings or overridden bindings does not require you to rebuild. Items like adding or removing hierarchies, reordering or adding members to hierarchies still require rebuilding. At least the dev team is on the right track. I’d like to see more cases where you don’t have to rebuild, but at least some things are better.

Hoo Hoo, you can now set the order of dimensions in the Cube schema. This was a huge hole in the functionality. In at least 11.1.1.3, you could emulate this by inserting the dimensions in a particular order,but once done,  you could not change them. If you did it manually in the cube, if you deleted and rebuild members, the order would go away. Such a little change can make such a big difference.

Automatic placement of stored members before shared members. This goes along with an Essbase change and is important for ASO cube especially, although it does have an impact on BSO cubes. ASO cubes will fail to build if the shared members come first. This figures that out and set the first instance to be the stored member and subsequent members to be shared.

No longer do we have to parse through CPL code to figure out what a deployment did in order to recreate it in MaxL. We now have the ability to save off the deployment as a script to use later for automation. Of course we can also edit the script to add encryption and other steps, but it makes life easier. Talking about making life easier, when you run a deployment through the studio console the console will show you the step it is performing. You could watch the Studio Server window to see the same thing; if you were connected to the server, but this is much easier.

Recursive Hierarchy drill-through. That is a mouthful. While I was able to do this with custom SQL it was a pain to do. If I did it myself,  I could not set the level to allow drill-through  to occur at. Since recursive hierarchies are one of the main ways dimensions are created (especially if you use products like DRM) it did not make sense to not be able to do this. I know the code must have been a bit difficult for the dev team, I’m glad they figured it out.

While there are other features added, these are the ones I think have the biggest impact.

On a related note, in my last blog entry, I talked about a bug the Smartview team owned about being able to drill-through on intersections you do not have access to . I got notification that they are working on implementing a fix for this in one of the 11.1.1.3 releases. I look forward to it.

Essbase Studio issue and work around

I was working with data loads in Essbase Studio and ran into some odd behavior (OK working with Studio IS odd behavior). I had a data load that kept failing with a data item found before all members error. . I took the generated SQL and ran it through a SQL tool and it returned properly. SO what could it be. From Studio 11.1.1.1 I remembered you could not edit dimension or load rule files, so the first thing I did was try to open the data load rule (I never learn). To my surprise it opened. For fun I tried dimension build rules and they fail with a message that you can't open file created in Studio, oh well). I went and looked at the SQL in the rule and it was the same as the SQL I pulled from the Essbase Model. Since the SQL should work, I tried to retrieve it into my load rule to see what the data looked like there. Below is what I got:

Notice the two columns. It turns out these were manually created dimensions that I had set the default bindings to set. In my SQL (Oracle based) The two columns looked like:

'MTD' as View,

'Actual' as Scenario.

In one case (don't ask we why) it took MTD and only returned the "M" for Actual it returned nothing! Huh??? To fix this I got creative. remembering back to old old old problems with certain data types from SQL, I modified the code to look like

Cast('MTD' as Varchar2(10)) as View,

Cast('Actual' as Varchar2(25)) as Scenario



And it worked like a champ. There is something in how it is handling the literals that does not work right.



I have spent about two months working with Oracle support showing them the issue (Three different web conferences with three different people and sent them my code) but they have not been able to recreate the issue on the support environment. So after a lot of time, I gave up and closed the ticket. This means we won't have a fix for what I consider a bug, but support doesn't recognize, but at least now when you run into it, you know how to work around it.


There is a lot of potential in Essbase Studio and I look forward to the 11.1.2 release where a lot is supposed to be fixed. When 11.1.2 comes out, I'll look it over and let you all know all the cool stuff it will do.

One item that will not be fixed (that I know of) is with Drill-through. There is an issue I found where if you do not have access to an intersection of data from Smart View, you can still drill-through to detail. I don't know if this is a Smart View issue or a Studio Drill-through issue, but The Smart View team has taken ownership of the issue (Thank you Matt) and is looking for a fix to this. I commend the Smart View team for their consistent willingness to listen to problems and react.