Finance and Accounting vs. data warehouse - stewardship of metadata
Todd Boyle CPA - 1997

There was an article on page 43 of Byte Magazine August 1997, entitled "Data Warehousing's Credibility Crisis". It discusses the problem that many data warehouses constructed in large businesses do not reconcile with revenue and expenses in the accounting system or the reported financial results after adjustments.  All those marvellous detailed analyses of expense and income don't equal what really happened.  Often wrong, by miles.

There is a great need, and opportunity, today for accountants to get more involved in the management of enterprise data. This is one example where a little bit of cooperation and support by the controller can go a long way towards making the expensive data mining efforts pay off.

Controllers have a core responsibility as watchdogs, over the basic accuracy and credibility of financial aspects of reports from a data warehouse. You can't say "it's not my job" any more. Even if the database is an IT department project, it is in controllers' best interest to see rock-solid accuracy to the last dollar, backed up by audit. Line managers won't be able to snipe at the reports to avoid taking painful actions, but more importantly, the data will be less prone to major inaccuracies that can drive bad decisionmaking.

Articles from the computing press, such as the Byte article, appear fairly frequently and they indicate the database people are aware of the huge discrepancies that arise out of different cutoffs, different accounting systems in various locations, different cost or revenue codes, broken mapping between various charts of accounts when they change, currency conversion or translation differences. Database people need accountants' help, so let's give it to them.

Monitoring data in the data warehouse and speaking out about inaccurate reports is one example of the changing responsibilities of accountants and CPA's which in my opinion, are not being adequately met by them.

What can we do?

A. Be aware that in multinational companies, accounting data is a hypercube with the following groupings and dimensions, as a minimum:
1. Account group, and Account code Let's say Rows, of Excel.
2. Period, year and month Columns.
3. Reporting entity, division, dept 3rd dimension= cubes (multiple spreadsheets)
4. Actual, Budget, Forecast Stacks of 3-dimension cubes of sheets
5. By product line, product, etc. Pallets of stacks of boxes of sheets
6. Currencies -  ...getting beyond usability.
7. by executive, manager, supervisor responsible
8. productivity or billing, by professional or employee, depending on industry, etc. etc.


The only possible way to administer the company is with a relational database.

B. Be aware that for a relational database to work, it must have accurate metadata, "data about the structure of the data", e.g. the names of the dimension labels listed above. The database also needs the meanings of all of the reference codes that occur within your accounting data (account code, department, product, etc)

C. Everybody's codes in various systems, in different operating units of the company, are typically different. Nobody's codes are probably being used in the reporting database. Unless you can establish common vocabulary, consolidated views must be done by mapping your codes against some theoretical master code scheme.  All enterprise controllers are completely familiar with this problem.

D. Just be aware that whenever you change the way you classify transactions (account code, department, product, etc) or move them to a different grouping, it will always break the existing mapping into the database. Thus, every controller has a responsibility to maintain records of all of your codes, and the way you group them into reporting subtotals.

In my opinion, a competent controller should have an accurate history of the changes in your metadata: dates and details of how your business unit has added codes, dropped codes, re-labeled transactions retroactively, changed the usages of codes, etc. This is not something you can do after yearend, during audits, or when I.T. system analyst asks you about your data. It has to be done contemporaneously, and even then it requires an uncommon level of discipline.

You can see why controllers take refuge in saying, "not my job". But it is definitely your job. The challenge of correct revenue and expense totals was the challenge of a previous generation. They rose to their responsibility and now, we should rise to ours. You don't have to build the data warehouse - but you have to understand your data, communicate the issues to computer nerds, and understand what is needed to make it work. Your job is stewardship of metadata.  

Finally, in my opinion, the controller is responsible to read the reports from any data warehouse, and insist that they be correct.  A controller should go to war with the data warehouse managers if they do not produce correct numbers. The moral dilemma faced by controllers is that they often need deep/broad IT relationships more than they need relationships with executives and owners.  It takes guts to fight with the CIO, CTO etc. and often find errors in the GL as frequent as the data warehouse.

more stuff like this:  

http://www.gldialtone.com/hypercub.htm 
http://www.gldialtone.com/datamart.htm  
http://www.gldialtone.com/dimensions.htm 
http://www.gldialtone.com/rootledgerXML.htm