Quick Start for HVR on Salesforce

From HVR
Jump to: navigation, search

This appendix shows how to set up two HVR channels, one for database to Salesforce (called hvr_demo40) and one for Salesforce to Database (called hvr_demo41). For simplicity, the database will be located on the same machine as the hub database. Before following this quickstart, please make sure the requirements have been met; see Requirements for Salesforce.


Create Test Schemas, Tables and Objects

Create two test schemas, one for integration into Salesforce and the other for capture from Salesforce. Both of these schemas should contain two empty tables named dm01_product and dm01_order. If replication is configured between existing databases and tables then this step should be skipped.
First, create two schemas testdb1 and testdb2. See the other Quick Start appendices for HVR on a specific database for instruction. For the test of this appendix, Oracle databases are assumed.
Create the test tables.

$ cd $HVR_HOME/demo/hvr_demo40/base/oracle
$ sqlplus testdb1/hvr < hvr_demo40.cre
$ sqlplus testdb1/hvr < hvr_demo40.mod
$ sqlplus testdb2/hvr < hvr_demo40.cre
$ sqlplus testdb2/hvr < hvr_demo40.mod

Now create the Salesforce objects:
SC-Salestforce-CustomObject HvrDemoProduct edit.png

  1. Log into Salesforce
  2. Select menu item Setup
  3. Select section App SetupCreateObjects
  4. Select button New Custom Object
  5. Fill in object details
  6. Select button Custom Fields & RelationshipsNew
  7. Fill in column details; use Text fields for this demo.

SC-Salestforce-NewCustomerField price.png

Create object dm40_order with custom fields prod_id and cust_name.
SC-Salestforce-CustomObject HvrDemoOrder detail.png

Also create object dm40_product with custom fields prod_price and prod_descrip.
SC-Salestforce-CustomObject HvrDemoProduct detail.png

Install Salesforce Data Loader

  1. Log into www.salesforce.com
  2. Select menu item Setup
  3. Select section Administrative Setup ▶ Data Management ▶ Data Loader
  4. Download the ApexDataLoader.exe and install.

Install HVR

First read section Introduction which explains the HVR's terminology and architecture. In particular this explains the importance of a hub database.
Then install the HVR software on the hub machine by following the installation steps in section Installing HVR on Unix or Linux or Installing HVR on Windows. If the hub machine is a Unix machine then HVR can either be installed on a Windows PC (so the HVR GUI can run on the PC and connect to the Unix hub machine) or the HVR GUI can be run on the Unix hub machine and connect back to an X server running on a PC.

Create the Hub Database

Create the hub database, in which the HVR GUI will store the channel definition. This can be an Oracle schema, an Ingres database, a SQL Server database or a DB2 database. The steps to create an Oracle hub database schema are as follows:

$ sqlplus system/manager
SQL> create user hvrhub identified by hvr
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
SQL> grant create session to hvrhub;
SQL> grant create table to hvrhub;
SQL> grant create sequence to hvrhub;
SQL> grant create procedure to hvrhub;
SQL> grant create trigger to hvrhub;
SQL> grant create view to hvrhub;
SQL> grant execute any procedure to hvrhub;
$ sqlplus
Enter user–name: / as sysdba
SQL> grant execute on dbms_alert to hvrhub;
SQL> exit;

Connect to Hub Database

Start the HVR GUI on a PC by clicking on the HVR GUI icon (this is created by the HVR Installer for Windows) or by running hvrgui on Linux.
First, Register the hub database: right–click on hub machines ▶ Register hub. Enter connection details.
SC-Hvr-RegisterHub Oracle remote 4343.png

In this example the hub is a machine called guam, where an INET daemon is listening on port 4343. See section Installing HVR on Unix or Linux for how to configure this.
For a new hub database a dialog will prompt Do you wish to create the catalogs?; answer Yes.

Create Oracle Locations

Next create two Oracle locations (one for each channel) using right–click on Location Configuration ▶ New Location. Ignore the Group Membership tab for now.
SC-Hvr-Location Oracle db1.png

Create a Salesforce location using right–click on Location Configuration ▶ New Location.
SC-Hvr-Location Salesforce.png

The Dataloader field must point to the .jar file in the Data Loader installation directory. The default installation on windows is C:\Program Files (x86)\salesforce.com\Data Loader, and the name of the .jar file depends on the exact version.
Now define a channel using Channel Definitions ▶ New Channel.
SC-Hvr-LocationGroup demo40 salesforce.png

The channel needs two location groups. Under the new channel: right–click on Location Groups ▶ New Group. Enter a group name, for example DB.
Add location db1 as a member of this group by checking the box for db1.
Then create a second location group, called SALESFORCE that has member sf1.
The new channel also needs a list of tables to replicate. This can be done as follows; right–click on Tables ▶ Table Explore.

  1. Choose the db1 location Connect.
  2. In the Table Explore window, click on both tables and click Add.
  3. In new dialog HVR Table Name click OK.
  4. Close the Table Explore window.

Define Actions

Finally the new channel needs two actions to indicate the direction of replication, and actions to map database table and column names to Salesforce Object and Field API names.
First we will specify the replication direction:

  1. Right–click on group DB ▶ New Action ▶ Capture.

Right–click on group SALESFORCE ▶ New Action ▶ Integrate. Check /OnErrorSaveFailed, this affects how replication errors are handled.
Note that the Actions pane only displays actions related to the objects selected in the left–hand pane. So click on channel hvr_demo40 to see both actions.
Next we will map the table and column names.

  1. Right–click on group SALESFORCE ▶ New Action ▶ TableProperties. Select Table dm40_order. Check /BaseName and fill in the Salesforce API Name dm40_order__c. This name is case–sensitive.
  2. Right–click on group SALESFORCE ▶ New Action ▶ ColumnProperties. Select Table dm40_order. Check /Name, click the [...] button and select column cust_addr. Check /BaseName and fill in the Salesfore API Name cust_addr__c. This name is case–sensitive.
  3. Repeat this for the other tables and columns in the channel to the following tables.
dm40_order dm40_order__c
cust_addr cust_addr__c
cust_name cust_name__c
ord_id Name
prod_id prod_id__c

dm40_product dm40_product__c
prod_descrip prod_descrip__c
prod_id Name
prod_price prod_price__c

Note that action Integrate only integrates insert and update statements, not deletes.

Enable Replication with HVR Initialize

Now that the channel definition is complete, create the runtime replication system.
Right–click on channel hvr_demo40 ▶ HVR Initialized. Choose Create or Replace Objects and click HVR Initialize.
SC-Hvr-Gui demo40 pending.png

From the moment that HVR Initialize is done, all changes to database testdb1 will be captured by HVR when its capture job looks inside the Oracle logging.
HVR Initialize also creates two replication jobs, which can be seen by looking at the Scheduler node of the hub database.

Start Scheduling of Replication Jobs

Start the Scheduler on the hub machine by clicking in the HVR GUI on the Scheduler node of the hub database.
SC-Hvr-Gui-Scheduler oracle.png

Next, instruct the HVR Scheduler to trigger the replication jobs.
SC-Hvr-Gui demo01 Start.png

Test Replication

To test replication, make a change in testdb1:

$ sqlplus testdb1/hvr
SQL> insert into dm01_product values (1, 19.99, 'DVD');
SQL> commit;

In the HVR log file you can see the output of the jobs by clicking on View Log. This log file can be found in $HVR_CONFIG/log/hubdb/hvr_demo01–cap–db1.
SC-Hvr-Gui demo01 viewlog.png

The job output looks like this:

hvr_demo40–cap–db1: Capture cycle 1.
hvr_demo40–cap–db1: Scanned 1 transaction (188720 bytes) from 4 \
seconds ago containing 1 row (1 ins) for 1 table from file sequence 736 in \
0.52 seconds.
hvr_demo40–cap–db1: Routed 215 bytes (compression=50.0%) from 'db1' \
into 1 location.
hvr_demo40–cap–db1: Finished. (elapsed=1.08s)
hvr_demo40–integ–sf1: Integrate cycle 1 for 1 transaction file (215 \
hvr_demo40–integ–sf1: Moved 1 file to \
hvr_demo40–integ–sf1[agent]: Processing 1 file...
hvr_demo40–integ–sf1[agent]: Uploading 1 rows for 'dm40_product' from 'sf–20120703152541860–dm40_product.csv'
hvr_demo40–integ–sf1[agent]: Uploaded 1 record from 'sf–20120703152541860–dm40_product.csv'
hvr_demo40–integ–sf1: Finished. (elapsed=21.71s)
hvr_demo40–cap_integ_all[eof]: Finished. (elapsed=22.84s)

This indicates that the jobs replicated the original change to Salesforce. You can verify the replication by looking at the Object data in Salesforce.

Create the Second Channel and Define Actions

To retrieve data from Salesforce, we will construct a second channel. Follow the steps above to create a channel hvr_demo41 with two groups; group SALESFORCE with group member location sf1, and group DB with group member locations db2.
First we will specify the replication direction:

  1. Right–click on group SALESFORCE ▶ New Action ▶ LocationProperties Check /BulkApi, this will use the Salesforce Bulk API for efficient transfer of large datasets.
  2. Right–click on group DB ▶ New Action ▶ Integrate. Check /Resilient to convert extra inserts to updates. Check /OnErrorSaveFailed, this affects how replication errors are handled.
    Note that HVR action Capture reads all rows from the Salesforce source table instead of only capturing changes.
    Next we will map the table and column names, exactly in the same way we did with the channel hvr_demo40 channel.
    Finally, we will specify scheduling actions. Since Capture does not capture changes, all rows will be captured each capture cycle. We will specify a Scheduling action so the job will only run once a day.
  3. Right–click on group SALESFORCE ▶ New Action ▶ Scheduling Check /CaptureStartTimes and click the [...] button. Select an appropriate start frequency, for example: Minute: 0, Hour: 0, and leave the other columns checked. This will result in a pattern of '0 0 * * *' which will schedule the job daily at midnight.
    SC-Hvr-Gui Salesforce demo40.png

Enable Replication with HVR Initialize

Right–click on channel hvr_demo41 ▶ HVR Initialize, and load the channel.
Since the scheduler is already running, it does not need to be started again.
The job hvr_demo41–cap–sf1 will remain PENDING until the scheduled start time.
SC-Hvr-Gui demo41 running.png

Test Replication

To test the hvr_demo41 channel, we will force a single cycle of the PENDING capture job by using hvrstart. It will capture all the data available in Salesforce, which is the data we inserted earlier with the hvr_demo40 test.
In the HVR log file you can see the output of the jobs:

2013–07–04 10:34:41: hvrscheduler: localhost/9: Updated job hvr_demo41–cap–sf1 \
 with [Trigger=2].
2013–07–04 10:34:41: hvr_demo41–cap–sf1: Received 1 trigger control.
2013–07–04 10:34:56: hvr_demo41–cap–sf1[agent]: Processing 2 tables...
2013–07–04 10:35:16: hvr_demo41–cap–sf1[agent]: Downloaded 1 rows for 'dm40_product' to 'sf–20120704083457–dm40_product.csv'
2013–07–04 10:35:17: hvr_demo41–cap–sf1: Capture cycle 1 for 2 files (112 \
2013–07–04 10:35:18: hvr_demo41–cap–sf1: Routed 214 bytes \
 (compression=50.2%) from 'sf1' into 1 location.
2013–07–04 10:35:18: hvr_demo41–cap–sf1: Moved 2 files from location \
2013–07–04 10:35:18: hvr_demo41–cap–sf1: Processed 1 expired 'trigger' \
 control file.
2013–07–04 10:35:18: hvr_demo41–cap–sf1: Finished. (elapsed=37.26s)
2013–07–04 10:35:18: hvr_demo41–integ–db2: Integrate cycle 2 for 1 \
 transaction file (214 bytes).
2013–07–04 10:35:18: hvr_demo41–integ–db2: Integrated 1 change from \
 'dm40_product' (1 ins).
2013–07–04 10:35:18: hvr_demo41–integ–db2: Integrate used 1 transaction for 1 \
 individual row and took 0.14 seconds.
2013–07–04 10:35:18: hvr_demo41–integ–db2: Waiting...

You can verify the replication by looking at the target database:

$ sqlplus testdb2/hvr
SQL> select * from dm40_product;
–––––––––– –––––––––– –––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
        1      19.99 DVD