CHAPTER I: FUNDAMENTAL CONCEPTS: INTRODUCTION On-Line Analytical Processing (OLAP) is an

TO DATA WAREHOUSE SYSTEMS advanced data analysis environment that supports decision
making, business modeling, and operations research
1. Introduction activities. OLAP systems are designed to use both operational
and Data Warehouse data.

Business intelligence comprises a collection of Four Main Characteristics of OLAP:

methodologies, processes, architectures, and technologies
that transform raw data into meaningful and useful  Use multidimensional data analysis techniques
information for decision making. Business intelligence and  Provide advanced database support
decision-support systems provide assistance to managers  Provide easy-to-use end user interfaces
at various organizational levels for analyzing strategic  Support client/server architecture
information. These systems collect vast amounts of data and
reduce them to a form that can be used to analyze
organizational behavior.

This data transformation comprises a set of tasks

that take the data from the sources and, through extraction,
transformation, integration, and cleansing processes, store
the data in a common repository called a data warehouse.
Data warehouses have been developed and deployed as an
integral part of decision-support systems to provide an
infrastructure that enables users to obtain efficient and
accurate responses to complex queries.

The Data Warehouse is an integrated, subject- Figure 2. OLAP Server Arrangement

oriented, time-variant, non-volatile database that provides
support for decision making.
The design of operational databases is typically
performed in four phases: requirements specification,
Integrated: The Data Warehouse is a centralized, conceptual design, logical design, and physical design.
consolidated database that integrates data retrieved from the During the requirements specification process, the needs of
entire organization. users at various levels of the organization are collected. The
specification obtained serves as a basis for creating a
Subject-Oriented: The Data Warehouse data is arranged database schema capable of responding to user queries.
and optimized to provide answers to questions coming from
diverse functional areas within a company. Databases are designed using a conceptual model,
such as the entity-relationship (ER) model, which aims at
Time Variant: The Warehouse data represent the flow of describing an application without taking into account
data through time. It can even contain projected data. implementation considerations. The resulting design is then
translated into a logical model, which is an implementation
Non-Volatile: Once data enter the Data Warehouse, they are paradigm for database applications. Finally, physical design
never removed. The Data Warehouse is always growing. particularizes the logical model for a specific implementation
platform in order to produce a physical model.

Multidimensional modeling views data as consisting of

facts linked to several dimensions.

Multidimensional Data Analysis Techniques

 The processing of data in which data are viewed as

part of a multidimensional structure.
 Multidimensional view allows end users to
consolidate or aggregate data at different levels.
 Multidimensional view allows a business analyst to
easily switch business perspectives.

A fact represents the focus of analysis (e.g., analysis of sales

in stores) and typically includes attributes called measures.

Figure 1. Comparison of Data Warehouse and Operational

Measures are usually numeric values that allow a
Database Characteristics
quantitative evaluation of various aspects of an organization.
For example, measures such as the amount or number of
A data warehouse system has the following characteristics: sales might help to analyze sales activities in various stores.

 It provides a centralized utility of corporate data or Dimensions are used to see the measures from several
information assets. perspectives. For example, a time dimension can be used to
 It is contained in a well-managed environment. analyze changes in sales over various periods of time,
 It has consistent and repeatable processes defined whereas a location dimension can be used to analyze sales
for loading operational data. according to the geographical distribution of stores.
 It is built on an open and scalable architecture that
will handle future expansion of data.
Hierarchies allow users to explore measures at various levels
 It provides tools that allow its users to effectively
of detail. Examples of hierarchies are month–quarter–year in
process the data into information without a high
the time dimension and city–state–country in the location
degree of technical support.
dimension.

A wide variety of systems and tools can be used for

accessing, analyzing, and exploiting the data contained in
data warehouses. From the early days of data warehousing,
the typical mechanism for those tasks has been online
analytical processing (OLAP). OLAP systems allow users to
interactively query and automatically aggregate the data
contained in a data warehouse. In this way, decision makers
can easily access the required information and analyze it at
various levels of detail.
 Over the years, spatial data has been increasingly
used in various areas, like public administration,
transportation networks, environmental systems, and public
health, among others. Spatial data can represent either
objects located on the Earth’s surface, such as mountains,
cities, and rivers, or geographic phenomena, such as
temperature, precipitation, and altitude. Spatial data can also
represent nongeographic data, that is, data located in other
spatial frames such as a human body, a house, or an engine.

Management of spatial data is carried out by spatial

Figure 3. OLAP Server with Multidimensional Data Store databases or geographic information systems (GISs).
Arrangement Since the latter are used for storing and manipulating
geographic objects and phenomena, we shall use the more
Conceptual Modelling of Data Warehouses general term spatial databases in the following. Spatial
databases are used to store spatial data located in a two- or
Star schema: A fact table in the middle connected to a set of three-dimensional space.
dimension tables
These systems provide a set of functions and
operators for querying and manipulating spatial data. Queries
Snowflake schema: A refinement of star schema where
may refer to spatial characteristics of individual objects, such
some dimensional hierarchy is normalized into a set of smaller
as their area or perimeter, or may require complex operations
dimension tables, forming a shape similar to snowflake
on two or more spatial objects.

Fact constellations: Multiple fact tables share dimension Topological relationships between spatial objects,
tables, viewed as a collection of stars, therefore called galaxy such as intersection, touches, and crosses, are essential in
schema or fact constellation spatial applications. For example, two roads may intersect,
two countries may touch because they have a common
border, or a road may cross a dessert. An important
characteristic of topological relationships is that they do not
change when the underlying space is distorted through
rotation, scaling, and similar operations.

Spatial data warehouses emerged as a

combination of the spatial database and data warehouse
technologies. Spatial data warehouses provide improved data
analysis, visualization, and manipulation. This kind of analysis
is called spatial OLAP (SOLAP), conveying a reference to
the ability of exploring spatial data through map navigation
and aggregation, as it is performed in OLAP with tables and
charts.

3. New Domains and Challenges

Nowadays, the availability of enormous amounts of data

is calling for a shift in the way data warehouse and business
intelligence practices have been carried out since the 1990s.
It is becoming clear that for certain kinds of business
intelligence applications, the traditional approach, where day-
to-day business data produced in an organization are
collected in a huge common repository for data analysis,
needs to be revised, to account for efficiently handling large-
Although data warehouses are, in the end, a particular scale data. In many emerging domains where business
kind of databases, there are significant differences between intelligence practices are gaining acceptance, such as social
the development of operational databases and data networks or geospatial data analytics, massive-scale data
warehouses. A key one is the fact that data in a warehouse sources are becoming common, posing new challenges to the
are extracted from several source systems. Thus, data must data warehouse research community.
be taken from these sources, transformed to fit the data
We already commented that the typical method of
warehouse model, and loaded into the data warehouse. This
loading data into a data warehouse is through an ETL process.
process is called extraction, transformation, and loading
This process pulls data from source systems periodically (e.g.,
(ETL), and it has been proven crucial for the success of a data
daily, weekly, or monthly), obtaining snapshot of the business
warehousing project.
data at a given moment in time. These data are then used for
Data analytics is the process of exploiting the refreshing the contents of the data warehouse.
contents of a data warehouse in order to provide essential
For some data analysis tasks (like worldwide price
information to the decision-making process. Three main tools
evolution of some product), the data contained in a
can be used for this:
conventional data warehouse may not suffice. External data
Data mining consists in a series of statistical techniques that sources, like the web, can provide useful multidimensional
analyze the data in a warehouse in order to discover useful information, although usually too volatile to be permanently
knowledge that is not easy to obtain from the original data. stored. The semantic web aims at representing web content
in a machine-processable way. The basic layer of the data
Key performance indicators (KPIs) are measurable representation for the semantic web recommended by the
organizational objectives that are used for characterizing how World Wide Web Consortium (W3C) is the Resource
an organization is performing. Description Framework (RDF), on top of which the Web
Ontology Language (OWL) is based.
Dashboards are interactive reports that present the data in a
warehouse, including the KPIs, in a visual way, providing an
overview of the performance of an organization for decision-
support purposes.

2. Spatial and Spatiotemporal Data

Warehouse