QUESTION

Why shouldn't I compact or purge my QCC files when I'm using Enterprise Data Loader (EDL)?


ANSWER

First, a bit of an explanation. QCC files are loaded to SQL Server via the Enterprise Data Loader. When EDL loads a QCC file to the database, it first checks if the file already exists in the SQL Server database. If it does, then EDL only loads the data that has been added/edited since the last time the load occurred. If it does not, then EDL creates the file in the database and loads all data it can find.

In order to determine QCC existence, EDL looks for a unique combination of QCC file name and an internal creation date on the file. This is an internal date within the QCC file that is not the same as the date visible in Windows Explorer. If the file name and date (to the second) match, then the file exists.

This way, customers can have the same file at different plant sites without a collision. The assumption is that it is exceedingly rare that two files would be created at the exact same moment in time with the same name at two different plants.

Keeping this in mind, here are a couple scenarios:

1. When you delete a QCC file in QC-CALC, the new file has a new creation date even though the name is the same. This will appear unique from an EDL perspective so it will reload the new file and leave the existing one in place. This will appear to duplicate the file. To combat this, EDL automatically flips an "archive flag" on the old file so it does not appear twice in Enterprise Report Scheduler (ERS).

2. When you purge a QCC file in QC-CALC, it deletes all records and then resets the internal creation date. This happens because there are already existing records in the SQL database. When the new records were added to the QCC file, they would collide with the existing records in SQL. To solve this, we reset the date to avoid collisions. Because the file name/date combination is unique, EDL will reload the entire file and set the older version of the file to archived.

3. When you compact a QCC file in QC-CALC, it deletes certain records and re-numbers the existing records. This would be impossible to match to the SQL server database. As such, we reset the date in the QCC file causing a new file to be created by EDL.

As can be seen above, when users are constantly compacting/purging QCC files, it is causing EDL to (1) do a lot of extra work reloading data that is already present in the database and (2) use up a lot of space on the hard drive of the SQL Server from all the duplicate data.

MORE INFORMATION

Fortunately, there are a few solutions to this problem both before and after it happens.

1. The first solution is to simply lock this functionality for all copies of QC-CALC. This can be done via the Administrative Tools using the Menu System Manager to lock the Purge and Compact menus in both QC-CALC Real-Time and QC-CALC SPC.

2. The other two are outlined in the linked article called Duplicate file names in ERS. Please see this article for more information.