WarehouseArchitect - User's Guide

Home

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 13: Creating Links to Operational Systems

Editing extraction expressions

Each time you create a link between a column in the WAM and an external column, you create an extraction expression. This expression corresponds to a SQL query that selects data from the external column and places it in the column in the data warehouse.

When you have linked all the columns in the WAM to columns in external databases, you concatenate all the extraction expressions and generate an extraction script.

Editing an extraction expression for a table

A table extraction expression is a SELECT query that lists the columns to select from tables in an external database.

You must create an extraction expression for each WAM table, for each external database to which the table is attached.

In a table extraction expression, you can use the following variables:

Variable

Description

%TABLE%

Name or code of the WAM table to which the column belongs (based on display preferences)

%TNAME%

Name of the WAM table to which the column belongs

%TCODE%

Code of the WAM table to which the column belongs

%EXTDTBS%

Name or code of the external database (based on display preferences)

%EXTDNAME%

Name of the external database

%EXTDCODE%

Code of the external database

%@SELECT%

List of expressions defined for each column

%@FROM%

List of all external tables linked to the WAM table

Variable	Description
%TABLE%	Name or code of the WAM table to which the column belongs (based on display preferences)
%TNAME%	Name of the WAM table to which the column belongs
%TCODE%	Code of the WAM table to which the column belongs
%EXTDTBS%	Name or code of the external database (based on display preferences)
%EXTDNAME%	Name of the external database
%EXTDCODE%	Code of the external database
%@SELECT%	List of expressions defined for each column
%@FROM%	List of all external tables linked to the WAM table

To edit an extraction expression for a table:
1. Double-click a table symbol in the WAM.
  The table property sheet appears.
2. Click the Sources button.
  The External Sources dialog box appears.
  The Destination tree shows the WAM table and its columns. By default, the WAM table is selected.
  The Source tree shows the external databases to which the WAM table is attached.
3. In the Source tree, select the external database for which you want to edit the table extraction expression.
  The extraction expression for the WAM table for the selected external database appears in the Expression box.
4. Edit the expression in the Expression box.
  or
  Click the Zoom button and use the script editor functions.
5. Click OK in each dialog box.

Editing an extraction expression for a column

In a column extraction expression, you can use the following variables:

Variable	Description
%TABLE%	Name or code of the WAM table to which the column belongs (based on display preferences)
%TNAME%	Name of the WAM table to which the column belongs
%TCODE%	Code of the WAM table to which the column belongs
%EXTDTBS%	Name or code of the external database (based on display preferences)
%EXTDNAME%	Name of the external database
%EXTDCODE%	Code of the external database
%COLUMN%	Name or code of the column

To edit an extraction expression for a column:
1. Double-click a table symbol in the WAM.
  The table property sheet appears.
2. Click the Sources button.
  The External Sources dialog box appears.
  The Destination tree shows the WAM table and its columns. By default, the WAM table is selected.
3. Select the WAM column whose extraction expression you want to edit.
4. Select the external database for which you want to edit the column extraction script.
  The column extraction expression appears in the Expression box.
5. Edit the expression in the Expression box.
  or
  Click the Zoom button and use the script editor.
6. Click OK in each dialog box.

Using the script editor

Using the script editor, you can insert the following categories of script language into a customized script:

Category

What it inserts in the script

Tables

Table code

Functions

DBMS-specific function

Operators

Logical operator

External Tables

External table code

External Columns

External column code

External Databases

External database code

Category	What it inserts in the script
Tables	Table code
Functions	DBMS-specific function
Operators	Logical operator
External Tables	External table code
External Columns	External column code
External Databases	External database code

To use the script editor:
1. Double-click a table symbol in the WAM.
  The table property sheet appears.
2. Click the Sources button.
  The External Sources dialog box appears.
3. Expand nodes in the Source tree to display the object for which you want to edit the extraction script.
4. Select an external table or an external database to edit a table extraction script.
  or
  Select an external column to edit a column extraction script.
  The extraction expression appears in the Expression box.
5. Click the Zoom button.
  The script editor window appears.
6. Click the position in the definition textbox where you want to insert the script.
7. Select a category from the Category list.
  For example, select Table to display a list of tables.
8. Select an item from the list for that category.
9. Click the Add button.
  The selected item is inserted at the cursor position.
10. Click OK in each of the dialog boxes.

Previewing an extraction expression

You can preview extraction expressions before you generate them. The preview shows the generated contents of the script with instanciated variables.

To preview an extraction expression:
1. Double-click a table symbol in the WAM.
  The table property sheet appears.
2. Click the Sources button.
  The External Sources dialog box appears.
3. Select an external object in the Source tree.
  The extraction expression for the external database appears in the Expression box.
4. Click the Preview button.
  The preview window shows the corresponding command for the target DBMS.
5. Click Close.
6. Click OK.

Making an expression invalid

An expression becomes invalid when you break the link between an object in the WAM and an external object. A list of invalid expressions appears when you check the model before generating an extraction script.

An expression automatically becomes invalid when you modify or delete a table or column in the WAM.

You can also invalidate an expression manually. When you do this, the expression remains visible, although it is no longer linked to the item.

To make an expression invalid:
1. Double-click a table symbol in the WAM.
  The table property sheet appears.
2. Click the Sources button.
  The External Sources dialog box appears.
3. Select an external object in the Source tree.
  The extraction expression for the external object appears in the Expression box.
4. Select the Invalid Expression checkbox.
  This renders the expression invalid for the item. The expression remains visible even though it is no longer attached to the item. To make the expression valid again for the item, you must check the Invalid Expression checkbox.
5. Click OK.


	Expand Search