Tuesday, November 1, 2011

Quick Guide to Implementing Business Intelligence, Data Warehousing & BPM

Definitions and Overview

Business Performance Management (BPM) establishes a framework to improve business performance by measuring key business characteristics which can be used to feedback into the decision process and guide operations in an attempt to improve strategic organisational performance. Other popular terms for this include; Enterprise PM (EPM), Corporate PM (CPM) Enterprise Information Systems (EIS), Decision Support Systems (DSS), Management Information Systems (MIS).

BPM: Cycle of setting objectives, monitoring performance and feeding back to new objectives.
Business Intelligence (BI) can be defined as the set of tools which allows end-users easy access to relevant information and the facility to analyse this to aid decision making. More widely the 'intelligence' is the insight which is derived from this analysis (eg. trends and correlations).

BI: Tools to Access & Analyse Data

Key Performance Indicators (KPIs) are strategically aligned corporate measures that are used to monitor, predict and anticipate the performance of the organisation. They form the basis of any the BPM solution and in an ideal world it should be possible to relate strategic KPIs to actual operational performance within the BI application.
KPIs provide a quick indication on the health of the organisation and guide management to the operational areas affecting performance.

In many companies analysis of data is complicated by the fact that data is fragmented within the business. This causes problems of duplication, inconsistent definitions, inconsistency, inaccuracy and wasted effort.
Silos of Data: Fragmented, Departmental Data Stores, often aligned with specific business areas.
Data Warehousing (DWH) is often the first step towards BI. A Data Warehouse is a centralised pool of data structured to facilitate access and analysis.

DWH: Centralised/Consolidated Data Store

The DWH will be populated from various sources (heterogeneous) using an ETL (Extract, Transform & Load) or data integration tool. This update may be done in regular periodic batches, as a one off load or even synchronised with the source data (real time).

ETL: The process of extracting data from a source system, transforming (or validating) it and loading it into a structured database.

A reporting (or BI) layer can then be used to analyse the consolidated data and create dashboards and user defined reports. A modelling layer can be used to integrate budgets and forecasting.

As these solutions get more complex, the definitions of the systems and what they are doing becomes more important. This is known as metadata and represents the data defining the actual data and its manipulation. Each part of the system has its own metadata defining what it is doing. Good management & use of metadata reduces development time, makes ongoing maintenance simpler and provides users with information about the source of the data, increasing their trust and understanding of it.

Metadata: Data about data, describing how and where it is being used, where it came from and what changes have been made to it.

Commercial Justifications

There is clear commercial justification to improve the quality of information used for decision making. A survey conducted by IDC found that the mean payback of BI implementation was 1.6 years and that 54% of businesses had a 5 year ROI of >101% and 20% had ROI > 1000%.

ROI on BI > 1000% from 20% of organisations

There are now also regulatory requirements to be considered. Sarbanes-Oxley requires that US listed companies disclose and monitor key risks and relevant performance indicators - both financial and non financial in their annual reports. A robust reporting infrastructure is essential for achieving this.

SarbOx requires disclosure of financial & non-financial KPIs

Poor data quality is a common barrier to accurate reporting and informed decision making. A good data quality strategy, encompassing non system issues such as user training and procedures can have a large impact. Consolidating data into a DWH can help ensure consistency and correct poor data, but it also provides an accurate measure of data quality allowing it to be managed more pro-actively.

Data Quality is vital and a formal data quality strategy is essential to continually manage and improve it.

Recent research (PMP Research) asked a broad cross section of organisations their opinion of their data quality before and after a DWH implementation.

- "Don't know" responses decreased from 17% to 7%
- "Bad" or "Very Bad" decreased from 40% to 9%
- Satisfactory (or better) increased from 43% to 84%

DWH implementations improve Data Quality.

Tools Market Overview

At present BI is seen as a significant IT growth area and as such everyone is trying to get onto the BI bandwagon:

ERP tools have BI solutions e.g SAP BW, Oracle Apps
CRM tools are doing it: Siebel Analytics,
ETL vendors are adding BI capabilities: Informatica
BI vendors are adding ETL tools: Business Objects (BO) Data Integrator (DI), Cognos Decision Stream
Database vendors are extending their BI & ETL tools:
Oracle: Oracle Warehouse Builder, EPM
Microsoft: SQL 2005, Integration Services, Reporting Services, Analytical Services

Improved Tools

Like all maturing markets, consolidation has taken place whereby fewer suppliers now cover more functionality. This is good for customers as more standardisation, better use of metadata and improved functionality is now easily available. BI tools today can now satisfy the most demanding customer's requirements for information.

Thinking and tools have moved on - we can now build rapid, business focussed solutions in small chunks - allowing business to see data, store knowledge, learn capabilities of new tools and refine their requirements during the project! Gone are the days of the massive data warehousing project, which was obsolete before it was completed.
A typical DWH project should provide usable results within 3 - 6 Months.

Advice & Best Practice

Initial Phase

Successful BI projects will never finish. It should perpetually evolve to meet the changing needs of the business. So first 'wins' need to come quickly and tools and techniques need to be flexible, quick to develop and quick to deploy.

Experience is Essential

Often we have been brought in to correct failed projects and it is frightening how many basic mistakes are made through inexperience. A data warehouse is fundamentally different to your operational systems and getting the initial design and infrastructure correct is crucial to satisfying business demands.

Keep Internal Control

We believe that BI is too close to the business and changes too fast to outsource. Expertise is required in the initial stages, to ensure that a solid infrastructure is in place (and use of the best tools and methods.) If sufficient experience is not available internally external resource can be useful in the initial stages but this MUST include skills transfer to internal resources. The DWH can then grow and evolve (with internal resourcing) to meet the changing needs of the business.

Ensure Management and User Buy In

It may sound obvious but internal knowledge and support is essential for the success of a DWH, yet 'Reporting' is often given a low priority and can easily be neglected unless it is supported at a senior business level. It is common to find that there is a limited knowledge of user requirements. It is also true that requirements will change over time both in response to changing business needs and to the findings/outcomes of the DWH implementation and use of new tools.

Strong Project Management

The complex and iterative nature of a data warehouse project requires strong project management. The relatively un-quantifiable risk around data quality needs managing along with changing user requirements. Plan for change and allow extra budget for the unexpected. Using rapid application development techniques (RAD) mitigates some of the risks by exposing them early in the project with the use of proto-types.

Educating the End Users

Do not under estimate the importance of training when implementing a new BI/ DWH solution. Trained users are 60% more successful in realising the benefits of BI than untrained users. But this training needs to consider specific data analysis techniques as well as how to use the BI tools. In the words of Gartner, "it is more critical to train users on how to analyse the data." Gartner goes on to say "... that focusing only on BI tool training can triple the workload of the IT help desk and result in user disillusionment. A user who is trained on the BI tool but does not know how to use it in the context of his or her BI/DWH environment will not be able to get the analytical results he or she needs...". Hence bespoke user training on your BI system and data is essential.

Careful planning of the training needs and making the best use of the different training mediums now available can overcome this issue. Look for training options such as: Structured classroom (on or off site), web based e-learning (CBT), on the job training & skills transfer, bespoke training around your solution & data.

Technical Overview

Information Portal: This allows users to manage & access reports and other information via a corporate web portal. As users create & demand more reports the ability to easily find, manage & distribute them is becoming more important.
Collaboration: The ability for the Information Portal to support communication between relevant people centred around the information in the portal. This could be discussion threads attached to reports or workflow around strategic goal performance.
Guided Analysis: The system guides users where to look next during data analysis. Taking knowledge from people's heads and placing it in the BI system.
Security: Access to system functionality and data (both rows and columns) can be controlled down to user level and based on your network logon.
Dashboards & Scorecards:
Providing management with a high level, graphical view of their business performance (KPIs) with easy drill down to the underlying operational detail.
Ad-hoc Reporting and Data Analysis: End users can easily extract data, analyse it (slice, dice & drill) and formally present it in reports & distribute them.
Formatted/ Standard Reports: Pre-defined, pixel perfect, often complex reports created by IT. The power of end user reporting tools and data warehousing is now making this type of report writing less technical and more business focussed.
Tight MS Office integration: More users depend on MS Office software, therefore the BI tool needs to seamlessly link into these tools.
Write Back: The BI portal should provide access to write back to the database to maintain: reference data, targets, forecasts, workflow.
Business Modelling/ Alerting: around centrally maintained data with pre-defined, end user maintained, business rules.
Real Time: As the source data changes it is instantly passed through to the user. Often via message queues.
Near Real Time: Source data changes are batched up and sent through on a short time period, say every few minutes - this requires special ETL techniques.
Batch Processing: Source Data is captured in bulk, say overnight, whilst the BI system is offline.

Relational Database Vs OLAP (cubes, slice & dice, pivot)

This is a complex argument, but put simply most things performed in an OLAP cube can be achieved in the relational world but may be slower both to execute and develop. As a rule of thumb, if you already work in a relational database environment, OLAP should only be necessary where analysis performance is an issue or you require specialist functionality, such as budgeting, forecasting or 'what if' modelling. The leading BI tools seamlessly provide access to data in either relational or OLAP form, making this primarily a technology decision rather than a business one.

Top Down or Bottom Up Approach?

The top down approach focuses on strategic goals and the business processes and organisational structure to support them. This may produce the ideal company processes but existing systems are unlikely to support them or provide the data necessary to measure them. This can lead to a strategy that is never adopted because there is no physical delivery and strategic goals cannot be measured.

The bottom up approach takes the existing systems and data and presents it to the business for them to measure & analyse. This may not produce the best strategic information due to the limited data available and data quality.

We recommend a compromise of both approaches: Build the pragmatic bottom up solution as a means to get accurate measures of the business and a better understanding of current processes, whilst performing a top down analysis to understand what the business needs strategically. The gap analysis of what can be achieved today and what is desired strategically will then provide the future direction for the solution and if the solution has been designed with change in mind, this should be relatively straight forward, building upon the system foundations already in place.

Advanced Business Intelligence

The following describes some advanced BI requirements that some organisations may want to consider: Delivering an integrated BPM solution which has business rules and workflow built in allowing the system to quickly guide the decision maker to the relevant information.

Collaboration and Guided Analysis to help manage the action required as a result of the information obtained.
More user friendly Data Mining and Predictive Analytics, where the system finds correlations between un-related data sets in order to find the 'golden nugget' of information.

More integration of BI information into the Front Office Systems e.g. a gold rated customer gets VIP treatment when they call in, data profiling to suggest this customer may churn, hence offer them an incentive to stay.

Increased usage of Real Time data.

End to end Data Lineage automatically captured by the tools. Better metadata management of the systems will mean that users can easily see where the data came from and what transformations it has undergone, improving the trust in the data & reports. Systems will also be self documenting providing users with more help information and simplifying ongoing maintenance.

Integrated, real time Data Quality Management as a means to measure accuracy of operational process performance. This would provide cross system validation, and verify business process performance by monitoring data accuracy, leading to better and more dynamic process modelling, business process re-engineering and hence efficiency gains.

Packaged Analytical Applications like finance systems in the 80's and packaged ERP (Enterprise Requirement Planning) in the 90's. Packaged BI may become the standard for this decade. Why build your own data warehouse and suite of reports and dashboards from scratch when your business is similar to many others? Buy packaged elements and use rapid deployment templates and tools to configure them to meet your precise needs. This rapid deployment capability then supports you as your business evolves.

BI for the masses: As information becomes more critical to manage operational efficiencies, more people need access to that information. Now the BI tools can technically and cost effectively provide more people with access to information, BI for the masses is now reality and can provide significant improvement to a business. The increased presence of Microsoft in the BI space will also increase usage of BI and make it more attractive. BusinessObjects' acquisition of Crystal and recent release of XI will also extend BI to more people, in and outside the organisation - now everyone can be given secure access to information!


The potential benefits from a BI/DWH implementation are huge but far too many companies fail to realise these through: lack of experience, poor design, poor selection and use of tools, poor management of data quality, poor or no project management, limited understanding of the importance of metadata, no realisation that if it is successful it will inevitably evolve and grow, limited awareness of the importance of training..... with all these areas to consider using a specialist consultancy such as IT Performs makes considerable sense.