While the terminology may have changed over the years, the requirement to “get the right information to the right people at the right time” remains a challenge given that, for many organisations, the IT infrastructure consists of a plethora of hardware and software environments.

Maddox Ford’s consultants have been developing applications and environments to address corporate information needs for many years, experience that has encompassed a multitude of technologies and included small-scale departmental solutions to larger, enterprise data warehousing architectures.

Our approach to addressing management information needs is conditioned by a ‘large-scale BI implementations usually fail’ philosophy. We advocate a phased approach based on a vision of where you want to go and a framework for getting there. The framework should consist of identifying a small set of requirements that builds on those satisfied from the previous phase(s). In this way, an infrastructure can be built on the back of multiple 'quick wins'.

Phases in the production of an iterative approach to a BI project might typically be (note that an iterative approach means that each phase may be revisited on many occasions as experience is gained and expectations evolve.

Strategic Planning

A cost/benefit analysis to provide justification for the project. A prototype - just a few days work to extract a small amount of basic data from various source systems, load it into a simple database structure and produce a few, simple reports against the data - to prove feasibility. A preliminary choice of software (e.g. relational database, multi-dimensional database, front-end tools, database/warehouse management tools, extract and load tools if necessary) and training requirements may also be made at this stage.

Project Planning

Maddox Ford has a standard framework for planning BI projects but project resources need to be identified and brought into the project as necessary. Work at this stage involves query profiling – identifying and interviewing key end users, defining reporting requirements, identifying data sources and granularity, evaluating historical data availability and requirements.

Data Model Design and Data Load

Initially, this consists of building a business model that identifies major ‘subject’ items, relationships between them and groupings of keys and attributes that more fully represent the major subjects.

It also involves building a Logical Data Model using reporting requirements as a guide. Activities will include - dimensional modelling, defining business measures (metrics), defining calculated measures, defining hierarchies for dimensions, identify characteristics (colour, size, etc.), incorporating a time element.

It is important to match individual data components to source systems (it is not unusual to find multiple sources for the same data) and to review how ‘clean’ the data is.

Creating the Physical Data Model involves identifying fact and lookup tables, defining data types and the indexing strategy, creating a data dictionary and establishing aggregated data and data partitioning. There are various schema designs that may be followed, such as the snowflake and star schemas.

Finally, and often one of the more challenging aspects of business intelligence projects, there is the extraction of data from source systems; the transformation of that data such that it is in the right format and the loading of it into the BI database (‘ETL’ - Extract, Transform & Load). This process might involve the use of specialist ETL tools or developing bespoke load routines.

Environment Setup

Building a development version of the BI environment; creating the physical model and effecting connectivity to source systems; populating a test database; determining how queries can be best implemented; establishing backup and recovery mechanisms; and defining configuration management rules and procedures.

Application Development

This primarily addresses the end-user query requirements; developing naming conventions and standards; considering the look and feel of the queries; building the metadata; installing query tool(s); creating templates and queries; and determining how reports are best tested for accuracy.

Database Configuration

Installing the test server for use during the pilot operation; installing the RDBMS; defining routines to upgrade as the development database changes; extraction and load routines are run to populate the test database; testing the data load (including historic data); checking load and back times; establishing the load and backup timetable; implementing login management and security; reviewing performance and tuning the database.

Interface Design

This involves confirming how information extracted from the BI environment is presented to users. For example, will there be web delivery?

Pilot

In effect, this is the user acceptance testing phase of the project. Users have to be trained and a system test defined to include load routines, load schedules, data validity, and error correction. Performance is monitored and the database, quesries, user interface, etc., refined as necessary.

Production

When the pilot has been completed to everyone’s satisfaction, the system is ready to go into production. Users must be trained; system and user documentation produced; a help desk is set up; the production database is created and loaded with historic data; standard load routines are put into effect and performance is monitored on an on-going basis.