Chapter 15 DBMS-Specific Features

Using data movement scripts

PowerDesigner provides the capability to generate data movement scripts to populate your AS IQ data warehouse from your other databases. The script can be used to:

• Generate a flat file for loading to the AS IQ data warehouse
• Create Insert Location statements for use with a proxy data base (for ASE and IQ only)

To create a data movement script, you must:

• Add the Data Movement IQ XEM to your AS IQ model
• Specify your data movement options
• [optional] Create a data source linked to a model of the database from which you want to draw the data to be moved
• [optional] Specify mappings between the tables in your data source and your AS IQ database
• Generate the data movement script

To add the Data Movement IQ XEM to your AS IQ model:

1. Select Model →Extended Model Definitions to open the List of Extended Model Definitions.
2. Click the Import an Extended Model Definition tool, select the Data Movement IQ and click OK to add this XEM to the model.
3. Click OK to close the List of Extended Model Definitions and return to the model.

To create a data source to populate your IQ data warehouse:

1. Create a PDM to model your source database, and ensure that it is open in your workspace.
2. In your AS IQ PDM, right-click the model name in the Browser and select New→Data Source.
3. Enter a name for the source and then click the Models tab.
4. Click the Add Models tool, and select your source model.
5. Click the ODBC tab, and complete the fields to enable an ODBC connection to your source database.
6. Complete the fields on the Data Movement tab and click OK.

Data Source properties Data Movement tab

The following fields are available on the Data Source properties sheet Data Movement tab:

To specify data movement options:

1. Right-click the model item in the Browser and select Properties from the contextual menu.
2. Click the Data Movement tab and enter the appropriate values for the model as a whole.
3. [optional] To override these global data movement options for a specific table, open its property sheet and enter table-specific values on the Data Movement tab. This tab also allows you to specify a table-specific dump file for importing into the table

Model properties Data Movement tab

The following fields are available on the Model properties sheet Data Movement tab:

Table properties Data Movement tab

The following fields are available on each Table properties sheet Data Movement tab:

To specify mappings between the tables in your data source and your AS IQ database:

1. Select Tools →Mapping Editor to open the Mapping Editor
2. Create the necessary mappings and then click OK. For detailed information about using the Mapping Editor, see the "The Mapping Editor" chapter in the General Features Guide .

To generate the data movement script:

1. Select Tools→Extended Generation to open the Generation window.
2. Specify a directory in which to generate your data movement files.
3. [optional] Click the Selection tab and specify for which Tables and/or Data Sources you want to generate a data movement script.
4. Click the Options tab and specify your data movement script generation options. You can set the following options:
   
   
   • Use Mappings – specifies whether any previously-created mappings should be used for the data movement
   • Data Movement Method – specifies which kind of script to generate. You can choose between:
     
     
     • Insert Location – [IQ or ASE only] PowerDesigner will create a loadscript for connecting the source database to the IQ server. Note that if the data source is not an IQ or ASE database, then no loadscript will be generated.
     • External File – PowerDesigner will create a dump file from the source database together with a loadscript to upload it to the IQ server.
5. [optional] Click the Generated Files tab to review the names and locations of the files to be generated.
6. Click OK to begin the generation of the data movement script.

Copyright (C) 2006. Sybase Inc. All rights reserved.