Tutorial 1: Load Data from Samples Folder

Tutorial 1 teaches how to load the data from extract data (XML) files in the Samples folder.

After completing this tutorial, users will be able to do the following:

• Start Related Loader.
• Create a Related Loader specification.
• Select the source for a new load specification.
• Create a Related Loader target database connector.
• Review the Load Specification notebook tabs.
• Run a load.
• Save the Related Loader specification.

 Refer to the Related Loader online help for detailed information about using Related Loader.

Database Description

First Load File

Target Database Name: fileaidex_tutorial

Load File and Location:

<install directory>:\Program Files\Compuware\File-AID_EX\Samples\ tutorialExtract_1\META\XML

Schema Name: dbo.category

Driving Table Name: category

Table Names: customer, shipper, orders, category, item, ordereditem, authors, supplier, employee

 Second Load File

Target Database Name: fileaidex_tutorial

Load File and Location:

<install directory>:\Program Files\Compuware\File-AID_EX\Samples\ tutorialExtract_2\META\XML

Schema Name: guest.authors

Driving Table Name: authors

Table Names: category, item, customer, shipper, orders, ordereditem, authors

Create the Target Database

Create a target database called fileaidex_tutorial. This database must be created before the information in this tutorial can be used and saved. The sample tutorials in this chapter were completed using Microsoft SQL Server, however any of the File-AID/EX-supported databases can be used. Refer to the Release Notes for a list of supported databases.

Select the Source for a New Load Specification - Part 1

1. Launch Related Loader like any other application based on your operating system. The Related Loader wizard launches.
2. From the File menu, select New or click New on the toolbar. The Create New Load Specification dialog box appears.
3. From the Load Input Specification Type list, select Extract Data File.
4. From the Path and File Name field, click Browse. The Select Extract File dialog box appears.
5. Navigate to <install directory>\Samples\tutorialExtract_1\META.
6. Double-click the XML file. The Create New Load Specification dialog box reappears with the path and file name populated.
7. Click OK. The Connect to Target Database dialog box appears.
8. In the Connectors list, select <new>.

9. From the Connector Type list, select the connector type that corresponds to the database used to create the target database above. The dialog box repopulates to display fields appropriate to the connector type selected.

   This tutorial uses MS SQL/JDBC. If another database type is chosen, the fields may vary.

10. In the Connector Name field, enter a name for the connector.

11. In the Server field, enter the machine name of the machine being used.

    Warning

    Important

    The machine name can be found in the Repository View of the File-AID/EX Homebase window.

12.  In the Database Name field, enter fileaidex_tutorial.
13. In the Port Number field, enter 1433.
14. In the User ID field, enter the database user ID.
15. In the Password field, enter the database password.
16. Click Connect to verify the database connection. A connection failed message appears if the connection information is incorrect; otherwise the connection was successful. Contact the system administrator if assistance is needed.

17. To save this connector for future use, click Save As Template.

    Warning

    Important

    This connection template is used in future tutorials.

18. Click Finish. The Generate Constraint Names dialog box appears.
19. Click Yes, which will result in the following:

• 
  
  • Unique constraint names are generated when the load is run if any target tables do not exist.
  • Primary keys begin with a K, followed by the name of the source table, and end with a number.
  • Foreign keys begin with an F, followed by the name of the source table, and end with a number.
  • Indexes begin with an I, followed by the name of the source table, and end with a number.

The Load Specification notebook appears showing the source and target tables.

Specify the Tables, Column Mapping, and Load Method

There are four tabs along the right side of the Load Specification notebook. The Table tab appears first. The Table tab is a graphical display of the source tables and the target tables into which data is loaded from the XML file. Here, initially, the source and target table are the same name.

1. Select the dbo.customer source table.

2. Right-click and select Change Target Creators. The Change Target Creators dialog box appears.

   Warning

   Important

   Right-clicking anywhere on a notebook page applies the selected action to the currently selected table.

    

3. From the Change Creator for Table list, select guest.

4. Select the Apply to All Tables check box and click OK.

   The high-level qualifying target table names on the Table tab are changed to guest.

   To view additional information and options, select the Column Mapping, Auto Create, and Options tabs.

   • The Column Mapping tab lists source table columns and their related target table columns, and displays lookups specified on target columns. It displays one source and target table combination at a time. To see the other tables, select the table tabs at the bottom of the page.
   • The Auto Create tab displays the specifications used to create the new table.
   • The Options tab displays the input file name, load methods, and options available. The fields displayed vary depending on the database and load method used.
5. Click Run Load to load the source tables into the target table. The SQL Insert Status window appears.

Run the Load

The SQL Insert Status window lets the user review the specification and verify its accuracy.

1. After reviewing the table names, click Load. An informational message appears when the load is complete.
2. Click OK.
3. Click Close to exit the SQL Insert Status window.
4. Click Yes on  the message that appears asking whether  to save the SQL Insert Report.  The Save report as dialog box appears.
5. In the File name field, type Tutorial_1a_Load_Report and click Save.

Save the Related Loader Specification

1. From the File menu, select Save. The Save Load Specification dialog box appears.
2. In the Name field, type Tutorial_1a_Load.
3. Optionally, in the Description field, type a description.
4. Select the Save with Connection Userid/Password check box.
5. From the Target Repository list, select Local Repository.
6. Click OK. The Load Specification notebook window appears and the name of the specification appears in the title bar.
7. From the File menu, select Close to close the load specification without closing Related Loader.

Select the Source for a New Load Specification - Part 2

Although the steps in this part of the tutorial appear to be  similar to the ones performed  in Part 1, it will load a second extract data file needed for future tutorials.

1. If Related Loader is not already started, launch it like any other application based on your operating system.
2. From the File menu, select New or click New on the toolbar. The Create New Load Specification dialog box appears.
3. From the Load Input Specification Type list, select Extract Data File.
4. From the Path and File Name field, click Browse. The Select Extract File dialog box appears.
5. Navigate to <install directory>\Samples\tutorialExtract_2\META.
6. Double-click the XML file. The Create New Load Specification dialog box reappears with the path and file name populated.
7. Click OK. The Connect to Target Database dialog box appears.
8. In the Connectors list, select the previously saved connector (in the example, it is SQL_Connector). The dialog box redisplays with the fields prepopulated.
9. Click Connect to verify your database connection. If the connection is valid, the Finish button is enabled.
10. Click Finish. The Generate Constraint Names dialog box appears.
11. Click Yes. The Load Specification notebook appears showing the source and target tables.

Specify the Tables, Column Mapping, and Load Method

The Table tab is a graphical display of your source tables and the target tables into which data is loaded from the XML file.

In Tutorial 1 Part 1, initially, the source and target tables were the same name. In Tutorial 1 Part 2, the source and target tables have different names. One reason for naming the tables this way is so that the tutorial user will have something to compare when using the ComparePro tutorials.

1. Optionally, view information on the other tabs by selecting them as done in Part 1.
2. Click Run Load to load the source tables into the target tables. The SQL Insert Status window appears.

Run the Load

The SQL Insert Status window lets the user review the specification and verify its accuracy.

1. Click Load to begin the load. An informational message appears when the load is complete.
2. Click OK.
3. Click Close to exit the SQL Insert Status window.
4. Click Yes on the message that  appears  asking whether to  save the SQL Insert Report. The Save report as dialog box appears.
5. In the File name field, type Tutorial_1b_Load_Report and click Save.

Save the Related Loader Specification

1. From the File menu, select Save. The Save Load Specification dialog box appears.
2. In the Name field, type Tutorial_1b_Load.
3. Optionally, in the Description field, type a description.
4. Select the Save with Connection Userid/Password check box.
5. From the Target Repository list, select Local Repository.
6. Click OK. The Load Specification notebook window appears and the name of the specification appears in the title bar.
7. From the File menu, select Exit to exit Related Loader.

Tutorial 1 is now complete.