ODBC-Compatible File Format Loading a Document with ODBC-Compatible Data
In this example, you will load a Document (Customer Profile) into the Customer Information database (C:\Sentinel\Customer.nsf) we used in Example #1. Make sure the Customer.nsf application is present on your Notes Workspace.
J This example requires the correct installation of the Microsoft Access ODBC Drivers. If you are unclear about the correct installation of the Microsoft Access ODBC Drivers, consult the ODBC Drivers Section of the Microsoft Windows Manual or read the On-Line Help Documentation provided with Windows.
Open the Sentinel Task Database
1. Double click on the Sentinel Tasks icon in the Notes Workspace.
ODBC Data Source
In this Example, we will load a Document titled Customer Profile into Customer.nsf.
The source file will be an ODBC-compatible Microsoft Access File (Customer.mdb) supplied with Sentinel and placed in the C:\Sentinel directory when Sentinel was installed.
Navigate to the Task View
1. Switch to the View titled 1. Task \ 2. Task Name. This View will lists all Task Documents you create. The ASCII document you created in Example #1 is visible in the View.
You will now create an ODBC Task Document to load the C:\Sentinel\Customer.mdb source file as a Document in Customer.nsf. Creating an ODBC Task in the Sentinel Task Database
To compose an ODBC Task in the Sentinel Task Database:
1. In the Sentinel Application, choose Compose....1. Task \2. ODBC to Notes from the Notes Main Menu.
2. The ODBC Task Document will appear on screen.
Name the Task Document
1. Name: Enter the Name or Title of the ODBC Task for display in the view. The title should reflect the nature of the file (i.e. Customer Name & Address for the example we will use). Enter Customer Name & Address now.
2. Description: Enter a description of the ODBC Task. The description should accurately describe the ODBC File (i.e. Loads Customer Profile Form in Customer.nsf from Customer.mdb for the example we will use). Enter Loads Customer Profile Form in Customer.nsf from Contact.mdb now.
ODBC Source File
Use this section of the form to identify where the ODBC Source File is located, which Tables you will read, and any SQL Select Statements you desire to run on the data prior to loading (Optional).
1. Data Source: Enter the name of the Data Source as it is listed in the ODBC Administrator in Windows. Enter MicrosoftAccess (one word) now.
2. Table Names: Enter the Table Names from the Source File you want to load into the Notes Response Document. Separate each Table Name with a Comma. In our example, we are loading a table named Customers. Enter Customers now.
3. Field Mapping: If you are using both The Sentinel with the Sentinel Information Catalog, you will change the default selection of this field to Yes. This will automatically map the fields from the Information Catalog into the Notes Form. For our example, we will use the default setting of No. Make sure the radio button next to No is selected.
4. SQL Select Statement: This option allows you to select certain data elements from the Tables you have selected in Step #2 above. In our example, we will be loading the entire Table. Leave this field blank now.
Advanced Techniques
SQL Statements
The ODBC File Type supported by Sentinel is Microsoft Access version 2 .MDB.
The Source File supplied with this program is CUSTOMER.MDB. It is installed in the C:\SENTINEL directory (or the directory chosen during the program install).
Using the SQL Statement Field
You do not need to enter a SQL statement. If you leave the Sequel Statement Field blank, Sentinel will use SELECT * FROM Customers to the ODBC driver. The fields in the Access File Table are:
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip
If more fields existed in the Extract File and you wanted to select only three fields from the Customers Table in the Extract File, you would use the SQL Statement:
SELECT CompanyName, CompanyAddress, CompanyCity FROM Customers.
To select certain values, the WHERE clause is required:
To select the three fields from the Customers Table in the Extract File with the value "Boston" in the CompanyCity field use:
SELECT CompanyName, CompanyAddress, CompanyCity FROM Customers WHERE CompanyCity = "Boston".
The advantage of using this approach is that less data is moved across the network. Only the selected data passes over the network through the ODBC driver.
The SQL Statement Field also supports data aggregation, which allows you to roll up sales numbers at their source rather than at the destination. The syntax is more challenging.
Supported Operators are AVE, SUM, MIN and MAX.
SQL Joins
SQL supports Joins within a Database. As an example, some companies do not store Customer Name in the Order Detail File. The Marketing Department needs to know Sales by Customer. A Join Operation is needed to fulfill this requirement.
Both Files usually share a common data element which we refer to as the Key. In this example, the Key is CustomerNumber.
The Join Statement to accomplish this would be:
WHERE Customer.CustomerNumber = Orders.CustomerNumber.
The Field Name before the period references the Source Table, in this case we reference two Source Tables (i.e. Customer table and Orders table).
If we needed to add fields from the Orders File, we would ADD the following to the SQL Statement: SalesQ1, SalesQ2, SalesQ3, SalesQ4
In the case where more than one Table is referenced, we need to place the Table Name in front of each Field preceded by a period:
CompanyName really looks like Customer.CompanyName.
The complete SQL Statement will now load correctly into a Notes Destination Database using Sentinel:
SELECT Customer.CompanyName, Customer.CompanyAddress, Customer.CompanyCity, Orders.SalesQ1, Orders.SalesQ2, Orders.SalesQ3, Orders.SalesQ4 FROM Customers, Orders WHERE Customer.CompanyCity = "Boston" AND Customer.CustomerNumber = Orders.CustomerNumber.
Using SUM and WHERE
You can use SQL Statements to SUM data. The data will be summed according to all non-aggregated fields. In the following example, one Summary Record is created for CompanyName, CompanyAddress and CompanyCity. To SUM Sales Data by CompanyName only, you do not need to use Address and City. The SQL Statement follows:
SELECT Customer.CompanyName, SUM(Orders.SalesQ1), SUM(Orders.SalesQ2), SUM(Orders.SalesQ3), SUM(Orders.SalesQ4) FROM Customers, Orders WHERE Customer.CustomerNumber = Orders.CustomerNumber GROUP BY Customer.CompanyName
To obtain TotalSales, you need to add the Notes Formula in the Task Form:
Field TotalSales := SalesQ1 + SalesQ2 + SalesQ3 + SalesQ4
Note: Sentinel does not support all ODBC functions. If an unsupported function is used, Sentinel records the failure code in the Message Log.
Joining Data from Dissimilar Sources (Heterogeneous Joins)
Sentinel can combine or join data from a SQL Source and an ASCII Source simultaneously. To perform Heterogeneous Joins, create a base data set consisting of Customer Name and Address; then join or import additional data to the base set.
The Sentinel executes Heterogeneous Joins efficiently. It is important to remember to select the Update choice in the Import Type file of the load form.
This will leave the same number of records you begin with but will also include the information that was imported subsequently.
Using Notes Select statement (instead of SQL Select)
If you are uncomfortable with SQL syntax but are comfortable with the Notes syntax , you can leave the SQL Select Statementfield blank and use the Notes formula Select CompanyCity = "Boston" instead of Select @All . This will result in more network traffic but Sentinel will discard everything except the data that fits the Notes Select statement. Notes Destination Information
Use this section of the Task Form to identify the Notes Destination Database.
1. Server Name: Enter the name of the Notes Server the Notes Destination Database is located on. In our example, we are loading a database that is located on the local drive. Enter LOCAL.
2. Database Name: Enter the complete Path and File Name of the Notes Destination Database. In our example, we are loading a Customer Profile Document into the Customer.nsf database. Enter C:\Sentinel\Customer.nsf in this field.
3. View Name: Every Notes Destination Database must have a view to load data into. Enter Customer in the View name field.
4. Key Fields: Key Field(s). For our example we are loading a Parent Document with the Key Field CompanyName Enter CompanyName now.
5. Form Name: Every Notes Destination Database must have a Form to load data into. In our example, we will be loading data into a Form titled Customer
Profile with the Alias Name Customer. Enter Customer in the Form Name field.
6. Document Type: Indicate the type of document you are loading in the Notes Destination Database. Choices are Document or Response. For our example we are going to be loading a Document titled Customer Profile. Select Document now.
7. Key Fields: Enter the parent document primary key field(s). Leave the Parent Key Field Blank now.
8. Import Type: You are loading a Customer Form. We want to load all of the records from the ODBC file into Customer.nsf. Deselect the default choices by clicking on them with the mouse and check the boxes next to Add Unique and Update now.
Task Options
1. Log Messages: Choose whether you want to see All Messages or Error Messages Only in the Sentinel Event Log (View...3. Logs\1. Messages By Time). For our example, we will use All Messages. Make sure All Messages is selected now.
2. NotesFormula Before : Enter any Notes formula(s) you wish to run on the data prior to loading it into the Notes Document. For our example we will be using a formula that sets the Compose Date to @Now and stamps Sentinel into the Document Author Field. Enter the following Formula in the Formula Field Now:
FIELD ComposeDate:=@Now; FIELD Doc_Author:="Sentinel"; SELECT@All
Saving the ODBC Task Document
1. Press the EscapeKey and then press the EnterKey to Save the ODBC Task Document
2. The ODBC Task Document is now available in the 1. Task Documents\3. By Task Type
You can now schedule the Task Document to be loaded.
Tips & Techniques
Prior to loading an entire data set, we recommend making a copy of the Notes Destination Database and a small subset (20 to 30 records) of the actual file information to test the accuracy of the names, type, and length specified. After you have specified the field names, type, and length and completed the Task Form in Sentinel, run a test load and check your results prior to loading the actual Destination Database with the complete file.
Running the ODBC Task
1. Make sure that the Customer.nsf Destination Database is added to your Notes Workspace.
2. Open the ODBC task form.
3. Click on the Run Button at the Top of the Task Form.
4. Check the results of the load by looking in the Customer Activity application (Customer.nsf) in the Notes workspace. If the load was successful, there will be several new Customer Profile records in the database. Highlight a Customer Profile record and press ENTER to inspect the data load.
5. From the Main Menu in Lotus Notes, change to the View titled 3. Logs\1. Messages By Time
6. Press Enter to inspect the activity log document and view the load results message.
7. If the load was successful, the log message will tell you how many documents/updates were loaded and if any errors occurred with any part of the load.
8. If the load was not successful, chances are a very simple mistake in syntax occurred when you created the Task Form. Enter the Sentinel Tasks application in Notes and locate the Task Document. Double check all spelling and syntax and then retry the load. Refer to the Trouble Shooting Section at the end of this manual for more information.
Exercise: Practice with the ODBC dBase File Format.
This exercise will allow you to load the Customer.nsf file using a different ODBC File (Customer.dbf). Try retracing the steps in Example #2 but use dBaseCustomer as the Data Source File.
Note: The dBaseIII ODBC drivers must be properly installed to complete this exercise.
Hint: Don't forget to delete the records you have loaded into Customer.nsf prior to running the load task. Use Compose...1. Task \ 2. ODBC to Notes to get started.