Introduction
This part will give brief description on problem statement, aims and objectives, methodologies and thesis outline. Problem statement will help to figure out the concern of a business people for making better decision. Similarly aims and objectives will identify the purpose of the thesis and the thesis outline will clarify the way of the thesis.
1.1 Problem Statement
In this modern era, evaluation of business performance and activity is the crucial thing for identifying business value and strategy in an organisation. For this organization must collect raw data which is then transformed into meaningful and relevant information and knowledge respectively. Before transforming raw data should be stored and processed in a consistent and traceable way for managing and optimising data flows, execution, alignment and transperancy when needed.
1.2 Aim and Objective
This dissertation will focus at how organization can implement data warehouse and business intelligence technologies in order to predict over all business performance and activity for making healthier decision. As it is the process which can help to turn raw data into information and then into knowledge. Its objective is to
Identify value chain of business intelligence.
Identify the crucial goal of business in implementing the data warehouse and business intelligence.
Identify the supplementary benefits acquired by the business by implementing the data warehouse and business intelligence.
Sketch the processes needed in designing and implementing a data warehouse and business intelligence.
Implement a data warehouse and business intelligence system.
Identify success factor of the data warehouse and business intelligence implementation.
1.3 Thesis Outline
Introduction
Literature review
Design, Development and Implementation
Conclusion and Recommendation
Literature Review
Business intelligence is the process that converts raw data into some meaningful and relevant information which then is converted into knowledge for better decision making and evaluating the performance and activity of business. BI is one of the advanced decision support application. And Data warehouse is the foundation of advanced Decision Support System (Shim et al., 2002).
Implementation of Data warehouse and Business Intelligence in any organisation will provide guidance that needs to be measured when venturing a Data warehouse and BI projects. It includes identification of value chain of BI, design of the data models, DW architecture, BI architecture and Identifying its success factor. This information will then be used for identifying DW engineering process and making effective and efficient report services/system which will help for business analysis.
2.1 Background
In the modern era, business environment is changing rapidly. They are seeking for valuable business information as being essential assets which will not only lead organisation towards the path of success but also it will help to sustain in a competitive environment. Business Intelligence (BI) is a model which relates managerial values and a tool which is used in an organisation to handle and filter information in order to healthy business decisions. It refers to the appropriate information and knowledge of the organisational behaviour and condition relative to its markets, clients, competitors and financial issues. Its process includes the detection of information requirements, information analysis, warehousing, and information deployment. The tools and technology tools used in information analysis, warehousing and information deployment is the major part of business intelligence (Pirttimäki et al., 2006).
Since last 20 years business intelligence is existing in the world business technology. It started from traditional queries which were developed by analysts for the business people for making organisational decision (Ortiz, 2002). At first management information systems (MIS) was used by various organisations for doing different tasks. But as it has limited features, it could not fulfil the expectations of decision makers such as making decisions in short duration of time, monitoring competition and analysing the raw data from different point of view to analyse the business performance and activity. It doesn't integrate the various distributed data properly and neither can it interpret such data efficiently by creating its interdependencies (Olszak and Ziemba, 2007). In 1970s, a system was designed that supports decision making named decision support systems. Then after, in the arena of decision support system various decision support systems, executive system, executive information system, online analytical processing (OLAP) and other analytical application have came out. In the early 1990s, the term business intelligence (BI) was formed by Howard Dressner [16]. In the mid-90's BI became a subject of concern in investigating good approach of data extraction, transformation and processing (Golfarelli). Today, BI has evolved from traditional, mono-function, in-house program to pre-packaged, versatile, business assets (Ortiz, 2002).
Figure Development of Management Information Systems.
(Source: Olszak and Ziemba, 2007)
Business Intelligence is associated to data mining and analytics. Data mining will determine significant forms in massive amount of data to prepare forecasting. Analytics will help non experts to utilise BI by using tools having simple interfaces in business applications (Ortiz, 2002). Its framework conducts two actions. The first one is getting data in, conventionally referred as a data warehousing. It is the process of moving data from a set of data sources into a data warehouse. Data sources can be found within the organisation, supplier or customer. Data warehouse extracts raw data from the data source and transforms it into meaningful information for decision support. This getting data in is the most essential and difficult part of BI. It may require round about 80% of the time and effort and more than 50% of total cost of the project. The difficulty may arise due to low quality of data in the data source, legacy system and bad policies of data owner. Bill Inman defines: "A data warehouse [as] a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process." The second one is getting data out which will allow users to access data from data warehouse to perform business reporting, OLAP, querying and analytics. It is referred as BI (Watson and Wixom, 2007).
Figure: Business Intelligence Framework
(Source: Watson and Wixom, 2007)
Benefits of Data Warehouse and Business Intelligence
The prime benefit of data warehousing is simplicity. The presentation of data in data warehousing is a single image. This single image is made by collecting data from different department of the organisation. Due to this time for production and operation of data will be reduced and thus simplifies the decision making as well. This reduction of time to access data also leads to increase the production and effectiveness. Data warehouse will also help to enhance the function of operational systems. It means that there won't be any hamper in the regular process of the system even if user will operate any other operation rather than normal one. As data warehouse has technical infrastructure, it is flexible and scalable in nature. Due to this it supports for both technical and business changeable environment. It gathers raw data as well as manages the flow of information (Furlow, 2001).
2.2 Business Intelligence Value Chain
Business intelligence is a combination of concepts, methods and procedures for making better business decisions using information from several sources and applying knowledge and presumptions to build up a precise understanding of business. It gathers, manages and analyse the raw data for making good understanding information for managers and analysts. BI integrates the core information with related one to perceive major procedures and clarify issues. It has the ability to monitor business activity and performance, to progress and adjust swiftly as situations changes, to make healthy business decision on indecisive judgements and conflicting information and to identity business opportunities. For this BI expects valuable information which can be developed from data resource.
If any organisation wants to implement business intelligence then first of all they should understand and clarify about the value of high-quality data resource that supports BI. This high-quality data resource contains data that will be utilised to produce the information and the information engineering process. Information engineering process helps in the determinations and presentation of business information to fulfil the organisational demand. So, high-quality data resource is directly proportional to information engineering process. Thus it can be said that data resource is the foundation of a business strategies. The quality of data resource will increase the quality of the value chain to promote the business. The value chain starts from data resource to make information from raw data that supports knowledge environment. Then this knowledge environment will act as basics for business intelligence for making business strategies (Brackett, 1999).
Information Engineering
Business Intelligence
Data Resource
Business Strategy
Knowledge Environment
Figure1: The Business Intelligence Value Chain
A case study methodology was attempted by (Brohman, 2000) to dig into the process of data warehouse practice and how it shapes the organisation. According to the qualitative analysis from the case study, a model was illustrated named Business Intelligence Value Chain as shown in Figure 2.
Analysis
Business Intelligence
Explanation (Reporting)
Prediction (Recommendation and model building)
Exploratory Data Analysis (EDA)
Drill
Down
Structured Data Analysis (SDA)
Business Value
Task Definition
Business Problem
Identify Data Analysis Needs
Clarify Identify Business
Problem
Strengthen Business Case
Decision Making
Figure 2: Business Intelligence Value Chain
In above shown figure, business intelligence and business value are the main concepts of the model. Business intelligence is the package for data analysis where as business value is the outcome from data warehouse development and practice.
Data Warehouse Architecture
According to (Weisensee et al.), Data warehouse architecture follows following principals:
Data Sources
Data Warehouses
Data Marts
Publication Services
Extraction, Transformation and Loading (ETL) process will occur between:
Data sources and the data warehouse and
Data warehouse and data marts.
Data Sources:
In an organisation, there will be huge amount of data sources occupying in different database management systems (DBMS) which may be from Marketing Campaign System, Sales Tracking System, Customer Support System and other systems (Ramsdale, 2001).
These data sources must be physically and logically designed which will identify wide view of organisational data review. Physical design will help to review DBMS tables and data sets that will be used by business processes. The logical design will help to make relationship among different tables of physical design through linkages to maintain information hierarchies for presentation and validation purpose. These processes will help in business intelligence (Weisensee et al.).
Data Warehouse:
A typical architecture of data warehouse was designed by (Wang et al., 2005). Figure 3 shows data warehouse architecture with its components. It includes:
Tools which will extract data from various operational databases and external sources; clean, transform and integrate the data; load data in the data warehouse; and regularly refresh the warehouse to reflect updates at the sources and to amend according to changes
The data warehouse and data marts
Online Analytical Processing (OLAP) servers which helps to stores and manages data. Then transforms multidimensional view of data to a various tools like query, analysis, report and data mining tools
Repository for storing and managing organisational metadata.
Tools for monitoring and administering the whole system.
(Jones and Johnson., 2010) has differentiated data mart and data warehouse. Data marts stores data associated to a subset of an organisation such as a branch or particular product. On the other hand, a data warehouse stores data associated to entire organisation. So, it can be said that data warehouse combines the data from data marts.
Figure 3 Typical Architecture of Data Warehouse
There is an analytical processing technology between data warehouse and tools called Online Analytical Processing (OLAP). It is based on multidimensional model of business data in the data warehouse to generate business information. This business information is multidimensional analysis of data. So, OLAP is referred as Analytical Processing or Dimensional Analysis (Kirkgiize et al., 1997). Multidimensional analysis is the manipulation of information through various related dimensions to ease analysis and understanding of the original data (Wang et al., 2005).
In (Reinschmidt and Francoise, 2000) OLAP is defined as "a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user." OLAP can be characterised as multi-dimensional analysis of data that supports logical and navigational querying of data. It helps the user to produce organisational information through relative personalised presentation and analysis of historical and proposed data of different models.
Metadata is the information of the data stored in a repository such as a description of the tables in the data warehouse/database including data types, mapping of fields, acceptable values etc. It also sketches the picture of how transformation of data will take place along with how the operation should be managed (Reinschmidt and Francoise, 2000).
There isn't any specific standard to model data warehouse. It can be built either using the "dimensional" model or the "normalised" model methodologies. Normalised model normalises the data into third normal form (3NF) where as dimensional model collects the transactional data in the form of facts and dimensions. Normalised model is easy to use as we can add related topics without affecting the existing data. But one must have good knowledge of how data is associated before performing specific query, so it might be difficult to handle. Reporting queries may not execute as well because massive numbers of tables may involve in each query. Dimensional model is very efficient to use for non experts and performs pretty well as data is classified in a logical way and same types of data are stored together. But while adding new topics whole warehouse must be reprocessed (Jones and Johnson, 2010).
Dimensional model is designed to optimise decision support query function in relational databases, where as normalised model is designed to eliminate redundancy problem of the data model, retrieving data which contains identifiers and therefore optimise online transaction processing (OLTP) performance (Firestone, 1998). Therefore it can be said dimensional model is best modelling method in data warehousing.
OLAP stores data in arrays which are the logical presentation of business dimensions. Multidimensional array represents intelligence of data elements relationships because analyst's views depend in it. This multidimensional array data model is called "Data Cube" (Kirkgiize et al., 1997). It consists of facts and dimensions instead of rows and columns as in relational data model. Facts are the accurate or numeric data of business activity and dimensions are the sets of attributes that put facts into context (Wang, Chen, Chiu, 2005). Dimensions are interconnected in hierarchies, for example, city, state, region, country and continent. Figure 4 shows three dimensional views data cube of sales data (Kirkgiize et al., 1997).
Figure 4: Data Cube
In data cube each cell contains one or several values called measures or metrics.
In data cube each cell contains one or several values called measures or metrics. It helps to analyse aggregated facts and the level of detail is directly proportional to number of dimensions in the cube. Here axes represents dimension and the space represents facts (Wang et al., 2005). Above shown figure of data cube contains three dimensions namely Time, Geography and Product. Each cell consists of (T, P, G) and business measures is the total sales. Here the amount of product P along with total sales sold in geography G in time period T is stored. Figure 5 shows hierarchy of dimensions.
Day
Year
Quarter
Month
Time:
City
Region
Country
Continent
State
Geography:
Product Line
Product
Product:
Figure 5: Hierarchy of Dimensions
Features of data cube:
Slice and dice
A data cube allows the end user to quickly range in on the exact view of the data required.
