Maintaining Data Tables

Example: Creating a Data Table

After you have a Data Group to group Data Tables, you can then add Data Tables to that group. This example creates a Data Table to be used as a look-up table, for example, to find the city and state associated with a specific zipcode.

Note: The following explanations describe the metadata and methods to achieve the desired results; it is assumed that the appropriate Data Warehouse Environment, Data Warehouse, Subject, Data Group, and ODD exist. [cautionend]

Define Data Table Properties

In the SAS/Warehouse Administrator Explorer, position the cursor on the Data Group, for example, Intermediate Tables Data Group, click the right mouse button, select Add Item, and then Data Table. In the Explorer window, a new Data Table is added under the Data Group as follows:

[untitled graphic]

To update the default metadata for the table, put the cursor on its icon, press the right mouse button, and select Properties. The Data Table Properties window displays for you to enter the appropriate information, which is described next:

General Tab

specifies the table's name Zipcode Lookup, a description, an owner, and an administrator.

[untitled graphic]

Columns Tab

specifies the columns to be included in the Data Table, which do not exist yet as follows:

[untitled graphic]

To import columns from an input source, click Import to display a list of input sources, and then select one, for example, Operational Data Sources. The Import Column Metadata window displays.

From the Import Column Metadata window, which lists the available sources, select an ODD, for example Geography, to display its available columns.

[untitled graphic]

Select the appropriate columns under Columns (which for this example are city_nam, region_n, state_na, and zipcode), use the double arrows to move them to Selected Columns, and then click OK. You are returned to the Columns tab in the Data Table Properties window, which lists the imported columns:

[untitled graphic]

Physical Storage Tab

specifies the physical storage attributes. This example specifies the storage format SAS and the load technique Refresh.

[untitled graphic]

Click Define to open the SAS Table Properties window and view its metadata.

Location Tab

specifies where the SAS table is stored.

[untitled graphic]

Access Location Tab

specifies a location to provide interactive access to the Data Table. For this example, the check box Use Physical Storage Location as the Access Location is selected by default, which enables the software to use information from the Physical Storage tab to provide interactive access to the table.

[untitled graphic]

Open Code Tab

specifies the source code used to view the table.

[untitled graphic]

Define Process Editor Job

In the Process Editor Job, the Data Table Zipcode Lookup is specified as the output target and the ODD Geography is specified as the input source. The following Process Editor window shows the Process Flow for the Job:

[untitled graphic]

The processes defined in the Job are summarized as follows:

Mapping Process

The source code to map columns is generated by SAS/Warehouse Administrator, rather than user written, as shown in the Source Code tab:

[untitled graphic]

Column mapping is defined as one-to-one mapping, as shown in the Column Mapping tab. To produce one-to-one mapping, first click 1 to 1 Mappings from the Column Mapping tab, which opens the One-to-One Column Mapping window. Then, click Quick Map.

[untitled graphic]

Load Step Process

The source code is generated by SAS/Warehouse Administrator, rather than user written, as shown in the Data Table Load Process Attributes window:

[untitled graphic]

For more information about Process Editor Jobs, see Maintaining Jobs. For more information about processes, see Maintaining Processes.

Top of Page