The SAS ETL Studio Interface in SAS BI
Sas ETL Studio: The Interface
ETL Studio is a Java client developed to control the ETL process. The interface has several “ease-of-use” features including
1. copy and paste in any text field
2. multiple windows can be open at one time (including multiple process flow diagrams)
3. Windows look and feel
4. wizard-driven interfaces.
Tools, Menus, and Online Help
SAS ETL Studio takes full advantage of toolbars and pull-down menus. The icons available on the toolbar depend on which window is active from within the interface.
The Shortcut Bar
One of the most significant features of SAS ETL Studio is the new process-driven functionality. Processes are available via a Shortcut bar on the far left side of the main SAS ETL Studio window.
The Shortcut bar is populated with icons for each task an ETL user would typically perform, including:
The SAS ETL Studio Tree View enables you to
1. view the metadata associated with the current metadata repository
2. display different views or “trees” of the current repository.
There are several tabs available in the tree view area:
The items in the Inventory tree view are grouped according to a set of predefined groups that an ETL user will typically define from within SAS ETL Studio (tables, OLAP cubes, jobs, and so on).
The items in the Custom tree view are grouped according to whatever groups the ETL user has defined (Source Tables, Target Tables, Sales Cubes, and so on). These groups could be determined by object types, intended uses for the objects, data marts, lines of business, and so on.
Selecting one of the transformations creates a template in the process view, with “drop zones” into which the user may drag-and-drop items.
Addititional tabs may be available in the tree view area depending on the selected options and settings.
Process Library Tree
The Process Library tree displays a collection of transformation templates. There are four collections (folders) of templates that are provided with SAS ETL Studio:
2. Data Transforms
The Analysis folder in the Process Library tree includes the following templates:
||creates an output table containing correlation statistics
|Correlations – Report
||creates an HTML report containing summary correlation statistics
||creates an output table containing a distribution analysis
||creates an HTML report containing a distribution analysis
||an output table containing frequency information
||creates an HTML report containing frequency information
||creates an output table containing summary statistics
||creates an HTML report containing summary statistics
||creates an HTML report containing summary tables
The Data Transforms folder in the Process Library tree includes the following templates:
||creates a single target table by combining data from several source tables.
||Lookup applies standardization schemes to the source data during the transformation step.
|Create Match Codes
||creates match codes for specified source columns.
||moves data directly from one machine to another.
||cleanses data before it is added to a data warehouse or data mart.
||selects multiple sets of rows from a source and writes those rows to a target.
|Fact Table Lookup
||loads source data into a fact table and translates business keys into generated keys.
|Key Effective Date
||enables change tracking in intersection tables.
||integrates a SAS Enterprise Miner model into a SAS ETL Studio data warehouse. Typically used to create target tables from a SAS Enterprise Miner model.
|Return Code Check
||provides status-handling logic at a desired point in the process flow diagram for a job.
||ranks one or more numeric variables in the source and stores the ranks in the target.
||reads data from a source, sorts it, and writes the sorted data to a target.
||selects multiple sets of rows from one source and writes each set of rows to a different target.
|SCD Type 2 Locator
||loads source data into a dimension table, detects changes between source and target rows, updates change tracking columns, and applies generated key values. This transformation implements slowly changing dimensions.
||selects multiple sets of rows from one or more sources and writes each set of rows to a single target.
||an output table containing data standardized to a particular number.
|Surrogate Key Generator
||loads a target, adds generated whole number values to a surrogate key column, and optionally sorts and saves the source based on the values in the business key column(s).
||creates an output table containing transposed data.
||retrieves a user-written transformation.
The Output folder in the Process Library tree contains the following template:
The Publish folder in the Process Library tree includes the following templates:
|Publish to Archive
||creates an HTML report and an archive of the report
|Publish to Email
||creates an HTML report and emails it to a designated address
|Publish to Queue
||creates an HTML report and publishes it to a queue using MQSeries
Process Designer View
The Process Designer window is the workspace for building ETL processes. The Process Designer view appears as a final step in the Process Designer wizard.
Once the process is defined, the Process Designer view is populated with icons that represent the chosen processes.
The Process Designer window can be used to
1. view SQL source code
2. review the SAS log (from submitting jobs)
3. view the resulting output from running a SAS job.
Process Designer and Overview Windows
The Overview window shows you the complete process from the process view.
From within the Overview window, you can control which part of the process is displayed in the Process View window.