SAS 9.1.3 ETL Studio: Users Guide

SAS ETL Studio 9.1.3

Features that are new in SAS ETL Studio 9.1.3 include the following:

• Support for the SAS Scalable Performance Data Server (SPD Server). The SAS 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 can be faster and more flexible than other storage options, including the SAS SPD Engine.

• Two new transformation templates provide additional support for slowly changing dimensions (SCD):

  • Unlike the SCD Type 2 transformation, which tracks changes to a table's descriptive attributes, the Key Effective Date transformation tracks changes in a table's keys. The Key Effective Date transformation can be used to track changes in a table that does not have descriptive attributes, such as an intersection table, which is a table that describes the relationships between two or more tables.

    For example, suppose that you have a table of users, called USERS, and a table of groups, called GROUPS. An intersection table called USERS_X_GROUPS could describe the many-to-many relationships between USERS and GROUPS. The Key Effective Date transformation would use date ranges (beginning and end dates) to detect when a new key combination has been entered in USERS_X_GROUPS.

  • The Surrogate Key Generator transformation enables you to create a unique identifier for records, a surrogate key. The surrogate key can be used to perform operations that would be difficult or impossible to perform on the original key. For example, a numeric surrogate key could be generated for an alphanumeric original key, to make sorting easier.

• The "Redeploy Jobs to Stored Process" feature rebuilds all stored processes that are associated with SAS ETL Studio jobs. Use this feature when you update a job for which a stored process has been generated. You can also use this feature when the computing environment changes, such as when a metadata repository is promoted from a test environment to a production environment, for example.

• The property window for the Mining Results transformation was updated to make it easier to select inputs and outputs.

Additional Information

For more details about the features that are new in SAS ETL Studio 9.1.3, perform the following steps:

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

2. From the menu bar, select Help Contents. The main Help window displays. The default Help topic is Introduction to SAS ETL Studio.

3. In the default Help topic, select What's New for SAS ETL Studio. The features that are new in SAS ETL Studio 9.1.3 are described in this topic.

SAS ETL Studio 9.1.2

Features that were new in SAS ETL Studio 9.1.2 include the following:

• Support for slowly changing dimensions. SAS ETL Studio enables you to track changes to data that occur over time. You can then analyze those changes and forecast the impact on future business decisions. For example, a company that provides long-distance telephone service could determine the group of customers that was most likely to move to a different company. Those customers could be targeted in a marketing campaign and offered an incentive to retain their current provider.

  SAS ETL Studio provides two new transformations, SCD Type 2 Loader and Fact Table Lookup, that enable you to track changes and retain historical records. With the SCD Type 2 Loader, you can load dimension tables and detect changes in source data, add change tracking information, and generate primary key values. (See "Example: Using Slowly Changing Dimensions" on page 145.) With Fact Table Lookup, you can map source columns into fact tables, make use of translation tables, and specify responses to the detection of missing values.

• Better status handling for ETL process flows. The status of a SAS ETL Studio job or a transformation within a job can be automatically sent in an e-mail, written to a file, or sent to an event broker that will pass the status code to another application. You can also use status code handling to capture job statistics, such as the number of records before and after the append of the last table loaded in a job.

• Easier data validation for ETL process flows. The new Data Validation transformation enables you to identify and act on duplicate values, invalid values, and missing values. You can also develop your own validation process that translates source values using expressions or translation tables.

• Export and import of SAS ETL Studio jobs. You can export jobs from SAS ETL Studio and then use SAS ETL Studio to import these jobs into the same metadata repository or into a different repository. The jobs are exported to a file in XML format.

• Support for stored processes. You can save SAS ETL Studio jobs to a file for the SAS Stored Process Server to execute later.

• Impact analysis and reverse impact analysis (data lineage). SAS ETL Studio enables you to identify the tables, columns, and transformations that would be affected by, or which have an impact on, a selected table or column. You can view a diagram and a report that show the affected jobs, transformations, tables, and columns.

• Support for SAS Enterprise Miner scoring models. The new Mining Results transformation enables you to read the metadata from a SAS Enterprise Miner scoring model and create an output table that applies the model to the source data.

• Support for table concatenation and N-to-one column mapping. The new Append transformation enables you to create a single target by appending (concatenating) two or more sources. The Append transformation supports N-to-one column mapping.

• Support for using a physical table to update that metadata for the table. If a change is made directly to a physical table, you can use the Update Table Metadata feature to update table metadata so that it matches the physical table.

• Support for optional macro variables in the code that SAS ETL Studio generates for a job. The variables enable SAS ETL Studio jobs to access the metadata server and retrieve relevant metadata. For example, the code that is generated for a job might access the metadata server to retrieve an event code that is defined in a repository.

• Source designer wizards enable you to specify the Custom tree group to which the new table metadata should belong.

• In the SAS ETL Studio tree view, if you select the metadata for a library, you can display its LIBNAME. If metadata has been defined for any tables in a library, you can expand the library and view its table metadata.

Additional Information

For more details about the features that were new in SAS ETL Studio 9.1.2, perform the following steps:

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

2. From the menu bar, select Help Contents. The main Help window displays. The default Help topic is Introduction to SAS ETL Studio.

3. In the default Help topic, select Understanding SAS ETL Studio. The features that were new in SAS ETL Studio 9.1.2 are described in this topic.