Due to various business requirements, I often find that one of the strategies I use for preserving point-in-time reporting functionality is to simply spin off a copy of cube.  In many organizations, the Measures dimension in a financial cube (modeled on the chart of accounts) can be quite large — and a moving target.  By spinning off a copy of the cube, you can provide the ability to query the cube later and recreate a report that was created earlier.

So, normally firing up EAS, right clicking on the application, and hitting copy isn’t too big of a deal.  But, what if you actually have to do this to a couple dozen large cubes?  Additionally, since these reside on the same server, we need to come up with different names for them.  Again, do-able in EAS, but good admins are lazy, and I’m a good admin.  So let’s cook up some quick and dirty automation to do this.

I’ll create a new folder somewhere to keep these files.   First I will create a text file that has the current name of the application, a comma, and what the copy of the application should be named.

Here is my file, called mapping.txt:

;       ,        x
CUBE03  ,YPCUBE03
CUBE04  ,YPCUBE04
CUBE05  ,YPCUBE05
CUBE06  ,YPCUBE06
CUBE07  ,YPCUBE07
CUBE08  ,YPCUBE08
CUBE09  ,YPCUBE09
CUBE10  ,YPCUBE10
CUBE11  ,YPCUBE11
CUBE12  ,YPCUBE12

The semi-colon at the top is actually a comment, and I put it in along with the comma and the x so that in a fixed-width editor like Notepad, I can easily see that I am indeed staying within the 8-character limit for application names (remember that this is a bunch of cubes with all different names so this just helps me avoid shooting myself in the foot).

Next, we need the MaxL file.  Here is the file copy.msh:

create or replace application $2 as $1;

What?!  But there’s hardly anything in it!  We’re going to tell the MaxL interpreter to login with parameters on the command line.

Okay, and lastly, we have the batch file (this is Windows we’re running the automation from).

:
: Parameters:
:
: server, username, password, mapping file
:

FOR /f "eol=; tokens=1,2 delims=, " %%i in (%4) do (
    ECHO Original app: --%%i-- target app: --%%j--
    essmsh -s %1 -l %2 %3 copy.msh %%i %%j
)

Okay, so what’s going on here?  It’s a batch file that takes four parameters.  We’re going to pass in the analytic server to login to, the user on that server to use (your batch automation ID or equivalent), the password for that user, and the mapping file (which happens to be the text file in the same directory, mapping.txt).

So from a command line, in the same folder as all these files, we can run this command as such:

copydb.bat analyticserver.foo.bar admin password mapping.txt

And of course replace the parameters with the actual values (although I’m sure some of you out there have admin and admin’s password is… password… tisk, tisk).

And there you have it!  We can easily adapt this to copy things out next year, we didn’t leave our master ID and password buried in some file that we’ll forget about, we don’t have any hard-coded paths (we need essmsh in the PATH but that should already be the case), and more importantly, instead clicking and typing and waiting and clicking and typing and waiting in EAS (some of these app copies take awhile), we’ll let the server knock it out of the park.  Be sure to watch the output from this to make sure everything is running to plan, since we aren’t logging the output here or doing any sort of parameter sanity checking or error handling.