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.

Quiz 3 Answer

Amarnath had the quiz answer about 99% there.
Note, I moved hie paragrapgs around a little to make it more meaningful but his reply was:

Since boolean function results in 0 or 1, if the current member has UDA as "Major Market" then the boolean function will result in 1, if not it will result in 0 and dividing by 0 results in #Missing. By this method it is not required to use an IF statement.


FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales"->"Market"->"Product" = "Major Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales"->"Market"->"Product" = "Small Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX


The problem with his answer is he needs to do a fix before his existing fix to clear out the values as he is writing to an upper level intersection and the code would not be rerunnable (is that a word?). It is nice that he tried to use the last quiz's answer as part of his response, but I was not bright enough to think about having that happen. It shows he actually read my solution :)

Clementine's modified version of his code is:

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales" = "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales= "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX

AGG(PRODUCTS,MARKET);


In this case, Clementine would have to have the agg statement but gets the ability to drill into Market to see what Markets made up each of the calculated members.

If Clementine was not so picky about Fix statements she could have done a fix around the Agg to only do the Aggs in the Actualscenario, but that would have broken her rule.(And added efficiency.). If the requirement was not to use the UDA, you could have also used the attribute dimension and would not have needed to calculate at all or you could replace the @ISUDA with @ISATTRIBUTE. There are a number of other solutions available and Jared had the beginnings of one, but his solution had a few problems.But is was a really good try. A little modification and it would most likely work. If you have another solution, please post it, In Calc scripts, like many other things in Essbase, there is more than one way to accomplish things.

Only two people tried to answer the quiz. Do I need to make the easier or harder, keep them as they are, forget about them or just post them as tips instead of quizes so you don't have to think too hard?

Quiz 3

In a cavern, in a canyon,
Excavating for a mine,
Dwelt a miner, forty-niner,
And his daughter Clementine.

Now Clementine in addition to being beautiful was a bit particular. She
was an Essbase Calc script wizard. As I said she was particular, this proves it.

She hated the word IF because she know if statements are slow and Clementine was a fast woman. She figured if she was going to Fix something it should be fixed the first time and not again.

She was asked; well it was highly requested or she would be swimming with the fish (and from the chorus, we all know how that turned out)
Ruby lips above the water,
Blowing bubbles soft and fine,

to create a calc script in the Sample.Basic database (BSO) that would take Sales and and add them into a one of two members.
Major Market Sales
Small Market Sales
(Members she had to create in the Accounts Dimension)
The calculation should be done on level zero of all dimensions and be based on the Market UDAs. Only one fix statement and no if statements.

Can you help Clementine?

I'm so lonely, lost without her,
Wish I'd had a fishing line,
Which I might have cast about her,
Might have saved my Clem

Note, Clementine does not refer to any real person, the song just popped into my head, so I thought it would be a fun to include it in the quiz and perhaps throw you off a bit.

OK, now for some other news, Edward Roske created yet another linked in group, but this one is just for us Essbase types. Not Hyperion, Not Oracle, Not planning, but just Essbase. If you want to be like one of the cool kids, come link it at http://www.linkedin.com/groups?gid=2905269

Kaleidoscope detailed agenda is now available

They just published the detailed agenda for Kaleidoscope at Kaleidoscope Agenda . What I want to know is why all of the presentations that I want to go to are always scheduled when I am presenting. The Best Practices for Performance, Scalability, and Reliability with Oracle BI Enterprise Edition from Mike Duran on Tuesday conflicts with my "MDX Basics, practical coding examples". IT will make it hard for people to choose which to go to.

Then on Thursday Edward Roske's Integrating Essbase and OBIEE: Implementing in the Real World conflicts with my Essbase configuration settings presentation. I would go to Edward's presentation. Finally my Load Rules Basics to advanced conflicts with Matt Milella's Convert Legacy Add-in Solutions in VBA to Smart View VBA or .Net. I really wanted ot go to this one. If everyone in my session goes to Matt's session, I can skip mine and go as well. I'm sure that won't happen, so I'll be presenting. I think mine is a good presentation I just finished putting it together and it has a little bit for everyone. It truly is Basics to Advanced.

As I said before, there is too much great content at the conference and you will not be able to attend everything you want to, but for most presetations the slides will be available. For my presentations, at some point interRel consulting will allow me to present them during their tuesday and Thursday webcasts.

Quiz 2 answer

I am amazed no one came up with the answer I was thinking about. Amarnath came up with an interesting idea on using a partition and ASO cube. While it might work, I have not tested it, but in my dealings with ASO cubes, partitions and calculations combined together, I have found less than satisfactory performance. Still it was thinking outside the box and he gets brownie points for that.


My solution (see code below) breaks the rules for using cross dimension operators on the left hand side of the equal sign. The trick here is to bypass all of the intermediate levels and only put data where we need it for our subsequent calculations. If necessary, we could agg the dimension later to get all the values populated. Since the number of level zero combinations is pretty small, cycling through them is very fast. The solution is always writing to the same block, so that block will most likely be kept in memory. Note, this solution will also work if one or two of the dimensions are not at top level. For example; if I had a customer dimension and wanted to get totals by customer, I would just fix on the level zero of customer. If I don’t put the customer dimension in the left side of the equation, it would do the calculation for each customer. If doing this, don’t forget to add the level zero parents in the first clear statement.

So what exactly is the solution?

In text, we clear out where we want to stick the data. This makes the code rerun able.

Then we get to the level zero of the dimensions and cycle through the members for each member we add to our total block. Since this is a dense calculation and it is only on level zero member, this calculation can be very fast.

/* Need to clear out any data that exists at the target intersection.
This really is clearing one block) */

FIX(@levmbrs("Year",0),"Actual","Product","Market")
   "Sales" = #Missing;
ENDFIX

/*Now fix on level zero of the dimensions and add to the total */

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

/*Note Sales is in a dense dimension member so we are doing a dense calculation */

/*Since I'm doing a cross dimension operator on the left side of the equal sign it has to be in a block statement */

"Sales" (
    "Sales"->"Market"->"Product" = "Sales"->"Market"->"Product" + "Sales";
)
ENDFIX

At a recent client, using this technique, I was able to cut 30 minutes off of multiple calculations. In total I cut 10 hours off of a calculation process since their calculations did a ton of allocations. It helped that when the calculation was done, the data was copied to an ASO cube for reporting, so I never had to really agg the cube. If I did have to agg the cube, It would have only been one time instead of multiple times. I should point out as side effect of this method was the cube size was brought down from 10 gig to 2 gig and fragmentation was reduced a lot.

Let me know what you have a different solution. If I continue on with this series of quizzes, you will soon know all I know (which is not much). So let me know if you find them interesting.

This and many more tips and tricks can be seen at the Kaleidoscope conference in Washington D.C. at the end of June. It is a worthwhile investment to attend.