Prep WIMS database for Rio Configuration Import [Q14783]

	Browse		Search		Login

Home : Prep WIMS database for Rio Configuration Import

Q14783 - INFO: Prep WIMS database for Rio Configuration Import

The following are suggested steps to improve the sync process between WIMS and Rio.

1. Remove Location names from variable names. See Default Variable Display. Example, in WIMS if you have a location Raw Water and a parameter named Raw Water pH in Rio it will appear as Raw Water Raw Water pH. Use the Var Admin Tool.

2. Use Rio Guid Util to set Rio Parameter Types (Guids) on your WIMS variables. NOTE: this does not need to be done for all parameters but will improve your experience in Rio, it is suggested to do for the common parameters (pH, Temp, Turb, DO, BOD, TSS...)

3. Update Units to match Rio Units

4. Set Rio Location Types using Location Setup

5. Set a location for all variables. Rio cannot import variables that have no location assigned. To find unassigned variables, use the variable browser and click the "Unassigned" location at the bottom of the location tree (if you do not see Unassigned it means you have no unassigned variables). Also, the Var Admin Tool will have an unassigned sheet displaying all variables with no location. Use Variable, Set Location in the Var Admin Tool to quickly move variables to an existing location.

6. Verify no duplicate MDE Forms (SELECT FORMNAME,COUNT(*) FROM MDE GROUP BY FORMNAME HAVING COUNT(*) > 1)

7. Rename duplicate variables in a location. SELECT LOCID, NAME, COUNT(*) FROM VARDESC GROUP BY LOCID,NAME HAVING COUNT(*) > 1

8. Update 1 minute, 5 minute, or 30 Minute variables to 15 minute variables so that data can be accessible in Rio. See Migrating data when changing a variable's Track Every Field (frequency).

9. Remove Orphaned records from the data tables. Orphaned data table records are created when a WIMS variable with data is deleted or it's frequency is updated. While this step is not required it avoids warning messages (for each orphaned record) when using the Data Import Utility. To find /delete these records:

SELECT * FROM DATATBL WHERE VARID NOT IN (SELECT VARID FROM VARDESC) / DELETE FROM DATATBL WHERE VARID NOT IN (SELECT VARID FROM VARDESC)
SELECT * FROM DATADDH WHERE VARID NOT IN (SELECT VARID FROM VARDESC) / DELETE FROM DATADDH WHERE VARID NOT IN (SELECT VARID FROM VARDESC)
SELECT * FROM DATADDF WHERE VARID NOT IN (SELECT VARID FROM VARDESC) / DELETE FROM DATADDF WHERE VARID NOT IN (SELECT VARID FROM VARDESC)
SELECT * FROM DATADD4 WHERE VARID NOT IN (SELECT VARID FROM VARDESC) / DELETE FROM DATADD4 WHERE VARID NOT IN (SELECT VARID FROM VARDESC)

10. Remove Lists from parameter type variables.

SELECT * FROM VARDESC WHERE VARTYPE IN ('P','H','F','4') AND LIST IS NOT NULL

UPDATE VARDESC SET LIST = NULL WHERE VARTYPE IN ('P','H','F','4') AND LIST IS NOT NULL