Transcript Business Intelligence

Creating Business Intelligence
Applications
Take small steps toward success
or take a big step toward failure
Walter Verhoeven
T & I, Database Integration
European Service Center
5 Nov 2007
Zurich - The Global Insurer
• Offices in North America and Europe as well as in Asia Pacific, Latin America and
other markets
• Servicing capabilities to manage programs with risk exposure in more
than 170 countries
•
•
•
•
Approximately 58,000 employees worldwide
Insurer of the majority of Fortune’s Global 100 companies
Net income attributable to shareholders of USD 4.5 billion in 2006
Business operating profit of USD 5.9 billion in 2006
Business Value Of BI
•
•
•
•
•
•
•
•
Share departmental data efficiently
Understand and improve cost structures
Capacity planning and optimization
Risk Management
Find organizational flaws
Recognize trend
Fraud and abuse detection
Customer attrition
Business intelligence is a cross-organizational initiative. The absence of a
enterprise wide system will lead to more data marts and more standalone BI
applications that are neither integrated nor reconciled. As a result, the
organization would continue to lose the opportunity to enhance its business
decisions and competitive advantage.
When You Think BI
• We plan
• Data mining
• Data warehousing
• Multidimensional data analysis
•
•
•
(online analytical processing or
OLAP)
Data security
Data presentation
Data input and imports
• We usually forget
• Business justification
• Project planning
• Data analysis
• Implementation consequences
• Deployment of BI systems
• Maintaining BI systems
• Implementing requirements that
come after the distribution and
users use the system
Designing a BI Application
2 ways of implementing a release concept
High-quality partial application
-
-
-
Requirements change over
time as users use the system
Project and development
know-how increases and is
re-used in the next cycle
Smaller teams = more
efficiency
Higher customer satisfaction
Justification
Planning
Low-quality inclusive application
VS -
Business analysis
Bigger initial budget
Easier resource management
Less billing overhead
Less customer specific
solution = higher re-use and
re-sale possibility
Design
Construction
Deployment
There are 16 Milestones In Every BI Application
1.
2.
3.
4.
5.
6.
7.
8.
Business case assessment
Infrastructure evaluation
Project planning
Requirement definition
Data analysis
Application prototyping
Modeling the business
Designing the database
9.
10.
11.
12.
13.
14.
15.
16.
ELT Design
Meta data repository design
ELT development
Application development
Data mining
Meta data repository development
Implementation/rollout
Release evaluation
1 - Business Case Assessment
Justification
Assess and identify the business needs that validate such a project and
create a return on investment (ROI) strategy for it
Deliverables resulting from this step:
If you skip this step:
• Strategic business goals
• You end up creating BI that does not answer
the strategic goals of the business
• Objectives of the proposed BI
• You will fail to get a strong management
• Statement of the business need
commitment
• How BI will satisfy that need
• Costs of the project will not be understood
• Ramifications of not addressing the
and you might not get the funds needed to
business need and not committing to the
implement the solution
proposed BI solution
• Business problems do not get solved
• Cost-benefit analysis results
• Risk assessment
• Recommendations for the current
business process
2-Infrastructure Evaluation
Planning
Understand and map out what you have. What hardware is
involved, what skills you have in-house, what databases you need
to extract data from, how the departments communicate with one
and other, and most important; what info is where
Deliverables resulting from this step:
If you skip this step:
• Standards, network and OS
• It is mandatory to assess the hardware,
middleware, DBMS, and tools to ensure
• Use of a development methodology
the BI application performs adequately.
• Estimating guidelines
• You will not know the knowledge skills
• Scope management procedure
needed to develop, maintain, and use
• Issues management procedure
the proposed solution.
• Roles and responsibilities
• You will not have identified the “key
• Security process
players” and subject experts that are
vital for the next steps.
• Meta data capture and delivery
• Your budget estimate will be off.
• Process for merging project-specific
logical data models into the enterprise
logical data model
• Test process requirements
• Service level agreements
3-Project Planning
Planning
Designing and implementing a BI decision-support environment is
very complicated, and BI projects are very costly. The risks of
undertaking such projects without adequate planning and control
are unacceptable.
• Function point analysis is a sure way to underestimate effort, budget, and
•
•
•
•
•
resources (80% data, 20% functionality)
Create triggers for risks identified in step 1
Quality is more important factor for success than budget, scope, effort (time),
and resources (capable and available people).
Assumptions “always” backfire.
BI is a catalyst for improved decision-making so change is good and must be
managed using change-control procedures
No 2 BI projects are alike, estimates are sure to be wrong.
3 - Project Planning
Planning
(continued)
Deliverables resulting from this step:
• Goals and objectives
• Statement of the business problem
• Results from the cost-benefit and
infrastructure gap analysis
• Functional project deliverables
• Subject area to be delivered
• Items not within the project scope
• Condition of source files and databases
• Team structures
• Assumptions made
• Constraints that need to be implemented
• Risk assessment
• Critical success factors
• Communication plan
• Availability and security requirements
If you skip this step:
• You can’t foresee the costs of a BI
application without careful
planning
• Giving a delivery date is guess
work without careful planning and
resource allocations and
commitment
• Its very easy to implement the
wrong solution.
• You may never get to finish your
implementation because your
sponsor jumps ship due to lack of
trust.
Poor planning and preparation produce poor results.
4 – Definition of Project Requirements
Business Analysis
Requirements can only be defined when interviewing all parties
involved, business sponsors, business representatives, power
users, stakeholders, subject matter experts, IT staff.
Deliverables resulting from this step:
• Technical infrastructure requirements
• Non-technical infrastructure
requirements
• Reporting requirements
• Ad hoc and canned query
requirements
• Requirements for source data,
including history
• High-level logical data model
• Data-cleansing requirements
• Security requirements
• Updated preliminary SLAs
• Tools used to create the solution
If you skip this step:
• Lose sight of objectives and scope of
the project.
• Functionality or data are requirements
are missed.
• Security issues are ignored.
• Requirements are not prioritized.
• Business objectives are not targeted.
Never skip it or combine it with
data analysis or with application
prototyping.
5 - Data Analysis
Business Analysis
BI is a logical data model, it is supposed to be a view on the
business. It involves top-down logical data modeling and bottomup source data analysis and you, like all others, will be confronted
with overwhelmingly poor-quality and conflicting data.
Deliverables resulting from this step:
• Foundation for normalized and fully
attributed logical data model
• Business meta data specifications
• Data-cleansing specifications
• Expanded enterprise logical data
model
Do not judge the success of a BI
project by the speed with which it
gets delivered, but rather by the
quality of its deliverable.
If you skip this step:
• You will create copies of existing
data impairments to the new BI
decision-support environment.
• You will compound existing data
problems.
• You will create additional redundant
and inconsistent BI target databases
and applications to maintain.
• You will create a decision-support
system, and not a BI solution.
• Your analysis models will provide
false information.
• You will not model the business.
6 - Application Prototyping
Ensures that everyone agrees on what is expected from the final
BI application, just make sure that the prototype does become the
proposed solution.
Business Analysis
Deliverables resulting from this step: If you skip this step:
•
•
•
•
•
•
Improved requirement specs.
Improved budget
Improved project plan
Skills matrix
Issue log
The prototype
Show-and-Tell
Mock-Up
Proof-of-Concept
Visual-Design
Demo
Operational
Scope & Resources
• The design of the database, the design of the
GUI, and the BI technologies selected will not
be able to meet the business requirements or
expectations.
• You will not be able to validate cost, effort and
resources needed to complete the proposed
solution.
Without a prototype you will find that
you may build a BI solution that will cost
much more, and take much longer than
you expected and that the skills for a
technology implemented are missing.
And
You will not realize it until it is too late.
7 - Modeling the Business
Business Analysis
Describe the organization in terms of its business activities and the
business objects on which the business activities are performed;
and ensure the correct interpretation thereof.
Deliverables resulting from this step:
• Logical data model divided into
• Business model
• Technical model
• Documentation of the above models
(Meta-Meta data)
If you skip this step:
• Ad -hoc business rules are invented
by departments that do not meet the
BI model.
• Without a model the business does
not know what it has and where it has
it.
• Failing to standardize will cause
frustration among users and they will
not want to use the BI application
because they do not want to be
technology/subject experts
8 - Designing the Database
Design
BI database models have to be designed for analysis and reporting.
Queries can take minutes hours or days, not milliseconds.
Deliverables resulting from this step:
• Target DBMS
• Physical data model
• Physical BI target databases
• Implementation plan
• Maintenance plan
• Disaster recovery plan
• Skill assessment matrix
If you skip this step:
• The database model could end up
being designed by developers and
not database administrators.
• Database is normalized like a data
entry application, killing performance.
• Poor design makes the database
non-maintainable.
• Data retrieval can become
impossible as dimensions are not
factored in to the design.
• Non-uniqueness of primary keys is
not factored in, resulting in false facts
and data load errors.
9 - ELT Design
Vendors will promise you heaven on earth. Take your time
validating their claims and know the limitations.
Design
• Load resource availability and planning its execution
• Implementation strategy: Load all data in one ELT load and unload it again to
•
•
•
•
•
•
validate against the source
Prepare ELT source data in three steps: Reformat, Reconcile, Cleansing
Design your three loads (Initial, Historical, Incremental)
Design data extraction programs
Design ELT specifications in a source-to-target mapping document showing
where and how each column transforms.
Test third-party vendor solutions or write your own
Create a “staging area” specifications document as well as the staging area.
9 - ELT Design
(continued)
Deliverables resulting from this step:
• Source-to-target mapping document
• Evaluate and test ELT tools with
Design
source data
• Designed ELT process flow
• Design the ELT load programs (initial,
historic, Incremental)
• ELT staging area
If you skip this step:
• There is no skipping this step, it has to
be done and it will take most of the BI
implementation design time.
• You need to implement the model
created for the business and store it in
the database.
• Cleansing the data is done here, it is
paramount that data integrity and
quality be implemented in this step.
10 - Meta Data Repository Design
Design
Remember it is a database designed to store contextual
information about the business data you are allowing to be
reported, analyzed, and mined. It can be a centralized or
decentralized object or relational database and it can be bought or
designed in-house.
Deliverables resulting from this step:
If you skip this step:
• Physical meta model
• You end up developing on an inferior,
inadequate foundation.
• Data definition language for the meta
data repository
• Might have to replace the repository for
another later in the project.
• Data control language for the meta
data repository (set of permissions
• Might not be able to facilitate the
and security constraints)
proposed solution and the project will fail.
• Meta data repository programming
• You fail to evaluate your vendor and:
specifications
• The might “extort” you when you need
• Third-party vendor evaluation
a change
• They might discontinue support
11 - ELT Development
Construction
You will need to implement the ELT design at some point in time
and this is a difficult task, mistakes will occur and cover-ups will
only cause more problems. Make sure you get your ELT processing
dependencies in order and test and validate the results.
Deliverables resulting from this step:
• ETL test plan
• ETL programs
• ETL program library and scripts
• Disaster recovery plan
• Skill matrix and a know-how
retention and transfer plan
• Commit budget to licensing thirdparty tools and development
If you skip this step:
• This step is very time consuming but
without it there is no BI data,
meaning no BI.
• If quality and testing is not done
comprehensively, you will end up
providing false figures and logical
data errors.
ELT in BI applications is all about
Junk in - Gold out
12 - Application Development
Construction
This is where you start implementing the prototyping results
together with your business “subject experts” and power users.
Deliverables resulting from this step:
If you skip this step:
• Application design document
• The success of your application
design and implementation can’t be
• Application test plan
measured if the test-plan is missing.
• Application programs and library and
• Staff may not have the skills to
scripts
implement the proposed solution.
• Training materials
• Solution might not scale well as
• Disaster recovery documentation
design or data access and tools do
• Maintenance plan and budget
not allow it.
• You fail to obtain necessary budget
funds for support as maintenance
requirements are not communicated.
13 - Data Mining
Construction
Implement the management and marketing questions in a high
quality and understandable presentation layer that is easily to
navigate.
Deliverables resulting from this step:
• Data mining database
• Analytical data model
• Training plan
• Know-how retention and transfer
strategy
• Operational budget
If you skip this step:
• Not being able to get to the data.
• Not being able to extract the BI out of
the tool.
• Users will not understand how to use
your tool or understand the data, get
frustrated and give up on it.
• The TCO and the ROI do not match or
not get advertised and the completed
application will be discontinued.
14 - Meta Data Repository Development
Construction
Do you create a custom-built in-house solution or will you use a
third-party product? If the latter, you will need “add-ons”. Keep in
mind that the business will evolve, and so will your model.
Deliverables resulting from this step:
• Physical meta data repository
database
• Meta data repository test plan
• Meta data repository programs and
library
• Meta data repository production
documentation
• Meta data repository training
materials
• Skill matrix and know-how retention
and sharing strategy
If you skip this step:
• You will have to create a “Logical”
repository in the application code
based on extracting the “repository”
from source data and DBMS tools.
• CASE tools are not a permanent
solution and not readable by
business users.
• Data is not understood, or not seen
in the context it should be.
• Your business know-how may not be
sharable or shared, taking the
Intelligence out of your business
intelligence application.
15 - Implementation/ Rollout
Deploy
Did you prepare for production? Have security and data preservation
regulations been implemented? Does the infrastructure support the load
and is it maintainable? How long does it take to do a disaster recovery?
Deliverables resulting from this step:
• Validated user acceptance test
• Production ETL program library
• Production application program
library
• Production meta data repository
program library
• Production BI target databases
• Production meta data repository
database
• Production documentation
• Verified disaster recovery plan
If you skip this step:
• When not done right your designs and
development might not be stable.
• You may run into problems if you can’t
recover this critical multiple-million
dollar project when the business
infrastructure becomes dependent on
it.
16 - Release Evaluation
• Post-Implementation Review
• Measures of Success
• Plans for the Next Release
• Deliverables
• Post-implementation review
meeting
Deploy
• Action items
• If you skip this step
• Fail to advertise the implementing
solution
• Lose support of sponsors for future
releases
If you do not remember your past mistakes you will repeat them
Questions
The only stupid question is one not asked.