 Basic BI Architectural Components: page 592 Table 13.

2
o Extraction, transformation, and loading (ETL): In a data warehousing environment, the
integrated processes of getting data from original sources into the data warehouse. ETL
includes retrieving data from original data sources (extraction), manipulating the data into
an appropriate form (transformation), and storing the data in the data warehouse
(loading).
o TABLE 13.2
BASIC BI Architectural Components

Component Description
Data extraction, transformation, and loading (ETL) tools collect,
ETL tools filter, integrate, and aggregate internal and external data to be saved
into a data store optimized for decision support.
The data store is optimized for decision support and is generally
represented by a data warehouse or a data mart. The data is
Data store
stored in structures that are optimized for data analysis and query
speed.
This component performs data selection and retrieval, and it is
Query and
used by the data analyst to create queries that access the
reporting
database and create the required reports.
This component presents data to the end user in a variety of
meaningful and innovative ways. This tool helps the end user
Data visualization select the most appropriate presentation format, such as summary
reports, maps, pie or bar graphs, mixed graphs, and static or
interactive dashboards.
This component allows real-time monitoring of business
activities. The BI system will present concise information in a
single integrated view. This integrated view could include specific
metrics about the system performance or activities, such as number
Data monitoring of orders placed in the last four hours, number of customer
and alerting complaints by product by month, and total revenue by region.
Alerts can be placed on a given metric; once the value of a metric
goes below or above a certain baseline, the system will perform a
given action, such as emailing shop floor managers, presenting
visual alerts, or starting an application.
This component performs data analysis and data-mining tasks
using the data in the data store. This tool advises the user as to
which data analysis tool to select and how to build a reliable
Data analytics
business data model. Business models are generated by special
algorithms that identify and enhance the understanding of business
situations and problems.

 Operational data vs. Decision Support (Analytics) Data: page 601 – 604

o Most operational data is stored in a relational database in which the structures (tables)
tend to be highly normalized. Operational data storage is optimized to support
transactions that represent daily operations.
 For example, each time an item is sold, it must be accounted for. Customer data,
inventory data, and other similar data need frequent updating. To provide
effective update performance, operational systems store data in many tables, each
 with a minimum number of fields. Thus, a simple sales transaction might be
represented by five or more different tables, such as INVOICE, INVOICE
LINE, DISCOUNT, STORE, and DEPARTMENT.
o Although such an arrangement is excellent in an operational database, it is not
efficient for query processing. For example, to extract a simple invoice, you would have
to join several tables. Whereas operational data is useful for capturing daily business
transactions, decision support data gives tactical and strategic business meaning to
the operational data.
 For example, to extract a simple invoice, you would have to join several tables.
o From the data analyst’s point of view, decision support data differs from operational data
in three main areas: time span, granularity, and dimensionality.
 Time span: Operational data covers a short time frame. In contrast,
decision support data tends to cover a longer time frame. Managers are
seldom interested in a specific sales invoice to Customer X; rather, they tend
to focus on sales generated during the last month, the last year, or the last
five years.
 Granularity (level of aggregation): Decision support data must be
presented at different levels of aggregation, from highly summarized to
nearly atomic. For example, if managers analyze regional sales, they must be
able to access data showing the sales by region, by city within the region, by
store within the city within the region, and so on. In that case, summarized
data to compare the regions is required, along with data in a structure that
enables a manager to drill down, or decompose, the data into more
atomic components—that is, finer-grained data at lower levels of
aggregation. In contrast, when you roll up the data, you are aggregating
the data to a higher level.
 Dimensionality: Operational data focuses on representing individual
transactions rather than the effects of the transactions over time. In contrast,
data analysts tend to include many data dimensions and are interested in
how the data relates over those dimensions. For example, an analyst might
want to know how Product X fared relative to Product Z during the past six
months by region, state, city, store, and customer. In that case, both place and
time are part of the picture.
o From the designer’s point of view, the differences between operational and decision
support data are as follows:
 Operational data represents transactions as they happen in real time. Decision
support data is a snapshot of the operational data at a given point in time.
Therefore, decision support data is historic, representing a time slice of the
operational data.
 Operational and decision support data are different in terms of transaction type
and transaction volume. Whereas operational data is characterized by update
transactions, decision support data is mainly characterized by read-only
transactions. Decision support data also requires periodic updates to load
new data that is summarized from the operational data. Finally, the
concurrent transaction volume in operational data tends to be very high compared
with the low to medium levels in decision support data.
 Operational data is commonly stored in many tables, and the stored data
represents information about a given transaction only. Decision support data is
generally stored in a few tables derived from the operational data. The
decision support data does not include the details of each operational transaction.
 Instead, decision support data represents transaction summaries; therefore,
the decision support database stores data that is integrated, aggregated, and
summarized for decision support purposes.
 The degree to which decision support data is summarized is very high when
contrasted with operational data. Therefore, you will see a great deal of
derived data in decision support databases. Decision support data might be
collected to monitor such aggregates as total sales for each store or for each
product. The purpose of the summaries is simple: they are used to establish
and evaluate sales trends and product sales comparisons and to provide
other data that serves decision needs.
 The data models that govern operational data and decision support data are
different. The operational database’s frequent and rapid data updates make
data anomalies a potentially devastating problem. Therefore, the data in a
relational transaction (operational) system generally requires normalized
structures that yield many tables, each of which contains the minimum number of
attributes. In contrast, the decision support database is not subject to such
transaction updates, and the focus is on querying capability. Therefore, decision
support databases tend to be non-normalized and include few tables, each of
which contains a large number of attributes.
 The frequency and complexity of query activity in the operational database tends
to be low to allow additional processing cycles for the more crucial update
transactions. Therefore, queries against operational data typically are narrow in
scope and low in complexity, and high speed is critical. In contrast, decision
support data exists for the sole purpose of serving query requirements. Queries
against decision support data typically are broad in scope and high in complexity,
and less speed is needed.
 Finally, decision support data is characterized by very large amounts of data. The
large data volume is the result of two factors. First, data is stored in non-
normalized structures that are likely to display many data redundancies and
duplications. Second, the same data can be categorized in many different
ways to represent different snapshots. For example, sales data might be stored
in relation to product, store, customer, region, and manager.

TABLE 13.5
CONTRASTING OPERATIONAL AND DECISION SUPPORT DATA
CHARACTERISTICS
CHARACTERISTIC OPERATIONAL DATA DECISION SUPPORT DATA
Historic data
Current operations
Data currency Snapshot of company data
Real-time data
Time component (week/month/year)
Granularity Atomic-detailed data Summarized data
Summarization level Low; some aggregate yields High; many aggregation levels
Non-normalized
Highly normalized Complex structures
Data model
Mostly relational DBMSs Some relational, but mostly
multidimensional DBMSs
Transaction type Mostly updates Mostly query
Transaction volumes High-update volumes Periodic loads and summary calculations
Transaction speed Updates are critical Retrievals are critical
Query activity Low to medium High
Query scope Narrow range Broad range
Query complexity Simple to medium Very complex
Data volumes Hundreds of gigabytes Terabytes to petabytes

 Data Warehouse and its components: page 606 – 608:

o DATA WAREHOUSE: An integrated, subject oriented, time-variant, nonvolatile
collection of data that provides support for decision making.
 TABLE 13.8
CHARACTERISTICS OF DATA WAREHOUSE DATA AND OPERATIONAL DATABASE
DATA
CHARACTERISTI OPERATIONAL
DATA WAREHOUSE DATA
C DATABASE DATA
Similar data can have different
representations
or meanings. For example, Social
Security
numbers may be stored as ###-
Provide a unified view of all data elements with
##-#### or
INTEGRATED a common definition and representation for
as #########, and a given
all business units.
condition may be
labeled as T/F or 0/1 or Y/N. A
sales value may
be shown in thousands or in
millions.
Data is stored with a functional,
or process, Data is stored with a subject orientation that
SUBJECT- orientation. For example, data facilitates multiple views of the data and decision
ORIENTED may be stored making. For example, sales may be recorded by
for invoices, payments, and credit product, division, manager, or region.
amounts.
Data is recorded as current
transactions. For
Data is recorded with a historical perspective
example, the sales data may be
in mind. Therefore, a time dimension is added
TIME-VARIANT the sale of a
to facilitate data analysis and various time
product on a given date, such as
comparisons.
$342.78 on
12-MAY-2016.
Data updates are frequent and
common. For
Data cannot be changed. Data is added only
example, an inventory amount
periodically from historical systems. Once the
NONVOLATILE changes with
data is properly stored, no changes are allowed.
each sale. Therefore, the data
Therefore, the data environment is relatively static.
environment
is fluid.

 In summary, the data warehouse is a read-only database optimized for data analysis and query
processing. Typically, data is extracted from various sources and is then transformed and
integrated—in other words, passed through a data filter— before being loaded into the data
warehouse. As mentioned, this process is known as ETL. Figure 13.4 illustrates the ETL
process to create a data warehouse from
operational data.
 Although the centralized and integrated data warehouse can be an attractive proposition that
yields many benefits, managers may be reluctant to embrace this strategy.
Creating a data warehouse requires time, money, and considerable managerial effort.
 Data Mart: page 609
o Definition: A small, single-subject data warehouse subset that provides decision support
to a small group of people.
o In addition, a data mart could be created from data extracted from a larger data
warehouse for the specific purpose of supporting faster data access to a target group or
function. That is, data marts and data warehouses can coexist within a business
intelligence environment.
o Advantages of Data Mart:
 Lower cost and shorter implementation time with the technological advances and
inevitable “people issues.”
 Provide customized decision support system to small groups that overcame the
centralized system.
 Embraces minor changes to support for improved decision-making process.
 people at different organizational levels are likely to require data with different
summarization, aggregation, and presentation formats. Data marts can serve as a
test vehicle for companies exploring the potential benefits of data warehouses.

 The only difference between a data mart and a data warehouse is the size and scope of the
problem being solved. The problem definitions and data requirements are essentially the same for
both. To be useful, the data warehouse must conform to uniform structures and formats to avoid
data conflicts and support decision making.

 Star Schema: page 609 – 616:

o Star Schema: A data modeling technique used to map multidimensional decision support
data into a relational database. The star schema represents data using a central table
known as a fact table in a 1:M relationship with one or more dimension table.
o The basic star schema has four components: facts, dimensions, attributes, and
attribute hierarchies.

1. FACTS:
a. Facts: In a data warehouse, the measurements(values) that measure a specific business
aspect or activity. For example, sales figures are numeric measurements that represent
product or service sales. Facts commonly used in business data analysis include units,
costs, prices, and revenues.
b. Fact table: In a data warehouse, the star schema table that contains facts linked and
classified through their common dimensions. A fact table is in a one-to-many relationship
with each associated dimension table. The fact table is updated periodically with data
from the operational database.
c. Metrics: In a data warehouse, numeric facts that measure a business characteristic of
interest to the end user. These variables are derived or computed from facts.
2. DIMENSIONS:
a. Dimensions: qualifying characteristics that provide additional perspective to a given fact.
For example, to study the Sales Revenues (fact), we can have 3 different dimensions,
including Product, Location, Time.
b. Dimension Table: In a data warehouse, tables are used to search, filter, or classify facts
within a star schema.

3. ATTRIBUTES:
  Each dimension table contains attributes. Attributes are often used to search, filter, or classify
facts. Dimensions provide descriptive characteristics about the facts through their
attributes.
 TABLE 13.10
POSSIBLE ATTRIBUTES FOR SALES DIMENSIONS
DIMENSION NAME DESCRIPTION POSSIBLE ATTRIBUTES
Anything that provides a description of
Location the location— for example, Nashville, Region, state, city, store, and so on
Store 101, South Region, and TN
Anything that provides a description of
Product type, product ID, brand,
the product sold—for example, hair care
Product package, presentation, color, size,
product, shampoo, Natural Essence
and so on
brand, 5.5-oz. bottle, and blue liquid
Anything that provides a time frame for
the sales fact—for example, the year Year, quarter, month, week, day, time of day, and
Time
2018, the month of July, the date so on
07/29/2018, and the time 4:46 p.m.

 Slice and dice: The ability to focus on slices of a data cube (drill down or roll up) to perform a
more detailed analysis.
o Time is an especially important dimension; it provides a framework from which
sales patterns can be analyzed and possibly predicted.
4. ATTRIBUTE HIERARCHIES:
 Attribute Hierarchy: A top-down data organization that is used for two main purposes:
aggregation and drilldown/roll-up data analysis.
 The attribute hierarchy provides the capability to perform drill-down and roll-up searches in a
data warehouse.
o For example, suppose a data analyst looks at the answers to the following query: How
does the 2017 month-to-date sales performance compared to the 2018 month-to-date
sales performance? The data analyst spots a sharp sales decline for March 2018, and thus
might decide to drill down inside the month of March to see how sales by regions
compared to the previous year. By doing that, the analyst can determine whether the low
March sales were reflected in all regions or in only one particular region. This type of
drill-down operation can even be extended until the data analyst identifies the store that is
performing below the norm.
 It is not necessary for all attributes to be part of an attribute hierarchy; some attributes exist
merely to provide narrative descriptions of the dimensions.

Thursday

 Online Analytical Processing (OLAP): page 620

o Relational OLAP: page 625
o Multidimensional OLAP: page 627
 SQL Analytic Functions
o ROLLUP: page 633
o SLICE and DICE: page 612