New Data Flow or SQL Script wizard

Data Flow / SQL Script

Data Warehousing Project

You must select the data warehousing project that you want to use. The generated data flow or SQL script is created in the Data Flows folder or SQL Scripts folder of the selected data warehousing project. The generated data flow has the same SQL execution database type as your profile.

Name

You can create a data flow or an SQL script, or you can create both.

Specify a name for the flow or the script to be created. If a data flow or an SQL script with the same name already exists, it is replaced.

Online mode or offline mode

You can create a data flow in online mode or in offline mode. In online mode, you must be connected to a database. In offline mode, you are working with a physical data model.

If you want to work with a database, you must select the database connection on the next page of the wizard.

Flow Details

In the data flow to be created, you can include a Target Table operator by selecting the appropriate check box.

If you include a Target Table operator in the data flow, you must specify the following parameters:

Target Table

You can reuse an existing table, or you can create a new table.

If you reuse an existing table, you must specify the name and the schema of the table. If you create the data flow in offline mode, you must also specify the data model.

You can view a list of schemas and tables in the database or in the selected data model by clicking Browse.

If you reuse a table, it must be compatible with the result table that is specified in the profile. This means that every column of the result table must also exist in the reused table with the same name and the same data type. If the reused table includes additional columns that do not exist in the result table, these columns are filled with NULL values. Therefore these columns must be nullable.

You can create a new table by specifying a name, a schema, and a table space for the table. If you create the data flow in offline mode, you must also specify the data model the table is to be created in.

In online mode, you can specify to create the new table after pressing Finish or when executing the generated flow. In offline mode, the table is created during the execution of the generated flow.

Execution Details

If you selected to create a Target Table operator, you can specify more parameters for the data flow. For example, you can specify whether the target table is not to be logged initially.

If the data flow is going to process thousands of rows, you can specify to run a commit operation every time after a particular amount of rows is processed. If you do not want to use this option, set the commit count to 0.

Script Details

In the SQL script to be created, you can include a SELECT statement or you can additionally create a table in which the data is to be inserted. You can also create a view.

Creating an INSERT statement

You can create an INSERT statement by specifying the target table and the execution details.

Target Table

You can reuse an existing table or create a new table.

You can reuse an existing table by specifying the data model and the name and the schema of the table. You can view a list of schemas and tables in the selected data model by clicking Browse.

If you reuse a table, it must be compatible with the result table that is specified in the profile. This means that every column of the result table must also exist in the reused table with the same name and the same data type. If the reused table includes additional columns that do not exist in the result table, these columns are filled with NULL values. Therefore these columns must be nullable.

You can create a new table by specifying the data model and the table space the table is to be created in and a name and a schema for the new table.

Execution Details

You can determine whether the target table is not to be logged initially by selecting the appropriate check box.

Creating a view

You can create a view by specifying a database model and a name and a schema for the view. You can view a list of schemas and tables in the selected data model by clicking Browse. If the specified view already exists in the database model, a DROP statement is added to the view to drop and recreate the specified view.

To create the data flow, the SQL script, or both, click Finish. Depending on your specification, the Data Flow editor, the SQL Query Builder, or both are opened.