Cube Designer - Tips and Tricks #1

For me, the Cube Designer is a powerful and natural way to design an Essbase cube.  I love the fact I can use the power of Excel to aid me in all aspects of dimension building.  However, one of the drawbacks of the DBX paradigm is that it is fairly rigid in it's support for incrementally updating an existing cube.  For example, by default you cannot move members from one parent to another in an existing cube.


But there is a way around this limitation.  It utilizes a few undocumented features of the DBX process.  I will discuss the process for enabling DBX to do everything except rename a member in an existing cube.

Single Dimension DBX Workbook

The first technique I will utilize is the ability to update an existing cube using a DBX workbook that contains only one of the dimensions within that cube.  The benefits to this are fairly obvious with a large cube.  The Excel workbook is much smaller for a single dimension and therefore the DBX processing is much quicker if all that you need to do is update one dimension.  Note: You must include the Essbase.Cube, Cube.Settings, Cube.Generations and the Dim.xxx worksheets in the workbook.

Multiple Dimension Worksheets

The second technique is the ability to have multiple worksheets for a single dimension within the same workbook.  The worksheets must follow a certain order and naming convention.  The Dim.xxx worksheet must come before the extra dimension worksheets.  The extra worksheets should be named Dim.xxx.1, Dim.xxx.2, and so on.  Note:  This technique of breaking one dimension into multiple worksheets is used by the DBX export if a dimension is larger than 1 million members since that is the limitation of an Excel worksheet.

Dimension Reset

OK, so now that the pre-requisite techniques have been covered it is time to get to the "trick".  Once a dimension exists in a cube, the DBX dimension editing allows for a continuous flow of edits based upon the dimension worksheets contained with a single workbook.


In this solution, the main dimension worksheet (Dim.xxx) is what I call the "Reset" worksheet.  The key is to set the Incremental Mode property to Remove Unspecified.  Also, the File Name and Rule Name properties should be set to the letter "A".  This dimension worksheet will contain just one member other than the dimension.  I do this to clear out the previous dimension hierarchies and there must be one member under the dimension to allow for all dimension storage types.  Do not worry, this dummy member will be removed by the next dimension worksheet and more importantly the data in the existing cube remains in the cube after this process.

The second dimension worksheet will contain the entire dimension with all of the edits to the members and their hierarchies.  Again, the key is to set the Incremental Mode property to Remove Unspecified.  The File Name and Rule Name properties should be set to the dimension name.  Note:  By setting the Allow Moves property to No, this technique will support shared member hierarchies.

So, the build process will use the first dimension worksheet to clear out the existing dimension hierarchies and the second dimension worksheet to rebuild the dimension with the new edits.


I have tried to use this technique with more than one dimension contained within the same workbook but the order of processing is not guaranteed.  So multiple dimensions in the same workbook might work for certain cubes and not for others.  Segregating the dimensions into their own workbooks works every time, for every cube I have needed to edit.