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]
Profile features of different court information systems - OLTP
[Online Transaction Processing].
Each database has its own schema.
Data is not coded consistently, using different terminology
Data not unifiied into single database. Consolidating data is
complicated by overlapping jurisdictions of the various court
Basic interface to data using MS Reporting Services does not
provide intuitive inquiry
Data is too detailed for executive analysis
Data uses different descriptions and terminology
The Court System needs a diiffent way of analyzing data.
OLAP [Online Analysis Processing]
MS SQL Services Analysis is about OLAP and a core component
of SQL Server 2005.
Comprehensive data organization with complex business intelligence
This gives both flexiblity and insight. Data can be interacted
with using MS Excel in ways not previously available.
Star Schema reorganizes data to make it available to data warehouses
and querying schema. Each schema is organized around a central event
Users understand these related fact tables and are better able
to base analysis and projection.
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.
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
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.
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
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
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
Team from each data source:
Client Team Project Manger
This should include:
Super user/Subject Expert/ Client QA Tester
IT architecture specialist
System Vendor Project Team
Vendor Project Manager
Data warehouse designer
Project Implementation Milestones
Business Analyst inventories all data systems in use
Works with Systems Analyst to identify all data required, measures
Executive sign off of measures and dimensions
Data warehouse designer, designs the star schemas
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.
Data Warehouse Designer assures quality of the data
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.
Databases are deployed to servers to be accessed by end users
QA personnel assure the correctness of the end product.
Define data mining models to permit mathematical projections
and discover relationships and trends not obvious in cube data.