Last edit: 05-03-17 Graham Wideman

Personal

Demonstrations: Operational and Decision-Support

With SIMS and SIMS-R situation at least progressing (though not in a particularly inspiring manner),  I turned my attention to the core accounting system FAS. This COBOL system provided by the CSU Chancellor's Office already had the facility to extract data to Oracle tables on a nightly basis. However, some of these tables are quite cryptic, and there is no documentation or data dictionary. Unlike the SIMS situation, there was no on-campus FAS expert to guide the way. Though we had several expert users of the accounting system itself, unsurprisingly none were familiar with the raw data tables which bear only a passing resemblance to the screens and reports that users see.

So we embarked on an analysis of the data to discover what it all meant. Some of this was straighforward normalization to extract entities that were otherwise implicit -- for example generating an explicit "expense categorization" entity (aka "SubCode") by extracting  the codes and descriptions from other tables.

But the Holy Grail was to be able to understand the Transactions Table -- no general warehouse could be built unless Transactions could be made available for arbitrary summaries.  Unfortunately, what passed for the Transactions table (about a million records a year) was full of records of many different types, with fields having different meanings depending on the "transaction type".  It also had no unique row-id, the row order was different from one night to the next (not simply appended) and contained extract errors -- with account numbers sometimes showing up in dollar fields -- not a pretty sight.

Evenually, after several months of research, interviews, email marathons, and a great deal of trial and error in MS Access, we nailed down all the secrets. We were able to demonstrate a full understanding of the many different Transaction types by performing transformations and sums that arrived at all ~100,000 account subtotals in complete agreement with the accounting system itself. Hurrah!!

So, next step stick it in a warehouse, right?  Well, almost -- the transformations had been prototyped in MS Access, so we still needed to rewrite them in Oracle PL/SQL (about 7000 lines as it turned out) and install stored procedures on the Oracle database on the campus IBM mainframe to do the processing every night.  

Unbelievably (and frustratingly) that ended up taking another several months, primarily due to bugs and other problems with the Oracle implementation on MVS (later OS390). These shortcomings had been evident for some time(SDSU had been running Oracle on MVS for over three years), but many of Oracle's features had not been used, and efforts had not been completely successful to resolve nagging problems that had occured. So though we were expecting to step into a mature environment, we ended up detouring to create the definitive tests which placed the problems solidly on Oracle's side of the court. After seemingly endless rounds of bug fixes and workarounds, we finally came to the hard-to-swallow conclusion that Oracle on MVS was not stable enough to use. We finally got the resources to move the whole thing to a unix box, and then experienced relatively smooth sailing.

In the meantime, my colleague Patrick had latched on to the MS Excel web-query facility, and demonstrated that this might be a good vehicle for delivering data to end-users.  We could generally rely on users having network-connected PCs or Macs, and this would be a close-to-zero cost deployment method.  So as the warehouse neared completion in spring 98 (aside from sorting out the frequent Oracle crashes), we developed a suite of web-server-based queries and a set of Excel spreadsheets to deliver frequently-needed data to end-user desktops.  The whole thing worked quite well for being so cheap!

Finally, for the first time since its inception in the late 70's, venerable old FAS could deliver meaningful data to end users. A big tangible satisfactory success, right?

Not exactly.  Between the start of our FAS project (summer 97) and ready for deployment (spring 98), management had suddenly decided to team up with efforts at San Francisco State U. to engage Oracle to replace FAS and numerous other systems with Oracle Financials and other vertical applications. Though this would take place over a number of years, all accounting attention, and available analyst effort, was now directed to the Next Big Thing.

Hopefully this will ultimately turn out for the best, particularly since the Oracle system has the capability to associate more categorical  information with each transaction (for example the ability to identify transactions as pertaining to a particular project -- good for activity-based analysis).  However, this move took an area of data which we had finally wrestled into pretty good shape, and turned it into an area that may soon be replaced,  precipitating an attitude of "why bother commiting further effort to it?".

Ironically, our FAS effort helped to lubricate the skids under the Oracle Financials effort in a couple of ways. 

First, prior to our analysis,  there had long been a general dissatisfaction with FAS, but a reluctance to take action because it was felt that we perhaps had not exploited FAS to it's full capability.  This "underutilization" stemmed from the awkwardness of understanding what the system could do, and hence a belief that it might do several desirable things, if only we would invest the effort to discover how. The analysis (along with other developments)  lent a greater degree of clarity as to the actual functionality available, and helped to increase confidence that replacement was a supportable course of action.

Secondly, once the Oracle Financials choice was underway, there was a need to identify the platform for it -- the obvious one being the existing campus IBM mainframe.  As it turned out, the testing we had done to try to get the FAS warehouse code to run reliably was instrumental in convincing management that MVS/OS390 version of the Oracle database was not a very viable environment for us -- a position that Oracle quietly agreed with by encouraging SDSU's decision to obtain a whole new unix platform for the Oracle applications.

(As a side note, because of other processing requirements, the SIMS-R project is still committed to using the Oracle OS390 environment -- which to my mind has significant risk ramifications.)

Meanwhile, the FAS warehouse and Excel access mechanism are still to be widely deployed, as they await final management approval and promotion to users. 

[Back to "Strategy"]

Go to: