Model Usage

Usage Scenario: Creating Metadata for a Cube

This usage scenario describes how to create metadata that represents a cube. The metadata for a cube consists of metadata types from several submodels, including the Analysis Submodel, the Relational Submodel, and the Transform Submodel. This scenario illustrates only one usage pattern.

Developers are discouraged from creating or using cube metadata with the SAS Open Metadata Interface. Instead, cubes should be created with either SAS OLAP Cube Studio or PROC OLAP. This guide contains sample PROC OLAP code that shows how to create a cube metadata object and related cube metadata.

Requirements

To use this usage scenario, you should have a general understanding of the Analysis Submodel, the Relational Submodel, and the Transform Submodel of the SAS Metadata Model. You should have at least introductory knowledge of OLAP concepts. If you are not familiar with OLAP concepts, see SAS OLAP Cube Studio or PROC OLAP documentation.

Creating Metadata Objects That Represent a Cube

A cube is a set of data that is organized and structured in a hierarchical, multidimensional arrangement. The cube is usually derived from a subset of a data warehouse. Unlike relational databases, which use two-dimensional data structures (often in the form of columns and rows in a spreadsheet), OLAP cubes are logical, multidimensional models that can have numerous dimensions and levels of data. Cube metadata consists of information about the dimensions, levels, and measures; information about the relational tables that contain the data from which a cube is derived; and information about the transformation that creates the cube.

Figure 1 shows the metadata objects that represent a typical input table.

[The metadata objects that represent an input table.]

Figure 1. The metadata objects that represent an input table.

The SAS Metadata Model defines several metadata types that represent tables. Cubes are built from tables that are described by the PhysicalTable metadata type. PhysicalTable represents a relational table or data set that physically resides in a file system or DBMS. A PhysicalTable object must have an associated SAS library, directory, and server defined. These are represented by the SASLibrary, Directory, and ServerComponent metadata types. A table's columns are represented by the Column metadata type. SASLibrary has a DeployedComponents association to ServerComponent (numbered 1 in Figure 1). It has a UsingPackages association to Directory (numbered 2), and a Tables associations to PhysicalTable (numbered 3). PhysicalTable has a Columns association to its Column objects (numbered 4).

Figure 2 shows the process that creates the cube.

[Figure 2. The metadata objects that represent how to create a cube.]

Figure 2. The metadata objects that represent how to create a cube.

A cube is represented by the Cube metadata type. The transformation that creates a cube is represented by the ClassifierMap metadata type and associated TransformationStep, TransformationActivity, and Job metadata types. Cube has a TargetClassifierMaps association (numbered 7 in Figure 2) to ClassifierMap. ClassifierMap has a ClassifierSources association (numbered 6) to the PhysicalTable object. The CubeType= attribute of the Cube object is set to MOLAP to indicate that the cube is built from a detail table with no aggregate data in relational tables. The CubeName= attribute of the Cube object remains blank until PROC OLAP physically creates the cube. The Name= attribute of the ClassifierMap object should be set to Load Map, and the TransformRole= attribute should be set to BASE.

ClassifierMap has a Steps association (numbered 5) to the TransformationStep object. TransformationStep has an Activities association (numbered 4) to the TransformationActivity object. TransformationActivity has a Jobs association (numbered 3) to the Job object. Job has a Properties association (numbered 1) to a Property object, and Property has an OwningType association (numbered 2) to a PropertyType object. The Name= and PropertyName= attributes of the Property object associated with Job are both set to Editable, and the DefaultValue= attribute is set to 0. PropertyType has the Name= attribute set to Boolean, and the SQLType= attribute set to -7. TransformationStep also has a Properties association (numbered 1) to a Property object. This Property has an OwningType association (numbered 2) to a PropertyType object that has Name= and PropertyName= attributes of String. The Property object that is associated with TransformationStep has both its Name= and PropertyName= attributes set to CLASS. The DefaultValue= attribute of the Property is set to com.sas.olapadmin.designer.model.CodeGeneration. The Role= attribute is set to TRANSFORMATIONSTEP. The SQLType= attribute of the PropertyType that is associated with the CLASS Property is set to 12.

Cube has an AssociatedFile association (numbered 8) to a File object, and File has a Directories association (numbered 9) to a Directory object. File represents the storage location for the cube. The FileExtension= attribute of File is set to CUBE, and the TextRole= attribute is set to METAUTILITY. Cube has an AssociatedOLAPSchema association (numbered 10) to an OLAPSchema object, and OLAPSchema has a DeployedComponents association (numbered 11) to a ServerContext object. The OLAPSchema metadata type is used to group cubes to be processed by an OLAP Server. The OLAP Server is represented by a ServerContext object.

Cube has a Properties association (numbered 1) to two Property objects. Each Property has an OwningType association (numbered 2) to a PropertyType object. The first Property object has its Name= and PropertyName= attributes set to Version. The DefaultValue= attribute is set to 2. The PropertyType object for the Version Property has its Name= attribute set to Integer and its SQLType= attribute set to 4. The second Property object has the Name= and PropertyName= attributes set to NO_NWAY. This attribute indicates whether the cube has an NWAY aggregation saved with the cube. If the DefaultValue= attribute for Property is set to NO, then NWAY is saved. If it is set to YES, then NWAY is not saved. The PropertyType object for the NO_NWAY Property has the Name= attribute set to String, and the SQLType= attribute set to 12.

Figure 3 shows the dimensions, levels, and hierarchies for a cube. These are represented by the Dimension, Level, and Hierarchy metadata types. In this usage scenario, Cube has a single dimension defined, called Geography. The Geography dimension has two levels, Country and Region, and one hierarchy called Geography. Each level is derived from a single column in the input table.

[Figure 3. The metadata objects that represent the dimensions, levels, and hierarchies of a cube.]

Figure 3. The metadata objects that represent the dimensions, levels, and hierarchies of a cube.

Cube has a Dimensions association to the Dimension object (numbered 2 in Figure 3), and a Hierarchies association (numbered 1) to the Hierarchy object. Dimension and Hierarchy both have a Levels association (numbered 3) to a Level object. The Dimension object has the DimensionType= attribute set to STANDARD. The Hierarchy object has a Levels association to ordered list of Level objects. The Dimension object also has a Levels association to the Level objects, but the Level objects are not ordered. Each Level object has a TargetFeatureMap association (numbered 5) to a FeatureMap object, and FeatureMap has a FeatureSources association (numbered 8) to a Column object. The TransformRole= attribute of the FeatureMap objects is set to ONETOONE.

Cube has a TargetClassifierMaps association (numbered 4) to a ClassifierMap object named Load Map. ClassifierMap has a ClassifierSources association (numbered 7) to a PhysicalTable object, and a FeatureMaps association (numbered 6) to each FeatureMap object. The PhysicalTable object has a Columns association (numbered 9) to the Column objects.

Figure 4 shows the metadata objects that represent the measures for a cube. In this example, Cube has a single measure, ActualSUM, which is derived from a single column in the input table.

[Figure 4. The metadata objects that represent the measures of a cube.]

Figure 4. The metadata objects that represent the measures of a cube.

Cube has a Dimensions association (numbered 1 in Figure 4) to two Dimension objects. The first Dimension object represents the dimension described in Figure 3. The second Dimension object represents the Measures dimension and must have the DimensionType= attribute set to MEASURES. Dimension and Cube both have a Measures association (numbered 3) to a Measure object, Measure has a TargetFeatureMap association (numbered 5) to a FeatureMap object. FeatureMap has a FeatureSources association (numbered 7) to a Column object. The Measure object's Statistic= attribute is set to SUM, and the IsStoredStatistic= attribute is set to 1. The TransformRole= attribute of the FeatureMap object is set to TRNSFORM. Cube has a TargetClassifierMaps association (numbered 2) to the ClassifierMap object. ClassifierMap has a FeatureMaps association (numbered 4) to FeatureMap, and a ClassifierSources association (numbered 7) to PhysicalTable. PhysicalTable has a Columns association (numbered 8) to Column.

Figure 5 shows the aggregations for the cube. In this example, Cube has two aggregations. The first aggregation has the levels Country and Region. The second aggregation has only the Country level. Cube has an Aggregations association (numbered 1 in Figure 5) to the Aggregation objects. Each Aggregation object has a Levels association (numbered 2) to a Level object. The first Aggregation object has its Name= attribute set to Default, and the Nway= attribute is set to 1. This aggregation represents the NWAY (sometimes called the default) aggregation. Its Levels association (numbered 2) must contain each Level. The second Aggregation object has its Name= attribute set to Aggr1, and the Nway= attribute is set to 0. Its Levels association only contains the Country Level.

[Figure 5. The metadata objects that represent the aggregations of a cube.]

Figure 5. The metadata objects that represent the aggregations of a cube.

Sample PROC OLAP Code

The following sample PROC OLAP code shows how to create the metadata objects in this usage scenario.

LIBNAME OLAPLIB BASE "c:\olaptestfiles" ;

PROC OLAP Data=OLAPLIB.PRDSALE cube=prdmddb Path="c:\v9cubes";
METASVR host="hostname" port=9999 protocol=bridge userid=""
pw="" repository="Foundation" olap_schema="Olap Schema";

DIMENSION Geography hierarchies=(Geography) CAPTION='Geography';
HIERARCHY Geography levels=( COUNTRY REGION ) CAPTION='Geography' DEFAULT ;
LEVEL REGION CAPTION='Region' ;
LEVEL COUNTRY CAPTION='Country' ;

MEASURE ACTUALSUM STAT=SUM COLUMN=ACTUAL CAPTION='Sum of ACTUAL' FORMAT=DOLLAR12.2 ;

AGGREGATION COUNTRY;

RUN;

Top of Page