|SAS/MDDB Server Administrator's Guide|
There are four ways that you can build an MDDB. You can use
Note: Regardless of the method
that you choose, the information that you specify when creating an MDDB is
similar to that specified when using the SUMMARY procedure. The NWAY cube
correlates to the NWAY data produced by the SUMMARY procedure, and subcube
data correlates to _TYPE_ records produced by the SUMMARY procedure. If you
are already familiar with the SUMMARY procedure, keeping these similarities
in mind could help you understand how to create MDDBs.
Once an MDDB has been created, you can copy or transport it to any platform that supports the MDDB object type. For information on copying or transporting MDDBs, refer to Transporting MDDBs Across Operating Environments.
|Building an MDDB with the MDDB Procedure|
This section provides the syntax for the MDDB procedure and explains how to use the procedure to create an MDDB. An example PROC MDDB statement is also provided.
PROC MDDB <option(s)>;
|PROC MDDB <option(s)>;|
You can use the following options in the PROC MDDB statement:
You can specify read, write, and alter passwords using the same syntax as for data sets. For example, each of the following is valid:
out=libname.memname (pw = "password" read = "read_password" write = "write_password" alter = "alter_password")
You can also specify the PWREQ= option to control whether a password requestor window appears when a required password is either missing or incorrect. By default, the password requestor window does not appear when creating an MDDB but does appear when reading an MDDB. To use the PWREQ= option, specify
pwreq = 'NO' | 'YES'
For example, you can specify the following code to ensure that a password requestor window appears when the required password is missing or incorrect:
libname.memname (pw = "password" pwreq = 'YES' )
Use the following as guidelines when you specify passwords:
pw="password", that password is used for the read, write, and alter passwords.
|CLASS var1 var2 ... / <order-options>;|
Use the CLASS statement to specify variables from the base table that are to be used as the classification variables in the MDDB.
You can specify one or more CLASS statements. However, a given variable can only appear once in all CLASS statements. The class variable can be either numeric or character. If you do not specify a sort order, ASCENDING is used.
You can use the following options in the CLASS statement:
You can also specify a different sort order for each CLASS variable. To do this, use a separate CLASS statement for each variable to be sorted.
|HIERARCHY class-var1 class-var2 ... / <NAME=name | "name" DISPLAY=YES | NO | NODATA> <TOTALS=YES | NO>;|
You can define one or more subcubes to be stored in your MDDB by using a HIERARCHY statement. If you do not specify a hierarchy, only the NWAY cube hierarchy is stored in the MDDB. You can specify multiple CLASS variables; however, you can specify a CLASS variable only once in each HIERARCHY statement.
You can use the following options in the HIERARCHY statement:
If you do not specify a name for your hierarchy, the default name HIER n is used, where n is a number (beginning with 1).
The following examples illustrate how to specify a subcube using the HIERARCHY statement:
hierarchy country region division /name=geo display=YES;
hierarchy country region division /name="geographic hierarchy";
Note: If you specify two or more identical hierarchies, SAS/MDDB
Server stores only the first of the identical hierarchies and issues a warning
that the duplicate hierarchies are not stored.
|VAR var1 var2 ... / <stat-options>;|
The VAR statement enables you to specify variables from the base table to be used as the analysis variables in the MDDB.
You can specify one or more VAR statements. However, a given variable can only appear once in all VAR statements. The variables must be numeric. If you do not specify a statistic, SUM is used.
You can use the following options in the VAR statement:
If you specify WEIGHT=, its value must be the name of a numeric variable in the data set. If you also specify SUMWGT, the weighted sum will be stored in the MDDB. If you specify only WEIGHT=, the weight will be used in calculating the SUM statistic, but the weighted sum will not be stored, and the other statistics that would be calculated based on the weighted sum will not be calculated (that is, they will have missing values).
If you specify SUMWGT but do not specify WEIGHT=, then the request to store SUMWGT will be ignored.
|ADDHIER class-var1 class-var2 ... / <NAME=name|"name" DISPLAY=YES|NO|NODATA> <TOTALS=YES|NO>;|
ADDHIER statement enables you to update an MDDB by adding an hierarchy. The
syntax and requirements for the ADDHIER statement are exactly the same as
those for the HIERARCHY statement. The ADDHIER statement is valid only when
updating an MDDB.You can have zero or more ADDHIER statements. See Updating an MDDB Using the MDDB Procedure for details on the techniques
for updating MDDBs.
|REMOVEHIER class-var1 class-var2 ... / <NAME=name|"name">;|
The REMOVEHIER statement enables you to update an MDDB by removing an hierarchy. The syntax and requirements for the REMOVEHIER statement are exactly the same as those for the HIERARCHY statement, except that there are no DISPLAY= or TOTALS= options on the REMOVEHIER statement. The REMOVEHIER statement is valid only when updating an MDDB. You can have zero or more REMOVEHIER statements.
If you specify the NAME option on the REMOVEHIER statement, only the hierarchy matching that name will be removed. Otherwise, all hierarchies containing only the specified classifiers will be removed.
See Updating an MDDB Using the MDDB Procedure
for details on the techniques for updating MDDBs.
This example shows you how to use the MDDB procedure to build an MDDB from the source table SASHELP.PRDSALE. The SASHELP.PRDSALE table contains the classification columns COUNTRY, REGION, DIVISION, PRODTYPE, PRODUCT, QUARTER, YEAR, and MONTH. The analysis variables are ACTUAL and PREDICT. Based on logical assumptions about how users would want to drill down through the data, you can write a PROC MDDB statement to create the correct MDDB with multiple subcubes that will meet anticipated user requests.
proc mddb data=sashelp.prdsale out=sasuser.mddb label='MDDB from SASHELP.PRDSALE'; class product prodtype year quarter month country region division; hierarchy country region division /name="Geographic Hierarchy"; hierarchy product year /name="Product-Time Hierarchy"; hierarchy year quarter month; var predict /sum; var actual /n nmiss sum uss min max; run;The resulting MDDB is called SASUSER.MDDB. The NWAY cube contains each of the classification variables. One analysis variable, PREDICT, has the statistic SUM; the other analysis variable, ACTUAL, has the statistics N, NMISS, SUM, USS, MIN, and MAX. The HIERARCHY statements create subcubes that optimize drill-down performance. No matter where a user is in any of the drill hierarchies, there is a subcube with related aggregations.
|Building an MDDB with SAS/EIS Software|
This section provides instructions on how to use SAS/EIS software to build an MDDB. You supply information about the MDDB in a series of SAS/EIS windows. When you build an MDDB using SAS/EIS software, the MDDB will be registered automatically in the SAS/EIS metabase facility.
To build an MDDB with SAS/EIS software, you must first register the detail data in a SAS/EIS repository. The types of SAS/EIS reports that are produced from the MDDB will help you determine how to register the detail data. Your registration should contain columns defined with the CATEGORY and ANALYSIS attributes and can contain the HIERARCH table attribute. See the SAS/EIS software online Help for more details on the data requirements for specific reports.
Once you have determined the data requirements, register the detail data in a SAS/EIS repository. Then follow the steps below to build the MDDB.
Note: Re-executing the MDDB application, by editing
the MDDB and selecting [Create] or [Test] from
the Build EIS window, or by using the RUNEIS APPL=eis-app-name
command, will cause the MDDB to be re-created, overwriting any previous changes.
|Building an MDDB with the SAS/MDDB Server Classes|
SAS/MDDB Server includes two classes that you can use to create MDDBs:
Two additional classes, MDDB_H and MDDB_M, are provided to help you work with
MDDBs. The MDDB_H class reads an existing MDDB and returns header information.
The MDDB_M class reads and returns data from the MDDB. For details on these
classes, see the SAS/MDDB Server online Help.
This section summarizes the functionality of the two
classes that allow you to create MDDBs. For complete documentation of the
classes, refer to the SAS/MDDB Server
The MDDB class reads and summarizes a SAS table and stores the minimum sufficient set of summarized data in an MDDB library member. The methods specific to the MDDB class are
The MDDB_C class enables you to create an MDDB from any data source. You can use this class to create an MDDB that is
The SCL code in this section shows how you could build the same MDDB as in Building an MDDB with the MDDB Procedure using the MDDB class.
/*-- load the MDDB class to create the MDDB entry from data set--*/ /*-- using the CLASS instead of the PROC --*/ dcl object dataid=_new_ sasshelp.mddb.mddb(); init: /*-- create classification variables list --*/ classlist=makelist(); rc=setnitemc(classlist, 'ASCENDING', 'PRODUCT'); rc=setnitemc(classlist, 'ASCENDING', 'PRODTYPE'); rc=setnitemc(classlist, 'ASCENDING', 'YEAR'); rc=setnitemc(classlist, 'ASCENDING', 'QUARTER'); rc=setnitemc(classlist, 'ASCENDING', 'MONTH'); rc=setnitemc(classlist, 'ASCENDING', 'COUNTRY'); rc=setnitemc(classlist, 'ASCENDING', 'REGION'); rc=setnitemc(classlist, 'ASCENDING', 'DIVISION'); /*-- create hierarchies/subcubes--*/ hlist=makelist(); h2list=makelist(); rc=insertc(h2list, 'COUNTRY', -1); rc=insertc(h2list, 'REGION', -1); rc=insertc(h2list, 'DIVISION', -1); rc=setniteml(hlist, h2list, 'GEOGRAPHIC HIERARCHY'); /*-- create hierarchies/subcubes --*/ h2list=makelist(); rc=insertc(h2list, 'PRODUCT', -1); rc=insertc(h2list, 'YEAR', -1); rc=setniteml(hlist, h2list, 'PRODUCT TIME HIERARCHY'); /*-- create hierarchies/subcubes --*/ h2list=makelist(); rc=insertc(h2list, 'YEAR', -1); rc=insertc(h2list, 'QUARTER', -1); rc=insertc(h2list, 'MONTH', -1); rc=insertl(hlist, h2list, -1); /*-- setup analysis and applicable stats --*/ alist=makelist(); a2list=makelist(); rc=insertc(a2list, 'SUM',-1); a3list=makelist(); rc=insertc(a3list, 'N',-1); rc=insertc(a3list, 'NMISS',-1); rc=insertc(a3list, 'SUM',-1); rc=insertc(a3list, 'NMISS',-1); rc=insertc(a3list, 'USS',-1); rc=insertc(a3list, 'MIN',-1); rc=insertc(a3list, 'MAX',-1); rc=setniteml(alist, a2list, 'PREDICT'); rc=insertl(alist, a3list, -1, 'ACTUAL'); put 'Creating mddb: sasuser.mddb'; dataid._summary('SASHELP.PRDSALE', /*-- dataset name --*/ 'SASUSER.MDDB', /*-- mddb name --*/ classlist, /*-- list of --*/ /*-- classification vars--*/ hlist, /*-- hierarchies list --*/ alist, "MDDB from SASHELP.PRDSALE"); rc=rc; return;
|Building an MDDB with SAS/Warehouse Administrator Software|
SAS/Warehouse Administrator software provides a graphical user interface that enables you to specify the classification variables, analysis variables, and summary levels (hierarchies) for an MDDB. Additionally, you specify a location for storing the MDDB and other information specific to features of SAS/Warehouse Administrator software. For details about how to create an MDDB using SAS/Warehouse Administrator software, refer to the SAS/Warehouse Administrator User's Guide.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.