Implementing A Business Intelligence System

Getting the best out of business intelligence requires a single true view of data. With the large number of legacy databases, this is often easier said than done.

The first step in implementation of a BI system is the Program Initiation. This phase identifies the benefits, costs and risks of the system and helps to define the BI strategy.

Once the BI program is cleared to proceed, the implementation project begins. To help illustrate how a typical BI project is rolled out, we use a case study below.

Court Business Intelligence System

The following video outlines one project to build a business intelligence system for the Court System. [24:00]

Key Challenges

1. Profile features of different court information systems - OLTP [Online Transaction Processing].
2. Each database has its own schema.
3. Data is not coded consistently, using different terminology
4. Data not unifiied into single database. Consolidating data is complicated by overlapping jurisdictions of the various court systems.
5. Basic interface to data using MS Reporting Services does not provide intuitive inquiry
6. Data is too detailed for executive analysis
7. Data uses different descriptions and terminology

Business Need

The Court System needs a diiffent way of analyzing data.

Solution

1. OLAP [Online Analysis Processing]
2. MS SQL Services Analysis is about OLAP and a core component of SQL Server 2005.
3. Comprehensive data organization with complex business intelligence tools.

This gives both flexiblity and insight. Data can be interacted with using MS Excel in ways not previously available.

Star Schema

Star Schema reorganizes data to make it available to data warehouses and querying schema. Each schema is organized around a central event fact table.

Users understand these related fact tables and are better able to base analysis and projection.

ELT

All data inconsistency issues are dealt with during the ETL phase [ Extraction, Transformation and Loading].

The ELT system often reveals errors in the initial capture of the data. This makes the transfer of data from the old OLTP databases into the Star Schema more complicated.

SSIS

MS Tools assist with the ELT Process; much of it automatic. This is known as SQL Server Integration Services. [SSIS]

Much of the SSIS is object oriented, meaning core intelligence can be reused for many different unique projects. They take common issues of many systems and have predesigned components for many of them. This allows developers to focus on the attributes and tasks associated with a particular project and it's data.

A constraint of the Star Schema Data Warehouse is that simple queries can only deliver one piece of data at a time. The intersection of a measure with a single value for each of the dimensions.

SSAS

Greater querying ability is thus required. This introduces SQL Server Analysis Services. This stores data in a cube, but often has more than three dimensions. Time: County: Judges. The values of dimensions are referred to as members.

This gives a broader, comparative view of the data, and enables real time query.

User Interface

MS Excel is the best interface for Anlaysis Services. Users from many PC's using Excel to connect simultaneously to the data. Security is based on Windows login, and can be limited by dimension and measure.

Connect to external OLAP data source and drop dimensions and measures data into Pivot Table or use dimensions as filters.

The configured spreadsheet can be saved as a web-page, using an ActiveX control, giving all the interactivity of Excel and the access convenience of the web. Each viewer "instance" of the page is limited to those data elements for which they have authorization to view.

You can also save a sector of the cube as a local cube. A batch process can update the data, which is no longer connected to the database.

Predicitive Mathematical Models

Tested mathematical models are used for valid projections. In this project, in can be used to project the number of cases, the types, and the locations in which they will be filed. For instance, it can project the number of Judges required, and facility load.

Data mining answers all these questions.

They apply intelligence to transactional data to transform it into useful information using dimensions and measures in star schema data warehouses.

Project Resources

Program Manager

Client Team

Team from each data source:

• Client Team Project Manger
• This should include:
• Super user/Subject Expert/ Client QA Tester
• Data analyst
• IT architecture specialist

System Vendor Project Team

• Vendor Project Manager
• Business analyst
• Data warehouse designer
• ETL developers
• QA Tester

Project Implementation Milestones

1. Business Analyst inventories all data systems in use
2. Works with Systems Analyst to identify all data required, measures and dimensions
3. Executive sign off of measures and dimensions
4. Data warehouse designer, designs the star schemas
5. ETL developers automate the transfer of data from the old OLTP databases into star schemas. This is the biggest and most complex part of the project, and usually employs several ETL developers working in parallel.
6. Data Warehouse Designer assures quality of the data
7. ETL developers automate transfer of data from Analysis Systems into the Analysis Services database, and the processing of the data to aggregate it in advance of its use.
8. Databases are deployed to servers to be accessed by end users
9. QA personnel assure the correctness of the end product.
10. Define data mining models to permit mathematical projections and discover relationships and trends not obvious in cube data.

NEXT: Business Intelligence On Virtual Earth

Back To Top

Business Intelligence | Augmented BI | BI Governance | BI Architecture | BI Interfaces | BI Tools | BI 2.0 | BI 1/2 Comparison | Using BI | BI Program Initiation | BI Implementation | BI Products | Intelligent Networks | Intelligent Information Networks