Archiving data and storing metadata for historical reference are important to maintain an effective data warehouse. In this lesson, you will learn how to create backups and archives of your OLAP Services databases and how to migrate the OLAP Services repository from a Jet database (the default) to a SQL Server database.
After this lesson, you will be able to:
- Maintain data and metadata by archiving and restoring OLAP Services databases
- Migrate the OLAP Services Repository to SQL Server
Estimated lesson time: 60 minutes
Deciding what, when, and how to archive data depends on your business environment. It affects available disk space as well as the data loading process and query performance.
The process of archiving data encompasses the data in the database and the scripts that create it. Archiving or backing up OLAP Services databases is not always necessary, because you can rebuild the OLAP Services databases from the source databases. You should archive data and scripts when you want to
Archiving data is a logical part of backing up a database. When determining what data to archive, consider archiving the following types of data tables:
When you archive data, you should consider:
You can archive and restore OLAP Services databases by using OLAP Manager or the msmdarch command.
Using the Archive and Restore Database Add-in for OLAP Manager archives each database to a single .CAB file, which you can reserve for restoration or migrate to other server computers. This file is called an archive file. The archive file contains the contents of the Windows NT folder that is given the same name as the database.
For example, the sample FoodMart database is stored in the \Program Files\OLAP Services\Data\FoodMart folder. The folder contains all the files for the FoodMart OLAP Services database. All these files are stored in the archive file when you use the Archive and Restore Add-in to create an archive of the FoodMart database.
The contents of the archive file vary according to the storage types of the cubes and partitions in the database.
NOTE
The Archive and Restore Add-in does not archive write-back tables, source data, or aggregations for relational OLAP (ROLAP) cubes and partitions. You should back up this data using your standard relational database backup procedures.
Obtaining the Archive and Restore Add-in
The Archive and Restore Add-in is not part of OLAP Manager. You need to install it after installing OLAP Services. To download the latest version of the add-in from the Microsoft SQL Server OLAP Services Web site, start the OLAP Manager, and click OLAP Services on the Web on the Getting Started tab of the HTML (that is, the right) pane. You need to have a valid connection to the Internet before you do this. A copy of the addins.exe file appears in the \Software\Addins folder on the companion CD-ROM if you are not able to download it from OLAP Services on the Web.
After downloading the addins.exe file, execute it to install the add-in. The Archive Database option will now be available when you right-click a database in OLAP Manager, and the msmdarch command-line utility will be installed in the OLAP Services folder.
You also can archive and restore OLAP Services databases by executing the msmdarch command. The msmdarch command is a command-prompt version of the Archive and Restore Add-in that you can call from batch files.
["command-path]msmdarch["] /a server "data-path"
"database-name" "archive-path-and-file-name" ["log-path-and-file-name"]
Example
This example archives the sample FoodMart database included in OLAP Services by using the msmdarch command:
"C:\Program Files\OLAP Services\Bin\msmdarch" /a myserver "C:\Program Files\OLAP Services\Data\" "FoodMart" "C:\My Archives\FoodMart.cab" |
["command-path]msmdarch["] /r server "data-path"
"archive-path-and-file-name" ["log-path-and-file-name"]
Example
This example restores the sample FoodMart database included in OLAP Services by using the msmdarch command:
"C:\Program Files\OLAP Services\Bin\msmdarch" /r myserver "C:\Program Files\OLAP Services\Data\" "C:\My Archives\FoodMart.cab" |
In this exercise, you will back up the Northwind_DSS database and then restore it from the archive by using the Archive and Restore Database Add-in.
In this procedure, you will archive the Northwind_DSS database by using the Archive and Restore Database Add-in.
In this procedure, you will restore the Northwind_DSS database by using the Archive and Restore Database Add-in.
You should see the restored Northwind_DSS OLAP database.
OLAP Services creates a repository for your OLAP Server. OLAP Services stores metadata for multidimensional objects such as cubes, dimensions, and so on in this repository. This repository, called the OLAP Services Repository, is initially created as a Jet (.MDB) database on the server computer where OLAP Services is installed. The default path and filename for the OLAP Services Repository database is C:\Program Files\OLAP Services\Bin\msmdrep.MDB.
TIP
The OLAP Services Repository is unique to OLAP Services and is not the same as the Microsoft Repository that is installed with SQL Server. The Microsoft Repository can be used by DTS to store data lineage for data that you transfer into your data warehouse but is not used by OLAP Services to store cube metadata.
You should migrate the OLAP Services Repository from the default Jet database to a SQL Server database because migrating the repository
Use the Migrate Repository Wizard in OLAP Manager to migrate the repository to a SQL Server database on the same or another server computer. Later, you can use the wizard to migrate the repository to another SQL Server database, but you cannot migrate the repository back to a Jet database.
Before you start the Migrate Repository wizard, you should create or identify the SQL Server database to which you want to migrate the OLAP Services Repository. After migration, the old Jet database file remains, but OLAP Services uses only the new database. You can delete the old database manually.
In this exercise, you will create a database in SQL Server for the OLAP Services Repository database.
In this procedure, use the information in the following table to create the OLAP_Repository database.
File | Space Allocated | File Growth | Maximum File Size |
---|---|---|---|
Database | 2MB | 10% | 15MB |
Transaction log | 1MB | 10% | 8MB |
In this procedure, you will migrate the OLAP Services Repository from the Jet database to SQL Server by using the Migrate Repository wizard.
Parameter | Value |
---|---|
Server | Type your server name |
Authentication | Windows NT authentication |
Database | OLAP_Repository |
SELECT * FROM OLAP_Repository.dbo.olapobjects |
What object information is stored in the OLAP Services Repository?
Each OLAP Services database includes data and metadata that is not stored in SQL Server. To recover the structure of an OLAP Services database or to move an OLAP Services database, you need to archive and restore the database. Microsoft provides the Archive and Restore Add-in for OLAP Manager to make this a simple task.
OLAP Services stores the metadata about your cubes in the OLAP Services Repository. This repository is stored in a Jet database. You should migrate it to SQL Server for greater fault tolerance and manageability.