Implemention Technology
Business Intelligence
  FOCUS Six for Windows
Data Extraction
  IBI Enterprise Copy Manager
  IBI EDA Servers
Data Marts
  Information Builders (IBI)
    SmartMart
Data Warehouse Administration
  Site Analyzer
  SmartMode
Warehouse Engine
  Oracle

Data warehouses promise easy access to business data and a faster, better way to answer complex questions. It was precisely these business benefits that motivated the British Columbia Ministry of Finance to create a warehouse-driven reporting system for user access to financial data.

"We wanted to establish a general purpose reporting environment that all of our customers would find easy to learn and use," explains Ejler Mogensen, a project manager for the Ministry of Finance. "We now have an effective decision support environment in place that complements and extends our operational systems."

Mogensen knows firsthand the angst that an improperly designed reporting system can bring both to an IS department and to end users. As the financial controller for the British Columbia Government, the Ministry of Finance maintains a very high profile within this western Canadian province. Its customers are other government ministries ­ agencies that depend on the Ministry of Finance for fast, accurate and comprehensive financial data processing. The Ministry is constantly looking for ways to improve the quality of the services it provides to these customers. The financial welfare of the province depends on it.

One of the key financial systems maintained by the Ministry is the centralized corporate accounting system (CAS), which contains the accounting books for the entire provincial government. Based on the Walker Financials application package, CAS resides on a mainframe system running the VSAM operating system. It includes accounts payable, purchase order general ledger ­ all the typical functions of a corporate accounting system.

CAS includes mainframe-based query and reporting capabilities through a utility called Walker ReportBuilder. While all the 20 government ministries in the province feed information into the CAS system, only 12 were using its built-in reporting functions. The other eight ministries accessed canned reports from the CAS general ledger. "Our IS resources are limited, making it difficult for us to offer custom reporting services, unless those services are useful to a wide variety of users," says Mogensen.

Complicating the issue was the impact of user reporting activities on mainframe performance. Lacking a separate database for decision support, report procedures had to be run against the operational files of the Walker applications. This led to contention for mainframe resources during peak times of the day. As some ministries began to acquire their own PC-based query and reporting tools, Mogensen and his team saw the wisdom in creating a separate decision support database on the LAN.

"Our approach was to create a data warehouse optimized for reporting purposes, then offload data to that database so customers wouldn't impact system performance by querying operational files," Mogensen explains.

Data warehouses separate the transaction processing activities of production applications from the ad hoc data processing exercises carried out by users such as forecasting, profiling, summary reporting and trend analysis. IS pros at the Ministry of Finance used Information Builders' SmartMart products to establish this type of decision support system. As financial data accumulates in the CAS production databases, it is periodically extracted, filtered and then loaded into a dedicated warehouse server that is accessible to end users. The warehouse database is updated from CAS operational systems on a periodic basis, generally during off-hours when network and CPU utilization is light.

The Ministry opted for a relational database and SQL reporting environment so customers could continue to use the PC-based query and reporting tools with which they were already familiar. The data warehouse platform was established in an Oracle database on a Windows NT server, a British Columbia Government standard. To encourage users to actively use the new asset, they gave it an active, hands-on name: the Corporate Online Data Exchange, or CODE. Unlike the production databases underlying CAS, CODE is designed explicitly for user query and reporting.

Populating the CODE warehouse involves moving data from the Walker financial applications to the Oracle warehouse. Enterprise Copy Manager, part of Information Builders' SmartMart tool suite, makes this an easy process. Enterprise Copy Manager integrates heterogeneous data sources, applies custom transformation logic to validate and cleanse data, and supports scheduled and ad hoc warehouse maintenance.

SmartMart provides other useful software tools for the Ministry, such as EDA Server which IS pros have installed on both the mainframe and NT platforms to streamline data access and conversion. The EDA servers also helped the Ministry implement system-wide security. When users sign on to CODE, their passwords and IDs are authenticated through Windows NT. From there, they are handed over to the Oracle database where field-level security ensures only authorized users can view the information.

Using EDA servers with Copy Manager has saved Mogensen's team a considerable amount of work. "We managed to apply 95 percent of the essential data extraction rules without writing a lot of code," Mogensen notes.

SmartMart has other tools that let Mogensen fine-tune the database. One is SiteAnalyzer, which lets database administrators analyze how the Oracle database is being used, clear down to the field level. As such, SiteAnalyzer is a useful performance monitor for gauging the strengths and weaknesses of the data warehouse design. Database administrators can use it to see whether or not users are accessing historical data and then decide whether to archive that data or keep it online.

Another useful tool is the SmartMode Intelligent Governing Service, which lets database administrators limit access to data and guard against runaway queries. "You can set limits on how many rows a particular query is allowed to return, how many CPU cycles each query is allowed or even which columns in the database can be accessed ­ on a user-by-user basis," Mogensen explains.

Building and optimizing the database is just half of a data warehouse project. Once that data has been structured and cleansed for reporting, decision support applications must be developed to make the data useful. The Ministry of Finance used FOCUS Six for Windows Managed Reporter Edition which gives warehouse users fast information access and many different reporting options in an easy-to-use, graphical environment. Many common querying and reporting tasks are easier to do with FOCUS than with other querying and reporting tools.

CODE quickly proved to be a popular asset for users wishing to perform ad hoc queries and reports. "We can search a million records and create a report on whatever parameters we set for it in about a minute," says Alan Strickland, communications manager at the Ministry's CAS Office. "It used to take hours to achieve the same results with the mainframe reporting system." Best of all, customer satisfaction has improved in the process.

One ministry has begun using ODBC to combine data from CODE and another SQL-compliant database to create custom reports. Mogensen expects to see more of that in the future. "People are going to start merging information from disparate systems," he says. "We can now link Walker Financial systems data with payroll information from PeopleSoft, for example."

Customers are also beginning to use the information from CODE to streamline the Ministry's key business processes such as purchasing and billing. Ultimately, the Ministry expects some customers to construct their own subject-oriented data marts, populated by data extracted from CODE.

In the end, it's clear that CODE is more than just a reporting tool. Mogensen sees it as the springboard for British Columbia government ministries to move into the future. "The data is structured in a very flexible manner, which means that people with completely different needs can derive unique information from the same data," he says. "We want to build an awareness in the user community of the potential of this great resource and how to use it to improve the business."

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access