Supporting Case and Special Characters in Table and Column Names

 < Day Day Up > 



SAS ETL Studio cannot access tables or columns with case-sensitive names or with special characters in the names unless the appropriate options have been specified. For the example data warehouse, assume that all tables are in SAS format, and that all names for tables and columns follow the standard rules for SAS names.

Case and Special Characters in SAS Table and Column Names

By default, the names for SAS tables and columns must follow the standard rules for SAS names. However, SAS ETL Studio will support case-sensitive names for tables and columns, as well as special characters in column names, if the appropriate options are specified in the metadata for the SAS table.

SAS ETL Studio users can set name options in the metadata for individual tables. For description of this task, see "Setting Name Options for Individual Tables" on page 68.

As an alternative to setting name options in the metadata for individual tables, you can set default name options for all table metadata that is entered with a source designer or a target designer in SAS ETL Studio. For details, see "Setting Default Name Options for Tables and Columns" on page 51.

Case and Special Characters in DBMS Table and Column Names

SAS ETL Studio cannot access a DBMS table with case-sensitive names or with special characters in names unless the appropriate name options are specified in the metadata for the database library that is used to access the table, and in the metadata for the table itself.

One approach would be for administrators to specify name options in the metadata for the database library, as described in this section. Administrators could then let SAS ETL Studio users know which DBMS name options to specify in the metadata for tables in that library. SAS ETL Studio users can set name options in the metadata for DBMS tables. For description of this task, see "Setting Name Options for Individual Tables" on page 68.

As an alternative to setting name options in the metadata for individual tables, you can set default name options for all table metadata that is entered with a source designer or a target designer in SAS ETL Studio. For details, see "Setting Default Name Options for Tables and Columns" on page 51.

Enabling DBMS Name Options for a New Database Library

The steps in this section describe how to enable name options when you enter the metadata for a new database library. These steps are appropriate for an administrator who does not have to use the change-management facility. The steps for a user would be similar, except that the user would have to check in the metadata for the new library as a last step.

  1. Follow the general instructions in "Enter Metadata for a Library" on page 48. In the first window of the New Library wizard, select the appropriate kind of database library and click .

  2. Enter a name for the library and click .

  3. Enter a SAS LIBNAME for the library, then click . The Advanced Options window displays.

  4. In the Advanced Options window, click the Output tab.

  5. To preserve DBMS column names, select Yes in the Preserve column names as in the DBMS field.

  6. Click the Input/Output tab.

  7. To preserve DBMS table names, select Yes in the Preserve DBMS table names field.

  8. Click and enter the rest of the metadata as prompted by the wizard.

Enabling DBMS Name Options for an Existing Database Library

The following steps describe one way to update the existing metadata for a database library in order to specify name options. These steps are appropriate for an administrator who does not have to use the change-management facility. The steps for a user would be similar, except that the user would have to check out the library, update the metadata as described in the following steps, then check in the metadata for the library as a last step.

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

  2. Open the metadata profile that specifies the repository where metadata for the library is stored. The steps for opening a metadata profile are described in "Open a Metadata Profile" on page 58.

  3. In SAS ETL Studio, click the Inventory tab to display the Inventory tree.

  4. In the Inventory tree, expand the folders until the Libraries folder is displayed.

  5. Select the Libraries folder, then select the library whose metadata must be updated.

  6. Select File Properties from the menu bar. The properties window for the library displays.

  7. In the properties window, click the Options tab.

  8. On the Options tab, click . The Advanced Options window displays.

  9. In the Advanced Options window, click the Output tab.

  10. To preserve DBMS column names, select Yes in the Preserve column names as in the DBMS field.

  11. Click the Input/Output tab.

  12. To preserve DBMS table names, select Yes in the Preserve DBMS table names field.

  13. Click twice to save your changes.

Setting Default Name Options for Tables and Columns

You can set default name options for all table metadata that is entered with a source designer wizard or a target designer wizard in SAS ETL Studio. These defaults apply to tables in SAS format or in DBMS format.

Note

For details about these defaults as they relate to SAS tables, see "Case and Special Characters in SAS Names" on page 184.

Defaults for table and column names can make it easier for users to enter the correct metadata for tables. Administrators still have to set name options on database libraries, and users should at least verify that the appropriate name options are selected for a given table.

The following steps describe how to set default name options for all table metadata that is entered with a source designer wizard or a target designer wizard in SAS ETL Studio.

  1. Start SAS ETL Studio.

  2. Open the metadata profile that specifies the repository where metadata for the tables is stored.

  3. From the SAS ETL Studio desktop, select Tools Options from the menu bar. The Options window is displayed.

  4. In the Options window, select the General tab.

  5. On the General tab, select Enable case-sensitive DBMS object names to have source designers and target designers support case-sensitive table and column names by default.

  6. On the General tab, select Enable special characters within DBMS object names to have source designers and target designers support special characters in table and column names by default.

  7. Click to save any changes.



 < 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