As Business Management noted recently, we live in an information economy, and “smart use of information and information technology can be one of the most effective ways to gain a competitive advantage and thrive in times of change”. Business (BI) intelligence and data warehousing (DWH) are two important terms as regarding using information for decision making in business. They can be considered two sides of the same coin and we will discuss how BI and DWH can be used in business to bring practical benefits.

The link between BI and DWH ?

Business intelligence (BI) is about the analysis of quality information in order to make continuous business improvements. Business intelligence is partly process related, as in what steps are needed to complete the analysis and it is also partly technology related, as in what database and reporting technology will be used to prepare and present the data.

A key element of business intelligence is its iterative nature, where business scenarios (e.g. new products, pricing programs, customer care initiatives, product quality improvements etc) are reviewed, then rolled out, and then the consequences are studied with further possible changes to follow.

Data warehousing (DWH) can be considered as the means with which data is delivered from different operational systems such as Purchasing, Stock or Sales systems for the business intelligence process. DWH consists of the database and the software to amalgamate, prepare and transform the data for analysis in the Business Intelligence process.
Please note that reports can also be taken directly from these operational transaction systems and used in the BI process, without going through any transformation in a data warehouse.

Operational systems in the back and front offices of organisations are designed to quickly process transactions. Their normalized data structure facilitates this real-time transactions processing but it limits the degree of decision support capability, outside of transaction based reports such as;

• How many widgets did we sell last month?
• What is the current stock levels for product A?
• What customer invoices are still outstanding over 30 days ?

As ever, businesses need to have more than a one dimensional view of information, they need the capability to able to bring data from different operational systems together and make it available for analysis. This is where data warehousing steps in with the capability to answer questions such as

• How many red coloured widgets were sold in the first quarter in Sales territory by our account managers Mary and Joe?

Data to answer such a question could eventually be sourced from the Sales transaction system, but data warehousing enables these questions to be answered quicker and therefore enable more time for the value-added analysis. This is a key principle when evaluating the effort that is required at the start of a data warehousing project; while the effort can be large, it can pay off with time savings later on in having data readily available for detailed analysis.

Who uses BI ?

There can be a perception that BI solutions are only really used by large companies and can be very large systems with many terabytes (Tbs) of data. Not necessarily so, in a survey in June 2008, by IDC and Computerworld, 75% of data warehousing deployments were found to be under 10Tb in size. In addition, looking at various data warehousing deployments, three different types are identifiable;

• Personal BI, where an individual has built up a repository of data organically and is using it to analyse information particular to their role or area of responsibility.
• Team BI differs from Personal in scope and the degree of sharing of data with reference to the particular team’s goals. It will also be relatively more organised in terms of the policies and definitions that are being used and the type of analyses being completed
• Organisational BI is based on the organisation’s goals and the required analysis, measurements and reports that assist in assessing progress towards these goals. Of the three types, this has the largest scope and can be the most organised, as opposed to growing organically as may be the case with the other two types.

It is important to view the three BI types as being on a continuum, with insights and feedback being exchanged between each level,
rather than being separate silos with no links.

What are users looking for in a typical Data Warehouse solution?

A survey by Kickfire at the 2009 MySQL conference provides a good overview of the typical features that users are looking for. If you have an existing data warehouse or are considering developing one, this list provides a useful checklist. Looking at the list, performance is regarded as important;

1. Query speed
2. Data scalability – As the volume of data increases, the data warehouse has the capability to process queries speedily.
3. High availability
4. User query concurrency – important in a team or organisational BI context when multiple users may be processing queries
5. Load speed – replenishing data from the source transaction systems in a quick manner and minimising any impact on users processing queries
6. Near real-time data refresh

Getting the most out of BI and DWH

To get the most benefit from business intelligence, you should regard it as an iterative process, using principles from the Plan – Act – Check – Do management approach. Start by establishing the business objectives that you want to achieve. Then with these objectives in mind, agree on a set of measurements that show how much progress is being made. From these measurements, the data that is required to support the analysis can be found in the various transaction systems and extracted, transformed and loaded into the data warehouse for analysis. Typical kpi examples include;

• Customer satisfaction level with a target of X%
• Number of orders delivered within X days
• Number of daily service calls per engineer

Your measurements, also known as key performance indicators (kpi’s) and your actual/current status against your kpi targets can be displayed in a dashboard using a combination of graphs, charts and data. The advantage of the dashboard is that you can quickly identify where shortfalls are happening with targets and it will facilitate a better analysis on the important aspect of the implications of the current kpi values.

Sample dashboard from Pentaho

To get the appropriate BI analysis process with a supporting data warehouse solution in place does take a lot of effort, but the pay-back in more effective analysis to develop your business makes it worth the effort.