SAS/MDDB Server Administrator's Guide

Updating an MDDB

It is not unusual to have gigabytes of source data that need to be summarized. Summarizing this volume of data can take hours or even days, depending on the type of CPU and other factors surrounding the data. Therefore, it is important to be able to update an MDDB with new data without having to completely rebuild the MDDB. SAS/MDDB Server software allows for incremental updates.

When you update an existing MDDB, remember that you cannot change the basic structure of the MDDB. You cannot add a classification variable, analysis variable, or statistic that was not in the original MDDB definition. If you want to perform any of these tasks, you will need to rebuild the entire MDDB from the original detail data. You can, however, performance-tune your MDDB by adding hierarchies (subcubes) that more closely match user data requests or delete hierarchies that are never requested.

This section discusses how to update an existing MDDB using the MDDB procedure, SAS/EIS software, and the MDDB class. Regardless of the method you use, an MDDB will be created containing the new data. During this process, the old MDDB must still exist, so make sure there is enough disk space before the update is attempted. When the process is complete, you must copy the new MDDB into a permanent library (if it is not already in one), delete the old MDDB, and then rename the new MDDB so that it has the same name as the old MDDB. If you do not give the new MDDB the name of the old MDDB and you are using the MDDB in SAS/EIS applications, the MDDB will not be recognized by your SAS/EIS metabase registrations.

Note: No explicit action is required to update an MDDB using SAS/Warehouse Administrator. When an MDDB is defined as part of the data flow of your data warehouse, it will be automatically updated by SAS/Warehouse Administrator software when the base table for the MDDB is updated. [cautionend]

Updating an MDDB Using the MDDB Procedure

To update an existing MDDB using the MDDB procedure, you must specify the existing MDDB using the IN= option, specify the table that contains the update data using the DATA= option, and specify the name of the updated MDDB using the OUT= option. Note that the values of the IN= and OUT= options must be different. You can also use the LABEL= option to specify a label for the updated MDDB and the ADDHIER and REMOVEHIER options to add and remove hierarchies from an MDDB. When you update an existing MDDB, the CLASS, VAR, and HIERARCHY statements are ignored.

Example 1: Building an MDDB Using the MDDB Procedure shows how to create an MDDB called SASUSER.MDDB. To update that MDDB, you will use a PROC MDDB statement to create a new MDDB that contains the update data. Data for the new MDDB will come from the existing MDDB and from an updated base table.

In the following statement, the DATA= option specifies the name of the updated base table, the IN= option specifies the existing MDDB, and the OUT= option specifies the name of the new MDDB. Note that the LABEL= option is also used.

proc mddb data=sasuser.sales in=sasuser.mddb
   out=sasuser.mddbnew label='Updated sales MDDB: 15 Nov 1998';
run;

After the procedure runs, you would use the DATASETS procedure to delete the old MDDB and rename the new MDDB so that it has the same name as the old MDDB:

proc datasets library=sasuser;
   delete mddb /mt=mddb;
run;
   change mddbnew=mddb;
run;
quit;

Updating an MDDB Using SAS/EIS Software

SAS/EIS software provides a graphical user interface that enables you to incrementally update an existing MDDB. The update data must exist in a SAS table that contains the variables in the MDDB. The format, informat, length, and type of the MDDB variables must also match those on the table. Any SAS table variables that are not in the MDDB are ignored during the update.

The Incremental MDDB Updates window in SAS/EIS software enables you to specify

a SAS name that is unique for the application type in the current application database
an optional description that appears in the application windows and in application selection lists
the name of the MDDB to be updated
the repository in which the MDDB registration is stored
whether the MDDB will have a read-protect password
the SAS table used to update the MDDB.

For detailed instructions on how to update an MDDB using SAS/EIS software, refer to the SAS/EIS software online Help.

Updating an MDDB Using the MDDB Class

The _updateMDDB method of the MDDB class updates an MDDB with the latest information specified in the table and in the original MDDB. For complete documentation of the _updateMDDB method, refer to the SAS/MDDB Server online Help.

Chapter Contents
Previous
Next
Top of Page