OLAP Q & A

In response to readers' OLAP questions, here's the first installment of the OLAP Q & A, which will appear occasionally in this column. If you have an OLAP question, email us at [email protected] and we'll try to cover your question in a future issue.

Q: I'm currently evaluating SQL Server 7.0, especially its OLAP features. I created a cube and want to transfer it (including the definitions) to another machine. Is there a transfer feature that I can use? If no transfer feature is available, how can I back up the cube?

There's bad news and good news about cube transfers. The bad news is there's no good way to make a copy of a cube on another server. The good news is that a solution is almost ready and might even be released by the time you read this column. Microsoft is providing an add-in to the OLAP Manager Microsoft Management Console (MMC) to handle this problem.

Unfortunately, we have no good news about cube backup. There's no good way to back up an entire cube in one simple operation from the OLAP Manager. To understand the backup options and problems that exist with cubes, you need to learn the basics about how OLAP storage models work. (We'll discuss OLAP storage in detail in a future article.) Multidimensional OLAP (MOLAP) stores cube data in files with data, index, map, and .prt extensions. Relational OLAP (ROLAP) stores cube data in a standard relational database similar to the SQL Server or Oracle database. Hybrid OLAP (HOLAP) is a hybrid of MOLAP and ROLAP that stores cube data in both file types. The problem is that the current release of OLAP Services doesn't provide any integrated backup facilities, which you expect from relational databases. You must use standard file backup techniques to back up MOLAP databases. Because SQL Server stores ROLAP data in a relational database, you can use RDBMS standard backup utilities, but there is no clean way to use the OLAP Manager to integrate and manage the backups. This problem is especially difficult if you store different partitions of a cube in different relational databases. HOLAP doesn't make backup issues easier because SQL Server stores data in both MOLAP and ROLAP structures.

(Note: As we were writing this article, Microsoft released beta add-ins for OLAP Services that let you back up and restore an OLAP cube. But this add-in doesn't deal with the issue of integrated backup when data exists in both relational and MOLAP storage.)

The backup situation isn't as apocalyptic as it might seem. Robust, easy-to-use backup facilities are absolute requirements for OLTP systems. But the read-only nature of OLAP systems makes them less reliant on sophisticated backup capabilities. We're not saying your life hasn't been made more difficult by Microsoft's lack of decent OLAP backup utilities; we're just saying you can probably manage to get by for now.