Essbase 6x Lift & Shift to the Cloud

Background: I recently performed a POC where the customer was running Essbase 6.5 (yes…I was in junior high when 6 came out!) and wanted to see the level of complexity of migrating to the cloud so they could utilize BI and DV with Essbase (as well as the additional Essbase functionality).

I am cautious when I tell people that it’s pretty simple to move to the Essbase cloud because it seems each organization has a ‘gotcha’ of some sort. This one was no different… Because they were using Essbase 6, I could not use some of the new, fancy tools to extract Essbase artifacts to a cloud ready tool because…it’s old. Just to open the outline was a bit of work itself because I didn’t have a ready on-premises Essbase environment available and I wasn’t even sure if a version 6 outline would load to, say, 11.1.2.4. I even tried creating a shell application in EssCS to see if I could import an otl file, but OAC does not accept that as a file type to upload…

(Note that you cannot use the OAC “dbxtool” to extract an outline from an OP version of an Essbase cube that is version 6.)

Finally, I reached out to an Essbase friend for a copy of AppMan (Application Manager) from back in the version 6 days and he came through to save the day! I was able to view the outline for any gotchas (none, and didn’t expect any). Since the otl didn’t load, I was curious is my rules files would (1) load and (2) be able to be used without modification. The answer was yes to both! This made loading dimensions and data MUCH easier since the older style is apparently still in use in EssCS… (Don’t even ask me about creating new rules files in EssCS…)

Below is how I planned to tackle the lift and shift (LnS) from Essbase 6.5 to EssCS:

  1. Open otl in AppMan to verify structure and outline aggregation.
  2. What dimensions are static and need to be built manually either in EssCS or in the Cube Builder worksheet?
  3. Create the static dimensions in the Cube Builder workbook.
  4. Load the Excel workbook to OAC including the skeleton dimensions.
  5. Load the dynamic member text files and rules files to OAC
  6. Load data
  7. Create and run “default” aggregation
  8. Check to see if data aggregated correctly

I had the customer export the artifacts from their Essbase cube:

Note that is cube is, what I referred to as, “Vanilla”. They said “Kindergarten”…not my words! J

Okay, to my planned attack method…

In AppMan (thanks, CubeCoderDotCom!), I see that most dimensions are all summed to the top. The only one that is not is the Scenario dimension when comparing the Actual vs Budget.

Of these dimensions, the following are static versus dynamic, meaning updated each time the cube is updated. I’ve also added if each dimension is Dense or Sparse as an FYI.

  • Fund – Static; Dense
  • Cost – Dynamic; Sparse
  • Organizational – Dynamic; Sparse
  • Functional – Dynamic; Sparse
  • Responsible – Dynamic; Sparse
  • Year – Static; Dense
  • Scenario – Static; Dense

I download the BSO outline workbook to have a place to start:

Next, I started filling it in:

Essbase.Cube worksheet:

Cube.Settings worksheet:

Note that I changed the “Aggregate missing values” and “Two-Pass calculation” to “Yes” and also added our Alternate Alias Table so we have a placeholder.

At this time, I am not going to add any Generation names to my levels, so I cleared the Cube.Generations worksheet.

Now we start the dimensions… Since all 3 of our dense dimensions are stored, I’m going to go ahead and build out the outline while for the remaining, sparse dimensions, I’m going to create the shell so we can load them later via text and rules files.

Dim.Year:

Dim.Scenario

Dim.Fund

Dim.Cost

Dim.Organizational

Dim.Functional

Dim.Responsible

I deleted the data worksheet since we will be loading that via OAC.

Let’s see how good my skills are with an upload of this workbook to OAC.

An error: Property “Dimension Name” must have value in sheet “Cube.Generations”.

Let me remove the Cube.Generations worksheet to see if it will take:

Darn it: Invalid value “Replace” for property “Incremental Mode” in sheet “Dim.Year”; expected values Remove Unspecified, Merge.

I really thought that Replace was an option, but when I go to the “Understanding Dimension Worksheets” in the documentation, clearly it is not an option.

Okay, fine. I’ll use Merge on all the worksheets.

Let’s try this again.

Phew. Past the first hurdle.

Aaaaand past the second.

Now we want to load up the dimension, data, and rules files to our application.

Note that some rules and text files already exist. These were created as part of our Excel workbook upload process.

Let’s upload some files…10 at a time…

Here are my first 10…

Click Upload…

And I should get a successful message:

Now let me load up the rest…

Now I’m going to go to “Jobs” so I can load the dimensions and data.

Click on “Jobs”, then “New Job”, then choose the correct rules file to go with your data file.

Click “Execute”.

Okay, it has started. We can click “Refresh” until it is finished to see if it worked or failed.

We are good!

I’m going to finish loading the remaining dimensions and data files…

Uh oh. I got an error: Incorrect Parent for Member.

Lemme check that out…

Ah. I see two records where the parent and child are the same. Lemme remove these, reload the text file, then reload the dimension.

It’s going to ask me if I want to replace the file…yep.

Hooray for green check marks.

If you remember in previous versions of Essbase, you could right-click on a database to perform a default aggregation of the cube. Well, we don’t have that option in the cloud so we have to create a calculation. I created a basic calc to do just that:

Let’s go back to “Jobs” and run the calc.

Got the all clear…

Now, let’s create a connection to the cube to make sure there is data (you could also do Smart View or BI in OAC):

Aaaand create a new Data Source:

Choose our new cube:

Voila

From here we can create a DV project or BI Analysis

I chose Analysis and the following elements as a basic check of data:

And when I go to Results, I see values I expected. Great! I can, obviously, tailor things from here, but I have lifted and shifted my on-premises Essbase cube to the cloud!

Now, what about automation? The version I am using does not have MaxL (the current version does, however), so I’m going to use Essbase CLI (command line interface).

Note: This will not work as a batch script! You will need to create another batch script to call the batch processing items. This screenshot is to give you an idea of what the automation scripting looks like. It will not work if you copy and paste it, changing your values. J

And the end of the script…

PS – You may have noticed a .alt file in the export from the customer, an alternate alias file. While OAC does not accept a .alt file to load to the files, you can use MaxL to load the alias file to the outline. My version of OAC did not have this functionality at the time which is why it was not shown. To be updated later!

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s