How ODBC to Notes Works
The Sentinel "talks" to the ODBC driver to get the data for integration (either add or update) into a Notes database.
Sample Uses
1. Pull customers from an AS/400 into a Notes customer tracking database.
2. Map ODBC data fields to Notes database. This is useful if the Notes database is already designed and its fields do not match those in the ODBC source. The Field Mapping Database contains all field mappings for re-use across many databases.
3. Sum order line items into total sales in a Notes Customer database.
4. Pull authorized purchase orders into a Notes reporting database.
Points to Remember
1. Sentinel can only read from ODBC sources that you have set up in the ODBC administrator. Most sources come with a "Test" program to check the installation and the connection to the database. This is a good step to run before attempting data integration with the Sentinel.
2. Not all ODBC data sources work the same. Some have more features and support more grammar than others.
3.The Sentinel allows documents to be added as main (parent) or response documents.
4. The Sentinel will update previously entered documents.
5. All adds or updates are based on a key.
6. Following are the possible ways to Add or Update information in the Notes destination:
Add All: This choice adds the entire contents of the load database to the Notes database. This is useful for News feeds, where you know in advance that all of the information in the load is new. Be careful not to run this Task twice, as you will find duplicates. This choice does NOT check the key.
Add Unique: This will add Unique Records as New Documents. If it does not find the Key, it will add the record to the Notes database.
Update: This will examine the Key Fields for duplicates. If it finds the key, the Sentinel will update the document If it does not find the key, it will NOT add the record to the Notes database.
Sync Deletions: This will match the Notes Database Documents to the Import (or load) File Records. For example, if the Notes database has 50 documents, and the load file has only 40 documents, the Sentinel will delete the 10 documents that are no longer in "Synch". This is useful for maintaining an Employee database in Synch when terminated employees are no longer in the Employee Database. The only way to know they are terminated is by their absence from the load file.
7. After a database is initialized, most tasks use both Add Unique and Update to refresh the Notes database. This will add any new records, and update existing records if the source has changed.
8. The Sentinel will NOT update a record in the destination if the source record is the same. This minimizes replication activity, and is a major feature of the Sentinel.
9. You can match up on multiple keys. The Sentinel has no limit on the number of key fields allowed.
10. Every Notes Destination Database must have a view to load data into. We suggest developing a hidden Import View in each Notes database you are going to load data into. The first column(s) in the View are Key Fields. These Key Fields are always sorted, should match the view exactly, and in the same order.
Performance Statistics
The following statistics are based on a Pentium 100 processor.
1. The average speed of an ODBC to Notes data integration is over 100,000 records per hour on a first time add (using Add All).
2. If the records do not exist in the destination, it is preferred to use Add All on a first time add. This will be very fast (100,000 records per hour).
3. Using Add Unique checks for a key in every add, slowing down performance. Use Add Unique after the database is initialized.
4. Updates run at the rate of up to 100,000 per hour.
Tips and Techniques
1. If you are updating but do not want any records to be added, select Update.
2. Use the formula section to add any Notes formula you want. This is useful for last update. Field LastUpdate = @Now.
3. Don't forget that the formula is calculated only if the Sentinel adds or updates a record. If you need to calculate a formula for every Notes, use the Run Notes Macro choice instead of the Notes formula.
Tips and Techniques
1. 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.
2. 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.
SQL Examples
1. Using the SQL Select 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.
2. Selecting only Certain Fields: If more fields existed in the Source File than you need, and you wanted to select only three fields from the Customers Table in the Source File, you would use the SQL Statement: SELECT CompanyName, CompanyAddress, CompanyCity FROM Customers.
3. 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.
4. 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.
5. 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: 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).
6. If we needed to add fields from the Orders File, we would ADD the following to the SQL Statement: SalesQ1, SalesQ2, SalesQ3, SalesQ4
7. 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.
8. 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.
9. Using SUM and WHERE You can use SQL Statements to SUM data. The data will be summed according to the group by clause. 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
10. Total Sales: To obtain TotalSales, you need to add the Notes Formula in the Task Form:
Field TotalSales := SalesQ1 + SalesQ2 + SalesQ3 + SalesQ4
Note: Sentinel supports all SQL functions. If an SQL function is unsupported by the ODBC driver, Sentinel records the failure code in the Message Log.
11. 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.
12. 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.