Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Tuesday, 23 August 2011

Controlled Use of Excel for BI

1. Introduction

In most Financial Institutions the use of Excel is so embedded that any project to curb this is almost doomed to failure from the start.

This blog is an attempt to elicit discussions and to explore the appetite to address some of the inherent risks with Excel spreadsheets and to see how one can deliver supportable MI using Excel. It should be stressed that this proposal should not be seen as a replacement for strategic tools or processes.

  
2. Benefits and Risks of Using Excel

The use of Excel comes with a multitude of pros and cons. In days past the downsides were considered to be worth the trade-off against the benefits but his is starting to change with the introduction of more stringent governance via legislation such as Solvency II, Basel II and SOX.
Below is a list of benefits and risks of using Excel in your core data streams.


2.1. Benefits
• The skills base for Excel is large and practically every person within a company will be familiar with it.


• Excel is relatively cheap and most users in an organization will have it.

• Excel lends itself to very easily creating financial models in a relatively short time span which normally could not be done in a more structured environment. Less reliance on IT means more flexibility and less time.

• The user experience from creating datasets to graphs is very intuitive and rich.


2.2. Risks
• Any VBA code or macros are specific to the workbook and are not held in a central repository to be reused. This raises concerns about version and change control over the code.

• If the required data set is sourced from any application/database within the IT estate, no way exists to document data lineage or do impact assessments when designing changes to these sub systems.

• Although Excel skills are widespread, most users will develop the model based on easy to develop rather than efficiency of code. No standard way of developing models makes it difficult to support/hand over.

• Any links to external data are also held in the workbook or specific to the PC. Often they contain T-SQL specific to the use-case and this is also not subject to version or change control.

• Excel has restrictions on the amount of data that it can consume. (1 million rows for Excel 2010, 2007 and 65,000 rows for Excel 2003)

• Security to the underlying database is not controlled via a centrally maintained application account but rather based on individual users. This requires users to be added and maintained at Database level.

• Access to spreadsheet models in not restricted via permissions or passwords as they are mostly stored in shared drives accessible to large groups of people.

• Users often save versions of the spreadsheet at various points during a process cycle and between process cycles for archiving. This leads to an explosion of redundant data and cuts of the code being held leading to increased storage costs. This can also lead to confusion as to what is the most recent and correct version. These issues are compounded by users sharing files by e-mailing them to each other.

• The models can become complex in nature and often process large volumes of data. These models can take a long time to run on a PC and in some cases can crash.

• Distributed development and locations lead to key person dependencies.


3. Risk Mitigation

In order to mitigate the risks outlined above one could put the following processes in place. Refer to Annexure A for the risk mitigation matrix.

  
• Set up standards on structure and development of Excel models.

• Ensure all models are developed against these standards, peer reviewed, tested, documented and appropriately transitioned into production.

• Store the models centrally in a controlled and identifiable location.

• Convert macros and VBA into add-ins and store them centrally.

• Implement security around the models for both access and changes.

• Implement appropriate change control and monitoring of the models to ensure that they are not changed without authorization.

In addition to the new or changed processes, technology can be used to mitigate some of the risks. Based on the risk matrix in Annexure B, a combination of Excel Services 2010 with PowerPivot and Prodiance, in theory could mitigate all the identified risks.
Below is a discussion around these technologies and how they could mitigate the risks.


4. Excel Services

Excel Services is the Excel engine delivered through Sharepoint and has the following key advantages:
• As it is delivered via Sharepoint on a server you can leverage the availability of larger memory, many more processors and 64 bit technology. This should reduce the time it takes to run larger and complex data models.

• Security is controlled via standard Sharepoint functionality.

• Centrally used data connections can be used thus eliminating the need for users to be granted direct access to the data.

• When published to Sharepoint one can only make certain parts of the model visible to the users thus hiding any business logic and underlying assumptions, enhancing security even further.

• Automatic scheduling of data updates can be implemented thus making refreshed reports instantly available to the users.

• As workbooks are versioned you can always restore to a previous one should you it be required.
 
Excel services does have some disadvantages listed below:
• Not everything that is native to the desktop version will work in Excel services such as add-ins and certain controls. Also some functionality becomes limited such as Pivot Tables and screen split and zoom functions.

• In order to modify the models you still need to pull them into the desktop version first and then republish them.

• Sharepoint is not a particularly easy technology to deploy and maintain and in order for Excel Services to be installed you have to have the Enterprise version.

5. PowerPivot

PowerPivot is an Excel add-in which has been developed by the Microsoft SQL Server Analysis Services Team and uses in-memory column compression technology branded as Vertipaq. This technology is very similar to that used by such market leaders as Qlikview. PowerPivot allows you to bring in large data sets into Excel and even join these sets to each other and then use the resulting data to report off. It is very efficient at consuming large amounts of data and when used in x64 bit mode with large amounts of memory is quite astonishing in its response.
 
PowerPivot also extends to Sharepoint and when combined with Excel Services in Sharepoint the uses and power suddenly start to become apparent.

6. Prodiance

In one of my blogs a while back I mentioned 4 companies that provide Excel Spreadsheet Control Software. We ultimately decided to back Prodiance as our choice, a decision that has now been ratified by Microsoft.
 
Up until 7th June 2011, Prodiance was an independent company specializing in risk and control software for Excel. It has now been purchased by Microsoft and is a wholly owned subsidiary. With Microsoft now purchasing the company more integration with the Office suite and Sharepoint is anticipated.
Advantages of implementing Prodiance over your spreadsheets are as follows (as per Prodiance pdf):
 
• Electronic sign-off and optional eSignatures

• Email notification of significant or unauthorized changes (e.g. exceptions, policy violations)

• Extensive cell-by-cell, file level and workflow audit trails

• Side-by-side comparison of changes between versions

• Management reports, dashboards and drill-down into detailed reports

• Automated document versioning

• Check-in/check-out (optional)

• Web based access to all historical versions

• Association of parent/child versions and unified view across all audit trails and reports

• Robust document security model integrated with Active Directory/LDAP users and groups

• Permissions to grant appropriate folder and document access levels (e.g. view, add, update, delete, approve, etc.).

• Microsoft Information Rights Management (IRM) encryption for spreadsheets containing sensitive information

• Workbook, worksheet and cell level protection

• Optional lock-down for cell level input control with data validation

• Excel Services for displaying and publishing read-only versions of critical spreadsheets and BI reports

• Extensive cell-by-cell, file level, system level and process level audit trails

• Tracking of changes to key inputs, outputs, spreadsheet data, formulas, macros and queries

• Tracking of changes to queries and data connections to external data sources

• Auditing support for spreadsheets and Access databases

• Optional lock-down for cell level input control with data validation

• Interactive link/dependency diagrams

• Auditing of specific input ranges (including named ranges)

• Automated email alerts upon changes to input ranges

• Color scheme tool to highlight (used and unused) input cells

• Spreadsheet validation and testing via automated cell, formula and range diagnostics

• Proactive identification of spreadsheet development and structural problems

• Comprehensive document and records management

• Enterprise class workflow management

7. Conclusion

In order to achieve the controlled use of Excel within an organization you will need to go through a change in technology, process and culture. I hope this blog has provided some readers with a spark for debate.

Appendix A
 
 
 
Appendix B
 
 
 

  

  

  

Tuesday, 16 February 2010

PowerPivot Storage

For you hard core techies out there, stop reading now. This post is for those people in the world who had no idea that the Office 2007 (and upwards) files are actually just a bunch of files zipped up. I was curious to see how PowerPivot Data was stored with the Excel spreadsheet and was amazed to find out that a number of my associates were unaware of the make up of the new Excel formats.

In order to illustrate the concept take an Excel (or Word) 2007/2010 file and just to be safe make a copy of it. Then change the file extension from XLSX to ZIP and then open in Winzip. Shock and horror we find a whole bunch of files (mostly XML) in various directory structures. This was done in order to comply with Office Open XML Format. Read all about it here.

Back to the original question about how the PowerPivot Data is stored. It is stored in a directory called xl\customData with the extension DATA within the Excel (zip) file. Here is a bit more on how the data interacts with your memory.

- Paul Steynberg

Monday, 1 February 2010

PowerPivot

Microsoft promised BI for the masses and PowerPivot is the first real taste of this promise. Originally code named Gemini this Excel Add-in was developed by the SQL Server Team. In a nutshell it is SQL Server Analysis Services on your PC. In order to get it up and running you have to have Office 2010 (Beta) installed and XP should be on SP3. Here is a full list of requirements.

I am not going into the full technical bits of the product as this has been done many times before and here is a full list of links to get you up to speed:


Take a look at the demo videos as they are quite quick and really show the power of the product. To illustrate I took a normalised data source and imported 3.5 million rows into PowerPivot in under 2 minutes. I then created a Pivot Table from the data with the new look and feel of having slicers across the top and down the left hand side. The results were blistering fast.

This is a first release and the product is full of bugs. As with most Microsoft products wait for V2 before releasing into your production environment. More to come later.

- Paul Steynberg

Saturday, 29 August 2009

A Novel Way of Dealing with Excel Models

Excel is both heaven and hell in the business world. It's a great calculator, modeler, reporter, you name it. With this incredible flexibility comes a trade off, your data is stored in multiple locations throughout the company in an unstructured and often unsecured manner. This data is not visible to the Enterprise and in most cases is not integrated into your reporting stack. Loads of capturing and data copying results in huge inefficiencies and in many cases errors.

My current employer embarked on a project 18 months ago to "black box" a number of these high profile models. Without going into the gory details let's just say that it did not work. I joined them in April of this year when my family and I made the move to London and was tasked with firstly evaluating the models to give an opinion on whether they were fit for purpose and could they be supported into the future. Sadly they could not and it was my unfortunate responsibility to stop the bleeding and bring the project to a conclusion. That was the easy part, the hard part is what do you do now as the original issues around the Excel Models were still relevant.

The obvious choice would be to take a look at the short listed products again that were selected at great expense and find the next suitable candidate. I was not comfortable with this approach and we did not want to try and rebuild them in PerformancePoint as all development for the product has been discontinued by Microsoft. (Don't even get me started on this subject, I have bitten my tongue since they shook the market with this announcement in January 2009).

I have come up with a rather "out of the box" approach which my team is in the process of designing at the moment. We needed to achieve a few objectives from the project being:
  • The models should be flexible
  • They should have access control
  • Changes should follow appropriate change control
  • They should be auditable
  • The data should be stored centrally and available to other business systems
Package these requirements up with the new Solvency II requirements and SOX etc, and you have quite a tall order.

Off course the most flexible model is still Excel. So what if you could keep Excel and also apply security, access control, change control, auditing etc. This led me into a journey of trying to find software that would give us this functionality. And guess what, it does exist, albeit a software concept in its infancy. Gartner have even released a paper on it (March 11, 2008) under the heading "MarketScope for Spreadsheet Control Products, 2008". It was quite comforting to find out that we had made contact with all the major players prior to this report and that our findings were in line with Gartners. Here are the major players in this space:
They all have a presence in London. Cimcon and Prodiance are US based suppliers whereas ClusterSeven and Finsbury are UK based.

Now they all do pretty much the same thing but in slightly different ways. Essentially each product has 3 major functionality groups:
  • Discovery and Risk Assessment - Basically trolling through all your locations looking for spreadsheets, categorising them and creating a baseline.
  • Monitoring - Keeping track of your spreadsheets and keeping a full audit trail of all changes and versions.
  • Development Tools - Some are add-ins, others applications that assist in developing your spreadsheets to minimise risk, errors etc.
So in a nutshell you can see who did what to which spreadsheet over time and set up alerts and workflows etc. You will be able to see broken links between spreadsheets and a whole host of interesting things that are out of the scope of this paper. We are in the process of getting RFP's from the suppliers and although I have some opinions will reserve them until a later date.

This piece of software should take care of most of the issues surrounding the Excel Models. Now for centrally storing the data and making it available to the rest of the business. My team is in the process of requirements gathering for a framework that will allow us to upload many different data sets from these models into a SQL Server database and thus make it available to our reporting layer. This may sound quite simple but to build a framework that is scalable. supportable and flexible is no easy task.

Watch this space!

- Paul Steynberg

Sunday, 22 June 2008

The Financial System Manager's Toolbox (Part II)

A while back I promised to elaborate on Microsoft Analysis Services as part of a Financial Systems Manager's Toolbox, so here it is. If there was an Accounting God this would be the tool that he would bestow upon his most loved people. SSAS is shipped with certain SQL Server versions. To see if yours is please follow this link:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

I cannot even begin to tell you how incredible this software is for accountants. (Well actually for most people but you get the idea). It allows one to take huge amounts of data and store it in such a way that you can retrieve aggregated data on any combination of dimensions FAST. Two words in that sentence are of importance. The first is dimension and the second is fast.

A dimension in SSAS terms is one of a possible number of ways to describe a number. Here is an example. I give you a number, say 100. That means nothing to you. Now I start to give you some dimensions and dimension members and suddenly the 100 makes sense. Here they are:

Dimensions and Member of the Dimension
Entity=Contoso Head Office
Department=Accounting
Account=Bank Charges
Scenario=Actual
Period=Y2009P03
Currency=USD

Now that you have the dimensions and the dimension member the 100 suddenly makes sense. It is the actual bank charges (as apposed to the budget bank charges) for the accounting department at Contoso Head Office for the accounting period 3 of Year 2009 in dollars. Now imagine having millions of rows of data each having these dimensions describing the number in each row. To try and make sense of this would be madness. Along come SSAS.

SSAS takes the data and builds OLAP cubes which pre-aggregate the data based on internal algorithms in order to allow the user to bring back information at every possible intersection of data with incredible speed.

Here at my organisation I have built a few financial cubes off our underlying ERP system. These cubes allow the rapid slice and dicing of information from a number of delivery mechanisms. I prefer delivering the data in pivot tables in Excel linked directly to the cube. One can also use tools such as ProClarity and PPS Monitoring and Analytics. The uses are endless. PerformancePoint Server uses SSAS as the data point for all its components including Management Reporter, a product that we are rolling out in parallel with our new ERP System, Dynamics AX 2009.

Once you start using something like this you will know that the Accounting Gods are smiling upon you.

- Paul Steynberg

Sunday, 11 May 2008

Leverage Your Existing Investment in Microsoft (Part I)

Under utilisation (Correctly spelled, see footnote 1) of one's current investment is probably one of the most common issues surrounding IT projects. This is especially true with Microsoft. Just as an example when I speak to other Managers about Analysis Services I am most often greeted with a look typically sported by the lesser spotted stunned mullet. It is quite unbelievable how many companies install an ERP system with SQL Server as the underlying database and have no idea that it is shipped with some great additional features such as Analysis Services and Reporting Services. To check what your version is shipped with go to: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Just as a quick FYI, Analysis Services is a framework that allows one to produce cubes for typically “slicing and dicing” ones data, often referred to as OLAP. Reporting Services is a tool that allows one to author and deliver reports from within a web environment. I am not going to go into much detail on these 2 gems (They will feature in a future blog), as the biggest culprit is the Office Environment, Excel being the ring leader.

Excel, Excel wherefore art though, a product that seems as old and familiar as Bill Shakespeare himself. Yip, I’m back here again, lurching on about Accountants and Excel, but you must understand, you just cannot separate them. So if this is true then why do so many Financial Systems not ship with Excel tools as standard. I had this happen to me some years back with our current system. The original decision makers who attended the demonstrations on the product were amazed by how easy the information could be pulled into Excel. However when it came time to actually implement this great functionality we were told that it costs “extra” and we were not licensed. Not just a little, but a lot extra.

Act II – In walks my stubborn alter ego, has a look at the product and says “We can write something like that for free”.

Evil Vendor – Ah, but our product is fully integrated with securities etc.

Stubborn Alter Ego – Big deal.

And so our Excel integration was born a few weeks later with a bit of help from our Database guru. We only worked on this from time to time when we needed a break from our daily routine and if we had to work out the actual man hours we would estimate that it was no more than 40 hours in total.

We now run 7 Excel based extracts from our Financial Systems, 1 Fixed Assets, 2 Accounts Payable and 4 General Ledger. We benchmarked them against the products supplied by our vendor and found that on average they ran 20 times faster. Not kidding, 20 times faster. How was this achieved? There are only 2 questions to be answered. Firstly is the person making the query allowed access to the data and secondly where is the data.

The first question can be answered by finding the security related tables within the database. One normally starts with finding the user table which holds the user id, name and other details related to the user. From here you then link to tables that associate the user with different entities and then profiles/menus/accounts etc etc. You need to understand how the security is set up in order to make the connections. A little bit of digging and you will work it out.

Once you understand the security model you then need to find out where the data is that is required. In my example of one of our Excel extracts we extract the balances of certain accounts chased down from a higher level account into several columns based on some criteria. Below is screenshot of the criteria that the users would enter in order to get the information. This particular extract will look to the account master table in order to return the account description and also the account balance table in order to retrieve the balances for the selected periods.



Most of the sheet is protected which gives the user limited input. Everything in grey cannot be changed, only the white blocks can be. Each of the input fields has a comment attached to it for on-line help and most of them are validated so that users cannot capture invalid information. In reality the users will complete the input fields and save the spreadsheet for repeated usage.

Once all the fields are updated the user then clicks the “Run Extract” button. This button has some Visual Basic code behind it which is also password protected. Once clicked the code traverses through the input fields and passes them as parameters to a Stored Procedure in SQL Server which in turn returns a data set and populates the data sheets.

Here is a step by step explanation of how this particular extract works.

The amount columns from 1 through 6 represent 6 different columns of data for each account that can be extracted and can change for each column based on the fiscal year, the scenario (Actual/Budget/Forecast) the currency (drop down validated to the currency table), Currency type (Also drop down and validated), from and to periods. I have not had anyone ask for more than the 6 comparative columns yet so one can assume that it is a safe number.

The accounts are then entered. If the account is a parent account you can specify the chase level which is just how many accounts down from this account do you want to display. The show levels switch if set to “N” will only show the accounts at the bottom of the chase and if set to “Y” will show the original account chased and all its subordinates down to the chase level. The New Sheet switch if set to “N” will return the data set to the DataDefault Sheet and if set to “Y” will create a new sheet for each account line’s data set that is fetched. Each sheet will be named with the account number.

There is setup information higher up for the Server Name, Database Name and version number of this particular extract. I have not shown this for obvious security reasons.

Now once the user hits the “Run Extract” button the VB Code collects the input data and builds a string that eventually gets sent to the server and database in the set up information as a stored procedure.

The stored procedure accepts the parameters and now goes through a set of validation procedures starting with the user access. When the user calls the stored procedure their domain and user name are available for the procedure to use in order to compare to the security settings within the product. If the user is allowed to see this org and the account then one allows the procedure to continue. If not we return an error message indicating lack of security. As in our case you may have to create a security link table that holds the Windows full domain name of the user logged in and then the user name of the user within the Financial System. If your system is not Windows Security compatible you may have a spare field in the user table to store the windows login.

Once the data sets are returned the VB script does a whole lot of quick steps such as:

Format the numbers columns correctly
Correct the column sizes to fit the data
Put in some totals

Some procedures done before the data is returned are:

Clear the data area to be populated
Remove and formatting including subtotals etc.
Delete any sheets that are not in the original sheet. (We have made provision for specially named sheets that are never deleted so the user can link them up for other purposes etc.)

Some of my other extracts are even more elaborate. For example the AP Voucher extract allows one to return the entire vendor master into Excel for an org, by double clicking the vendor line you return all vouchers from the system in another sheet. By double clicking a voucher one can return the lines that make up the entire voucher and even further down to the distribution etc. Our users just LOVE it, no need to navigate the application if you can just double click in an Excel sheet.

The point of this blog was merely to demonstrate that we must seek out ways to maximise our Microsoft investment and to challenge the hold that some vendors appear to have over us. In my next session on this I will highlight Analysis Services and how this product can make a full grown Accountant weak at the knees.

- Paul Steynberg

Footnote 1

An excerpt from a letter supposedly penned by John Cleese to the citizens of the Unites States of America: You should look up "revocation" in the Oxford English Dictionary. Then look up "aluminium". Check the pronunciation guide. You will be amazed at just how wrongly you have been pronouncing it. The letter 'U' will be reinstated in words such as 'favour' and 'neighbour', skipping the letter 'U' is nothing more than laziness on your part. Likewise, you will learn to spell 'doughnut' without skipping half the letters. You will end your love affair with the letter 'Z' (pronounced 'zed' not 'zee') and the suffix "ize" will be replaced by the suffix "ise". You will learn that the suffix 'burgh is pronounced 'burra' e.g. Edinburgh. You are welcome to respell Pittsburgh as 'Pittsberg' if you can't cope with correct pronunciation. Generally, you should raise your vocabulary to acceptable levels. Look up "vocabulary". Using the same twenty seven words interspersed with filler noises such as "like" and "you know" is an unacceptable and inefficient form of communication. Look up "interspersed". There will be no more 'bleeps' in the Jerry Springer show. If you're not old enough to cope with bad language then you shouldn't have chat shows. When you learn to develop your vocabulary then you won't have to use bad language as often.

A rebuttal to this can be found at http://www.authorsden.com/categories/article_top.asp?catid=19&id=26498

Friday, 9 May 2008

PerformancePoint Server and the Art of Debits and Credits

All good accountants know that if you add up the trial balance for a company you should always get to zero. True? Maybe not when loading your data from your current General Ledger to PerformancePoint Server (PPS). An accountant is drilled from day one that debits are positive numbers and credit are negative numbers. This is the way most Financial Systems store the data and when you see that an asset is positive and a liability is negative it makes sense to everyone. Now try and explain to a non-accountant why income is a negative as well. Its a no go area and with good reason, it makes sense that everything good is positive and everything bad is negative.

Back to PPS, if you load your trial balance into the product with everything correctly signed, you will find that your trial balance when viewed from something like an Excel Pivot Table does not add up. So what has happened?

The answer is that the Account Dimension in the Planning Business Modeler has intelligence built into it. When you define an account you must also define the account type. Associated with the account type are time balance attributes as debit/credit attributes. In the screen shot below from the help file we can see what Microsoft attribute associations for income statement accounts.







So this tells us that for expenses the debit/credit attribute is debit and the incomes are credit. Just like the Prof at University said. As a result of this intelligence you must therefore be careful when loading your trial balance as you must switch the signs for all those accounts that you would normally show as negatives but have credit attributes in the account type.

The non-accountant likes to see sales and expenses and positive numbers because he subtracts one from the other. The account likes to see sales and a negative and expenses as a positive and adds them.


Below is an example of how the back ground intelligence works. The account series 1 are assets, 2 are liabilities, 3 sales, 4 cost of sales, 5 through 9 expenses. An account looking at this table would just add the whole lot up and inform you that it does not cast. The pivot table in Excel is reading from the cube directly and as you can see comes back to the holy grail of trial balances, ZERO.




Score? One for the man in the street and zero to the accountants.


- Paul Steynberg

Thursday, 8 May 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