Registering Data Sources

Example: Creating an ODD That Registers the Location of a Data Source

This example describes how to create an ODD that registers the location of a SAS table or view, or one that registers the location of a DBMS table with the help of a SAS/ACCESS LIBNAME definition. Use this approach when you want to bring source data into a Warehouse Environment without transforming it at the ODD level. (You can transform this data later in a Process Flow--between an ODD and a data store that it feeds, for example.)

Note: The following explanations describe the metadata and methods to achieve the desired results; it is assumed that the appropriate ODD Group exists. [cautionend]

Define ODD Properties

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

[untitled graphic]

To update the default metadata for the ODD, position the cursor on its icon, click the right mouse button, and select Properties. The Operational Data Definition Properties window displays for you to enter the appropriate information.

General Tab

specifies the ODD's name, such as Customer, as well as a description, an owner, and an administrator.

[untitled graphic]

To specify an owner or an administrator, click the down arrow to select a name from a list. If you need to add a new Contact record, click the right arrow to display a properties window. For details about that window, click its Help button.

The next task is to specify the location of the data source for the ODD. Two examples of metadata for the Data Location tab are shown next.

Example 1: Data Location tab for a remote SAS table

If you want to register a remote SAS table, specify the definition for the host where the target library resides (such as unix1); specify the target SAS library (such as Remote Src Data); and specify the SAS table or view within that library (such as Customer). The next display shows an example of this metadata:

[untitled graphic]

To specify a host or SAS library, click the down arrow to select a name from a list. If you need to add a new host or library, click the right arrow to display a properties window. For details about that window, click its Help button.

To specify a SAS table or view within the library, click the down arrow beside the SAS Table or View field to select a name from a list.

Note: If you can select a SAS table by clicking the down arrow beside the SAS Table or View field, then SAS was able to assign the library that is specified in the SAS Library field. If you are unable to access a table, read the SAS log to identify the problem. [cautionend]

Example 2: Data Location tab for a local SAS/ACCESS LIBNAME

If you want to register a local SAS/ACCESS LIBNAME for a remote DBMS table, specify the definition for the host where the SAS/ACCESS LIBNAME statement will be executed (such as local); specify the SAS/ACCESS LIBNAME (such as SAS/Oracle Lib); and specify the DBMS table within the DBMS (such as Customer). The next display shows an example of this metadata:

[untitled graphic]

To specify a host or a SAS/ACCESS LIBNAME, click the down arrow to select a name from a list. If you need to add a new host or SAS/ACCESS LIBNAME, click the right arrow to display a properties window. For details about that window, click its Help button.

To specify a DBMS table that is accessed via the local SAS/ACCESS LIBNAME, click the down arrow beside the SAS Table or View field to select a name from a list.

Note: If you can select a DBMS table by clicking the down arrow beside the SAS Table or View field, then SAS was able to assign the SAS/ACCESS LIBNAME that is specified in the SAS Library field. If you are unable to access a table, read the SAS log to identify the problem. [cautionend]

The next task is to specify column metadata for the ODD.

From the Columns tab, you can specify the column metadata to be included in the ODD. No column metadata has been specified yet, as shown next:

The column metadata for a data store must accurately specify the columns that you want to "map" to other data stores in a Process Flow, such as the one shown in ODD in a Process Flow. In the case of an ODD that simply registers the location of an existing data source, you will use the "Import from Supplied Data Location" method to import the metadata for all columns in the data source.

To import columns from the data source, select Import, then select Supplied Data Location. All of the columns from the data source specified on the Data Location tab are imported, as follows:

[untitled graphic]

After you specify the column metadata, you are finished creating the ODD. Click OK to save its metadata record and return to the Explorer.

No Jobs for ODDs That Only Register Locations

A Job is a metadata record that specifies the processes that create one or more data stores (output tables). You create Jobs in the Process Editor window. Most data stores in SAS/Warehouse Administrator require Jobs that execute code. However, if an ODD simply registers the location of a data source and it does not execute any code, then no Job is needed to create the ODD.

Test the ODD

To verify that an ODD can successfully generate a LIBNAME statement (as it will have to do in a Process Flow), position the cursor on the ODD's icon in the Explorer, click the right mouse button, and select Assign Libref. Check the SAS log or the Active Libraries panel of the SAS Explorer window to verify that the SAS library associated with this ODD was assigned.

After you have tested your ODD, you can use it as an input to another data store. See also the maintenance note in Keeping ODD Column Metadata Current.

Top of Page