Introduction:

DataTrend is a database software program from CH2MHill that allows users to track Wastewater/Water plant data.  CH2MHill has ended support for the program and Hach has offered a data migration to the Hach WIMS software.  DataTrend utilizes a Microsoft Access or Oracle database (may also use MS SQL Server, not confirmed).  This utility reads migrates the data from the Datatrend database to Hach WIMS.

Step 1: Install OPSSQLIMPORT.EXE version 7.0.6 or later

The latest copy of the OPSSQLIMPORT.EXE file can be found on the IIM Server at I:\Services\OPSSQLIMPORT_LatestVersion.  If you do not have access to the server, contact IIM Tech support (IIMSupport@hach.com).  Create a OPSSQLIMPORT folder in your HachWIMS folder (i.e. c:\HachWIMS\OPSSQLIMPORT\).   Copy OPSSQLIMPORT.exe and OPSSQL.INI to the new folder.  NOTE:  The utility can only be used on a computer that already has HachWIMS V7.0.1 installed or later.  Edit the OPSSQL.INI file and change the DBTYPE and connection string to connect to your WIMS database.

Step 2: Create a facility to migrate data to

Use the HachWIMS ServerSetup program to create a new database.  Click on the New Facility tab in ServerSetup, fill out the Facility Name and click Execute.

3. Run the OPSSQLIMPORT.exe program and log into the new facility.

4.  Start the DataTrend Utility

5. Set the Source Connect to your existing DataTrend database:

For DataTrend Access datbases:

For DataTrend Oracle Databases:

6. Import the variable, calculations and Locations using the Create Variables button.

Click .  Variables will be created from the DataPoint table in the Access database.  A location will be created using the Name Field before the first colon.  For example, if the DataPoint.Name field = "AB1:DO" we will create a location called AB1 (if it does not already exist), a variable with a short name of AB1:DO and we will assign the variable to the AB1 location. 

Calculated variables will be created from the DataTrend Calculations table and assigned to the Calculations location in WIMS.  NOTE:  The WIMS Equation will be set to SKIP plus the formula field from DataTrend.  You will need to edit the equations in WIMS.   The following table displays suggested WIMS Formulas for DataTrend Formulas:

DataTrend Formula	WIMS Formula
MN(30,CBODREM)	MOV30(C33) or MAVG(C33) - If CBODREM is variable 33.
MN(7,INF:BOD)	WAVGn(C11), see WAVG help topic to determine proper week definition
M(7, TSI)	MOV7(C20)

NOTE: See attached Datatrend_Instructions.pdf for more information on Datatrend formulas. 

7. Import Data.

Set the Start and End fields to the date range you want to import data for.  Click .  This will import data from the DataTrend Data Table for the date range specified. 

NOTE:  You can reuse the Import Data and a later date to import additional data.

8. Create Monthly Data Entry Forms

Click .  This will create a Monthly Data Entry Form from the DataTrend FormName and FormData tables. 

9. Create Spread Report Templates.

Click .  This will create a spread report template (*.ss3 file) for each DataTrend RPTNAME record (using RPTDEF define which variables to display in the report).  You will need to copy the templates from your OPSSQLIMPORT folder to your HachWIMS TEMPLATES folder (c:\HachWIMS\client\templates).  See Templates in help for more info on using templates.

The migration utility is now done.  You will need to:

1. Update Equations.

2. Create Areas and assign the locations to Areas to further organize the database.

Source Data Examples:

The following are example data screen shots from a DataTrend (data trend) Access Database:

Oracle DataTrend Source Data using SQL_Console:

IMPORTING a DataTrend Oracle DMP file Notes:

In order to import an Oracle Dump File into your Oracle Instance you must first create a User/Schema Owner to import the data into.   

1. Use WIMS Oracle Server Setup and create a Facility with NO Facility Objects:

2. Use the Remove from List button to remove the New Facility from the WIMS Existing Facility List since it is not a WIMS facility.

3. Use the Oracle Imp command to import the data into the database:

IMP system/****@XE fromuser=datatrend touser=opsdt file=datatrend.dmp