AALIM MUHAMMED SALEGH COLLEGE OF ENGINEERING

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

CCW331 - BUSINESS ANALYTICS

UNIT II BUSINESS INTELLIGENCE

Data Warehouses and Data Mart – Knowledge Management – Types of Decisions – Decision
Making Process – Decision Support systems – Business Intelligence- OLAP- Analytic Functions

DATA WAREHOUSES AND DATA MART

1.1 Introduction

Data Warehouses
A Data Warehouse (DW) is an organised collection of integrated, subject- oriented databases
designed to aid decision support functions.
DW is organized at the right level of granularity to provide clean enterprise-wide data in a
standardized format for reports, queries and analysis. DW is physically and functionally separate
from an operational and transactional database.
Creating a DW for analysis and queries represents investment in time and effort.
It has to be constantly kept up-to-date for it to be useful.

A Data Warehousing (DW) is process for collecting and managing data from varied sources to
provide meaningful business insights.
A Data warehouse is typically used to connect and analyze business data from heterogeneous
sources. The data warehouse is the core of the BI system which is built for data analysis and
reporting.
It is a blend of technologies and components which aids the strategic use of data. It is electronic
storage of a large amount of information by a business which is designed for query and analysis
instead of transaction processing.
It is a process of transforming data into information and making it available to users in a timely
manner to make a difference.

Data warehouse system is also known by the following name:

Decision Support System (DSS)
Executive Information System
Management Information System
Business Intelligence Solution
Analytic Application
Data Warehouse

How Data warehouse works?

A Data Warehouse works as a central repository where information arrives from one or more data
sources. Data flows into a data warehouse from the transactional system and other relational
databases.

Data may be:

Structured
Semi-structured
Unstructured data

 The data is processed, transformed, and ingested so that users can access the processed data
in the Data Warehouse through Business Intelligence tools, SQL clients, and spreadsheets.
 A data warehouse merges information coming from different sources into one
comprehensive database.
 By merging all of this information in one place, an organization can analyze its customers
more holistically. This helps to ensure that it has considered all the information available.
Data warehousing makes data mining possible.
 Data mining is looking for patterns in the data that may lead to higher sales and profits.

Types of Data Warehouse

Three main types of Data Warehouses are:
Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse. It provides decision support service

across the enterprise. It offers a unified approach for organizing and representing

data. It also provide the ability to classify data according to the subject and give access according
to those divisions.
Operational Data Store:

Operational Data Store, which is also called ODS, are nothing but data store required 1.2 when
neither Data warehouse nor OLTP systems support organizations reporting needs. In ODS, Data
warehouse is refreshed in real time. Hence, it is widely preferred for routine activities like storing
records of the Employees.
Data Mart:

A data mart is a subset of the data warehouse. It specially designed for a particular line of
business, such as sales, finance, sales or finance. In an independent data mart, data can collect
directly from sources.
Components of Data warehouse

Four components of Data Warehouses are:

Load manager: Load manager is also called the front component. It performs with all the
operations associated with the extraction and load of data into the warehouse. These operations
include transformations to prepare the data for entering into the Data warehouse.
Warehouse Manager: Warehouse manager performs operations associated with the management
of the data in the warehouse. It performs operations like analysis of data to ensure consistency,
creation of indexes and views, generation of denormalization and aggregations, transformation
and merging of source data and archiving and baking-up data.
Query Manager: Query manager is also known as backend component. It performs all the
operation operations related to the management of user queries. The operations of this Data
warehouse components are direct queries to the appropriate tables for scheduling the execution of
queries.
End-user access tools:

This is categorized into five different groups like 1. Data Reporting 2. Query Tools 3. Application
development tools 4. EIS tools, 5. OLAP tools and data mining tools.

Who needs Data warehouse?

Data warehouse is needed for all types of users like:

Decision makers who rely on mass amount of data
Users who use customized, complex processes to obtain information from multiple data sources.
It is also used by the people who want simple technology to access the data
It also essential for those people who want a systematic approach for making decisions.
If the user wants fast performance on a huge amount of data which is a necessity for reports, grids
or charts, then Data warehouse proves useful.
Data warehouse is a first step If you want to discover 'hidden patterns' of data- flows and
groupings.

What Is a Data Warehouse Used For?

Here, are most common sectors where Data warehouse is used:

Airline:

In the Airline system, it is used for operation purpose like crew assignment, analyses of route
profitability, frequent flyer program promotions, etc.

Banking:

It is widely used in the banking sector to manage the resources available on desk effectively. Few
banks also used for the market research, performance analysis of the product and operations.
Healthcare:
Healthcare sector also used Data warehouse to strategize and predict outcomes, generate patient's
treatment reports, share data with tie-in insurance companies, medical aid services, etc.
Public sector:

In the public sector, data warehouse is used for intelligence gathering. It helps government
agencies to maintain and analyze tax records, health policy records, for every individual.

Investment and Insurance sector:

In this sector, the warehouses are primarily used to analyze data patterns, customer trends, and to
track market movements.
 Retail chain:

In retail chains, Data warehouse is widely used for distribution and marketing. It also helps to track items,
customer buying pattern, promotions and also used for determining pricing policy.

Telecommunication:

A data warehouse is used in this sector for product promotions, sales decisions and to make distribution
decisions.
Hospitality Industry:

This Industry utilizes warehouse services to design as well as estimate their advertising and promotion
campaigns where they want to target clients based on their feedback and travel patterns.
Steps to Implement Data Warehouse

The best way to address the business risk associated with a Data warehouse implementation is to employ a
three-prong strategy as below

Enterprise strategy: Here we identify technical including current architecture and tools. We also identify
facts, dimensions, and attributes. Data mapping and transformation is also passed.
Phased delivery: Datawarehouse implementation should be phased based on subject areas. Related business
entities like booking and billing should be first implemented and then integrated with each other.
Iterative Prototyping: Rather than a big bang approach to implementation, the Datawarehouse should be
developed and tested iteratively.
Here, are key steps in Datawarehouse implementation along with its deliverables.

Step Tasks Deliverables

1 Need to define project scope Scope Definition

2 Need to determine business needs Logical Data Model

3 Define Operational Datastore requirements Operational Data Store Model

Acquire or develop Extraction tools Extract tools and Software

Define Data Warehouse Data requirements Transition Data Model

Document missing data To Do Project List

Maps Operational Data Store to Data Warehouse D/W Data Integration Map

Develop Data Warehouse Database design D/W Database Design

Extract Data from Operational Data Store Integrated D/W Data Extracts

Load Data Warehouse Initial Data Load

Maintain Data Warehouse On-going Data Access and Subsequent Lo

1,6 Best practices to implement a Data Warehouse

Decide a plan to test the consistency, accuracy, and integrity of the data.
The data warehouse must be well integrated, well defined and time stamped.
While designing Datawarehouse make sure you use right tool, stick to life cycle, take care about data
conflicts and ready to learn you're your mistakes.
Never replace operational systems and reports
Don't spend too much time on extracting, cleaning and loading data.
Ensure to involve all stakeholders including business personnel in Datawarehouse implementation process.
Establish that Data warehousing is a joint/ team project. You don't want to create Data warehouse that is
not useful to the end users.
Prepare a training plan for the end users.

Advantages of Data Warehouse:

Data warehouse allows business users to quickly access critical data from some sources all in one place.
Data warehouse provides consistent information on various cross-functional activities. It is also supporting
ad-hoc reporting and query.
Data Warehouse helps to integrate many sources of data to reduce stress on the production system.
Data warehouse helps to reduce total turnaround time for analysis and
reporting.
Restructuring and Integration make it easier for the user to use for reporting
and analysis.
Data warehouse allows users to access critical data from the number of
sources in a single place. Therefore, it saves user's time of retrieving data
from multiple sources.
Data warehouse stores a large amount of historical data. This helps users to
analyze different time periods and trends to make future predictions.
Disadvantages of Data Warehouse:

Not an ideal option for unstructured data.

Creation and Implementation of Data Warehouse is surely time confusing
affair.
Data Warehouse can be outdated relatively quickly
Difficult to make changes in data types and ranges, data source schema,
indexes, and queries.
The data warehouse may seem easy, but actually, it is too complex for the
average users.
Despite best efforts at project management, data warehousing project scope
will always increase.
Sometime warehouse users will develop different business rules.
Organisations need to spend lots of their resources for training and
Implementation purpose.
The Future of Data Warehousing

Change in Regulatory constrains may limit the ability to combine source of

disparate data. These disparate sources may include unstructured data which
is difficult to store.
As the size of the databases grows, the estimates of what constitutes a very
large database continue to grow. It is complex to build and run data
warehouse systems which are always increasing in size. The hardware and
software resources are available today do not allow to keep a large amount
of data online.
Multimedia data cannot be easily manipulated as text data, whereas textual
information can be retrieved by the relational software available today. This
could be a research subject.
retrieved by the relational software available today. This could be a research
subject.
Data Warehouse Tools

There are many Data Warehousing tools are available in the market. Here,
are some most prominent one:
MarkLogic:

MarkLogic is useful data warehousing solution that makes data integration easier and faster using an array of
enterprise features. This tool helps to perform very complex search operations. It can query different types of
data like documents, relationships, and metadata.
Oracle:

Oracle is the industry-leading database. It offers a wide range of choice of data warehouse solutions for both on-
premises and in the cloud. It helps to optimize customer experiences by increasing operational efficiency.
Amazon RedShift:

Amazon Redshift is Data warehouse tool. It is a simple and cost-effective tool to analyze all types of data using
standard SQL and existing BI tools. It also allows running complex queries against petabytes of structured data,
using the technique of query optimization.
Differences between Data Warehouse and Data Mart

1.
Parameter Data Warehouse Data Mart
A Data Warehouse is a large repository of A data mart is an only subtype of a Data
Definition data collected from different organizations or Warehouse. It is designed to meet the need of a
departments within a corporation. certain user group.
Usage It helps to take a strategic decision. It helps to take tactical decisions for the
business.
The main objective of Data Warehouse is to
Objective provide an integrated environment and A data mart mostly used in a business division
coherent picture of the business at a point in at the department level.
time.
Designing The designing process of Data Warehouse is The designing process of Data Mart is easy.
quite difficult.
May or may not use in a dimensional model. It is built focused on a dimensional model using
However, it can feed dimensional models. a start schema.
Data warehousing includes large area of the Data marts are easy to use, design and
Data Handling corporation which is why it takes a long time implement as it can only handle small amounts
to process it. of data.
Data warehousing is broadly focused all the Data Mart is subject-oriented, and it is used at a
Focus departments. It is possible that it can even department level.
represent the entire company.
The data stored inside the Data Warehouse Data Marts are built for particular user groups.
Data type are always detailed when compared with Therefore, data short and limited.
data mart.
The main objective of Data Warehouse is to
Subject-area provide an integrated environment and Mostly hold only one subject area- for example,
coherent picture of the business at a point in Sales figure.
time.
Designed to store enterprise-wide decision Dimensional modeling and star schema design
Data storing data, not just marketing data. employed for optimizing the performance of
access layer.
 Time variance and non-volatile design are Mostly includes consolidation data structures to
Data type strictly enforced. meet subject area’s query and reporting needs.
Data value Read-Only from the end-users standpoint. Transaction data regardless of grain fed directly
from the Data Warehouse.
Data mart contains data, of a specific
Scope Data warehousing is more helpful as it can department of a company. There are maybe
bring information from any department. separate data marts for sales, finance,
marketing, etc. Has
limited usage

Source In Data Warehouse Data comes from many In Data Mart data comes from very few sources.
sources.
Size The size of the Data Warehouse may range The Size of Data Mart is less than 100 GB.
from 100 GB to 1 TB+.
Implementation The implementation process of The implementation process of Data Mart is
time Data Warehouse can be extended restricted to few months.
from months to
years.

Knowledge Management (KM): Concept, Features and Process

Concept of KM:
KM may be defined as follows:
Knowledge management is a process of acquiring, generating, accumulating and using knowledge for the benefit
of the organisation to enable it to gain a competitive edge for survival, growth and prosperity in a globalized
competitive economy.
According to some management experts, notably Peter F. Drucker, KM is a bad term; in as much as knowledge
cannot be managed.
Rather, KM requires conditions for the emergence of a learning organisation; which is necessary for generation,
sharing and use of knowledge residing in the minds of people.
Features of Knowledge Management
Some salient features of KM are described below:
KM is a systematic process; consisting of standardized procedures to collect, store, distribute and use
knowledge. The essence of KM is to get right knowledge to right people, at the right time.
Knowledge is of two types – explicit and implicit. Explicit knowledge is visible information available in
literature, reports, patents, technical specifications, communication with customers, suppliers, competitors etc. It
can be embedded in rules, systems, policies and procedures etc. of the organisation.
Tacit or implicit knowledge is personal knowledge residing in the minds of people as a result of their personal
beliefs, values, perspectives and experience. There is a need for a learning organisation for enhancement, sharing
and utilisation of tacit knowledge.

KM is a continuous process; as the world economy is dynamic and full of challenges. It requires constant
creation of new skills and capabilities and improvement of existing ones.
KM requires whole-hearted support of top management, to provide cultural and technical foundation for the
origination and implementation of KM practices.

The objective of KM is improvement in organisational performance; to enable the organisation acquire, sharpen
and utilize its competitive edge for survival and growth in the global economy of today.
Knowledge Management and Information Technology:
KM is not an outgrowth of IT. Rather, KM requires human skills, creativity and innovative capabilities of
people; which are the base of KM. In fact I there are tools of IT like Intranets, Lotus Notes, MS-Exchange etc.;
which provide an infrastructure for the free play of human creativity and innovative powers for the formulation
of corporation strategy, in a competitive globalized environment.
The above ideas are illustrated with the help of the following diagram:

Knowledge Management IT and Corporate Strategy

An Overview of the Process of KM:
KM broadly consists of the following major steps:
Identification of Knowledge Needs:
The first step in KM is an identification of what type of knowledge is required for the successful designing and
implementation of corporate strategy.
Determination of Knowledge Assets:
The management must identify what are the knowledge assets of the organisation; which basically are
competitors, suppliers, governmental agencies, products and processes, technology etc. Management must plan
to get maximum returns out of knowledge assets.

Generation of Knowledge:
Generation of knowledge requires two sources:
Acquisition of knowledge through knowledge assets e.g. knowledge about new products (from competitors),
new technologies, social, economical, political changes. It
also requires transformation of raw information into knowledge, useful to solve business problems.
Generation of knowledge, by creating conditions for the emergence of a learning organisation. This is the most
important internal source of knowledge generation which makes tacit knowledge of individuals available for
organisational purposes.
Knowledge Storage:
It includes preserving existing and acquired knowledge in knowledge repositories. (A knowledge repository is
an on line computer based storehouse of organised information about a particular domain of knowledge).
Knowledge Distribution:
It is a process which allows members of the organisation to have an access to the collective knowledge of the
organisation.
Knowledge Utilization:
It requires embedding knowledge in products, processes, procedures etc. of the organisation. Best utilisation of
knowledge takes place when managers utilize knowledge in organisational decision making. A learning
organisation creates conditions for sharing and utilizing knowledge in organisational contexts.
Feedback on Knowledge Management
Feedback on KM implies evaluating the significance of knowledge assets. It also includes impact of KM on
organisational performance; and devising techniques for betterment of KM in future.

An overview of the process of KM- at a glance

Significance of Knowledge Management
Significance of KM could be highlighted with reference the following advantages which KM provides to the
organisation:

Building and Sharpening Competitive Edge:

KM enables a corporation to build and sharpen its competitive edge, for survival and growth in the competitive
globalized economy. In fact, KM aided by IT tools enables a corporation to design and implement most
appropriate corporate strategies.
Betterment of Human Relations:
KM is basically built on the knowledge generated, shared and utilized through a learning organisation. There is
no doubt that learning organisation provides the foundation on which the building of KM could be built. A
learning organisation through facilitating interaction among people of the organisation, leads to betterment of
human relations; which is a very big permanent asset an organisation can boast of to possess.
Improvement in Organisational Efficiency:
KM provides knowledge which can be embedded in organisational processes. It makes knowledge available for
decision-making purposes. Thus it helps to improve organisational efficiency, resulting in reduced costs and
increased profits, for the organisation.
Enhancement of Human Capital Capabilities
KM-its concept and practices – motivate people to enhance their intellectual capabilities, resulting in new skills,
improvement of existing skills etc. Thus not only does KM enhance the intellectual elements of people; but also
indirectly prevents depreciation of human capital.
Enhancement of Enterprise Goodwill:
Initiation and practices of KM help an enterprise enhance its goodwill in the global market; enabling it to acquire
more success and prosperity.
Types of Decisions in Business Intelligence
The characteristics of decisions faced by managers at different levels are quite different. Decisions can be
classified as structured, semi structured, and unstructured. Unstructured decisions are those in which the decision
maker must provide judgment, evaluation, and insights into the problem definition. Each of these decisions is
novel, important, and nonroutine, and there is no well-understood or agreed-on procedure for making them.

Structured decisions, by contrast, are repetitive and routine, and decision makers can follow a definite procedure
for handling them to be efficient. Many decisions have elements of both and are considered semi structured
decisions, in which only part of the problem has a clear-cut answer provided by an accepted procedure. In
general, structured decisions are made more prevalently at lower organizational levels, whereas unstructured
decision making is more common at higher levels of the firm.

Senior executives tend to be exposed to many unstructured decision situations that are open ended and
evaluative and that require insight based on many sources of information and personal experience. For example,
a CEO in today’s music industry might ask, “Whom should we choose as a distribution partner for our online
music catalog— Apple, Microsoft, or Sony?” Answering this question would require access to news,
government reports, and industry views as well as high-level summaries of firm performance. However, the
answer would also require senior managers to use their own best judgment and poll other managers for their
opinions.
Middle management and operational management tend to face more structured decision scenarios, but their
decisions may include unstructured components. A typical middlelevel management decision might be “Why is
the order fulfillment report
showing a decline over the last six months at a distribution center in Minneapolis?” This middle manager could
obtain a report from the firm’s enterprise system or distribution management system on order activity and
operational efficiency at the Minneapolis distribution center. This is the structured part of the decision. But
before arriving at an answer, this middle manager will have to interview employees and gather more
unstructured information from external sources about local economic conditions or sales trends.
Rank-and-file employees tend to make more structured decisions. For example, a sales account representative
often has to make decisions about extending credit to
customers by consulting the firm’s customer database that contains credit information. In this case the decision
is highly structured, it is a routine decision made thousands of times each day in most firms, and the answer has
been preprogrammed into a corporate risk management or credit reporting system.

The types of decisions faced by project teams cannot be classified neatly by organizational level. Teams are
small groups of middle and operational managers and perhaps employees assigned specific tasks that may last a
few months to a few years.
Their tasks may involve unstructured or semistructured decisions such as designing new products, devising new
ways to enter the marketplace, or reorganizing sales territories and compensation systems.

SYSTEMS FOR DECISION SUPPORT

There are four kinds of systems used to support the different levels . We introduced some of these systems in
Management information systems (MIS) provide routine reports and summaries of transaction- level data to
middle and operational-level managers to provide answers to structured and semistructured decision problems.
Decision-support systems (DSS) are targeted systems that combine analytical models with operational data and
supportive interactive queries and analysis for middle managers who face semistructured decision situations.
Executive support systems (ESS) are specialized systems that provide senior management making primarily
unstructured decisions with a broad array of both external information (news, stock analyses, industry trends)
and high-level summaries of firm performance. The purpose of ESS to help the C- level managers to focus on
the information that really affect the overall profitability and success of the firm. The leading methodology for
understanding the really important information needed by the firm’s executive is called the Balanced Score Card
Method, a frame work for operationalizing the firm’s strategic plan by focusing on measurable outcomes on four
dimensions of firm [Link],business process ,customer, learning and growth. Performance on
each dimension is measured using KPI’s.

Group decision-support systems (GDSS) are specialized systems that provide a group electronic environment in
which managers and teams can collectively make decisions and design solutions for unstructured and
semistructured [Link] guided meetings takes place in a conference rooms with special software and
hardware tools to facilitate group decision [Link] makes possible to increase the meeting size and increase in
[Link] individuals contribute simultaneously at the same time rather than one at a time.
STAGES IN THE DECISION-MAKING PROCESS

Making decisions consists of several different activities. Simon (1960) describes four different stages in decision
making: intelligence, design, choice, and implementation
The decision-making process can be described in four steps that follow one another in a logical order. In reality,
decision makers frequently circle back to reconsider the previous stages and through a process of iteration
eventually arrive at a solution that is workable.
Intelligence consists of discovering, identifying, and understanding the problems occurring in the organization—
why is there a problem, where, and what effects is it having on the firm. Traditional MIS that deliver a wide
variety of detailed information can help identify problems, especially if the systems report exceptions.

Design involves identifying and exploring various solutions to the problem. Decisionsupport systems (DSS) are
ideal in this stage for exploring alternatives because they possess analytical tools for modeling data, enabling
users to explore various options quickly.

Choice consists of choosing among solution alternatives. Here, DSS with access extensive firm data can help
managers choose the optimal solution. Also group decisionsupport systems can be used to bring groups of
managers together in an electronic online environment to discuss different solutions and make a choice.

Implementation involves making the chosen alternative work and continuing

to monitor how well the solution is working. Here, traditional MIS come back into play by providing managers
with routine reports on the progress of a specific solution.
Support systems can range from full-blown MIS to much smaller systems, as well as project-planning software
operating on personal computers.

In the real world, the stages of decision making described here do not necessarily follow a linear path. You can
be in the process of implementing a decision, only to discover that your solution is not working. In such cases,
you will be forced to repeat the design, choice, or perhaps even the intelligence stage.

For instance, in the face of declining sales, a sales management team may strongly support a new sales incentive
system to spur the sales force on to greater effort. If paying the sales force a higher commission for making more
sales does not produce sales increases, managers would need to investigate whether the problem stems from
poor product design, inadequate customer support, or a host of other causes, none of which would be “solved”
by a new incentive system.
3.2 Trends in Decision Support and Business Intelligence

Systems supporting management decision making originated in the early 1960s as early MIS that created fixed,
inflexible paper-based reports and distributed them to managers on a routine schedule. In the 1970s, the first
DSS emerged as standalone applications with limited data and a few analytic models. ESS emerged during the
1980s to give senior managers an overview of corporate operations. Early ESS were expensive, based on custom
technology, and suffered from limited data and flexibility.

The rise of client/server computing, the Internet, and Web technologies has made a major impact on systems that
support decision making. Many decision-support applications are now delivered over corporate intranets. We see
six major trends:

Detailed enterprise-wide data. Enterprise systems create an explosion in firmwide, current, and relatively
accurate information, supplying end users at their desktops with powerful analytic tools for analyzing and
visualizing data.
Broadening decision rights and responsibilities. As information becomes more widespread throughout the
corporation, it is possible to reduce levels of hierarchy and grant more decision-making authority to lower-level
employees.
Intranets and portals. Intranet technologies create global, company-wide networks that ease the flow of
information across divisions and regions and delivery of near real-time data to management and employee
desktops.
Personalization and customization of information. Web portal technologies provide great flexibility in
determining what data each employee and manager sees on his or her desktop. Personalization of decision
information can speed up decision making by enabling users to filter out irrelevant information.
Extranets and collaborative commerce. Internet and Web technologies permit suppliers and logistics partners to
access firm enterprise data and decision-support tools and work collaboratively with the firm.
Team support tools. Web-based collaboration and meeting tools enable project teams, task forces, and small
groups to meet online using corporate intranets or extranets. These new collaboration tools borrow from earlier
GDSS and are used for both brainstorming and decision sessions.
Business Intelligence
Business intelligence combines business analytics, data mining, data visualization, data tools and infrastructure,
and best practices to help organizations make more data-driven decisions. In practice, you know you’ve got
modern business intelligence when you have a comprehensive view of your organization’s data and use that data
to drive change, eliminate inefficiencies, and quickly adapt to market or supply changes. Modern BI solutions
prioritize flexible self-service analysis, governed data on trusted platforms, empowered business users, and
speed to insight
Business Intelligence is a set of processes, architectures, and technologies that convert raw data into meaningful
information that drives profitable business actions. It is a suite of software and services to transform data into
actionable intelligence and knowledge.
BI has a direct impact on organization’s strategic, tactical and operational business decisions. BI supports fact-
based decision making using historical data rather than assumptions and gut feeling.
BI tools perform data analysis and create reports, summaries, dashboards, maps, graphs, and charts to provide
users with detailed intelligence about the nature of the business.

Why is BI important?

Measurement: creating KPI (Key Performance Indicators) based on historic data

Identify and set benchmarks for varied processes.
With BI systems organizations can identify market trends and spot business problems that need to be addressed.
BI helps on data visualization that enhances the data quality and thereby the quality of decision making.
BI systems can be used not just by enterprises but SME (Small and Medium Enterprises)
How Business Intelligence systems are implemented?
step 1) Raw Data from corporate databases is extracted. The data could be spread across multiple systems
heterogeneous systems.
step 2) The data is cleaned and transformed into the data warehouse. The table can be linked, and data cubes are
formed.
Step 3) Using BI system the user can ask quires, request ad-hoc reports or conduct any other analysis.

Examples of Business Intelligence System used in Practice

Example 1:
. In an Online Transaction Processing (OLTP) system information that could be fed into product database could
be

add a product line

change a product price

Correspondingly, in a Business Intelligence system query that would beexecuted for the product subject area
could be did the addition of new product line or change in product price increase revenues
In an advertising database of OLTP system query that could be executed

Changed in advertisement options

Increase radio budget

Correspondigly, in BI system query that could be executed would be how many new clients added due to change
in radio budget
In OLTP system dealing with customer demographic data bases data that could be fed would be

increase customer credit limit

change in customer salary level

Correspondingly in the OLAP system query that could be executed would be can customer profile changes
support support higher product price
Example 2:

A hotel owner uses BI analytical applications to gather statistical information regarding average occupancy and
room rate. It helps to find aggregate revenue generated per room.

It also collects statistics on market share and data from customer surveys from each hotel to decides its
competitive position in various markets.
By analyzing these trends year by year, month by month and day by day helps management to offer discounts on
room rentals.
Example 3:

A bank gives branch managers access to BI applications. It helps branch manager to determine who are the most
profitable customers and which customers they should work on.
The use of BI tools frees information technology staff from the task of generating analytical reports for the
departments. It also gives department personnel access to a richer data source.
Four types of BI users
Following given are the four key players who are used Business Intelligence System:

The Professional Data Analyst:

The data analyst is a statistician who always needs to drill deep down into data. BI system helps them to get
fresh insights to develop unique business strategies.
The IT users:

The IT user also plays a dominant role in maintaining the BI infrastructure.

The head of the company:

CEO or CXO can increase the profit of their business by improving operational efficiency in their business.
The Business Users”

Business intelligence users can be found from across the organization. There are mainly two types of business
users
Casual business intelligence user
The power user.

The difference between both of them is that a power user has the capability of working with complex data sets,
while the casual user need will make him use dashboards to evaluate predefined sets of data.
Advantages of Business Intelligence
Here are some of the advantages of using Business Intelligence System:

Boost productivity

With a BI program, It is possible for businesses to create reports with a single click thus saves lots of time and
resources. It also allows employees to be more productive on their tasks.
To improve visibility

BI also helps to improve the visibility of these processes and make it possible to identify any areas which need
attention.

Fix Accountability

BI system assigns accountability in the organization as there must be someone who should own accountability
and ownership for the organization’s performance against its set goals.
It gives a bird’s eye view:

BI system also helps organizations as decision makers get an overall bird’s eye view through typical BI features
like dashboards and scorecards.
It streamlines business processes:

BI takes out all complexity associated with business processes. It also automates analytics by offering predictive
analysis, computer modeling, benchmarking and other methodologies.
It allows for easy analytics.

BI software has democratized its usage, allowing even nontechnical or non-analysts users to collect and process
data quickly. This also allows putting the power of analytics from the hand’s many people.
BI System Disadvantages
Cost:

Business intelligence can prove costly for small as well as for medium-sized enterprises. The use of such type of
system may be expensive for routine business transactions.

Complexity:

Another drawback of BI is its complexity in implementation of datawarehouse. It can be so complex that it can
make business techniques rigid to deal with.
Limited use

Like all improved technologies, BI was first established keeping in consideration the buying competence of rich
firms. Therefore, BI system is yet not affordable for many small and medium size companies.
Time Consuming Implementation

It takes almost one and half year for data warehousing system to be completely implemented. Therefore, it is a
time-consuming process.

What is OLAP?
A core component of data warehousing implementations, OLAP enables fast, flexible multidimensional data
analysis for business intelligence (BI) and decision support applications.
OLAP (for online analytical processing) is software for performing multidimensional analysis at high speeds on
large volumes of data from a data warehouse, data mart, or some other unified, centralized data store.

Most business data have multiple dimensions—multiple categories into which the data are broken down for
presentation, tracking, or analysis. For example, sales figures might have several dimensions related to location
(region, country, state/province, store), time (year, month, week, day), product (clothing, men/women/children,
brand, type), and more.
But in a data warehouse, data sets are stored in tables, each of which can organize data into just two of these
dimensions at a time. OLAP extracts data from multiple relational data sets and reorganizes it into a
multidimensional format that enables very fast processing and very insightful analysis.
What is an OLAP cube?

The core of most OLAP systems, the OLAP cube is an array-based multidimensional database that makes it
possible to process and analyze multiple data dimensions much more quickly and efficiently than a traditional
relational database.
A relational database table is structured like a spreadsheet, storing individual records in a two-dimensional, row-
by-column format. Each data “fact” in the database sits at the intersection of two dimensions–a row and a
column—such as region and total sales.
SQL and relational database reporting tools can certainly query, report on, and analyze multidimensional data
stored in tables, but performance slows down as the data volumes increase. And it requires a lot of work to
reorganize the results to focus on different dimensions.
This is where the OLAP cube comes in. The OLAP cube extends the single table with additional layers, each
adding additional dimensions—usually the next level in the “concept hierarchy” of the dimension. For example,
the top layer of the cube might organize sales by region; additional layers could be country, state/province, city
and even specific store.
In theory, a cube can contain an infinite number of layers. (An OLAP cube representing more than three
dimensions is sometimes called a hypercube.) And smaller cubes can exist within layers—for example, each
store layer could contain cubes arranging sales by salesperson and product. In practice, data analysts will create
OLAP cubes containing just the layers they need, for optimal analysis and performance.
OLAP cubes enable four basic types of multidimensional data analysis:

Drill-down

The drill-down operation converts less-detailed data into more-detailed data through one of two
methods—moving down in the concept hierarchy or adding a new dimension to the cube. For example,
if you view sales data for an organization’s calendar or fiscal quarter, you can drill-down to see sales
for each month, moving down in the concept hierarchy of the “time” dimension.
Roll up

Roll up is the opposite of the drill-down function—it aggregates data on an OLAP cube by moving up
in the concept hierarchy or by reducing the number of dimensions. For example, you could move up in
the concept hierarchy of the “location” dimension by viewing each country's data, rather than each city.
Slice and dice
The slice operation creates a sub-cube by selecting a single dimension from the main OLAP cube. For
example, you can perform a slice by highlighting all data for the organization's first fiscal or calendar
quarter (time dimension).
The dice operation isolates a sub-cube by selecting several dimensions within the main
OLAP cube. For example, you could perform a dice operation by highlighting all data by
an organization’s calendar or fiscal quarters (time dimension) and within the U.S. and
Canada (location dimension).
Pivot

The pivot function rotates the current cube view to display a new representation of the
data—enabling dynamic multidimensional views of data. The OLAP pivot function is
comparable to the pivot table feature in spreadsheet software, such as Microsoft Excel,
but while pivot tables in Excel can be challenging, OLAP pivots are relatively easier to
use (less expertise is required) and have a faster response time and query performance.
MOLAP vs. ROLAP vs. HOLAP

OLAP that works directly with a multidimensional OLAP cube is known as

multidimensional OLAP, or MOLAP. Again, for most uses, MOLAP is the fastest and
most practical type of multidimensional data analysis.
However, there are two other types of OLAP which may be preferable in certain cases:

ROLAP

ROLAP, or relational OLAP, is multidimensional data analysis that operates directly on

data on relational tables, without first reorganizing the data into a cube.
As noted previously, SQL is a perfectly capable tool for multidimensional queries,
reporting, and analysis. But the SQL queries required are complex, performance can drag,
and the resulting view of the data is static—it can't be pivoted to represent a different
view of the data. ROLAP is best when the ability to work directly with large amounts of
data is more important than performance and flexibility.

HOLAP
HOLAP, or hybrid OLAP, attempts to create the optimal division of labor between
relational and multidimensional databases within a single OLAP architecture. The
relational tables contain larger quantities of data, and OLAP cubes are used for
aggregations and speculative processing. HOLAP requires an OLAP server that supports
both MOLAP and ROLAP.
A HOLAP tool can "drill through" the data cube to the relational tables, which paves the
way for quick data processing and flexible access. This hybrid system can offer better
scalability but can't escape the inevitable slow-down when accessing relational data
sources. Also, its complex architecture typically requires more frequent updates and
maintenance, as it must store and process all the data from relational databases and
multidimensional databases. For this reason, HOLAP can end up being more expensive.
OLAP vs. OLTP

Online transaction processing, or OLTP, refers to data-processing methods and software

focused on transaction-oriented data and applications.

The main difference between OLAP and OLTP is in the name: OLAP is analytical in
nature, and OLTP is transactional.
OLAP tools are designed for multidimensional analysis of data in a data warehouse,
which contains both transactional and historical data. In fact, an OLAP server is typically
the middle, analytical tier of a data warehousing solution. Common uses of OLAP include
data mining and other business intelligence applications, complex analytical calculations,
and predictive scenarios, as well as business reporting functions like financial analysis,
budgeting, and forecast planning.
OLTP is designed to support transaction-oriented applications by processing recent
transactions as quickly and accurately as possible. Common uses of OLTP include ATMs,
e-commerce software, credit card payment processing, online bookings, reservation
systems, and record-keeping tools.