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
No comments:
Post a Comment