|
| |
Oct 1998 SAS Institute Inc.
MDP Metadata Batch Utility (experimental)
The Multidmensional Data Provider Metadata Batch Utility provides
a description of a format for MDP Metadata as flat text and a facility
to convert stored MDP Metadata into this format and vice versa.
Use the MDP Metadata Batch Utility to
 | provide MDP Metadata on platforms that do not offer the MDMDDB windows. |
| programmatically update MDP Metadata without the need to use the MDMDDB windowing interface. |
| merge MDP Metadata from different repositories |
| conveniently transport MDP Metadata between platforms |
Syntax
AF C=SASHELP.HOLAPMD.HBATCH.SCL REP=repository FILE='file' MODE=READTEXT/WRITETEXT [RPATH='repository path' PARSE=Y/N CHECK=Y/N];
Where
REP= |
Required |
Name of Repository for MDP Metadata storage. Note: repository names are case sensitive. |
FILE= |
Required |
Quoted name of file for MDP Metadata text storage. |
MODE= |
Required |
READTEXT - convert text file into Metadata
WRITETEXT - convert Metadata into text file |
RPATH= |
Optional |
Repository path |
PARSE= |
Optional |
Y (default) - on READ, perform syntax check
N - perform no syntax check of flat file |
CHECK= |
Optional |
Y (default) - on READ, perform check on Metadata integrity
N - perform no Metadata integrity check |
MDP Metadata Flat File Format:
The flat file format allows empty lines. Any text after an asterisk '*' in a line is ignored.
Each line holds one keyword and, for certain keywords, one additional value. Definitions for
Individual items (e.g. Servers, Data Groups, Data Sources, etc.) are grouped and with a label
in the form LABEL_ and end with a label in the form _LABEL.
Label |
Attribute |
Values (defaults bold) |
Required? |
SERVER_ |
name |
Name of the remote session id (max 8 chars, valid SAS name).
Needs to be unique server name in repository. |
YES |
| |
COMAMID |
TCP, APPC, CPIC, DECNET, EHLLAPI, NETBIOS, SPX |
YES |
| |
SCRIPT |
Physical name of SAS/CONNECT script file |
|
| |
ADDRESS |
Server address (depends on COMAMID) |
|
| |
TYPE |
B = Both R and S
R = RLS
S = Remote Submit |
|
| |
STATUS |
Y = show download status window
N = don't show download status window |
|
| |
SIGNON |
S = signon at data group setup
I = signon at first query that requires server data |
|
| |
SIGNOFF |
C = at data group termination
I = immediately after data request
N = do not signoff |
|
| |
POSTSON |
Name of SOURCE entry with code to submit locally immediately after signon. |
|
| |
PRESOFF |
Name of SOURCE entry with code to submit locally immediately before signoff. |
|
| |
MDDBSERV |
Y = SAS/MDDB Server is licensed on server
N = SAS/MDDB Server is not available |
|
| |
WEIGHT |
1, any integer. Indicates the server’s priority. The highest value takes precedence. |
|
| |
DESC |
200 char free text. Defaults to server name |
|
| |
_PWW_ |
Write password for metadata |
|
| |
_PWR_ |
Read password for metadata |
|
_SERVER |
|
|
|
HGROUP_ |
name |
Name of Data Group (32 chars max, valid SAS Name, unique across repository).
The name must be unique across the repository. |
YES |
| |
DESC |
200 char free text. Defaults to Data Group name. |
|
| |
DSOURCE_
_DSOURCE |
One or many lists of physical data sources that make up the Data Group. |
At least one |
| |
_PWW_ |
|
|
| |
_PWR_ |
|
|
| |
EIS_
_EIS |
Indicates how to register Data Group as SAS/EIS Metabase registration. |
Maximum one per Data Group |
_HGROUP |
|
|
|
DSOURCE_ |
Lib.mem |
The SAS name of the data source. (LIB.MEM). Data Source name must be unique across the Data Group. |
YES |
| |
DESCRIPTION |
200 char free text. Defaults to Data Source name. |
|
| |
TYPE |
The MEMTYPE of the data source: DATA, MDDB, or VIEW |
YES |
| |
PASSWORD |
The read password for the data source. A valid SAS name up to 8 characters
or the special value, _HOLAP_PROMPT_. |
|
| |
SERVER |
The name of the server on which the data source resides.
The server definition needs to reside in the same repository. |
|
| |
DBMS |
Y = Data Source is a SAS/ACCESS Data Source (not applicable for MDDBs)
N = Data Source is not SAS/ACCESS Data Source |
|
| |
VAR_
_VAR |
One or many lists describing the Data Sources measures (analysis variables and statistics) |
At least one |
| |
SUBT_
_SUBT |
One or many lists describing the subtables residing in the data source |
At least one |
| |
STAR_
_STAR |
One or many lists describing any subtables that are stored as Star Schemas. |
|
_DSOURCE |
|
|
|
VAR_ |
name |
Name of the original column. Must be a unique across the Data Source. |
YES |
| |
Name of column |
Statistic keyword (NMISS, N, SUM, MIN, MAX, USS)
For unsummarized tables, the name of the column and the
name of the original column must be identical.
Only one column/stat pair possible.
For Summary tables, only one column/stat pair possible.
For MDDB, multiple column/stat pairs are possible. |
YES |
_VAR |
|
|
|
SUBT_ |
name |
SAS name. Must be unique across the Data Source |
YES |
| |
DESC |
200 char free text |
|
| |
HIERNAME |
Only for MDDBs: MDDB subtable name. Empty for non-MDDBs. |
YES |
| |
WEIGHT |
1, any integer. Subtable priority: low value means high priority. |
YES |
| |
WHERE_N |
Variable Name |
If one of these items is given, all must be present. |
| |
WHERE_T |
Type: C = Character, N = Numeric |
| |
WHERE_O |
Operator: EQ, NE, GT, LT, LE, GE |
| |
WHERE_V |
Value |
| |
CLASS_
_CLASS |
One or many lists describing the class variables representing the subtable |
At least one |
_SUBT |
|
|
|
CLASS_ |
name |
Name of class variable. Must be unique for subtable |
YES |
| |
Data value |
Quoted data value |
If a value is given, the same value needs to be present for all identicall CLASS variables in the Data Source. |
_CLASS |
|
|
|
EIS_ |
|
|
|
| |
MDDB |
SAS name of skeleton MDDB |
YES |
| |
REPOSITORY |
Name of repository for EIS registration |
YES |
| |
UPDATE |
REFRESH – merge new info into existing registration
REPLACE – overwrite existing registration |
YES |
_EIS |
|
|
|
STAR_ |
|
|
|
| |
FACT_
_FACT |
A list describing the Fact Table of the Star Schema definition. |
YES |
| |
DIMT_
_DIMT |
One or many lists describing the Dimension Tables of the Star Schema definition. |
At least one |
_STAR |
|
|
|
FACT_ |
Lib.mem |
SAS name of the Fact Table (LIB.MEM) |
YES |
| |
KEY_ |
SAS variable name. |
At least one |
_FACT |
|
|
|
DIMT_ |
Lib.mem |
SAS name of the Dimension Table (LIB.MEM). Name must be unique within Star Schema. |
YES |
| |
CLASS_ |
SAS variable name. |
At least one |
| |
KEY_ |
SAS variable name. Key needs to be present in the Fact Table definition. |
Exactly one |
_DIMT |
|
|
|
MDP Metadata Flat File Format Examples:
**** MDP Metadata File, generated on 01/01/99
**** Server Metadata
SERVER_ MYSERV
SCRIPT !sasroot\connect\saslink\tcpunix.scr
TYPE B
STATUS Y
SIGNON S
SIGNOFF C
ADDRESS 15.16.17.18
MDDBSERV Y
WEIGHT 1
DESC Server for both Remote Submit and RLS
COMAMID TCP
POSTSON mylib.mycat.logon.SOURCE
PRESOFF mylib.mycat.logoff.SOURCE
_PWR_
_PWW_
_SERVER **** REMBOTH
**** Group Metadata
HGROUP_ SAMPLE
DSOURCE_ MYDATA.SALESDS
DESCRIPTION Sample Data Set
TYPE DATA
PASSWORD
SERVER MYSERV
DBMS N
VAR_ ACTUAL
ACTUAL SUM
_VAR
VAR_ PREDICT
PREDICT SUM
_VAR
SUBT_ NWAY
DESC NWAY (mydata.salesds)
HIERNAME
WEIGHT 1
CLASS_ COUNTRY
_CLASS
CLASS_ DIVISION
_CLASS
CLASS_ MONTH
_CLASS
CLASS_ PRODTYPE
_CLASS
CLASS_ PRODUCT
_CLASS
CLASS_ QUARTER
_CLASS
CLASS_ REGION
_CLASS
CLASS_ YEAR
_CLASS
_SUBT **** NWAY
_DSOURCE **** MYDATA.SALESDS
DSOURCE_ MYDATA.SALESSDS
DESCRIPTION Sample Summary Data Set
TYPE DATA
PASSWORD
SERVER
DBMS N
VAR_ ACTUAL
ACTUAL SUM
_VAR
VAR_ PREDICT
PREDICT SUM
_VAR
SUBT_ TYPE1
DESC Type 1 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 1
CLASS_ MONTH
_CLASS
_SUBT **** TYPE1
SUBT_ TYPE2
DESC Type 2 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 2
CLASS_ QUARTER
_CLASS
_SUBT **** TYPE2
SUBT_ TYPE3
DESC Type 3 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 3
CLASS_ QUARTER
_CLASS
CLASS_ MONTH
_CLASS
_SUBT **** TYPE3
SUBT_ TYPE4
DESC Type 4 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 4
CLASS_ YEAR
_CLASS
_SUBT **** TYPE4
SUBT_ TYPE5
DESC Type 5 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 5
CLASS_ YEAR
_CLASS
CLASS_ MONTH
_CLASS
_SUBT **** TYPE5
SUBT_ TYPE6
DESC Type 6 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 6
CLASS_ YEAR
_CLASS
CLASS_ QUARTER
_CLASS
_SUBT **** TYPE6
SUBT_ TYPE7
DESC Type 7 (mydata.salessds)
HIERNAME
WEIGHT 1
WHERE_N _TYPE_
WHERE_T N
WHERE_O EQ
WHERE_V 7
CLASS_ YEAR
_CLASS
CLASS_ QUARTER
_CLASS
CLASS_ MONTH
_CLASS
_SUBT **** TYPE7
_DSOURCE **** MYDATA.SALESSDS
DSOURCE_ MYDATA.STAR
DESCRIPTION Sample Star Schema
TYPE VIEW
PASSWORD
SERVER
DBMS N
VAR_ ACTUAL
ACTUAL SUM
_VAR
VAR_ PREDICT
PREDICT SUM
_VAR
SUBT_ NWAY
DESC NWAY (mydata.star)
HIERNAME
WEIGHT 1
CLASS_ PRODTYPE
_CLASS
CLASS_ PRODUCT
_CLASS
_SUBT **** NWAY
STAR_
FACT_ MYDATA.FACT
KEY_ PRODUCT
_FACT **** MYDATA.FACT
DIMT_ MYDATA.DIM1
CLASS_ PRODTYPE
KEY_ PRODUCT
_DIMT **** MYDATA.DIM1
_STAR
_DSOURCE **** MYDATA.STAR
DSOURCE_ MYDATA.SALESMDDB
DESCRIPTION Sample MDDB
TYPE MDDB
PASSWORD
SERVER
DBMS N
VAR_ PREDICT
PREDICT SUM
_VAR
VAR_ ACTUAL
ACTUAL SUM
_VAR
SUBT_ GEOGRAPHIC
DESC GEOGRAPHIC (mydata.salesmddb)
HIERNAME GEOGRAPHIC
WEIGHT 1
CLASS_ COUNTRY
_CLASS
CLASS_ REGION
_CLASS
CLASS_ DIVISION
_CLASS
_SUBT **** GEOGRAPHIC
SUBT_ HIER4
DESC HIER4 (mydata.salesmddb)
HIERNAME HIER4
WEIGHT 1
CLASS_ COUNTRY
_CLASS
_SUBT **** HIER4
SUBT_ HIER5
DESC HIER5 (mydata.salesmddb)
HIERNAME HIER5
WEIGHT 1
CLASS_ COUNTRY
_CLASS
CLASS_ REGION
_CLASS
_SUBT **** HIER5
_DSOURCE **** MYDATA.SALESMDDB
EIS_
MDDB MYDATA.EISSKMDDB
REPOSITORY MYDATA
UPDATE REPLACE
_EIS
DESC Sample Data Group
_PWR_
_PWW_
_HGROUP **** SAMPLE
Usage Example:
Consider a Data Group where two levels of aggregation are stored on a server.
The top level is an MDDB with the higher aggregation level.
The second level holds one MDDB each with the data for one month aggregated by month.
Every month, a new second level MDDB is added, and the first level MDDB is recreated.
The new second level MDDB must be declared in the metadata.
To do this,
| write out the metadata using the MDP Batch tool |
| edit the text file to duplicate the definition of one of the second level datasources,
and change the name and where condition on the MONTH class variable accordingly. |
| convert the updated text file back into Metadata format using the MDP Batch tool |
Reference:
|
