The Calm Before the Storm

I haven't had a lot to post about lately. I had been wondering if it was my fault but someone set my mind at ease this week. There really hasn't been much new Essbase functionality released lately. A lot of Oracle's focus has been on getting Essbase Cloud Services ready, which I think, has diverted some of the attention from the on premises version. So I view this as a quiet period before our next round of fun begins. Fully functioning Hybrid will be a part of that in all likelihood. That should be a game changer.

I've been plugging away at ASO procedural calcs but that isn't anything cutting edge anymore. I'm hoping to get Essbase 12c up and running now that it included EAS. I'll share any thoughts I have on it in this space soon.

Kscope16 Reflections

I've had a few weeks to digest all the Kscope16 content and experiences I absorbed. I really had a great, if exhausting, time at the end of June with all of my favorite EPM people. This was my second Kscope conference and I think that I got more out of this year's conference than Kscope15. Part of that has to do with relationship building. There's no good substitute for meeting people face to face, interacting with them and discussing things. I will note three main reflections from the conference.

People and Relationships

I put this first because I believe this is the single greatest benefit of Kscope. I do interact with people via Twitter, OTN, Network54, email but I am the type of person who needs personal contact. I got a chance to speak with people from around the world catching up with them on topics ranging from the latest technology trends to the Brexit announcement. It was really cool to hear the opinions of such a diverse bunch of folks. Having access to the greatest minds in the EPM world, including the Essbase product management team is pretty much a once a year opportunity and Kscope is it. I look forward to Kscope17 where I'll be able to further develop the bonds begun this year.

Hybrid

Much of my technical interest in the conference was directed toward Essbase Hybrid aggregation. The panel of customers and consultants who are running Hybrid in production was particularly enlightening. It seems clear to me from listening to Oracle product managers that this is the direction Essbase is headed. Having said that, I really feel like Oracle needs to have a single message related to Hybrid. I heard "Hybrid is just BSO", which is kind of true. Then I heard "Hybrid is BSO with ASO aggregations" which is also true. I think I heard a few other things. Couple that with the fact that the word "hybrid" means a lot of things, even just in the EPM world, and there is the potential for a lot of confusion. Having said all that, I'm looking forward to coming enhancements that will take Hybrid to the next level.

My Sessions

I'm not sure what I was thinking when I submitted three abstracts for Kscope16. I guess I forgot how much actual work goes into creating a technical presentation. Thankfully only two abstracts were accepted. I thought the presentations went fairly well but then I got back my evaluations yesterday. They weren't bad but it was funny to read them. Apparently my audience thought that their fellow audience members "asked too many annoying questions." Anyway, the feedback was important and hopefully I can use it to improve my future presentations. There are a few reasons I'd encourage others to present at a future Kscope. 1) When creating a presentation it really forces you to know your topic. You learn a lot during that preparation. 2) You get to show what you can do to potential future clients and co-workers. Imagine walking in to meet an interviewer and they remember you from the killer presentation you gave a couple of years ago. 3) The amount of interaction and discussions your session fosters with attendees is incredible. I cannot even count the number of people who came up to me in the hours and days following my presentation to thank me and discuss the topics further. Those were some of the best conversations I had all week.

Final Thoughts

If you haven't been to Kscope before, I'd encourage you to go. It really is a lot of fun. If you've been and haven't presented, I'd encourage you to think about submitting an abstract on a topic near and dear to your heart.

Getting Ready for Kscope16

Now that I've finally uploaded the final drafts of both my presentations I feel like I can get excited for Kscope16 in Chicago. I'm really looking forward to seeing old and new faces. I can't wait to see some of the presentations. A few stand out:

Hybrid in Production Panel
Hybrid, hybrid, hybrid is all I heard at Kscope15. Let's see what people are doing with it.

Essbase, the Cloud, and Why You Should Care
I have a feeling Kscope16 is going to be all about the cloud, cloud, cloud.

Strange Essbase Experiments
This sounds like it will be right up my alley.

I'll be speaking about my own experiments and findings with Hybrid on Monday at 10:15AM. I'll also be sharing my ASO tuning and calculation experiences on Monday at 2:00PM. Please feel free to stop by and say hello afterward.

Essbase.cfg setting QUERYRESULTLIMIT Follow-up

I had a couple of questions that nagged me a bit from my original write-up on QUERYRESULTLIMIT.

1. What happens when you set the value to zero?

In some Essbase config file settings a zero value indicates an "unlimited" setting. In this case when I tested a zero value it actually set the query result limit to 0 cells. So I don't think there's a way to set it to unlimited. I'm not quite sure why anyone would set the limit to 0 but it's there for you if you need it.

2. Does Essbase run the query and then quit or just figure out that the query is too big and never bother running it?

From the minimal testing I did on this, it seems like Essbase figures out how large the query is going to be and throws an error if it is too big, never actually asking the database for any data. I tested this by creating two identical cubes with different QUERYRESULTLIMIT settings. Then I fired an MDX statement that took about 7 seconds on the cube with a larger setting. The cube with the smaller setting returned the error immediately. More extensive testing on this might be necessary.

Essbase.cfg setting QUERYRESULTLIMIT

I had to piece together information from a few readme files to figure out what happened with the QUERYRESULTLIMIT essbase.cfg setting. It looks like the most complete information is in the Readme for the Essbase 11.1.2.3.508_22314799 patch set which you can find here.

In a nutshell, this new Essbase configuration file setting now controls the maximum number of rows and columns that Essbase will return. This was formerly controlled by the essbase.properties APS file. The service.olap.dataQuery.grid.maxRows and service.olap.dataQuery.grid.maxColumns settings are deprecated as of Essbase versions 11.1.2.3.508_22314799+ and 11.1.2.4.008+. I'm not sure why 11.1.2.4.008 since the setting is included in 11.1.2.4.007 -- possibly because the APS settings aren't yet deprecated in that version.

I think it is important to note that the setting specifies the maximum number of cells, not rows, to an MDX or spreadsheet query. It's interesting to me that now I don't think there's a way to limit users to 256 columns on their sheet.

Also note that patch .009 warns: The default value is 1,000,000 and can be increased to 100,000,000 but no other value will be honored. I don't like this wording -- it makes it seem like the only allowable values are 1,000,000 and 100,000,000 but that's not true. The original readme file specifies an upper limit for 2^31 which is just over 2 billion cells. I want to talk to the user who needs that much data. For now I'm assuming all values between 0 and 100,000,000 are acceptable.



Like most Essbase configuration settings, the setting can apply to either the entire server or you can set it on a database level.

Syntax

QUERYRESULTLIMIT [appname [dbname]] n

Some questions I had that I tested out on 11.1.2.4.009.

1. Do member names count toward the number of cells?
No, they do not.

2. Does the setting apply to Report Scripts?
I don't think so. I tested it out and never got an error. I found no mention of Report Scripts in the documentation.

3. Does the setting apply to the old Excel add-in?
Yes it does.

4. I don't understand the .009 documentation -- which values work?
I successfully tried the following settings using 11.1.2.4.009:
1,000
10,000
100,000
1,000,000
2,000,000

I couldn't get Smart View to give me back 100,000,000 cells so I didn't test those upper limits. I'm going to assume that the documentation means that any value entered above 100,000,000 will be treated as 100,000,000.

5. What happens when you hit the limit?

MDX:
MAXL> SELECT {[Jan],[Feb],[Mar],[Apr]} on columns,
   2>
   3> [Stores].members
   4>
   5> on rows
   6>
   7> from [ASOsamp.Sample];

   ERROR - 1200766 - The number of query result cells exceeds the limit [1000] set by the QUERYRESULTLIMIT configuration setting.
   ERROR - 1241101 - Unexpected Essbase error 1200766.

Smart View

Add-in

Please feel free to share with me any results you get that can add to this analysis.

FAQ: MDX Crossjoin()

This is a post to address one of the most frequently asked MDX related questions or issues. Someone will ask: why isn't this CrossJoin function working?

CrossJoin({[Actual],[Budget]},{[Account_123]},{[Jan]})

The reason the above won't work is that the function is being fed three arguments and the CrossJoin function will only accept two. The CrossJoin function will take two sets and expand them into all possible combinations of tuples. In order to expand more than two dimensions, one needs to use more than one CrossJoin. The above can be re-written correctly as:

CrossJoin(
    CrossJoin({[Actual],[Budget]},{[Account_123]}),
    {[Jan],[Feb]})

And will yield a set containing the following tuples:

([Actual],[Account_123],[Jan])
([Actual],[Account_123],[Feb])
([Budget],[Account_123],[Jan])
([Budget],[Account_123],[Feb])

You can nest as many CrossJoins as you like, adding one additional function for each new dimension you wish to expand.

On a related note, I also see people asking a similar question that I'd give a slightly different answer to. Why isn't this CrossJoin function working?

CrossJoin({[Actual]},{[Account_123]},{[Jan]})

The same answer still applies here that too many arguments are being sent to the CrossJoin function. However, in this case, no CrossJoin function is needed as there is only a single member from each dimension. Simply using a tuple here will be sufficient. That would be written as follows:

([Actual],[Account_123],[Jan])

Patching Essbase 11.1.2.4.x Recommendation

I'm sure everyone who patches their Essbase server reads all of the documentation thoroughly but just in case... Please note that the following recommendation is included in the Essbase 11.1.2.4.x patches:

Recommendation: After you apply a patch within the same release codeline, Oracle recommends as a best practice that you export the data from your databases, clear the data from the databases, and then reload the data.

For customers with a handful of databases this probably isn't a big deal. But for shops with 500 databases covering many terabytes of disk this could be a huge task literally requiring months of effort.

My best guess is that they've changed the format of files being written to disk but I'm not really sure. Also note that it's a recommendation. But not following those can come back to bite you when you're trying to fix a production down issue in the middle of the night and the level 1 support technician asks very casually, "did you do an export, clear and load after you patched this server?" You've been warned.

Smoking Hot Kscope16 Deal For Recent Graduates

I remember way back to the late 1990s when I started working with Essbase. I was a year out of college when I first learned the tool. I was very eager to learn so I'd go home in the evenings and read the Essbase DBA Guide which was a two volume paperback set.

I also had a manager who loved to talk about Essbase. He was a smoker and I was not but I'd still tag along with him on his smoke breaks so we could talk about Essbase. I'd fire question after question at him. I learned a lot quickly.

It was the spring of 1999 and the Hyperion conference was coming up. I asked him if he would take me. He didn't think I was ready to go. So being a brash young developer, I pushed a bit. I said, "If I get my Essbase certification will you take me?" I'm sure he figured this was an easy out so he agreed.

This pushed me to work even harder. I set a date to take the exam on a weekend and didn't tell anyone. I was able to pass the test and got to attend the conference in sunny Orlando, Florida.

It was at this conference that I made so many contacts in the Essbase world including a young Tim Tow just getting his company started. I also made a contact at Hyperion who invited me to participate in the v6 Essbase beta program. That experience got me a trip to Sunnyvale, CA for v6 training. That experience got me a slot giving a presentation at the Hyperion conference the following year in Las Vegas. I could go on and on about the cool people I got to meet and the great experiences that came out of that first conference I attended.

That brings me to my point: there is extreme value for recent graduates and newbies attending conferences like Kscope. The dividends pay off over years rather than days or weeks.

I'll close this posting with some really cool news. ODTUG is offering 50% scholarships to recent college graduates (2013 or later). Hurry up and try to get one of the 50 scholarships available.

I hope to see you in Chicago in June!

Creating Test Hierarchies

If you've ever wanted to test out an Essbase cube of a certain size, you might have run into the problem of having to create the necessary hierarchies. How does one quickly create a 100,000 member customer dimension to test out sizing? I wrote a simple Perl script that tackles the problem in a fairly simple manner. Just use the following code and it will create a balanced hierarchy extract in parent/child format that is x levels deep with y children under each parent.

1:  #!/usr/bin/perl  
2:  my $dim = "Product";  
3:  my $gens = 4;  
4:  my $members = 5;  
5:  my @hier=(1);  
6:  foreach (1...$gens){  
7:    $count = 0;  
8:    @copy_hier = @hier;  
9:    undef @hier;  
10:    foreach $member (@copy_hier){  
11:      foreach (1...$members){  
12:        $count++;  
13:        $child = $member."_".$count;  
14:        print $dim."_".$member.",".$dim."_".$child."\n";  
15:        push @hier, "$child";  
16:      }  
17:    }  
18:  }  

You can change lines 3 and 4 to alter the number of generations and members under each parent. Change line 2 if you desire a dimension name other than Product. This is very simple "starter" code -- there are endless possibilities of customization you could do such as randomizing the number of children.

If you want to know how many members the hierarchy will have there's an easy math trick that use. The equation for a geometric series is:

where x is the number of members under each parent and n is the number of generations. In this case it's 781 members. You can put the formula into an Excel equation and play around with the inputs until you get the dimension size you're looking for.

The file output looks like so:
Product_1,Product_1_1
Product_1,Product_1_2
Product_1,Product_1_3
Product_1,Product_1_4
Product_1,Product_1_5
Product_1_1,Product_1_1_1
Product_1_1,Product_1_1_2
Product_1_1,Product_1_1_3
Product_1_1,Product_1_1_4
Product_1_1,Product_1_1_5
Product_1_2,Product_1_2_6
Product_1_2,Product_1_2_7
Product_1_2,Product_1_2_8
Product_1_2,Product_1_2_9

Once you've got your test dimensions built, check out this tip for loading test data to the cube.

Don't Forget To Register For Kscope 16!

Friday, March 25th is the last day to save $300 on your Kscope16 registration. If you are going to attend one conference in 2016, this is the one to choose. I know many presenters are working hard, putting in extra time during their evenings and weekends, to get content ready for this terrific conference. Don't miss out. Click here to register.