The ETL process consists of all the steps necessary to extract data from different locations, transform raw operational data into consistent, high-quality business data, and load the data into a data warehouse. SAS provides all of this with the addition of an easy-to-use, metadata-driven warehouse management environment. This manual explains how to use SAS ETL Studio to specify metadata for sources, such as tables in an operational system; specify metadata for targets--the tables and other data stores in a data warehouse; and create jobs that specify how data is extracted, transformed, and loaded from sources to targets. This manual also summarizes how to set up servers, libraries, and other resources that SAS ETL Studio requires. A data warehouse for a fictional company, Orion Star Sports & Outdoors, is used to illustrate these tasks. The audience for this manual is users who are responsible for implementing data warehouses and who have a working knowledge of Base SAS software.
Author(s): SAS Publishing
Series: R
Publisher: SAS Institute
Year: 2006
Language: English
Pages: 256
Tags: Библиотека;Компьютерная литература;SAS / JMP;
Title Page
......Page 2
Cover
......Page 1
Copyright......Page 3
Contents......Page 4
Introduction......Page 8
Intended Audience for This Manual......Page 10
SAS Data Integration Studio Online Help......Page 11
About the Platform Tiers......Page 12
Process Flows and Jobs......Page 13
Intermediate Files for Jobs......Page 14
Main Software Features......Page 16
About the Main Windows and Wizards......Page 18
Overview of the Main Windows......Page 19
Metadata Profile Name......Page 20
Tree View......Page 21
Job Status Icon......Page 22
Expression Builder Window......Page 23
Open a Metadata Profile Window......Page 24
Options Window......Page 26
Process Designer Window......Page 27
Source Editor Tab......Page 28
Output Tab......Page 29
Process Library......Page 30
Java Transformations and Generated Transformations......Page 31
Source Editor Window......Page 32
Table or External File Properties Window......Page 33
View Data Window......Page 34
Overview of the Main Wizards......Page 36
New Job Wizard......Page 39
Transformation Generator Wizard......Page 41
Planning, Installation, and Setup......Page 44
Overview of Warehouse Design......Page 46
Step 2: Cleanse, Validate, and Load Data......Page 47
Planning a Data Warehouse......Page 48
Planning Security for a Data Warehouse......Page 49
Overview of Orion Star Sports & Outdoors......Page 50
Possible High-Level Questions......Page 51
Identifying Sources......Page 52
Creating the Report......Page 55
Identifying Sources......Page 56
Identifying Targets......Page 57
The Next Step......Page 58
Main Tasks for Administrators......Page 60
Overview of Installation and Setup......Page 61
Creating Metadata Repositories......Page 62
Registering Servers......Page 63
Creating a Metadata Profile (for Administrators)......Page 65
Registering Libraries......Page 66
Supporting Multi-Tier (N-Tier) Environments......Page 71
Preparation......Page 72
Deploy a Job for Scheduling......Page 73
Additional Information About Job Scheduling......Page 74
Task Summary......Page 75
About Stored Processes......Page 76
Generate a Stored Process for a Job......Page 77
Metadata Administration......Page 78
Supporting Metadata Import and Export......Page 79
Case and Special Characters in DBMS Table and Column Names......Page 80
Setting Default Name Options for Tables and Columns......Page 81
Overview of Generated Transformations......Page 82
Example: Creating a Generated Transformation......Page 83
Importing and Exporting Generated Transformations......Page 94
Additional Information About Administrative Tasks......Page 95
Creating Process Flows......Page 96
Main Tasks for Users......Page 98
Starting SAS Data Integration Studio......Page 100
Creating a Metadata Profile (for Users)......Page 101
Opening a Metadata Profile......Page 102
Main Tasks for Creating Process Flows......Page 103
Overview......Page 104
Introduction......Page 105
Creating, Running, and Verifying Jobs......Page 106
Enabling Parallel Execution of Process Flows......Page 109
Monitoring the Status of Jobs......Page 110
Working With SAS Data Quality Software......Page 111
Updating Metadata for Jobs......Page 112
Updating Metadata for Tables or External Files......Page 113
Updating Metadata for Transformations......Page 115
Setting Name Options for Individual Tables......Page 116
View Data for a Table or External File in a Process Flow......Page 117
Viewing Metadata for Jobs......Page 118
Viewing Metadata for Tables and External Files......Page 119
About Change Management......Page 120
Checking Out Existing Metadata......Page 121
Checking In Metadata......Page 122
OLAP Capabilities in SAS Data Integration Studio......Page 123
Additional Information About User Tasks......Page 124
Sources: Inputs to SAS Data Integration Studio Jobs......Page 126
Start SAS Data Integration Studio and Open the Appropriate Metadata Profile......Page 127
Select the SAS Source Designer......Page 128
Select the Library That Contains the Tables......Page 129
Select the Tables......Page 130
Specify a Custom Tree Group......Page 131
Save the Metadata for the Tables......Page 132
Preparation......Page 133
Start SAS Data Integration Studio and Open the Appropriate Metadata Profile......Page 134
Select an External File Source Designer......Page 135
Specify Location of the External File......Page 136
Set Delimiters and Parameters......Page 137
Define the Columns for the External File Metadata......Page 138
View the External File Metadata......Page 144
Next Tasks......Page 145
Targets: Outputs of SAS Data Integration Studio Jobs......Page 146
Preparation......Page 147
Select the Target Table Designer......Page 148
Select Column Metadata from Existing Tables......Page 149
Specify Column Metadata for the New Table......Page 151
Specify Physical Storage Information for the New Table......Page 152
Specify a Custom Tree Group for the Current Metadata......Page 153
Check In the Metadata......Page 154
Next Tasks......Page 155
Using Jobs to Create Process Flows......Page 156
Preparation......Page 157
Create the New Job and Specify the Main Process Flow......Page 158
(Optional) Reduce the Amount of Data Processed by the Job......Page 160
Configure the SQL Join Transformation......Page 162
Configure the Loader Transformation......Page 168
Run the Job and Check the Log......Page 169
Add the Publish to Archive Transformation to the Process Flow......Page 170
Run the Job and Check the Log......Page 173
Preparation......Page 174
Create and Populate the New Job......Page 175
Configure the Data Validation Transformation......Page 177
Run the Job and Check the Log......Page 179
Verify Job Outputs......Page 180
Preparation......Page 181
Create and Populate the New Job......Page 182
Configure the PrintHittingStatistics Transformation......Page 183
Run the Job and Check the Log......Page 185
Check In the Metadata......Page 186
Optimizing Process Flows......Page 188
Choosing Between Views or Physical Tables......Page 189
Managing Columns......Page 190
Managing Disk Space Use for Intermediate Files......Page 191
Minimizing Remote Data Access......Page 192
Using Transformations for Star Schemas and Lookups......Page 193
Introduction to Analyzing Process Flow Performance......Page 194
Simple Debugging Techniques......Page 195
Using SAS Logs to Analyze Process Flows......Page 196
Adding Debugging Code to a Process Flow......Page 198
Analyzing Transformation Output Tables......Page 199
SCD Concepts......Page 202
Type 2 SCD Dimensional Model......Page 203
Transformations That Support SCD......Page 205
About the SCD Type 2 Loader Transformation......Page 206
Preparation......Page 211
Create and Populate the Job......Page 212
Add SCD Columns to the Dimension Table......Page 213
Specify the Primary Key for the Dimension Table......Page 214
Specify the Business Key for the SCD Loader......Page 215
Specify the Generated Key for the SCD Loader......Page 216
Set Up Change Tracking in the SCD Loader......Page 217
Set Up Change Detection in the SCD Loader......Page 218
Run the Job and View the Results......Page 219
Check In the Metadata......Page 220
Appendixes......Page 222
Overview of the Process Library......Page 224
Analysis Folder......Page 225
Data Transforms Folder......Page 226
Output Folder......Page 227
Additional Information About Process Library Transformations......Page 228
Methods of Customizing or Replacing Generated Code......Page 230
Modifying Configuration Files or SAS Start Commands......Page 231
Specifying Options for Transformations......Page 232
Replacing the Generated Code for a Transformation with User-Written Code......Page 233
Adding a User-Written Code Transformation to the Process Flow for a Job......Page 234
Adding a Generated Transformation to the Process Library......Page 235
Recommended Reading......Page 236
Glossary......Page 238
Index......Page 246
