Example: Using the Target Table Designer to Enter Metadata for a SAS Table

 < Day Day Up > 



This example demonstrates how to use a target designer to enter metadata for a table in SAS format. A target designer is used for the following reasons:

  • The table does not yet exist in physical storage; it will be created by a SAS ETL Studio job.

  • The table will reuse column metadata from other tables that have already been registered in a current metadata repository.

The example is based on a target table that is needed for the example data warehouse, as described in "Which Sales Person Is Making the Most Sales?" on page 29. The table in this example will subsequently serve as the target in the SAS ETL Studio job that is described in "Example: Creating a Job That Joins Two Tables and Generates a Report" on page 132.

Preparation

For the current example, assume that the following statements are true:

  • A project plan identified the need for a new table called Total_Sales_By_Employee. The new table will be created by joining two other tables, ORDER_FACT and ORGANIZATION_DIM. The new table will include employee name, total revenue, employee ID, job title, company, and department.

  • The table will be in SAS format and will be stored in a SAS library called Ordetail.

  • Metadata for the Ordetail library has been added to the main metadata repository for the example data warehouse. For details about libraries, see "Enter Metadata for Libraries" on page 44.

  • The main metadata repository is under change management control. For details about change management, see "Working with Change Management" on page 64.

  • You have selected a default SAS application server for SAS ETL Studio, as described in "Select a Default SAS Application Server" on page 59.

Start SAS ETL Studio and Open a Metadata Profile

Perform the following steps to begin work in SAS ETL Studio:

  1. Start SAS ETL Studio as described in "Start SAS ETL Studio" on page 56.

  2. Open the appropriate metadata profile as described in "Open a Metadata Profile" on page 58. For this example, the appropriate metadata profile has access to metadata about the Ordetail library.

You do not need to check out a library in order to add metadata for tables in that library. Accordingly, the next task is to select the appropriate target designer.

Select the Target Designer

Follow these steps to select the wizard that enables you to enter metadata for a SAS table:

  1. From the menu bar on the SAS ETL Studio desktop, select Tools Target Designer. The Target Designer selection window is displayed as follows. Note that the list of available target designers might differ at your site.

    click to expand
    Display 8.1: Target Designer Selection Window

  2. In the Target Designer selection window, click the Target Table icon and click . The wizard attempts to open a connection to the default SAS application server. If the connection is successful, the name and description window is displayed.

Enter a Name and Description

Use the first window in the Target Table Designer to enter a name and description for the metadata object that will specify the table.

Note

The metadata object might or might not have the same name as the corresponding physical table. You will specify a name for the physical table in a later window in this wizard.

In this example, the name of the metadata object is Total_Sales_By_Employee. The description is as follows: "Provides a total sales figure and job information for each salesperson. The table is created by joining the source tables ORDER_FACT and ORGANIZATION_DIM."

click to expand
Display 8.2: Name and Description Window

When the text is complete, click to display the import columns window.

Select Column Metadata from Existing Tables

If you want the columns in the new table to be similar to the columns in tables that are already defined, use the import columns window to import metadata for the appropriate columns.

For example, as noted in "Preparation" on page 90, the tables ORGANIZATION_DIM and ORDER_FACT will be joined and transformed to supply data to the target Total_Sales_By_Employee. Accordingly, it would be appropriate to import selected columns from ORGANIZATION_DIM and ORDER_FACT.

Follow these steps to import metadata for the appropriate columns:

  1. In the import columns window, locate the Available Columns tree. In that tree, click the icon for the table ORGANIZATION_DIM. Then click the right arrow to move all of the columns in this table into the Selected Columns list box.

  2. In the Available Columns tree, click the icon for the table ORDER_FACT, then click the right arrow again to move the columns of that table into the Selected Columns list box.

    In this example, a pop-up message is displayed to indicate that one column in the table ORDER_FACT is not added to the Selected Columns list box, because that same column was already added from the table ORGANIZATION_DIM. Click to clear the pop-up message.

    click to expand
    Display 8.3: Import Columns Window

  3. Click to display the target columns window.

Specify Column Metadata for the New Table

Use the target columns window to review and update any imported metadata for columns. You can also add metadata for new columns.

click to expand
Display 8.4: Target Columns Window

Scroll down through the target columns to verify that you have the columns that you need. For our example, the columns are correct. When we create and run the job as described in "Example: Creating a Job That Joins Two Tables and Generates a Report" on page 132, we will modify these original column specifications.

Scroll to the top, then scroll right to see the column metadata. You can change any metadata value by selecting it with the left mouse button. In our example, you could add descriptions to the columns that came from the ORDER_FACT table.

Note that you are defining column metadata for the new table. You have not yet created the new table on a file system. The metadata in the current window indicates where the data can be found and how it is to be formatted.

When you have reviewed and updated the column metadata, click to display the physical storage window.

Specify Physical Storage Information for the New Table

Use the physical storage window to specify the format and location of the new table, as shown in the following display.

click to expand
Display 8.5: Physical Storage Window

For our example table, you would follow these steps:

  1. In the DBMS field, select SAS.

  2. In the Library field, click the down arrow. A list of existing libraries is displayed.

  3. Scroll down through the list and choose the library Ordetail.

  4. In the Name field, accept the default, Total_Sales_By_Employee. The default is the name that you entered in the first window of the Target Table Designer wizard.

  5. After you have specified a format, a library, and a table name, click Next to go to the finish window.

Usage Hints for the Physical Storage Window

Keep the following in mind as you use the physical storage window:

  • The name that you specify in the Name field must follow the rules for table names in the format that is selected in the DBMS field. For example, if SAS is the selected DBMS, the name must follow the rules for SAS data sets. If you select another DBMS, the name must follow the rules for tables in that DBMS.

  • For a SAS table or a table in a database management system, you can enable the use of mixed-case names or special characters in names. See "Setting Name Options for Individual Tables" on page 68. See also the usage note "Case and Special Characters in SAS Names" on page 184.

  • You can specify new libraries or edit the metadata definitions of existing libraries using the and buttons.

  • You can use the button to specify options for SAS tables and tables in a database management system.

Save Metadata for the Table

After you have specified physical storage information, you review all of the metadata that you have defined for your new table.

In the finish window, scroll down to confirm that you have the metadata that you need. If you need to change any of the metadata, click to display the wizard windows that you need to make your changes.

click to expand
Display 8.6: Finish Window

When you have confirmed that the metadata is correct, click to store the metadata for your new table. The table is displayed in the Project tree. Next, you check in the metadata object for the table.

Check In the Metadata

Under change management, new metadata objects are added to the Project tree on the SAS ETL Studio desktop.

click to expand
Display 8.7: Project Tree with a Metadata Object for a New Table

Follow these steps to check the new table into the change-managed repository:

  1. In the Project tree, select the repository icon (Project: etlUser1).

  2. From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

The metadata object in the project repository is checked into the change-managed repository. The new object is displayed as checked-out in the Inventory and Project trees. The new table is now ready to be used in a job, as described in "Example: Creating a Job That Joins Two Tables and Generates a Report" on page 132.



 < Day Day Up > 



SAS Institute - SAS 9.1.3 ETL Studio. User's Guide
SAS 9.1.3 ETL Studio: Users Guide
ISBN: 1590476352
EAN: 2147483647
Year: 2004
Pages: 127
Authors: SAS Institute

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net