The City of Calgary is implementing a data warehouse strategy to supplement the reporting and analytical functions provided by various corporate online transaction processing systems (OLTPs). These OLTPs are designed for the efficient recording of transactions rather than efficient reporting of transactions. A data warehouse is specifically designed for reporting efficiency. The data warehouse will complement the operational systems by allowing management to prepare performance and statistical analysis without affecting the performance of the operational systems. The first initiative in the Integrated Business Information System (IBIS) project is to develop a data mart for the Building Regulations business unit.
1. Critical Success Factors
When IBIS was initiated in late 2000, several critical success factors were identified:
1. The sponsorship of business and information technology groups.
2. A focus on business objectives and needs.
3. The creation of a federated solution with an enterprise view.
4. The use of proven methodology, platform and tools.
5. A rededication to data and metadata quality management.
6. A renewed emphasis on data and process stewardship.
7. The selection of scalable technology.
8. The empowerment of clients.
9. The management of project scope.
A major contribution to the success of the project was the purchase of appropriate tools to support the initial data mart as well as to handle the transition into an enterprise data warehouse, comprising many data marts. The focus of this article is the technology selection component of the IBIS project and, more specifically, the data access tool.
2. Technology Architecture
One of the first steps in the technology selection phase of the project was to decide on a preferred architecture. Three software components were considered essential: extraction, transform and load (ETL), online analytical processing (OLAP) and data access.
Extract, Transform and Load Tool
An extract, transform and load tool was required to allow for the efficient development and maintenance of processes to move data from sources to targets. A robust ETL tool will allow the IBIS project team to extract data from any of the city’s data sources and then transform it by reformatting or restructuring based on the required business rules. Finally, the tool should be able to populate any type of data structure on any type of platform. The key consideration here is that “hand-coding” is kept to a minimum to allow for fast development times and to ease the burden of maintaining the processes.
Online Analytical Processing (OLAP) Tool
An online analytical processing (OLAP) tool was required to allow for efficient storage of, and fast access to data. An OLAP tool is an effective way of providing fast query response times because of the multi-dimensional data structures that are populated. These structures ease the burden during the ETL phase, as there is a greatly reduced need to create aggregate tables in the Integrated Business Information System. In addition, data access is significantly improved, as the business users will be able to ask questions of their data and get virtually immediate response.
Data Access Tool
A new data access tool was required to allow the business users to efficiently retrieve data from a variety of data sources. A key consideration in the IBIS project is to empower the business users by allowing them access to their business data without unnecessary intervention by the Information Technology (IT) group.
A robust data access tool will shield users from all the complexity of the underlying data structures and allow “point-and-click” access to their business data. The business user should also have the option of accessing data through an application on the desktop or via a Web browser. The Web browser will also allow the business user to access important business data from any location in the world.
Other Considerations
The OLTP applications are the incubators for data in IBIS. There is a rich supply of data in a large number of applications at the city. Many of these applications are now packaged offerings from vendors, including enterprise resource planning (ERP) systems. As the function provided by OLTP has improved, the complexity of the foundation data layers, and difficulty in data query, has increased.
Metadata is often referred to as “data about data.” This data describes the content of the source systems and data warehouse in a wide variety of ways. It is the glue that holds the warehouse together.
3. Request For Proposal
To select the tools to best meet the needs of the city, the project team created a Request for Proposal (RFP) outlining the requirements for each type of tool.
Timing
The IBIS team worked within tight timelines during 2001. The RFP document was created over the last week of February and the first two weeks of March. It was distributed to 40 prospective vendors on March 15. Another 16 requested it – eight by telephone and eight through the MERX electronic tendering system. RFP pre-proposal meeting questions were due on April 2 and the RFP pre-proposal meeting was held on April 4. RFP responses were due at noon April 18. The shortlisting of vendors was completed by April 26; this was followed by product demonstrations. The final selection recommendation was made by May 4. Proofs of concept projects were completed by June 29 and vendor negotiations were finalized in July.
Format
The RFP had several sections and sub-sections. At the lowest level there were individual questions. Each of the project team’s questions was designed to address the required/desired functionality for the tools. To give some perspective on the thoroughness of the RFP, the number of questions asked is listed in Table 1, organized by section.
Results
As a result of the analysis conducted to determine the shortlist, as well as the understanding gleaned from the product demonstrations, the evaluation team identified four primary solutions for the tools requested through the RFP: a best of breed solution and three different vendor suite solutions. The vendor suite solutions offered lower cost and more integration, but functionality was significantly lower than with the best of breed combination.
Best of Breed Solution
The best of breed solution offered the city exactly that, a package that purchased the best tools in each of ETL, data access and OLAP. The only discrepancy from the preferred tool architecture to this best of breed architecture is that the metadata repository is split into three (one for each of the tools); however, the OLAP and data access tools can leverage the ETL metadata for the initial construction and for subsequent changes in the ETL process. Metadata that is created specifically in the OLAP and data access tools remains in those separate repositories. When future budgets permit, the centralization of metadata can be achieved with specialised repository software.
The selected best of breed vendors were:
– Data Access – Business Objects (Business Objects)
– ETL – Informatica (Power Centre)
– OLAP – Microsoft (Analysis Services)
4. Data Access: BusinessObjects, WebIntelligence, BusinessQuery
The Business Objects data access suite incorporates a patented technology, known as the Semantic Layer, that effectively shields the business user from the complexity of the underlying data structures. The three main components in the BusinessObjects reporting suite are:
1. BusinessObjects – A full-client desktop version that allows developers to build and maintain reports as well as allowing for full functionality in conducting analysis. The BusinessObjects tools allow business users access to data from the IBIS warehouse, other relational data structures, OLAP cubes, spreadsheets and text files.
2. WebIntelligence – A strong Web deployment that allows business users to access and analyze data (whether simply viewing reports or creating new queries) without having to load software directly on to their desktops.
3. BusinessQuery – A full-client desktop tool that allows the business user to connect directly to the OLAP cubes. This gives the business user the added advantage of being able to “write-back” data to the cube. The obvious application for this write-back capability is in a centralized budget scenario.
5. Summary
The best-of-breed solution recommended by the evaluation team offered a high-scalable solution that meets almost all of the requirements set out by the preferred tool architecture. Although it requires a larger financial investment, the city will recoup these costs because:
– Business users will make more informed decisions due to better access to and better understanding of their data.
– The best-of-breed solution will enable future growth in both the Integrated Business Information System and the operational side of the business.
– All technology components will be in place to ensure that data marts will be flexible, scalable and high-performance.
– Data access can be immediately delivered across intranets and the internet.
– In the future, it will be possible to quickly leverage the product bridges to ERP and other OLTP applications, allowing for faster development of data marts.
Ed Greidanus is employed in the Office of the Architecture of The City of Calgary as an Information Architecture Leader. He is currently leading the data warehousing initiative and participating in other architecture projects. He was assisted in the preparation of this article by Gail Howitt, Richard Stevens and Tadeusz Doruch, fellow information architecture analysts, and by Barry Hench of Quadrus Development Inc.
