Lesson 4: Maintaining Data and Metadata

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

Archiving Enterprise Data

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.

Why Archive Data

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

  • Preserve raw, unfiltered, and unchanged source data
  • Separate and consolidate historical business data from the application package
  • Analyze data changes in the business environment over time

What Data to Archive

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:

  • Transaction tables that record events that never change for example, an invoice history table
  • Slowly changing dimension tables for example, product or customer tables
  • Modifiable tables that record events that change for example, prospective customers or unpaid orders tables
  • Tables having a fixed time period condition for example, a sales price table

How to Archive Data

When you archive data, you should consider:

  • Updating archives at expected, scheduled intervals
  • Creating an intermediate data staging area that contains an audit or archive database that data warehouse users can query to access raw business data and view versions of historical data
  • Archiving data during the data loading process rather than when the data is out of date and it is time to move it out of the data warehouse
  • Using Snapshot replication to save a copy of the data or using Transactional replication to mirror or copy the information to multiple data staging areas

Archiving OLAP Data

You can archive and restore OLAP Services databases by using OLAP Manager or the msmdarch command.

Archive and Restore Add-in

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.

msmdarch Command

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.

Archive Syntax

["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" 

Restore Syntax

["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" 

Exercise 1: Archiving OLAP Databases

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.

  • To archive the Northwind_DSS database
  • In this procedure, you will archive the Northwind_DSS database by using the Archive and Restore Database Add-in.

    1. Switch to Windows NT Explorer and create a folder named C:\OLAP_Backup.
    2. Switch to OLAP Manager, expand the OLAP Servers folder, and then expand your server.
    3. Right-click the Northwind_DSS database, and then click Archive Database.
    4. Accept the default filename and specify the file location as C:\OLAP_Backup.
    5. Click Archive.
    6. After the archive is successful, click Save Log, and then click Save to accept the default file name and file location.
    7. Click Close to close the Archive Database Progress dialog box.
    8. In Windows NT Explorer, go to the C:\OLAP_Backup folder. The archive file named Northwind_DSS.CAB and the log file named Archive_mmddyyyy.LOG are in the folder.
    9. Use Notepad to open and view the log file. The names of all the files from the Northwind_DSS database are listed in the log.

  • To restore the Northwind_DSS database
  • In this procedure, you will restore the Northwind_DSS database by using the Archive and Restore Database Add-in.

    1. In OLAP Manager, right-click the Northwind_DSS database, and then click Delete. You are asked if you are sure that you want to delete the database. Click Yes.
    2. In the console tree, right-click your server, and then click Restore Database.
    3. In the Open Archive File dialog box, select the Northwind_DSS.CAB file located in C:\OLAP_Backup, and then click Open.
    4. In the Restore Database dialog box, click Restore.
    5. After the restore is successful, click Save Log, and then click Save to accept the default filename and file location.
    6. Close and then restart OLAP Manager.
    7. Expand OLAP Servers and then expand your server.
    8. You should see the restored Northwind_DSS OLAP database.

    Migrating the OLAP Services Repository

    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

    • Brings the OLAP Services Repository database into a consistent environment in which you can manage with the same backup and restore methods that you use in SQL Server
    • Reduces the number of management tasks
    • Takes advantage of the fault tolerance in SQL Server, which makes it a reliable environment for storing repository data

    Using the Migrate Repository Wizard

    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.

    Exercise 2: Migrating OLAP Services Repository

    In this exercise, you will create a database in SQL Server for the OLAP Services Repository database.

  • To create the SQL Server OLAP Services Repository database
  • In this procedure, use the information in the following table to create the OLAP_Repository database.

    FileSpace AllocatedFile GrowthMaximum File Size
    Database2MB10%15MB
    Transaction log1MB10%8MB

    1. In the console tree in SQL Server Enterprise Manager, expand your server group, then expand your server.
    2. Right-click Databases, then click New Database.
    3. Enter the name OLAP_Repository for the new database.
    4. Use the values from the table at the beginning of the procedure to change the properties of the database. You will need to use the General tab to change database file properties and the Transaction Log tab to change the transaction log file properties.
    5. Click OK to create the new database.
    6. Expand the Databases folder, right-click the OLAP_Repository database, then click Properties and verify the properties of your new database.

  • To migrate the OLAP Services Repository from the Jet database to the OLAP_Repository database
  • In this procedure, you will migrate the OLAP Services Repository from the Jet database to SQL Server by using the Migrate Repository wizard.

    1. Open OLAP Manager, expand OLAP servers, and then expand your server.
    2. Right-click your server, and then click Migrate Repository.
    3. Use the information in the following table to migrate the OLAP Services Repository.
    4. ParameterValue
      ServerType your server name
      AuthenticationWindows NT authentication
      DatabaseOLAP_Repository

    5. Restart OLAP Manager.
    6. Open Windows NT Explorer, and delete the msmdrep.MDB (Jet database) file from C:\Program Files\OLAP Services\bin.
    7. Open SQL Server Query Analyzer.
    8. Type and execute the following statement to view the repository data:
    9. SELECT * FROM OLAP_Repository.dbo.olapobjects

      What object information is stored in the OLAP Services Repository?

    Lesson Summary

    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.



    Microsoft Corporation - Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
    Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
    ISBN: 0735606706
    EAN: 2147483647
    Year: 1999
    Pages: 114

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net