Thursday, May 8, 2008

The Financial System Manager's Toolbox (Part I)

A common question. What tools should a Financial Systems Manager have in his/her toolbox in order to be effective? I started to add up the bits and pieces that my team and I surround ourselves with and it turns out to be quite a lot. I suppose the list depends on what responsibilities the team is tasked with in each organisation but here we do pretty much everything from project management, help desk support, training, operational checks, backups, server updates, installs etc. So what do we use?

Excel - Without a doubt the corner stone of every accountants existence. Take this away and you risk imploding the known universe.

Word - Pretty obvious but essential for documenting. We make sure that every system is fully documented with backup and recovery plans. Hand in hand with documentation comes screen shots and this is where a tool such as Snag-It comes into play. A must have in order to make your documentation "idiot" proof. Once you have finished your document it is a good idea to save it as a pdf so that it becomes difficult for others to tamper with it. Microsoft has an add-in for the Office Suite which saves to pdf or xps. You can find it here.

Text Editor - I use EditPad. Especially useful when you receive and or send files between PC based technology and mainframes etc. It allows you to check record lengths, convert between Windows, UNIX and Mac, change cases and a whole lot more. Very useful tool.

SQL Editor - The current version of SQL Server Management Studio does not provide intellisense or auto complete. It also does not allow you to save your scripts in an orderly searchable fashion. I use Speed SQL IDE from Quest Software (which is no longer supported) as it provides all this functionality required and saves versions of your scripts in a central repository which can be backed up daily. They have replaced it with Toad, which I do not like. You can also use a tool called SQL Prompt from Red-gate. It works within SQL Server Management Studio and Visual Studio.

Backups - All our Financial System have been consolidated to our standard database, SQL Server. Sometime back I started to run out of disk space and also window time for backups. We found a product, also from Quest Software, called LiteSpeed. Now this is a product that saved my bacon. Our backups were taking 1,5 hours and size on disk of 80Gb's for our main ERP system. After installing LiteSpeed the backup times reduced to less than 30 minutes and the files size only 10Gb's. It compresses on the fly as it backs up. An absolute winner, saves you time and cost of additional disk space.

Text File Searching - A number of older style ERP systems produce files for every process within the business. These files are mainly text based and for example can be log files and spool files. Now in the past if things did not go according to plan these files would contain error messages or warnings. If the person in charge of the system was not aware of any problems they may go unchecked. These files were also produced during interfaces between systems and the only way to make sure that they were successful was to troll through the files and make sure that they were OK. A massive task if you have as many orgs as we do with as many interfaces running daily. I could not find anything to do this job automatically back in 2000 so speced and had my own application written by a Delphi programmer. It still works today and saves my guys hours. One sets up a search location, a file mask for the files being searched, what you are looking for or not looking for, a location for the file to be copied or moved to, an SMS number and message if you need to SMS someone etc. It was written as a service or to be interactive. Love it!

All the above tools combined assist myself and my team in maintaining our Financial Systems environment to a very high standard. Maybe one or two of them are just what you are looking for.

In Part II I will touch on more specific tools such as MS Integration Services, Analysis Services and Reporting Services.

- Paul Steynberg


No comments: