Before using data from an SQL database to create a rules file or perform a data load or dimension build, you must connect to the database.
In the Open SQL Data Sources dialog box, you specify the information needed to connect to the database. After you connect, you can view the contents of specified tables in Data Prep Editor.
In the Data Source Name group, you select an option:
SQL data sources—Select a type.
Substitution Variables—Select a substitution variable that is of the type that you selected in “SQL data sources.”
Oracle Call Interface (OCI) Service Name—Select an Oracle database, using the format $OCI$<server_name>.
No SQL Data Source—Either there is no data source for this rules file, or remove previously-set SQL data source information from this rules file.
In the Connect group, you enter connection information, such as database name, server name, application name, username, or password. Different databases require different information.
In the following boxes, you enter the following items, either directly or by clicking the Open button and entering information in a larger window:
Select—The SQL select statement
The select statement specifies the data to retrieve from the SQL database. The default value is * (which selects all columns in a row).
From—The directory path that specifies the location of the preferred SQL database or table
Where—The SQL where clause
The where clause determines the conditions that must be met to retrieve the data. By default, the where clause is * (which selects all rows in the table).
Note: | In the Select, From, and Where boxes, you can enter substitution variables, rather than "field=value" strings. When entering a substitution variable, place an ampersand (&) before the variable name. |
You conclude the connection setup by clicking one of the following buttons:
OK/Retrieve—To connect to the SQL database immediately
OK/Save—To save your settings without connecting to the SQL database