Cross-reference (Xref)

From Starfish ETL
Jump to: navigation, search

Starfish ETL has built-in methods for maintaining relationships between different systems. Put simply, you will be storing an ID from your Origin, and the new related ID in your destination for the record.

For an example, if you are migrating Company records from one system to another, and later in the process you’ll also be importing Contacts that will be related to these companies you’ll need a method to select which company the contact belongs to. One method would simply be to store the Old (Origin) primary company ID somewhere inside of the new company table on your destination. Then you could use a SQL/SmartLookup and pull down the ID by a filter clause. This is perfectly acceptable; however you'll take a performance hit because for each row you’ll have to perform another lookup operation. Using Xref functions, these ID relationships are stored in memory, thus making the lookup operation instantaneous – speeding up your process. The idea will be to write out the ID relationships for accounts while that job is running. Then, when the Contacts job runs it can initialize and read from this list to quickly & easily relate to the correct Company.

SQLite Xref

Starfish can now use a SQLite Database to store xref lists. This has the advantage of allowing you to read and write an xref in the same job. In addition, if you initialize the xref in append mode, it will append new items to the list but also update records that already exist. With the file system xref lists, records would be duplicated instead of updated. You can initialize the xref and use the append/overwrite checkbox to choose the behavior of the xref list upon write. You can also use database xref lists without initializing them. The default behavior of an un-initialized database xref list is append mode.

To enable using the SQLite database, edit the C:\inetpub\wwwroot\StarfishEngine\Web.config file. Change UseDatabaseXref to True:

<setting name="UseDatabaseXref" serializeAs="String">
    <value>True</value>
</setting>

The standard location for the SQLite Xref database is C:\inetpub\wwwroot\StarfishEngine\engine.db. You can read and edit this file with any SQLite reader. I've used SQLite Expert Personal edition: http://www.sqliteexpert.com/download.html. The open source DB Browser for SQLite is another option: http://sqlitebrowser.org/.

Text Xref

Internally, Xref Lists are stored in your StarfishEngine\Xref folder as simple text files with an .ids file extension. You may modify these files by hand if necessary.

File Format

This data is stored in a series of text files (by default) in the C:\inetpub\wwwroot\StarfishEngine\Xref\ folder, with the .ids extension. These files can be edited by using using a text editor. The format is simply OLD ID|NEW ID (pipe character separates the ID's) with one set on each line.

Using Xrefs In Starfish

Xref List Initialization

To begin, you must initialize an Xref List before you can read or write to it. This is done on a per-job basis through the Xref Lists button on the Mapping tab.

Xref1.png

Write to a new list to store a relationship between an Origin Primary Key and the Destination Primary Key. The checkbox indicated whether the list should be appended to. When it is written to, by default, the entire list will be blanked and written to as new when the job is run. Check to box to instead append to the end of the existing list. (for instance if you need to run multiple jobs which write to the same list, or need to run the job multiple times for some reason).

Reading from an existing list will allow you to retrieve the “New ID” for a system, given an “Old ID”. The drop-down on the Read side gets a list of Xref Lists that are available on the server.

Rather than using the built-in screens for Xref List Initializations, you may also use the XrefInitRead() and XrefInitWrite() VBScript functions.

Xref Writes

To write to an initialized Xref list, you may use the Exec Operation “Xref Write”. Select the list from the Xref List drop-down. Then supply a value or variable to use as the Origin/Old ID, and one for the Destination/New ID.

Xref2.png

Alternatively, you can use the XrefWrite() VBScript function to write to an Xref list.

Xref Reads

To read from an initialized Xref list, you may use the “Xref Read” function field in your mapping.

Xref3.png

Alternatively, you can use the XrefRead() VBScript function to read from an Xref List.

Converting From Text To SQLite Xref

You can do this by changing your text files from .ids to .csv extensions, reading from them with the Starfish CSV or ODBC Connector and writing into your local SQLite database.

Another option is to import the files directly into SQLite via the command line. You can do this with the SQLite Tools: https://www.sqlite.org/download.html.

See this example: http://www.sqlitetutorial.net/sqlite-import-csv/

sqlite> .mode csv
sqlite> .import c:/sqlite/city.csv cities