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;