Archives

ECM Auditing. Its Limitations and a Solution.

What is ECM (Engineering Change Management)?

The basics as to what it is and how to use it can be found on M2M Expert.

Here is an excerpt from the site. I’ve emphasized the text relating to today’s post.

This feature of Made2Manage manages the process of engineering changes in parts and associated documents such as job orders and sales orders. It provides a way to limit changes users can make to the database and a way to notify users of proposed changes in advance.

You can use ECM auditing to oversee changes users make to standard item master parts as well as documents (e.g., purchase orders, quotes, etc.).

How it works

Many companies, including my current employer, use this auditing to monitor changes to sales orders, quotes, and purchase orders. This is administered from the ECM tab of the CSPROD screen. If enabled, and I believe it is by default for all options except quotes, M2M will output all user induced changes to a SQL table called SYECAUDT. You can use the RPECAU, ECM Audit Report, to view them.

Sounds great, what is the catch?

ECM auditing is a good idea, and one that I suspect will be revamped and improved because of SOX compliance requirements. These improvements cannot come too soon because there are several problems with the system.

First, the report is not very intuitive. For example, the sort range descriptions don’t match the choices you make. You choose sales order and you get “Form Mnemonic” as a description. When you go to use it, you’ll see what I mean. I’ve written some Crystal Reports as a substitute for RPECAU and I may post them in the future if readers request it.

Second, M2M has not created a mechanism to allow the user to delete data older than a certain date. So, if you enable auditing of sales orders and leave it on, it will gather that data literally forever and take up needless space doing so. Since many companies have this enabled without realizing it, this is particularly troubling.

Third, it can take quite awhile to run the report depending on how many transactions are logged.

Finally, this audit runs every single time a table is changed and it uses resources to do so. Only enable the audits if you really need them.

A Perfect Example

I was recently working with a Made2Manage implementation that had been operating since 2001. They were tracking everything except Quotes with it and I ran some tests. Incidentally, the overall size of the database was approximately 11 gigabytes.
SYECAUDT Table Size: 470mb Record Count: 526,000

When I ran the RPECAU report on this database searching for a single job order number from about 2 weeks ago, I had to wait 21 seconds. I realize that this isn’t awful, but I’d rather not have users wasting time staring at their screens for that long if I can help it.

What can I do about it?

Well, SYECAUDT is a SQL table. You can use a SQL delete statement to cut down it’s size. Before I go any further, let me issue my standard disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code taken from this site, make sure you test it thoroughly on a test company, or better yet a test server, before you ever use it on your live data.

Here is a sample SQL statement which will delete all records over a month old:

DELETE FROM M2MDATAXX.DBO.SYECAUDT -- Replace XX with your company number such as 01
WHERE       FTDATE <= DATEADD(DAY,-31,GETDATE())

Incidentally, never create a delete statement without a WHERE clause as it will delete every record in the table. In fact, it's best to create a SELECT statement first so you can examine which records will be affected when you take the next step.

For example, suppose you only wanted to delete sales order records older than 2 months. However, you also wanted to leave those records where users have deleted items from sales orders in the SYECAUDT table. Here is the SELECT statement:

SELECT * FROM M2MDATAXX.DBO.SYECAUDT -- Replace XX with your company number such as 01
WHERE       FTDATE <= DATEADD(DAY,-62,GETDATE())
AND fcaction <> 'D'
AND fcform = 'SO'

After you have run the statement and reviewed the records returned, you would then simply substitute "DELETE FROM" FOR "SELECT *" and you're all set.

Anyway, after running the first delete statement against that sample database I mentioned I got the following:
SYECAUDT Table Size: 7mb Record Count: 6,000

As you can see, that is quite a difference. How did that affect performance of the RPECAU report? I ran the report with exactly the same parameters above and received the response in 3 seconds.

In addition, you can set this up as a recurring job in SQL Server to automate future clean ups. I'll demonstrate how to do that in a future post.

2 comments to ECM Auditing. Its Limitations and a Solution.

  • Good advice. Junior DBAs out there – if you want to be safe, instead of doing a DELETE statement, do this:

    SELECT *
    INTO M2MDATAXX.DBO.MyNewArchiveTableName
    FROM M2MDATAXX.DBO……

    That “Into” will take the records and insert them into a new table with the table name you’ve chosen. That way, you’ve got a backup table that will keep yourself safe in case you accidentally delete things you need later.

  • Thanks Brent, I completely forgot to mention that. Incidentally, Brent is a SQL Jedi Master, and one of the people I go to with SQL issues.

    If you use the select into statement, don’t forget to use a Drop Table statement on your temporary table afterward to clean up. Otherwise, you may run into problems when you migrate Made2Manage. At the very least, it will show up when you run the “ObjectCheck SQL Script” prior to migration.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>