Clients

The District of Columbia (“District”) is a city with a clear vision – to be the best city in America and to be a full voting member of Congress. The mission of the Office of the Chief Financial Officer (OCFO) is to enhance the fiscal and financial stability, accountability and integrity of the Government of the District of Columbia. The OCFO has oversight and direct supervision of the city’s budget, and is responsible for collecting, controlling and accounting for more than $5 billion in annual operating and capital funds. Not least among its mandates are to forecast revenue, develop fiscal impact statements for proposed legislation, perform tax expenditure analyses, and provide advice on economic development matters. In order to carry out its mission, the OCFO invests considerable resources in technology for processing financial information.

Challenge

The OCFO manages a data warehouse environment that sources data directly from transaction databases and transforms the data into dimensional data models. Currently the data warehouse has three data marts accounting, purchasing, and fixed assets. The environment was designed so that users were able to create reports using desktop software that connected directly to the database.

The data warehouse environment was a success but began to have growing pains as usage expanded beyond the initial group of hardcore users. Using the data warehouse and the reporting software required a commitment from users in technological know-how, time and training. Many of the targeted new users only wanted information from a standard set of reports, and didn’t need the training to develop reports on the fly. Other users, new and continuing, needed more detailed analyses that could only be created by expert data warehouse analysts.

Solution

Entigence helped the OCFO implement a website (given the name CFO$ource) on its intranet that provides a single point of access for financial and budgetary information. CFO$ource contains readily available business analytics and financial metrics that all agencies use to monitor their agencies on a regular basis. CFO$ource was built using the Cognos suite of web-based products. Offering access via the web was important because it allowed many more users to retrieve information from the data warehouse by way of a browser on their desktops. Entigence created standard reports and customized applications for the OCFO, all published to folders on CFO$ource. Each user’s login information was tied to security that allowed access only to information that user was authorized to see. CFO$ource sources data from the following enterprise transaction applications: Accounting, Procurement, Fixed Assets and Spending Plan/Projection. To meet its vision, the District, like all other local governments, has to demonstrate responsible fiscal management. The Office of Chief Financial Officer (OCFO), among other responsibilities, develops and manages financial applications that allow the District to transact daily business. The OCFO also provides users with a comprehensive and integrated enterprise-view of financial data using a dimensionally modeled data warehouse to enable financial and program executives to make timely and accurate decisions. The OCFO data warehouse supports multi-year trend analyses and complex financial reporting for critical business processes such as the creation of the District’s Comprehensive Annual Financial Report (CAFR).

Program Policy Support

The creation of CFO$ource involved a shift in way that the OCFO provided information to its staff and customers. Entigence advised the OCFO on how to leverage the power of its existing data warehouse by employing web-based technology. In converting to a web-focused strategy, the OCFO could conserve IT support resources that formerly were needed to administer desktop applications and code specialized reports.

Entigence also assisted the OCFO in a joint project with the Office of Budget and Planning (OBP) to improve its budget control process. Entigence helped coordinate the communication between the two divisions and made recommendations that resulted in the design and implementation of a spending plan application to be hosted on CFO$ource.

Entigence assisted the OCFO with strategies to win buy-in from major stakeholders, such as the city council and the Office of the Chief Technology Officer. Entigence provided a technical review of documentation sent by the OCFO to other city agencies.

Technical Advisory and Assistance Support

Once the OCFO decided to build CFO$ource, Entigence prepared analyses, performed interviews and compiled technical specifications to support the requirements phase. Entigence helped the OCFO determine what hardware and software needed to be acquired and how these purchases would affect the state of its IT architecture.

Entigence was heavily involved in updating the data models and designing the specifications for the standard reports. Entigence made extensive inquiries to discern the data elements most commonly needed by financial executives and analysts, inside and outside of the OCFO.

The OCFO also asked Entigence to analyze, on a regular basis, the output of the data warehouse.

Among the resulting benefits:

1) Entigence helped design standard reports that compared totals from key general ledger accounts in both the data warehouse and the financial accounting system

2) Entigence recommended performance tuning measures that increased the speed of report processing

By utilizing dimensional modeling techniques, the financial data warehouse provides added value to District users. The DW extracts data programmatically and through Oracle drivers from mainframe and UNIX databases. Informatica’s PowerMart suite is used to structure, transform, and cleanse source data and insert/update the data into an Oracle database on the primary data warehouse server which is an IBM/AIX server. The warehouse is refreshed nightly. Several NT servers are used to house reporting and analytical software and a security management application. These enable users to view data via web browsers and desktop applications from Cognos Corporation (Impromptu, PowerPlay, Upfront). OCFO plans to rollout Cognos Query in the near future.

Planned enhancements include development of a budget projection application, integration of projections with actual’s in the data warehouse and a method to provide executives with a comprehensive view of the fiscal health of the city through an “executive dashboard.” Additional managed reports required for monthly closing and capital project management are planned, along with additional multi-dimensional views. The rollout of Cognos Query will be a significant enhancement to the data warehouse environment. It will remove the burden of managing desktop applications across the District and transition almost all report creation activities into the web environment.

Inclusion of business analytics in the data warehouse environment had two immediate benefits. Consistent, reliable and accurate metrics are readily available to all agencies in the District, and this in turn enables financial and program managers to react faster to unexpected trends, research the underlying cause of trends quickly, and ultimately put in place business
process changes at agencies which will bring variances back in line in a timely manner. Providing a robust  web-based interface to manage reports, multidimensional views, and applications was visionary – it will, in the near-term and long-term, reduce operational costs of managing hundreds if not thousands of desktops across the District. Using products from market leaders such as Oracle, Cognos, Informatica, and IBM will ensure that the OCFO’s data warehouse can scale quickly to meet the growing demands for reporting and analysis.

Developing cooperation between agencies that share responsibility for financial data and financial applications was a challenge that was overcome with strong executive sponsorship. The project’s high-level sponsors helped us gain and sustain cooperation.

Using technology to create innovative decision-support applications with ready-made business analytics is commonly found in the commercial sector. We consider the OCFO data warehouse project to be unique in that the agency realized that they too could leverage data warehouse and business intelligence applications to create, sustain, and manage business analytics to drive and improve financial management in the District. Additionally, the impetus for CFO$ource and managed reports came from the sponsors of the projects because they saw a need that could be fulfilled quickly with technologies that were already present in the District. This enabled the District to control costs for this project.

Recognizing that over 100 agencies in the District regularly use the financial data warehouse to meet city and federal reporting mandates, we have assisted the CFO’s office to instituted a formal business process to reconcile information in the data warehouse with data in the District’s transaction applications. Reconciliation is done using an automated, web-based process each morning after the data warehouse refresh is completed.