page static_html.xsl b2005_03_15

Moving big cubes using a custom backup and restore add-in

Backing up and restoring Analysis Services (AS) catalogs is a common enough administrative task. But those of you who are pushing AS to its limit know that the backup and restore utility built into Analysis Manager has a size limit of 2G for any partition. It is not at all uncommon with a large data warehouse project to end up with cubes bigger than that. And when you cross that threshold you have two options a) just build the cube and don't try moving it, or b) build your own new and improved cube archive utility. Note that with the Enterprise Edition, you could create multiple partitions to keep the size within this limit.

Option A is not very attractive. In my OLAP work, the cubes were built on big Itanium machines. This expensive (at least at that time) machine was a dedicated, centralized cube build machine - it would easily outperform 32-bit boxes on cube builds. Cubes were built on the Itanium and then moved onto reporting servers. These were 32-bit boxes which are much less expensive but work perfectly well for querying the cubes. But the dilemma we soon faced was how to get cubes larger than 2G from the build machine to the reporting machine. Our solution was to roll our own archive utility using available technologies - DSO, VB, & WinZip. For this article I have cleaned up and commented this application (such utilities don't normally get such respect) and attached it as a zip download.

Programming tasks

There are two parts to a cube archive. The first part is the task of gathering up the metadata for the catalog. The second task is to compress the disk files associated with the catalog. The DSO library is used for the metadata gathering task. For the compression task, I use WinZip.

WinZip automation

One of the perhaps less well known features of newer (V9 +) versions of the WinZip compression utility is the command line invocation option. The other important feature is the ability to handle large files. You may have to update to a newer version of WinZip. And then you need to install the WinZip command-line add-on (http://www.winzip.com/downcl.htm). Your copy of WinZip must be registered in order to function properly with my code.

The command-line add-on will add two programs: wzzip.exe and wzunzip.exe. Add the folder containing these EXEs to your path. Then you can type wzzip at a command prompt and get the usage report.

To test what I will do programmatically in VB using a WshSell Run() method, I can open a command prompt to the folder containing my Analysis Services disk files (It's C:\Program Files\Microsoft Analysis Services\Data on my machine) and type:

wzzip -a -en  -r -p -ybc foodmart.zip "FoodMart 2000"

Now I have an archive file named foodmart.zip which contains all of this catalog's files.

The name of the folder containing the AS files must be looked up in the registry using the win32 function RegQueryValueEx() after connecting to the server which contains the database using RegConnectRegistry(). They registry key is "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion\RootDir"

An argument string just like the above manual invocation is constructed and then is executed using the WshShell Run() method. The return code indicates if the zip operation was successful.

Metadata archival

The real challenge of creating a cube backup is not in zipping the files but in archiving and restoring the AS metadata from and back into the repository. Without this metadata, AS doesn't know about the existence of the database. The VB class CubeZipArchive contains the code related to the metadata archival and restore operations.

It operates by extracting the metadata from the OLAP repository and using the FileSystemObject to save it to a file named "CubeMetaData.REP" in the root data directory where it will get zipped along with the data files. In the code this is accomplished in PreArchiveCube(), which calls ExtractMetaData() and RecursiveExtract() to recursively traverse the OLAP structures of a catalog and create a string representation of this metadata. This string is then saved to the REP file.

Archive and Restore

The archive and restore operations are, not surprisingly, programmatically the inverses of each other. The CubeZipDialog form is used for both - the Archive Boolean property specified if the form is in Archive mode (value = True) or Restore mode (value = False).

An instance of the CubeZipArchive class is created by the CubeZipDialog form. The private methods doDatabaseArchive() and doDatabaseRestore() in CubeZipDialog have the code which does both the zip and the metadata tasks for archival and restoration respectively.

Registering the add-in

Creating an Analysis Manager add-in is pretty straigh forward and is discussed in the following MSDN library article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/praddinref_9qum.asp

The download includes some .reg and .bat files. The file install.bat contains the lines

regsvr32 /s CubeZipArchiveAddIn.dll
regedit /s install.reg

which registered the dll and runs the registry script install.reg to register the add-in with Analysis Manager. If the add-in is successfully registered, you will see a new entry in the right-click menu for catalogs and servers. The script unistall.bat does the inverse by unregistering the dll and running uninstall.reg to remove the add-in.

Future Work

I have downloaded the gzip library (see http://www.info-zip.org/FAQ.html) and tested that it can perform the compression tasks for this application. When I have time I will change the code to use this library instead of doing WinZip automation.

downloads: CubeZipArchiveAddIn.zip (44K)