Partitions

As I mentioned in my first post, I've recently begun using partitioning to move data between cubes. After getting this working I repeatedly smacked my forehead while moaning, "Why, oh, why haven't I tried this before?!". BP (Before Partitioning, not to be confused with the petroleum giant) I would use one of several options to move data: export all level 0 data (this was usually the fastest option), export the results of a report script to a file, or run a calc script with DATAEXPORT with FIXes. These are all still valid options AP (After Partitioning; no, not Advanced Placement, although you will likely get credit from management for using partitioning), but going forward I will be using them in more limited, specialized circumstances due to the awesome power of partitioning.

Partitioning comes in 3 flavors: replicated, transparent, and linked. While I'm sure they're all delicious, I must admit that I have really only sampled replicated partitions. This is mainly because I've never had a requirement that two cubes remain synchronized in real-time, which would require transparent partitions. The functionality provided by linked partitions can also pretty much be replicated (no pun intended) with judicious use of @XREFs. Therefore, from here on out when I refer to partitions, you'll know I mean the replicated kind.

It seems that most people create their partitions on the fly, replicate data, then delete the partition. I can think of two reasons for this. First, you can only have one partition between any given source-target database combination. So, you may be asking yourself, what if I need to move a different set of data to the target cube than is defined in the current partition definition? You can use substitution variables to some extent, but they won't get you the whole way there. Second, if left to themselves for too long, partition definitions seem to have a disturbing tendency to become corrupted.

Well, without further ado, here is the MaxL:

login user password on 'server';

shell "'date'";
drop replicated partition SrcApp.SrcDB to TarApp.TarDB;
shell "'date'";
create or replace replicated partition SrcApp.SrcDB
area '@RELATIVE("Comparability",0),"Units","Actual",@RELATIVE("Product",0),@RELATIVE("Accounts",0),@RELATIVE("Q1",0),&CurrYr,"USD",@RELATIVE("Entity", 0),@RELATIVE("Brand",0)'
to TarApp.TarDB at localhost
as user identified by password
area '@RELATIVE("Comparability",0),"Units","ActualTest",@RELATIVE("Product",0),@RELATIVE("Accounts",0),@RELATIVE("Q1",0),&CurrYr,"USD",@RELATIVE("Entity", 0),@RELATIVE("Brand",0)' foo
mapped globally ("Actual") to ("ActualTest")
update allow;
shell "'date'";
refresh replicated partition SrcApp.SrcDB to TarApp.TarDB at 'server' all data;
shell "'date'";
drop replicated partition SrcApp.SrcDB to TarApp.TarDB;
shell "'date'";
logout;
exit;

MDX - LinkMember

I've been working with MDX in ASO much more lately.  Databases just keep getting bigger and bigger and they no longer perform well in BSO.  Having worked with relational databases and sql for years before stumbling into Essbase, I am really enjoying working with MDX.  The similarities with sql make it a much better match for me than report scripts, which I generally avoid at all costs.

LinkMember is my MDX friend-of-the-day.  I was working with a database that has multiple-hierarchies enabled for the entity dimension and was struggling to get a currency conversion MDX formula to work with the shared members of the alternate hierarchies.  Turns out LinkMember was the solution.  Take a look at it in the 11.1.1.3 techref.

Introduction

A couple of months ago I moved to Atlanta. I’m no longer consulting, now working in-house at a large company in the area. So far I’m enjoying the change in perspective. While consulting I often didn’t get to see the results of projects. Now I will be around to see exactly how my cubes are used, or abused. This will hopefully give me the opportunity to get more involved in optimizing cubes. I’m thinking here about MDX member formulas in ASO replacing BSO calc scripts. ASO databases seem to be Oracle’s preferred solution going forward, so I might as well get used to transitioning BSO cubes to them. I think the limited ASO calc functionality introduced in 11.1.2 is just the tip of the iceberg.

I’ve already gotten the chance to work with some new-to-me areas of Essbase. For example, I’ve used replicated partitions to move data from a BSO to an ASO rather than exporting level 0 data from the BSO then importing it into the ASO. Partitioning is an order of magnitude faster and easier to maintain. I’ve heard that partitions have a tendency to become corrupted, so I’m keeping a close watch on the replication process. I've also used MDX formulas in ASO to replace currency conversion calc scripts in BSO.

As I’m no longer working crazy consulting hours, I’m hoping to have more time to update this blog with my Essbase escapades. We’ll see how that works out...