Enter Metadata for Libraries

 < Day Day Up > 



In SAS software, a library is a collection of one or more files that are recognized by SAS and that are referenced and stored as a unit. SAS ETL Studio uses a combination of server metadata and library metadata to access the sources and targets that are referenced in SAS ETL Studio jobs. Accordingly, one of the first tasks for an administrator might be to specify metadata for the libraries that contain data stores or other resources.

Both SAS Management Console and SAS ETL Studio enable you to enter metadata for libraries. A typical approach would be for administrators to use SAS Management Console to add metadata for an initial set of libraries. SAS ETL Studio users would then use source designer wizards or target designer wizards to add metadata about specific tables in a library. Later, administrators and/or users could add metadata for other libraries as needed.

Note

Entering metadata for a library does not, in itself, provide access to tables in the library. You must also specify metadata for all tables that you want to access in the library, as described in "Using Source Designers" on page 61 and "Using Target Designers" on page 63.

Which Libraries Are Needed?

Administrators should ask questions such as these to determine which libraries are needed for a given data warehouse:

  • In what format are the source tables and target tables? Are they SAS files, Microsoft Excel files, DBMS tables, flat files, enterprise application files, or files in which values are separated with commas or other characters?

  • If the tables are in SAS format, do the tables use column formats that are defined in a SAS format library?

  • If the tables are in SAS format, will SAS/SHARE software be used to provide concurrent update access to the tables?

  • If the tables are not in SAS format, how do you plan to access these tables? With a database library (SAS/ACCESS software for relational databases)? With an ODBC library (SAS/ACCESS for ODBC)? With the external file interface? With an enterprise application library (such as a library that uses SAS/ACCESS to R/3)?

Answers to questions such as these determine the kind of library metadata that you need to enter.

Libraries for the Example Warehouse

For the example data warehouse, assume that most data sources and targets are in Base SAS format, and that some of these tables use custom column formats that are stored in a SAS library. Accordingly, metadata for the following Base SAS libraries must added to the foundation repository:

  • one or more Base SAS libraries for data sources

  • one or more Base SAS libraries for data targets.

The general steps for entering library metadata are described in "Enter Metadata for a Library" on page 48. You do not need to enter metadata for a library that contains SAS formats, but this library must be properly set up. See "Libraries for Custom SAS Formats" on page 45.

Assume that some of the source data for the example data warehouse is in comma-delimited files, and that the external file interface will be used to access these files. See "External Files" on page 47.

Base SAS Libraries

To access tables in Base SAS format, metadata for the appropriate SAS libraries must be defined and saved to a metadata repository. To access the tables, SAS ETL Studio will use the default SAS application server or the server that is specified in the metadata for the library. The general steps for entering library metadata are described in "Enter Metadata for a Library" on page 48.

SAS/SHARE Libraries

A SAS/SHARE server enables multiple users to access a library concurrently. To access tables that are under the control of a SAS/SHARE server, metadata for the SAS/ SHARE server and a SAS/SHARE library must be defined and saved to a metadata repository.

SAS SPD Server Libraries

The SAS Scalable Performance Data (SPD) Server is a high-performance, multi-user, parallel-processing data server with a comprehensive security infrastructure, backup and restore utilities, and sophisticated administrative and tuning options. The SAS SPD Server stores data in a special format that facilitates parallel processing. You can use the SAS SPD Server to access tables in SAS SPD Server format, using a special SAS library that is designed for this purpose.

To use the SAS SPD Server to access tables in SAS SPD Server format, metadata for the SAS SPD server and a SAS SPD Server library must be defined and saved to a metadata repository.

SAS SPD Engine Libraries

The SAS Scalable Performance Data (SPD) Engine is included with Base SAS. It is a single-user data storage solution that shares the high performance, parallel processing, and parallel I/O capabilities of SAS SPD Server for managing large data volumes, but without the additional complexity of a full server. The SAS SPD Engine can read and write data stores in SPD Server format.

To use the SAS SPD Engine to access tables in SAS SPD Server format, metadata for a SAS SPD Engine library must be defined and saved to a metadata repository. To access the tables, SAS ETL Studio will use the default SAS application server or the server that is specified in the metadata for the library.

Libraries for Custom SAS Formats

A format is an instruction that SAS uses to write data values. You use formats to control the written appearance of data values, or, in some cases, to group data values together for analysis. Some SAS tables use custom column formats that are stored in a SAS library.

Note

You do not need to enter metadata for a library that contains custom SAS formats. However, if a table uses custom formats that are stored in a SAS library, the library of formats must be available to the SAS application server that is used to display data in the table or to execute code for the table.

For details about setting up a SAS format library, see the Post-Configuration Tasks chapter in the SAS Intelligence Platform: Planning and Administration Guide.

DBMS Libraries

To access tables in a DBMS such as Oracle or DB2, metadata for the DBMS server and the appropriate DBMS library must be defined and saved to a metadata repository. See also the following sections about ODBC libraries and OLE DB libraries.

ODBC Libraries

Open Database Connectivity (ODBC) is an application programming interface (API). ODBC provides a standard interface for SQL databases. An application that uses the ODBC interface can connect to any database that has an ODBC driver, such as Microsoft Access, Microsoft Excel, Borland dBase, or IBM DB2. Use ODBC libraries when an interface for a specific DBMS is not available, and the DBMS complies with ODBC.

To use ODBC to access tables, the following requirements must be met:

  • The appropriate ODBC driver must be available on the computer where the database resides.

  • Metadata for the ODBC database server must be available in a current metadata repository.

For example, assume that you want to access tables in a Microsoft Access database and the database resides on a computer with the Microsoft Windows XP Professional operating system. You could use the ODBC Data Source Administrator administrative tool to define Microsoft Access as a system data source on that computer. You might create a system data source called msdb, for example. (A system data source can be more useful than a user data source because it is available to all users and to NT services on that computer.)

After the ODBC data source has been defined, an administrator could use SAS Management Console to enter metadata for the ODBC database server. The ODBC database server is the computer where the ODBC-compliant database resides. The metadata for the ODBC database server includes the network address for that computer, as well as the relevant ODBC driver (such as the msdb data source) on that computer. For details about entering metadata for servers, see the online Help for the Server Manager plug-in to SAS Management Console.

OLE Libraries

OLE DB is a Microsoft API for access to different data sources. An OLE library uses the SAS/ACCESS interface for OLE DB providers. Use OLE libraries when an interface for a specific DBMS is not available and the DBMS complies with OLE DB.

Libraries for Enterprise Applications

Optional data surveyor wizards can be installed in SAS ETL Studio that provide access to the metadata in enterprise applications such as PeopleSoft and SAP R/3. See the documentation for these wizards for details about any servers and libraries that must be defined to support the wizards.

External Files

An external file is a file that is maintained by the machine operating environment or by a software product other than SAS software. A flat file with comma-separated values is an example.

External files can be accessed in at least two ways.

  • External File source designer. The External File source designer is a wizard that guides you through the steps that are required to create and execute a SAS ETL Studio job. The job extracts information from an external file and writes it to a SAS table. Typically, the SAS table is used as a source table in another SAS ETL Studio job. The current External File source designer can extract information from flat files in fixed or delimited format. Supported file types are TXT, DAT, and CSV. See "Example: Extracting Information from a Flat File" on page 78.

  • ODBC library. ODBC libraries are used to read and write ODBC-compliant files as if they were SAS files. They provide interactive access to ODBC-compliant files. ODBC libraries are useful for applications that comply with ODBC, such as Microsoft Excel. The general steps for entering library metadata are described in "Enter Metadata for a Library" on page 48. In the first window of the New Library wizard, select the ODBC icon.

After an ODBC library has been created, SAS ETL Studio users can display the ODBC source designer and use it to enter metadata about the tables within the library. The general steps for using source designers are described in "Using Source Designers" on page 61.

Generic Libraries

When you display the New Library wizard from the SAS ETL Studio desktop, the first page of the wizard enables you to select the kind of library that you want to create. If you cannot find an exact match for the kind of data that you want to access, you can select a Generic library.

A Generic library enables you to manually specify a SAS engine and the options that are associated with that engine. Because it is general by design, the Generic library offers few hints as to what options should be specified for a particular engine. Accordingly, the Generic library might be most useful to experienced SAS users. For details about the options for a particular engine, see the SAS documentation for that engine.

Note

SAS has a number of library templates for specific data formats. The specific library templates will often give better results than the generic template, which has not been optimized for particular data formats. Use the templates for a specific format whenever possible.

Microsoft Excel and Microsoft Access Files

See "External Files" on page 47.

XML Files

To provide access to one or more tables that are defined in an XML file, you could create a Generic library and specify options that are appropriate for the XML LIBNAME engine and the XML file. After the Generic library (with the XML options) is registered in a metadata repository, SAS ETL Studio users can use the Generic source designer to generate metadata for the tables that are defined in the XML file.

Enter Metadata for a Library

The New Library wizard in SAS Management Console and SAS ETL Studio enables you to enter metadata about many different kinds of libraries. For details about entering metadata for different kinds of libraries, administrators should see the Managing Libraries chapter in the SAS Management Console: User's Guide.

The following steps summarize how to use SAS ETL Studio to enter metadata about a 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. 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 new library should be 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 File New from the menu bar. The New Library wizard displays.

  6. In the New Library wizard, expand the folders to view the folder for the kind of library for which you want to enter metadata. (The wizard includes folders for Database Libraries, Enterprise Application Libraries, and SAS Libraries, for example.)

  7. Expand the folder for the kind of library for which you want to enter metadata, such as SAS Libraries.

  8. Select the particular kind of library for which you want to enter metadata, such as SAS Base Engine Library and click .

  9. Enter metadata as prompted by the wizard.

After the metadata for a library has been entered and saved, it is available for use in SAS ETL Studio. For example, most source designer wizards and target designer wizards will prompt you to select the library that contains or will contain a given source table or target table.

Preassigned Libraries

It is possible to assign a SAS library to a server so that the library is assigned whenever the server is started. Such a library is said to be preassigned. Preassigned libraries are used whenever you want a SAS library to be available in the current session without explicitly assigning the library during the session.

For example, suppose that you wanted to use the View Data feature to display a table that contains custom SAS formats. The SAS library that contains the formats can be preassigned to the SAS application server that is used to access the table.

Some of the tasks that are associated with preassigning a SAS library must be done outside of SAS ETL Studio or SAS Management Console. For details, see the Post-Configuration Tasks chapter of the SAS Intelligence Platform: Planning and Administration Guide.

"Library is Preassigned" Check Box

The properties window for a library includes a Library is Preassigned check box. To display this check box, perform the following steps:

  1. From the SAS ETL Studio desktop, in the Inventory tree, open the Libraries folder and select the library that you want to view or update.

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

  3. Select the Options tab, then click the button. The Advanced Options window is displayed.

  4. Select the Pre-Assign tab. The Library is Preassigned check box is on that tab.

Note

Selecting the Library is Preassigned check box does not preassign the library. The check box indicates that the library has been pre-assigned, using the methods that are described in the Post-Configuration Tasks chapter of the SAS Intelligence Platform: Planning and Administration Guide.

Additional Information about Libraries

The online Help for SAS ETL Studio contains additional information about libraries. To display the relevant Help topics, perform the following steps:

  1. From the SAS ETL Studio menu bar, select Help Contents. The online Help window displays.

  2. In the left pane of the Help window, select Prerequisites Specifying Metadata for Libraries.



 < 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