Model Usage

Usage Scenario: Creating Metadata for Tables, Columns, and Keys

This usage scenario describes how to create metadata objects that describe data tables and their columns and keys. XML representations are included that show how to create the metadata objects.

Requirements

This usage scenario assumes that the user has a general understanding of the Resource Submodel and the Relational Submodel of the SAS Metadata Model.

Understanding Table, Column, and Key Objects

The SAS Metadata Model includes several metadata types that describe tables. All of them are subtypes of the DataTable abstract metadata type. DataTable defines attributes and associations that are common to all of the table types. These include NumRows= and TableName= attributes, as well as Columns, ForeignKeys, and UniqueKeys associations, among others. From the perspective of a DataTable, the Columns, ForeignKeys, and UniqueKeys associations are optional. A DataTable object can exist on the SAS Metadata Server with zero or many associated column or key objects.

Column, ForeignKey, and UniqueKey are dependent metadata types. These metadata types have a required association to a DataTable object. That is, a Column, ForeignKey, or UniqueKey object cannot be added to the SAS Metadata Server unless an association to a DataTable subtype is also defined. Additionally, a Column, ForeignKey, or PrimaryKey object can be associated with only one DataTable object. If the associated DataTable object is deleted, the dependent objects are automatically deleted from the SAS Metadata Server as well.

Column is associated with a UniqueKey or ForeignKey object with a Keys association. Column can have zero or many associated keys. When a foreign key relationship is defined on a column, Column needs an association to a KeyAssociation object that links it to its partner column in the other table. This can be a ForeignKeyAssociations or UniqueKeyAssociations association, depending on the type of foreign key that was defined on the DataTable. The SAS Metadata Model does not require a partner UniqueKey object to be defined; however, a valid ForeignKey definition has the corresponding UniqueKey defined. A UniqueKey can have zero or many associated ForeignKey objects.

Creating a SASLibrary and PhysicalTable Objects

To illustrate how the table, column, and key types are used, this scenario describes the metadata objects that might be defined to represent a directory containing sales information and four SAS tables named Period, Product, Sales, and Store. Figure 1 shows the top-level metadata objects created to describe the tables.

Figure 1. The metadata objects that represent the Sales Information SAS library and its four tables: Period, Product, Sales, and Store.

The metadata type SASLibrary represents the SAS table collection. The SAS Metadata Model includes several metadata types for describing tables. They are all subtypes of the DataTable abstract metadata type. PhysicalTable is the metadata type representing a relational table or data set that physically resides in a file system or DBMS. The RelationalTable metadata type represents tables that do not have a physical mapping (for example, a table created using a data modeling tool). The WorkTable metadata type represents transient tables. Transient tables are tables that reside in the SAS Work library. These tables reside in a directory. Therefore, they are represented by the PhysicalTable metadata type.

The metadata objects in Figure 1 can be created by issuing the following XML request:

 
<AddMetadata>
  <Metadata>
    <SASLibrary Name="Sales Information" Libref="SALEINFO" Engine="Base" 
      PublicType="Library" UsageVersion="1000000" >
       <UsingPackages>
          <Directory DirectoryName="Path" Name="/data" IsRelative="0" 
            DirectoryRole="LibraryPath" UsageVersion="1000000"/>
       </UsingPackages>
       <Tables>
          <PhysicalTable Name="PERIOD" SASTableName="PERIOD" PublicType="Table" 
              UsageVersion="100000"/>
          <PhysicalTable Name="SALES" SASTableName="SALES" PublicType="Table" 
              UsageVersion="100000"/>
          <PhysicalTable Name="PRODUCT" SASTableName="PRODUCT" PublicType="Table" 
              UsageVersion="100000"/>
          <PhysicalTable Name="STORE" SASTableName="STORE" PublicType="Table" 
              UsageVersion="100000"/>
       </Tables>
    </SASLibrary>
  </Metadata>
  <Reposid>A0000001.ABCDEFGH</Reposid>
  <NS>SAS</NS>
  <Flags>268435456</Flags>
  <Options/>
</AddMetadata>

In the XML request, SASLibrary defines the SAS library and has the attributes Name=, Libref=, Engine=, PublicType=, and UsageVersion=. The immediate XML subelements define associations to five new objects:

The UsingPackages association creates and defines a relationship with a Directory object that stores the physical pathname of the directory where the tables are located. For more information about defining SASLibrary and Directory objects, see Usage Scenario: Creating Metadata for a SAS Library.
The Tables association creates and defines a relationship with a PhysicalTable object for each of the four tables.

The omission of an object identifier from each associated metadata object definition instructs the SAS Metadata Server to create a new object that has the properties in the definition. For more information about how the AddMetadata method works, see Adding Metadata Objects in the SAS 9.2 Open Metadata Interface: Reference and Usage.

The PhysicalTable metadata type has three name attributes: Name=, SASTableName=, and TableName=. The Name= attribute is a descriptive or display name. The SASTableName= attribute is the name used by SAS to refer to the table. The TableName= attribute is used only if the table resides in a DBMS, and the DBMS name is different than the name used by SAS. In this case, TableName= is the DBMS name for the table.

When this XML request was sent to the SAS Metadata Server, the server allocated identifiers for the objects: for the SASLibrary, the ID is ABCDEFGH.A1000001; for the Period table, the ID is ABCDEFGH.A2000001; for the Sales table, the ID is ABCDEFGH.A2000002; and so on.

Creating Column and Key Objects

Figure 2 shows the metadata objects representing the columns and keys of the Period table. The column named Period_ID has a primary key defined on it.

Figure 2. The metadata objects that represent the columns and keys of the Period table.

A Column object is associated with an existing PhysicalTable object with a Table association (numbered 1 in Figure 2). A Table association is defined for each Column object to the PhysicalTable object. A primary key is represented by the metadata type UniqueKey. Column is associated with UniqueKey by creating a Keys association (numbered 2 in Figure 2). UniqueKey has the attribute IsPrimary=, which determines whether the object describes a primary key. The default value, 0, means that the key is not a primary key. When describing a primary key, be sure to set IsPrimary="1". UniqueKey also needs to be associated with a PhysicalTable object. The PhysicalTable Period has a UniqueKeys association (numbered 3) to the UniqueKey object.

The metadata objects in Figure 2 can be created by using the following XML request:

<AddMetadata>
  <Metadata>
    <Column SASColumnType="N" SASColumnName="PERIOD_ID" ColumnLength="10" Name="PERIOD_ID" 
     PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDFEGH.A2000001"/>
      </Table>
      <Keys>
        <UniqueKey IsPrimary="1" Name="PERIOD_ID_KEY">
          <Table>
            <PhysicalTable Objref="ABCDFEGH.A2000001"/>
          </Table>
        </UniqueKey>
      </Keys>
    </Column>
    <Column SASColumnType="N" SASColumnName="WEEK_IN_YEAR" ColumnLength="10" Name="WEEK_IN_YEAR" 
       PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDFEGH.A2000001"/>
      </Table>
    </Column>
    <Column SASColumnType="N" SASColumnName="MONTH_IN_YEAR" ColumnLength="10" Name="MONTH_IN_YEAR" 
     PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDFEGH.A2000001"/>
      </Table>
    </Column>
    <Column SASColumnType="N" SASColumnName="YEAR" ColumnLength="10" Name="YEAR" 
     ColumnName="YEAR" PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDFEGH.A2000001"/>
      </Table>
    </Column>
  </Metadata>
  <Reposid>A0000001.ABCDEFGH</Reposid>
  <NS>SAS</NS>
  <Flags>268435456</Flags>
  <Options/>
</AddMetadata>

A Column object cannot be created unless it has an association to a table object. In this example, each Column object has an association named Table, which defines an association to the Period PhysicalTable object, which has the identifier ABCDFEGH.A2000001. Submitting the PhysicalTable object's identifier in the ObjRef= attribute instructs the SAS Metadata Server to look for an existing object and to create the association without modifying any of the PhysicalTable object's other properties. This syntax is explained in Adding Metadata Objects in the SAS 9.2 Open Metadata Interface: Reference and Usage.

The first Column object defined in the XML has objects associated to it with the Keys association. The UniqueKey object named Period_ID_Key is created and associated with the Column named Period_ID with the Keys association. Additionally, the UniqueKey object is associated with PhysicalTable Period with a Table association. The SAS Metadata Model uses two association names to describe a relationship between two metadata types. The names describe the relationship from the perspective of each metadata type. From the perspective of PhysicalTable, a relationship to UniqueKey is created by defining a UniqueKeys association. From the perspective of UniqueKey, an association to PhysicalTable is created by defining a Table association. Figure 2 represents the relationship between the objects (numbered 3) from the perspective of the PhysicalTable. In the XML, the relationship is defined from the perspective of the UniqueKey. For more information, see Understanding Associations.

When this XML request was sent to the SAS Metadata Server, it created five new metadata objects: four Column objects and one UniqueKey object. The Period_ID column has the identifier ABCDFEGH.A3000001, and the UniqueKey object has the identifier ABCDFEGH.A4000001.

Figure 3 shows the metadata objects representing the columns and keys of the Sales table. The column named Period_ID has a foreign key defined on it.

Figure 3. The metadata objects that represent the columns and keys of the Sales table.

The ForeignKey metadata type represents the foreign key. The Column object representing the Period_ID column has a KeyedColumns association (numbered 2 in Figure 3) to ForeignKey. The PhysicalTable object representing the Sales table has a ForeignKeys association (numbered 3) to ForeignKey.

The metadata objects in Figure 3 can be created by using the following XML request:

<AddMetadata>
  <Metadata>
    <Column SASColumnType="N" SASColumName="PERIOD_ID" ColumnLength="10" Name="PERIOD_ID" 
     PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDEFGH.A2000002"/>
      </Table>
    </Column>
    <Column SASColumnType="N" SASColumnName="QUANTITY" ColumnLength="10" Name="QUANTITY" 
     PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDEFGH.A2000002"/>
      </Table>
    </Column>
    <Column SASColumnType="N" SASColumnName="REVENUE" ColumnLength="10" Name="REVENUE" 
      PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDEFGH.A2000002"/>
      </Table>
    </Column>
    <Column SASColumnType="N" SASColumnName="COST" ColumnLength="10" Name="COST" 
       PublicType="Column" UsageVersion="100000">
      <Table>
        <PhysicalTable ObjRef="ABCDEFGH.A2000002"/>
      </Table>
    </Column>
  </Metadata>
  <Reposid>A0000001.ABCDEFG</Reposid>
  <NS>SAS</NS>
  <Flags>268435456</Flags>
  <Options/>
</AddMetadata>

When this XML request was sent to the SAS Metadata Server, it assigned the Period_ID column in the Sales table the identifier ABCDEFGH.A3000007.

Associating Key Objects

ForeignKey objects have two additional required associations. They must have a PartnerUniqueKey association to a UniqueKey object, and a KeyAssociations association to a KeyAssociation object that links each source and target column in the unique key and foreign key relationship.

Figure 4. The metadata objects that create the primary key and foreign key relationship.

The ForeignKey object has a PartnerUniqueKey association (numbered 1 in Figure 4) with the unique key in the Period table. The ForeignKey has a KeyAssociations association (numbered 2) with the KeyAssociation object. The KeyAssociation object associates one column of the unique key with the corresponding column in the foreign key. From the KeyAssociation object, these associations are called UniqueKeyColumn (numbered 3) and ForeignKeyColumn (numbered 4). In this example, there is only one column for each key, so there is only one KeyAssociation object created. The KeyAssociations association is used to identify which columns correspond in a unique key and foreign key relationship. If the characteristics of one of the columns change, it is easy to identify the corresponding column and change its characteristics as well.

The metadata objects in Figure 4 can be created by using the following XML request:

<AddMetadata>
  <Metadata>
    <ForeignKey Name="PERIOD_ID_FOREIGN_KEY">
      <KeyedColumns>
        <Column ObjRef="ABCDEFGH.A3000007"/>
      </KeyedColumns>
      <Table>
        <PhysicalTable Objref="ABCDEFGH.A2000002"/>
      </Table>
      <PartnerUniqueKey>
        <UniqueKey Objref="ABCDEFGH.A4000001"/>
      </PartnerUniqueKey>
      <KeyAssociations>
        <KeyAssociation Name="KeyAssoc0">
          <ForeignKeyColumn>
            <Column Objref="ABCDEFGH.A3000007"/>
          </ForeignKeyColumn>
          <UniqueKeyColumn>
            <Column Objref="ABCDEFGH.A3000001"/>
          </UniqueKeyColumn>
        </KeyAssociation>
      </KeyAssociations>
    </ForeignKey>
  </Metadata>
  <Reposid>A0000001.ABCDEFGH</Reposid>
  <NS>SAS</NS>
  <Flags>268435456</Flags>
  <Options/>
</AddMetadata>

A ForeignKey object named Period_ID_Foreign_Key is defined. Next, the associations described in figures 3 and 4 are defined. The KeyAssociations association creates the KeyAssociation object and associates it with the columns.

Top of Page