Blog

The SAS ETL Studio Interface in SAS BI

  • (4.0)
  •   |   608 Ratings
Sas ETL Studio: The Interface
 
Sas 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:
 
 
 
Tree View
 
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:
 
1. Analysis
2. Data Transforms
3. Output
4. Publish.
 
The Analysis folder in the Process Library tree includes the following templates:
 
Correlations creates an output table containing correlation statistics
Correlations – Report creates an HTML report containing summary correlation statistics
Distribution Analysis creates an output table containing a distribution analysis
Distribution Analysis–Report creates an HTML report containing a distribution analysis
Frequency an output table containing frequency information
Frequency–Report creates an HTML report containing frequency information
Summary Statistics creates an output table containing summary statistics
Summary Statistics–Report creates an HTML report containing summary statistics
Summary Tables–Report creates an HTML report containing summary tables

 

The Data Transforms folder in the Process Library tree includes the following templates:
 
Append creates a single target table by combining data from several source tables.
Apply   Standardizations  Lookup applies standardization schemes to the   source data during the transformation step.                                                                           
Create Match          Codes creates match codes for specified source columns.
Data Transfer moves data directly from one machine to another.
Data Validation cleanses data before it is added to a data warehouse or data mart.
Extract 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.
Mining Results 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.
SAS Rank ranks one or more numeric variables in the source and stores the ranks in the target.
SAS Sort reads data from a source, sorts it, and writes the sorted data to a target.
SAS Splitter 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.
SQL Join selects multiple sets of rows from one or more sources and writes each set of rows to a single target.
Standardize 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).
Transpose creates an output table containing transposed data.
User-Written Code 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
 
 
Overview Window
 
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.

Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules