Previous Page

|

Next Page

SAS/Warehouse Administrator Metadata Types

Using Metadata Types

Relationships Among Metadata Types

This section describes the relationships among metadata types in SAS/Warehouse Administrator. By understanding these relationships, you can

• access metadata types by using their associated properties

• identify which metadata types can be created independently and which ones must be created in association with other types.

Metadata type relationships are presented in several diagrams, each diagram showing only a part of the total structure. These diagrams identify various ways to access a given type of metadata. The following notes apply to all diagrams:

• Each node in the diagram represents a type or supertype.

• Each line (connection) indicates that two types have a relationship between them. The text closest to each node indicates the name of the property that will return the corresponding node's general information.

• The type names that are contained in the nodes represent the highest supertype that can have this relationship. When you process a relationship, use the _IS_SUBTYPE_OF_ method to determine the current node type.

• Independent metadata types are represented as a rectangle. Dependent types are represented by a rectangle with rounded corners.

Independent and Dependent Metadata Objects

A metadata object is an instance of a metadata type--the metadata for an element in an application, such as a table or column.

An independent metadata object can be created by itself. For example, a WHPERSON object can be created independently of any other object.

A dependent metadata object cannot be created by itself. For example, a WHCOLUMN object cannot be created without first being associated with a WHTABLE object.

In the metadata type models in this section, independent metadata types are represented as a rectangle, and dependent types are represented by a rectangle with rounded corners.

General Metadata Type Model

The following figure shows how to access general information about any metadata object in SAS/Warehouse Administrator.

General Metadata Type Model

Host Metadata Type Model

The following figure shows how to access a common set of metadata for any host in SAS/Warehouse Administrator.

Host Metadata Type Model

Table Property Metadata Type Model

The following figure shows how to access property metadata for any table in SAS/Warehouse Administrator.

Table Property Metadata Type Model

Table Process Metadata Type Model

The following figure shows how to access process metadata for any table in SAS/Warehouse Administrator.

Table Process Metadata Type Model

Tables inherit the metadata that is shown in General Metadata Type Model, SAS Data Store Metadata Type Model, and DBMS Data Store Metadata Type Model.

Note:   There can be zero or more intermediate WHTBLPRC objects between two WHTABLE objects. Use the _IS_SUBTYPE_OF_ method to determine if the object that you are currently processing is WHTBLPRC.   

Note:   If there are no intermediate WHTBLPRC objects, the outputs from the OUTPUT TARGETS and OUTPUT OBJECTS properties are identical. The same is true for the INPUT SOURCES and INPUT OBJECTS properties.  

Note:   When you check the type of a table object, check for WHTBLPRC and not for WHTABLE. Because WHTBLPRC is a subtype of WHTABLE, this check would always come back true.   

Process Type Model

In SAS/Warehouse Administrator, load processes and similar jobs are defined through the Process Editor. Each process is defined by a metadata object. The following figure shows an example process flow.

Process Type Model

Note:   See the metadata types that are marked with an asterisk (*) in the previous figure. For those types, because the SOURCE CODE property points to an entry that is dynamically generated when requested, this relationship cannot be traversed in the WHDYNSRC to WHPRCMAN direction.  

Note that there can be zero or more WHTBLPRC type objects between two WHTABLE subtype objects. The previous figure shows one intermediate WHTBLPRC object.

This diagram shows the overall process flow, as well as any relationships that might be specific to the WHPRCMAN type objects. Note that for simplicity, the relationships have been drawn for only one of the WHPRCMAN type objects in the diagram, but these relationships exist for all WHPRCMAN type objects.

Physical Storage Metadata Type Models

Physical storage information is different for SAS data stores (type WHSASSTR) and DBMS data stores (type WHDBMSST). The following figure shows how to access a common set of metadata for a SAS data store.

SAS Data Store Metadata Type Model

The following figure shows how to access a common set of metadata for a DBMS data store.

DBMS Data Store Metadata Type Model

OLAP Metadata Type Model

The following figure shows how to access metadata that defines the structure of an OLAP table, Group, or MDDB in SAS/Warehouse Administrator including OLAP cubes, crossings, dimensions, hierarchies, and columns.

OLAP Metadata Type Model

Column Mapping Types: ODD to Detail Table Model

The following figure shows how to access the metadata that defines the column mappings between an operational data definition (ODD) and a detail table.

Mapping Model: ODD to Detail Table

The ODD type (WHODDTBL) and detail table type (WHDETAIL) inherit the metadata that is shown in Table Process Metadata Type Model.

Note:   If the logic that is needed to transform the operational data column into the detail data column is not important for your application, then you can use the output objects/input objects relationship. For details, see INPUT and OUTPUT Properties.  

Note:   There can be zero or more intermediate WHCTRNFM objects between two WHCOLUMN objects. Use the _IS_SUBTYPE_OF_ method to determine if the object that you are currently processing is a WHCOLUMN or a WHCTRNFM.   

Note:   If there are no intermediate WHCTRNFM objects, the outputs from the OUTPUT TARGETS and OUTPUT OBJECTS properties are identical. The same holds true for the INPUT SOURCES and INPUT OBJECTS properties.  

Writing Metadata

You can read all of the metadata types that are defined for SAS/Warehouse Administrator, but you cannot write them all. You can pass only certain types to the metadata API write methods. Not all write methods are valid for those types that can be written.

You can write metadata for many objects that can be displayed in the Explorer frame. You can also write metadata for host definitions and other entities that are shared among warehouses within an environment.

The documentation for each type in the metadata type dictionary identifies the write methods that are valid for each metadata type.

Writing Explorer Objects

Objects that are displayed in the SAS/Warehouse Administrator Explorer frame, such as warehouses, subjects, and tables, are members of groups. When you add the metadata for an Explorer object, you must identify the group to which it belongs. This is done by passing the metadata identifier of the target group along with the other parameters for the object.

The metadata types for Explorer objects have a GROUP property that lists the metadata identifiers of the groups in which to add a new object. The following figure lists the groups and the metadata types that are valid in each group.

Hierarchy of Groups and Members in SAS/Warehouse Administrator Explorer

Note:   Although the GROUP property takes a list of GROUP identifiers, the object is currently added only to the first GROUP that is specified in the list. For example, when adding a WHDETAIL type object (a detail table), the metadata identifier that is specified in the GROUP property list item must be of the type WHLDETL.  

Overview of the Process Editor

This section gives a brief overview of the Process Editor so that you can better understand how the process metadata types relate to the user interface. For details about the Process Editor window, display the Process Editor, then select Help  Using This Window from the menu bar.

If you open the Process Editor from the Explorer by selecting Tools  Process Editor from the menu bar, Job Hierarchy is the default view in the left panel, as shown in the following display.

Process Editor: Job Hierarchy and Process View

In the left panel, the Job Hierarchy displays all of the jobs that are defined in the current Warehouse environment. In the preceding figure, only two jobs are defined: Customer Detail Job and Customer ODD Job. The Customer Detail Job (item in the left panel with the rectangle around it) is the active job.

In the right panel, the Process View shows the process flow that is associated with the active job (Customer Detail Job). A process flow is a user-defined diagram in the Process View of the Process Editor. It is composed of symbols, with connecting arrows and descriptive text, that illustrate the sequence of each process that is associated with the job that is selected in the Job Hierarchy of the Process Editor. The process flow illustrates how the data moves from input source(s) to output table(s) and what extractions and transformations occur in between.

Note:   A job only creates the output table(s) that are listed under its icon in the left panel of the Process Editor. The other loadable tables in the process flow are inputs to the job.   

For example, in the preceding display, the Customer Detail Job only creates the Customer Detail table. It does not create Customer ODD. Customer ODD is created by a separate job--Customer ODD Job. Customer ODD is an input to the Customer Detail Job.

In the previous display, note that one event has been defined for the Toy Store Whouse. An event is a metadata record that specifies a condition for controlling a job, such as checking for certain return codes or verifying the existence of a file. To use events, you must create them, include them in a job flow, and then write a metadata API program that reads the job flow and generates code for it.

Job flows are displayed in the Job View of the Process Editor. In order to switch from the Process View to the Job View in the right panel of the Process Editor, click the right mouse button in the background and select Job View from the pop-up menu. The right panel in the following display illustrates a job flow that has been defined for the Customer Detail Job.

Process Editor: Job Hierarchy and Job View

Reading Process Flow Metadata

In SAS/Warehouse Administrator, a process is a metadata record that is used to generate or retrieve a routine that creates warehouse data stores, or one that extracts data, transforms data, or loads data into data stores. You can link these tables together to form a process flow. The Process Editor in SAS/Warehouse Administrator is used to create process flows such as the one shown in the following figure.

Process Flow in SAS/Warehouse Administrator Process Editor

In the previous figure, information moves from the bottom up--from the ODD named ODD 1, to a mapping step, to the Credit data table.

The icons shown in the figure--ODD 1 and Credit data table--represent loadable tables. A loadable table can be a source, such as ODD 1 in the figure; a target, such as Credit data table; or both a target and a source.

The Mapping box that is shown in the figure represents an intermediate output table--the output of a process step between sources and targets.

Note:   Process flow diagrams do not depict process objects. These diagrams show how data moves from one loadable table (icon), through an intermediate output table (box), to a target loadable table (icon).  

In addition to the process metadata, the process flow metadata has information about how the tables are related to the job. The following figure shows the properties that relate jobs to tables.

Process Flow Metadata: Jobs

Loadable Tables and WHTABLE Subtypes

Each loadable table has metadata of subtype WHTABLE. For a list of WHTABLE subtypes, see the diagram on the foldout in Appendix 2.

WHTABLE subtypes give you information about where the output data of the step resides and any other metadata about the object that has been gathered using the Properties frames, such as data host, data library, table name, and columns.

For each WHTABLE subtype, you can retrieve the corresponding process metadata (WHPROCES) by using the PROCESS property. Any step for which no process information exists will return an empty list for the PROCESS property. The RESPONSIBILITY property will indicate whether a process has been defined for this table, and if so, who is responsible for generating the code.

Intermediate Output Tables and WHTBLPRC Subtypes

Each intermediate output table has metadata of subtype WHTBLPRC. For a list of WHTBLPRC subtypes, see Metadata Type Inheritance.

All WHTBLPRC subtypes have a property, CREATES DATA, that indicates whether the table has output data or is a placeholder only. If CREATES DATA =0, then the table is a placeholder only. (The This process has no output data selection has been made on the process properties Output Data tab.) An analogy would be a DATA step that performs processing but is coded with DATA _NULL_.

Using the _IS_SUBTYPE_OF_ method of the API, you can determine if the currently returned table from the INPUT SOURCE property is an intermediate table or an actual loadable table. You can use the method as follows:

call send(i_api, '_IS_SUBTYPE_OF_',rc,
input_source_type,'WHTBLPRC',is_process_table);

If IS_PROCESS_TABLE is returned as a 1, then the current table is an intermediate table in a process step. If it returns a zero, then it is a loadable table.

Process Objects and WHPROCES Subtypes

Each process (metadata object that creates a table) is of subtype WHPROCES. For a list of WHPROCES subtypes, see Metadata Type Inheritance.

These subtypes give you the process information that has been entered using the Edit Load Step frame or the Process Properties frame for a loadable object. This information includes the name of the person who is writing the code, the host where the code should execute, and column mappings.

For each WHPROCES subtype, you can retrieve the corresponding WHTABLE by using the OUTPUT TABLES property. For more information on the relationships of metadata that are associated with processes, see the table and process models in Relationships Among Metadata Types.

INPUT and OUTPUT Properties

There are two sets of properties that deal with process flow to a table or column--one for input and one for output.

INPUT SOURCES

specifies an SCL list of general identifying information about the nearest intermediate output table or loadable table that is a source to the current table or column.

Given the process flow diagram that is shown in Process Flow in SAS/Warehouse Administrator Process Editor, the INPUT SOURCES property of Credit data table would return the intermediate table named Mapping.

INPUT OBJECTS

specifies an SCL list of general identifying information about the nearest loadable table that is a source to the current table or column.

Given the process flow diagram that is shown in Process Flow in SAS/Warehouse Administrator Process Editor, the INPUT OBJECTS property of Credit data table would return the loadable ODD table named ODD 1.

OUTPUT TARGETS

specifies an SCL list of general identifying information about the nearest intermediate output table or loadable table that is a target for the current table or column.

Given the process flow diagram that is shown in Process Flow in SAS/Warehouse Administrator Process Editor, the OUTPUT TARGETS property of ODD 1 would return the intermediate table named Mapping.

OUTPUT OBJECTS

specifies an SCL list of general identifying information about the nearest loadable table that is a target for the current table or column.

Given the process flow diagram that is shown in Process Flow in SAS/Warehouse Administrator Process Editor, the OUTPUT OBJECTS property of ODD 1 would return the loadable table named Credit data table.

Input Tables, Output Tables, and Job Metadata

Each job can have input and output tables that are associated with them. As shown in WHJOB: Input Tables and Output Tables, the WHJOB type has two properties, Output Tables and Input Tables, that can retrieve this information. Both properties will return WHTABLE subtypes. A WHJOB type can return more than one WHTABLE as its input or output. The WHTABLE subtype has two properties that associate it to the job: Using Jobs and Creating Job. The WHTABLE subtype property, Using Jobs, will return all WHJOB types that use the WHTABLE subtype as an input table. The WHTABLE subtype property, Creating Job, will return only one WHJOB type because you can create a table only in one job.

WHJOB: Input Tables and Output Tables

Reading Job Metadata

In SAS/Warehouse Administrator, a job is a metadata object that specifies the processes that create one or more data stores (output tables). You can join these processes together to form a job flow. The Process Editor in SAS/Warehouse Administrator is used to create job flows.

Each job has metadata of type WHJOB. WHJOB types give you information about the job that has been gathered using the Properties frames, such as scheduling server, location of generated source files, scheduling starting times, and tracking user prologs and epilogs. You can retrieve the corresponding metadata by using job properties. For example, to retrieve the associated tracking prolog for a job, you need to use the TRACKING PROLOG property that returns a WHJOBCAT metadata type that contains all of the tracking prolog information.

Each scheduled job requires a scheduling server association. When you request the SCHEDULING SERVER property of a job, a WHSERV object will be returned. The HOST property of WHSERV returns a WHHOST object that is the defined host for this scheduling server as shown in the following figure.

Job Type Model

Reading Job Flow Metadata

In SAS/Warehouse Administrator, job flow defines the relationship between jobs and events. This metadata is used to define dependencies between jobs within the warehouse. The Process Editor in SAS/Warehouse Administrator is used to create job flows such as the one shown in the following figure:

Job Flow in SAS/Warehouse Administrator

In the preceding figure you can interpret the chart as follows: Job 3: CRM Database Main Load is dependent in the settings of two events, Successful Dimension Table Load and Nightly Batch Start, as well as the execution of the job Customer Purchase Data. The following figure shows the metadata relationships that are defined between jobs and events. These relationships define the job flow.

Job and Event Relationships

The previous figure shows the relationship between jobs and events in the Job View of the Process Editor. These relationships are used to define Job Dependencies within SAS/Warehouse Administrator.

Note:   If there are no intermediate WHEVENT objects, the outputs from OUTPUT TARGETS and OUTPUT OBJECTS properties are identical. The same is true for INPUT SOURCES and INPUT OBJECTS.  

Reading Job Hierarchy Metadata

This section describes how to read the metadata for the objects in the Job Hierarchy panel of the Process Editor, as shown in Process Editor: Job Hierarchy and Process View. These objects have a PROCESS GROUPS property that lists the metadata identifiers of the group that contains the object. The types also have a PROCESS MEMBERS property that lists the metadata identifiers of the members of the object. The following figure shows the types that these properties can return.

Metadata Types in the Job Hierarchy

Using Icon Information

The catalog that is returned for the icon property will always be SASHELP.I0808. Depending on your particular use of the value that is returned, this image size might not fit your needs. The returned image name can reside in other SASHELP catalogs that contain different sizes. When the icon property is used with an _ADD_METADATA_ or _UPDATE_METADATA_ property list, the image name that is passed must exist in both the SASHELP.I0808 and SASHELP.I0404 catalogs.

If the image entry that is passed is not passed as residing in one of these catalogs or the passed entry name cannot be found in both of these catalogs, an error is returned. If a blank value is passed for the ICON item in the property list for the _ADD_METADATA_ or _UPDATE_METADATA_ method, the default icon for the type will be used. To reset the icon back to the default icon for this type, you should pass a blank value as the value of the ICON item in the property list that is passed to _UPDATE_METADATA_.

Previous Page

|

Next Page

|

Top of Page

Copyright © 2012 by SAS Institute Inc., Cary, NC, USA. All rights reserved.