A client wished to improve the timeliness and accuracy of
its mission critical financial reporting, planning, and
projection processes through the replacement of its existing
stand alone desktop application with a centralized web based
solution deployed via their corporate intranet. The application
was to be utilized by a population of over 200 division
Controllers and accounting professionals located in 60 separate
divisional offices across the nation. The primary motivators for
the reengineering effort included:
• Reduced application deployment, support, and maintenance
expenses
• Elimination and streamlining of manual and automated processes
and procedures
• Increased accuracy and timeliness of information
• Increased scalability
• Centralization access to financial reporting and projection
information
• Foundation for future product enhancements
Integrated Services engaged to provide overall project
management services and was responsible for coordinating all
project development, testing, and implementation activities.
Integrated Services managed a combined project team subdivided
into project management, web, relational database, and OLAP
development teams. Project teams were staffed with a combination
of personal provided by the client, Integrated Services, and
other outside consulting organizations. In addition to overall
project coordination activities, Integrated Services was also
specifically tasked with the following key deliverables:
• Requirement definition and application design
• Database design and management
• Stored procedure development
• Interface development for AS/400 DB2 databases
• Interface development for Excel financial models
• Database conversion processes
• Crystal report development
• Documentation development
• Technical support, management, and assessment
The reengineering project required the development of a single
centralized database for managing financial reporting, planning,
and projection data, a browser based database query,
maintenance, and reporting front-end, and specialized interfaces
for real-time integration with existing IBM AS/400 based
financial accounting systems, Microsoft Excel based financial
modeling and projection models, and Hyperion Essbase data marts.
Based on existing technical infrastructure strategies and
identified application requirements, the following system
software components were utilized in the development of the new
system:
Microsoft SQL Server – The centralized financial
reporting, planning, and projection database was developed
utilizing Microsoft SQL Server 7.0. SQL server stored procedures
were used extensively to automate key system integration
functions, significant batch processes, and to simplify overall
web front/backend end development.
Microsoft Visual Basic – Microsoft Visual Basic was
utilized to develop web deployable ActiveX components required
for interfacing the Web application and the backend SQL Server
database with local workstation based Excel financial planning
and projection models.
Microsoft Excel – All significant financial model and
projection activities were automated utilizing Microsoft Excel.
Financial and statistical data required by each planning and
forecasting model was obtained directly from the centralized SQL
Server database via custom developed ActiveX components. Custom
ActiveX components were also utilized to transfer calculated
results from all financial models from Excel to the centralized
SQL Server database.
Lotus Domino – As a result of existing technical
infrastructure strategies and availability, Lotus Domino was
selected as the application server and the Web based application
development environment of choice.
Hyperion Essbase – Hyperion Essbase was utilized as the
final repository for the results of the financial reporting and
projection processes. Web initiated stored procedures
interfacing with Lotus Domino were used to automate the extract
of financial data from SQL Server for import into Essbase.
Crystal Decisions Crystal Reports – All reports requiring
data from the SQL Server relational database where developed
using Crystal Reports and published via Crystal Report Web
services for browser based access.
HIT – OLE/DB-ODBC AS/400 – Real-time access and transfer
of financial accounting and statistical information from
division specific AS/400 DB2/400 databases was provided by SQL
Server stored procedures utilizing dynamic SQL and external
database connectivity provided by OLE/DB-ODBC drivers provided
by Hit Inc.
