Precision, Performance, Confidence. ™

Cleaning up data in MET/TRACK before a MET/TEAM Migration

If you have already migrated your MET/TRACK data into MET/TEAM, view these articles:

How to review Migrated MET/TRACK data in MET/TEAM video

 Where does my MET/TRACK data go in MET/TEAM?

Why do I need to clean up my data?

You know the old saying, “garbage in/garbage out”.  Now most of us don’t like referring to the data in our database as garbage.   But the saying can be applied to databases that have become disorganized with data that is duplicated due to misspellings, abbreviations, using a different word order, commas, etc.

Take the following:

What if we wanted to search  for something with the manufacturer of “HP?” We would need multiple search criteria, right?

The same holds true for any migration of data from one database format to another.  You want to make sure that your data is as clean as possible so you can start off in the new application with as little “garbage” as possible.  And MET/TEAM is a new application.  It is not just an upgraded version of MET/TRACK.  However the data that gets collected is still very similar and your MET/TRACK data, while in different locations and formats is still valuable and available. 

MET/TEAM uses a unique combination of Manufacturer and Model to create model “Types”.  It also writes Manufacturer field values to the "Facilities" table.  

The following section will focus on MET/TRACK fields that will make up the “Types” table.

How do I find out if my data needs a cleanup?

In a stock installation of MET/TRACK there is a report called “Basic Inventory” you can run that report to see some of the data that will eventually become your “type” data in Met/Team. Here is an example:

 Another method of getting this data may require support of a database expert using ISQL. 

Running a simple query in Interactive SQL (ISQL), an administrative tool in Sybase, can tell you exactly how many unique “types” you have in your database.  It will also point out all the different ways a model name or manufacturer, is listed in your database.

Here are instructions/scripts you will need for ISQL. 

First type the following command into the ISQL Statements window:

Select distinct I4202,4203,4204 from mt.inventory;

Click “execute” (it looks like a “play” button)

This will create a unique list of the Manufacturer, Model and Description of all the assets in your database.  However, since we used the word "Distinct" each unique combination will occur only once in the SQL query, even if there are multiple assets with this combination.

You will get something that looks like this:

 

You can already see that I have three different Model names for a Fluke 87V

Since this will create 3 different types in my MET/TEAM database, I need to do some housekeeping!  Note that the description is not a crucial field and it does not influence a "Type" creation, but it can be a good reference in your data that tells you which entries are valid. It will also tell you when the "description" field is what you are using to make an entry unique.  Since MET/TEAM doesn't use description when creating types, you may need to add additional descriptors to the model field to create two or more separate types.  For example, you may need to add -03 to a 5720A model number if "calibrator with wide band option" is in your description field and the model number is just 5720A.  This will create two distinct types if corrected before your migration:

Fluke 5720A

Fluke 5720A-03

Next add this to your ISQL statement:

Output to c:\temp\type_data.csv

Now execute the query again.

This last step will create a CSV file for you so you can actually have a record of all the entries.  It will be a useful reference.

Once you have identified any data problems, you have two methods of cleanup.

The first cleanup method is to use the “Global Change” application inside MET/TRACK.

How do I use Global Change  to clean my data?

**Backup your database before you make any changes to existing data**

Log in to MET/TRACK as the MT user.  Global Change is under the “database” menu:

I am using “Advanced Global Change” in this example. *Note, if you do not have the “advanced” button contact softwaresupport@flukecal.com for instructions on enabling it.

Just use the drop down arrows to populate your query:

I am updating the Inventory table (mt.inventory) with my changes, specifically the model field where the model number is "87 SERIES V."

Literally, I am telling MET/TRACK to:

“update mt.inventory set I4203 = 87V Where I 4203 = 87 SERIES V”

If there is more than one asset with the model I am trying to change then all of them will be changed at the same time.  I can check to see the number of records affected – to get a sanity check that I am doing the right thing and I can rollback if necessary.  Once I am certain about my changes, I can “commit”.

The second clean up method is to use ISQL

You can do the same thing using Interactive SQL (ISQL) again.  Since this method actually changes data in your database, you need to proceed with caution. 

**Backup your database before you make any changes to existing data**

  • Open ISQL, (logging in as the MT user)
  • Type this command in the command window:

update mt.inventory set I4203 = '87V' where I4203 = '87 SERIES V';

Once you have decided that this is what you want to do you can add another line to your command and run it again:

update mt.inventory set I4203 = '87V' where I4203 = '87 SERIES V';

commit

As committed changes cannot be rolled back – your changes will be permanent. 

You can edit this script to change manufacturer names, descriptions, model names, really anything in these three key fields that go into creating the “Type” record in MET/TEAM.  Manufacturer names are used in to create entries in another key table as well and can create a lot of headache if they are not cleaned up in advance.  This may take some work – but the rewards will be obvious after your data migration.

Can I make changes to other fields in my database?

Yes we have only been covering the fields that go into types at this point.  We will discuss other fields in the Met/Track database that will affect the integrity of your Met/Team migration later in the article.

The following section will focus on Met/Track fields that will contribute to the “Facilities” table

Facilities are really just companies.  The manufacturer of your equipment is a facility.  The businesses that send you equipment to calibrate is a facility, the company you work for or the company you send equipment to for calibration are facilities. Some facilities may be Manufacturers, some Labs, some sub-contractors.  All these companies, regardless of their function is a “Facility” in Met/Team.

How are Facilities created during a migration?

Facilities are created from three sources:

  • Manufacturers
  • Customers
  • Segregates

Manufacturers (I4202) were discussed earlier.  If you did not look for duplicates and misspelled Manufacturers in the “Types” section now is your chance.  If you did do some cleanup on the field “Manufacturer” – you will be doubly rewarded for your efforts.  This very same field used in the “Types” table also creates an entry on the “Facilities” table.

Customers (K4601, K4602) In the Met/Track database.

If you were using the “Customers” form for its intended use and you were careful to keep your customer table clean and up-to-date, you may not need to clean it up any further.  MET/TRACK used field K4601 as a unique “Customer ID” field and the field K4602 was the customer name.  These fields will be migrated as Facility Name from K4602 and Facility Number from K4601.  If you have duplicate customer names in K4602, now would be the time to change that information.  You can use tools in MET/TRACK to update customer/asset relationships to make sure they are current. 

Please note: If you were using any other fields on any other table in the MET/TRACK database in place of those that made up the customer table  you will want to export that data to a CSV file with specific information that links the associated asset records. The data will migrate, but it will be in fields called “extended data” fields which are basically just generic holding bins for “custom” data.    The data that gets imported will have use, but there are a few more steps to go through to make sure customer information is correctly linked up to asset information in MET/TEAM.  For assistance with exporting customer data from other locations in MET/TRACK and/or importing and linking that data in MET/TEAM contact softwaresupport@flukecal.com.

 

Have more questions? Submit a request

Comments

Please sign in to leave a comment.