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.