Within minutes from downloading ExportR, the software can be installed and running against all data tables – a process that could otherwise take months of detailed work by expensive legacy system experts.
The ExportR data extraction tool is used to unlock data ‘trapped’ in the multivalue Pick-based environments used by Revelation ROS files and Advanced Revelation (ARev) and OpenInsight .LK and .OV files. Extracting data from these Revelation and ARev environments is notoriously difficult due to non-standard characteristics such as variable length records, variable length fields, repeating field values and unfamiliar delimiter implementation.
As these unique features do not conform with the more widely understood relational database format, many otherwise technically competent IT personnel struggle in identifying the underlying metadata and in extracting this data into a usable and meaningful structure. We have developed and perfected our ExportR software to solve these data extraction needs by providing easy to use software that will produce usable data in familiar formats, for example CSV files or Microsoft Access tables. A task that could have taken months without the use of our specialised software can be completed within hours or even minutes. Because the ExportR extract is automated, the process is repeatable and gives a higher degree of accuracy.
Although alternate Revelation Database and OpenInsight Database extract solutions exist, none of these options compare with ExportR’s ease of use. Alternate methods include third party products or using the basic functions that already exist within Revelation and OpenInsight. Using any of the options other than ExportR requires identifying table names, field names and the associated data types. These options also rely on a very good working knowledge of the Revelation, Advanced Revelation or OpenInsight environment and command set.
What We Do
Amon Consulting are able to provide the following software and services to assist with data extraction from multivalue based ROS, LK and OV files originating from OpenInsight, Revelation G2B (or earlier) or Advanced Revelation (Arev) databases.
- Provision of the ExportR program to enable customers to manage their own data extraction process
- Provision of software to enable customers to automate the database creation and loading of all CSV output (produced by the ExportR process) to Microsoft Access
- Data conversion service either via remote access, on-site or with data being sent either by emailed, FTP or via remote connection
- Implementation of interface or integration solutions
The table, field and data type structures can be output as specified in the source OI/Revelation/Arev database (this is the default) or output can be mapped to an alternate database structure. Output data options include CSV, MS Access, Microsoft SQL Server or MySQL.
The ability to view the exported data in MS Access format facilitates the identification of wanted tables and fields, allows for reporting requirements and also provides a platform from which to reformat data for upload into the destination application’s database structure.
Approximate elapsed time for a full data extraction and conversion to MS Access format is 30 minutes per zipped megabyte.
Up to three CSV files are created for each Revelation table exported.
(1) Data Dictionary
- Includes table name, field name, position, description, type, column header detail, indexing detail, ‘help’ description text and any calculation code for symbolic fields
- A record is created for each field and each symbolic (calculated) field found in the data dictionary
(2) Data File
Primary data file
- All data is exported for all records as the default setting excluding cross reference fields
- Data for fields not specified in the data dictionary is also exported
- Any data conversion (formatting) that exists for a field is applied
- Date fields are output in the format dd mmm yyyy by default, for example, 21 FEB 2012
- A header record with column names is output with the data file by default
- Includes all multivalue fields which will also be ‘split out’ into a Multivalue Data File (see below)
- Column names allocated are a combination of the Revelation field number and field name (for example K1_COMP_NO,K2_CUST_CODE,F1_CUST_NAME)
(3) Multivalue Data File
For data tables that contain Multivalues, a further data file is created containing only the Multivalued fields
- A new record is created for each level of Multivalue
- If subvalues exist then a separate record is output for each subvalue within each Multivalue
- Records are keyed by the original record’s key plus a multi-level value number
- A header record with column names is output with the data file
- Column names allocated are a combination of the Revelation field number and field name (for example F19_AGE_PERIODS,F20_AGE_AMT)
LOG File Output
A comprehensive Log file is produced for each ExportR extract run
For each file specified for export, the log file will show
- Revelation filename, account name and DOS path
- DOS extract filename
- Data extract exceptions
- Output specification values
- Count of records exported
- Count of bytes exported
- Maximum record size
- Elapsed time – either actual or for sample record runs, an estimate for a full run
- Grand totals for file counts, field counts, records read, records output, bytes output, timings
- The ExportR software will run under all versions of COSMOS Revelation (aka RevG or G2B) or Advanced Revelation (aka Arev)
- A version of ExportR is also available for OpenInsight and will run under a supplied runtime licensed platform
- A free trial version of ExportR is available on request and will provide a sampling of tables and records to enable to ExportR software to be assessed
- Running the ExportR software requires very little or no knowledge of the Revelation product or application environment
- ExportR is a single program consisting of one file only
- Installation involves copying a single ExportR program file into the Revelation root directory – the existing application is left unaltered
- No change is necessary to the Revelation program files or data tables for the ExportR program to execute
- No change is made to the existing Revelation program directories, program files or data tables when the ExportR program executes
- All ExportR input and output is held in a directory nominated for this purpose and is specified as part of the ExportR initiation command
- ExportR is initiated through a single command entered at the Revelation Command Line (For example, at TCL the user will type RUN DOS EXPORTR C:/EXPORT)
- All application data and all field information for all attached data tables can be exported using the single command to initiate ExportR
- A knowledge of table names, field names and data types is not required
- By default all data is extracted for all attached data tables (system files and cross-reference data are excluded) and all fields for which any data exists
- Where they exist, field names and their associated dictionary data types are associated with the exported field data
- Data is exported using output specifications based on the dictionary (metadata) conversion (OCONV) formats
- Output specifications are automatically generated and are held in an external text based file
- Output specifications can be adjusted as required (using Notepad or an alternate text editor)
- By default, a ‘test’ run is executed initially with a maximum of 20k of data exported for each table, enabling wanted tables to be identified and the output specification adjusted accordingly
- Data types created by default in the output specification can be added or adjusted where required
For further information regarding the extensive output options available and for additional technical detail, please request a copy of our ExportR manual. In 90% of cases the manual will not be required as data extraction requirements are usually straight forward and the standard defaults are usually all that is needed.
Third Party Software Extraction
RevelationG data, Revelation G2B data, Advanced Revelation (Arev) data and Open Insight data (LK & OV files) can easily be extracted from the following software platforms, applications and packages using the ExportR automated data export software.
ADP * American Business Management Systems (ABMS) * American Business Networking * Angel Systems AVENUES * Argus * Ceridian Encore * Ceridian Ensemble * Ceridian Repertoire * COBRA * Comcheq * Comsense Advantage * Comsense Cecommand * Counterpoint Systems, Inc * Debtmaster * EXXIS * Furniture Retail Operations Group (F.R.O.G.) * GAAP * GPMate * Harmony Solutions HRM-ONE * HPMS * HR/PC Payroll * HR-1 * HR2000 Technologies * HR-InfoNet * HRIS * HRPro * IDEAS/OGS * Immigration AIDE * Infosphere * iTMS Software * J C Henry & Co * Jones Microcomputing Services Software * Maestro * MAPCON * Megamation * Meridian Data Systems * Mills & Murphy HPMS * National Software Systems * New Century Accounting Modules * PayDay Manager * PANDORA taxonomic database * Paylink * Powerforce Time & Attendance (T&A) * Powerforce Occupational Safety and Health Incident Management (OH&S) * Royal Bank * RetailWare (including ScubaWare) * Social Software ACE, UMIS, YOIS * Source 100 * Solution Specialties Tracker * Spectrum * SRP Computer Solutions Software * SurveyIt! * TD Bank * Totally Integrated Management System (TIMS), TPS, and more.