Data Extraction for Migration away from Jade

[Emardee]

The time has come for our charity to move from Jade to a new database which better matches our needs. We are therefore looking at the best way to handle migrating our data out of Jade into the flat file formats that the new Database provider has provided us for the import routines to the new database. (New database is in MS SQL and we are likely to handle the mappings and data manipulation using Access queries in the middle).

Our current test runs of this process suggest we have a very painful road ahead of us. But that could be because we are not using the best methods for exporting our data.

We need to be able to export it reliably at least 3 times in the same layout and format, so we can create the bridge between the two systems.

• 1st pass will be a limited set of data and records to get the process started.
• 2nd Pass will be as close to final full data as possible to give us access to a full working copy of our data on the new database for training and testing (any major problems will add extra passes to this stage until solved).
• Final Pass (3rd pass - assuming no major problems with 2nd pass) will be the roll out live re-running the export to migrate the full lot over for the final time (hopefully). These will be spread over several months, but we'd like the data export to be as quick as possible to minimise downtime during the migration.

We have about 31,000 records with Finance and CRM data we need to parse. There are many duplicates in this data, but the import routine for the new system is very good at de-duping.

Our current issues are that:

• Someone has discovered a 4000 records per export limitation from Jade. ???Seems odd to me???
• That some reports are not being saved, so we have to choose the columns we want exported each time we export (not good use of time, or for the repeatable reliability of data export each time!)
• That is appears that we have to export the data from 3 separate places to get everything (unless we are missing something)
• That data extraction itself seems to be painfully slow if you want lots of columns and rows (i.e. "everything" like we do!)

We are running v6.x (exact version will need to be confirmed). I am completely new to Jade, but have IT experience, which is why they've pulled me into this project! Where is the best place to get a fast track on info from migrating data out of Jade, so I can get up to speed quickly?

Bound to find more problems as we progress, but that is enough to start with!

I hope I've posted this in the right location. Thanks for reading....

Thanks

Mike

[BeeJay]

In previous migration exercises, I've found it to be more efficient to write some custom Jade logic to extract the required information into flat files in the desired format. I'm presuming from your description you're connecting to the Jade database via an ODBC connection and trying to extract the class instance details from the "relational view" of the underlying object model. One of the difficulties with this approach is that you frequently have to join disparate sets of data from multiple "tables", or in reality instances of multiple classes in the underlying Jade object oriented database. This process is generally much easier to achieve using native Jade code than trying to create all the relevant joins with some form of query tool over a Jade ODBC connection.

The other alternative is that the "RelationalView" accessible via the ODBC driver could be "flattened" by your Jade development team by programmatically adding additional "tables" & "columns" to the relational view using the RelationalView::addUserTable and RelationalView::addUserAttribute methods, and implement the JadeRelationalEntityIF and JadeRelationalAttributeIF interfaces on all the relevant classes in your Jade database. This will mean the "table" and it's "columns" could come from multiple different class instances in Jade, but be represented as though it were a single table with multiple columns of data, without you having to concern yourself with creating lots of different joins between "tables" in your query tool.

Hope that helps,
BeeJay.