$30.00 Due in 24 hour Technical work Homework Assignment 3

mek

chapter13.pptx

Home >Computer Science homework help >$30.00 Due in 24 hour Technical work Homework Assignment 3

Chapter 13

Business Intelligence and Data Warehouses

‹#›

Learning Objectives

After completing this chapter, you will be able to:

Describe the role of business intelligence in providing comprehensive business decision support

Describe the architecture, reporting styles, evolution, and benefits of business intelligence

Differentiate between operational data and decision support data

Identify the purpose, characteristics, and components of a data warehouse

Develop star and snowflake schemas for decision-making purposes

Describe the characteristics and capabilities of online analytical processing (OLAP)

Describe the role and functions of data analytics and data mining

Explain how SQL analytic functions are used to support data analytics

Define data visualization and explain how it supports business intelligence

© 2019 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

The Need for Data Analysis

Organizations tend to grow and prosper as they gain a better understanding of their environment

Evaluate through tracking daily transactions and analyzing company data

Organizations are always looking for a competitive advantage

Product development, market positioning, sales promotions, and customer service

Companies and software vendors addressed these multilevel decision support needs by creating autonomous applications for particular groups of users

This more comprehensive and integrated decision support framework within organizations became known as business intelligence

‹#›

Business Intelligence (1 of 2)

Comprehensive, cohesive, integrated set of tools and processes

Captures, collects, integrates, stores, and analyzes data

Generates and presents information to support business decision making

Allows transformation

Data into information

Information into knowledge

Knowledge into wisdom

‹#›

Business Intelligence (2 of 2)

Concepts, practices, tools and techniques to help business

Understand core capabilities

Provide snapshots of the company situation

Identify key opportunities to create a competitive advantage

Provides a framework

Collecting and storing operational data and aggregating it into decision support data

Analyzing decision support data and presenting generated information to end users to support business decisions

Making business decisions which generate more data

Monitoring results to evaluate outcomes and predicting future outcomes with a high degree of accuracy

‹#›

Business Intelligence Architecture (1 of 3)

‹#›

Business Intelligence Architecture (2 of 3)

Table 13.2 Basic BI Architectural Components
Component	Description
ETL tools	Data extraction, transformation, and loading (ETL ) tools collect, filter, integrate, and aggregate internal and external data to be saved into a data store optimized for decision support.
Data store	The data store is optimized for decision support and is generally represented by a data warehouse or a data mart. The data is stored in structures that are optimized for data analysis and query speed.
Query and reporting	This component performs data selection and retrieval, and it is used by the data analyst to create queries that access the database and create the required reports.
Data visualization	This component presents data to the end user in a variety of meaningful and innovative ways. This tool helps the end user select the most appropriate presentation format, such as summary reports, maps, pie or bar graphs, mixed graphs, and static or interactive dashboards.
Data monitoring and alerting	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 of orders placed in the last four hours, number of customer 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.
Data analytics	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 business data model. Business models are generated by special algorithms that identify and enhance the understanding of business situations and problems.

‹#›

Business Intelligence Architecture (3 of 3)

Practices to manage data

Master data management (MDM): collection of concepts, techniques, and processes for identification, definition, and management of data elements

Governance: method of government for controlling business health and for consistent decision making

Key performance indicators (KPI): numeric or scale-based measurements that assess company’s effectiveness in reaching its goals

General

Finance

Human resources

Education

Modern BI reporting styles

Advanced reporting

Monitoring and alerting

Advanced data analytics

‹#›

Business Intelligence Benefits

Improved decision making is the main goal of BI, but BI provides other benefits

Integrating architecture

Common user interface for data reporting and analysis

Common data repository fosters single version of company data

Improved organizational performance

Achieving all these benefits takes a lot of human, financial, technological resources, and time

BI benefits are not achieved overnight; are the result of a focused company-wide effort that could take a long time

‹#›

Business Intelligence Evolution (1 of 2)

‹#›

Business Intelligence Evolution (2 of 2)

Decision support system (DSS) is an arrangement of computerized tools used to assist managerial decision making

Typically has a much narrower focus and reach than a BI solution

BI information technology has evolved from centralized reporting styles to the current, mobile BI and Big Data analytics style in the span of just a few years

The rate of technological change is not slowing down; technology advancements are accelerating the adoption of BI to new levels

‹#›

Business Intelligence Technology Trends

Several technological advances are driving the growth of business intelligence technologies

Data storage improvements

Business intelligence appliances

Business intelligence as a service

Big Data analytics

Personal analytics

‹#›

Decision Support Data

Although BI is used at the strategic and tactical managerial levels within organizations, its effectiveness depends on the quality of data gathered at the operational level

Operational data is seldom well suited to decision support tasks

‹#›

Operational Data versus Decision Support Data (1 of 3)

Operational data and decision support data serve different purposes

Operational data is useful for capturing daily business transactions

Decision support data gives tactical and strategic business meaning to the operational data

Decision support data differs from operational data in three main areas

Time span

Granularity (level of aggregation)

Dimensionality

‹#›

Operational Data versus Decision Support Data (2 of 3)

‹#›

Operational Data versus Decision Support Data (3 of 3)

Table 13.5 Contrasting Operational and Decision Support Data Characteristics
Characteristic	Operational Data	Decision Support Data
Data currency	Current operations Real-time data	Historic data Snapshot of company data Time component (week/month/year)
Granularity	Atomic-detailed data	Summarized data
Summarization level	Low; some aggregate yields	High; many aggregation levels
Data model	Highly normalized Mostly relational DBMSs	Non-normalized Complex structures 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

‹#›

Decision Support Database Requirements

Database schema

Must support complex, non-normalized data representations

Data must be aggregated and summarized

Queries must be able to extract multidimensional time slices

Data extraction and filtering

Allow batch and scheduled data extraction

Support different data sources and check for inconsistent data or data validation rules

Encourage advanced integration, aggregation, and classification

Database size

Very large databases (VLDBs)

Advanced storage technologies

Multiple-processor technologies

‹#›

The Data Warehouse (1 of 3)

Collection of data

Provides support for decision making

Components

Integrated

Subject-oriented

Time-variant

Nonvolatile

‹#›

The Data Warehouse (2 of 3)

Table 13.8 Characteristics of Data Warehouse Data and Operational Database Data
Characteristic	Operational Database Data	Data Warehouse Data
Integrated	Similar data can have different representations or meanings. For example, Social Security numbers may be stored as ###-##-#### or as #########, and a given condition may be labeled as T/F or 0/1 or Y/N. A sales value may be shown in thousands or in millions.	Provide a unified view of all data elements with a common definition and representation for all business units.
Subject-oriented	Data is stored with a functional, or process, orientation. For example, data may be stored for invoices, payments, and credit amounts.	Data is stored with a subject orientation that facilitates multiple views of the data and decision making. For example, sales may be recorded by product, division, manager, or region.
Time-variant	Data is recorded as current transactions. For example, the sales data may be the sale of a product on a given date, such as $342.78 on 12-MAY-2016.	Data is recorded with a historical perspective in mind. Therefore, a time dimension is added to facilitate data analysis and various time comparisons.
Nonvolatile	Data updates are frequent and common. For example, an inventory amount changes with each sale. Therefore, the data environment is fluid.	Data cannot be changed. Data is added only periodically from historical systems. Once the data is properly stored, no changes are allowed. Therefore, the data environment is relatively static.

‹#›

The Data Warehouse (3 of 3)

‹#›

Data Marts

Small, single-subject data warehouse subset

Provides decision support to a small group of people

Benefits over data warehouses

Lower cost and shorter implementation time

Technologically advanced

Inevitable “people issues”

‹#›

Twelve Rules That Define a Data Warehouse

Table 13.9	Twelve Rules for a Data Warehouse
Rule No.	Description
1	The data warehouse and operational environments are separated.
2	The data warehouse data is integrated.
3	The data warehouse contains historical data over a long time.
4	The data warehouse data is snapshot data captured at a given point in time.
5	The data warehouse data is subject oriented.
6	The data warehouse data is mainly read-only with periodic batch updates from operational data. No online updates are allowed.
7	The data warehouse development life cycle differs from classical systems development. Data warehouse development is data-driven; the classical approach is process-driven.
8	The data warehouse contains data with several levels of detail: current detail data, old detail data, lightly summarized data, and highly summarized data.
9	The data warehouse environment is characterized by read-only transactions to very large data sets. The operational environment is characterized by numerous update transactions to a few data entities at a time.
10	The data warehouse environment has a system that traces data sources, transformations, and storage.
11	The data warehouse’s metadata is a critical component of this environment. The metadata identifies and defines all data elements. The metadata provides the source, transformation, integration, storage, usage, relationships, and history of each data element.
12	The data warehouse contains a chargeback mechanism for resource usage that enforces optimal use of the data by end users.

‹#›

Star Schemas (1 of 5)

Data-modeling technique

Maps multidimensional decision support data into a relational database

Creates the near equivalent of multidimensional database schema from existing relational database

Yields an easily implemented model for multidimensional data analysis

‹#›

Star Schemas (2 of 5)

Basic star schema components

Facts: numeric values that represent a specific business aspect

Dimensions: qualifying characteristics that provide additional perspectives to a given fact

Attributes: used to search, filter, and classify facts

Slice and dice: ability to focus on slices of the data cube for more detailed analysis

Attribute hierarchies: provide a top-down data organization

Aggregation and drill-down/roll-up data analysis

‹#›

Star Schemas (3 of 5)

Star schema representation

Facts and dimensions represented by physical tables in data warehouse database

Many-to-one (M:1) relationship between fact table and each dimension table

Fact and dimension tables

Related by foreign keys

Subject to primary and foreign key constraints

Primary key of a fact table

Composite primary key because the fact table is related to many dimension tables

Always formed by combining the foreign keys pointing to the related dimension tables

‹#›

Star Schemas (4 of 5)

‹#›

Star Schemas (5 of 5)

Performance-improving techniques for the star schema

Normalizing dimensional tables

Snowflake schema: dimension tables can have their own dimension tables

Maintaining multiple fact tables to represent different aggregation levels

Save processor cycles at run time, thereby speeding up data analysis

Denormalizing fact tables

Improves data access performance and saves data storage space

Partitioning and replicating tables

Partitioning: splits tables into subsets of rows or columns and places them close to the client computer

Replication: makes copy of table and places it in a different location

Periodicity: provides information about the time span of the data stored in the table

‹#›

Online Analytical Processing (OLAP)

Online analytical processing (OLAP) is a BI style whose systems share three main characteristics

Multidimensional data analysis techniques

Advanced database support

Easy-to-use end-user interfaces

‹#›

Multidimensional Data Analysis Techniques

Data are processed and viewed as part of a multidimensional structure

Particularly attractive to business decision makers who tend to view business data as being related to other business data

Augmented advanced functions

Data presentation

Data aggregation, consolidation, and classification

Computational

Data-modeling

‹#›

Advanced Database Support

OLAP tools must have the following features to deliver efficient decision support:

Access to many different kinds of DBMSs, flat files, and internal and external data sources

Access to aggregated data warehouse data and operational database detail data

Advanced data navigation features

Rapid and consistent query response times

Ability to map end-user requests

Support for very large databases

‹#›

Easy-to-Use End-User Interface

When proper implementation leads to simple navigation and accelerated decision making or data analysis

Advanced OLAP features are more useful when access is kept simple

Many interface features are borrowed from previous generations of data analysis tools

‹#›

OLAP Architecture (1 of 2)

Designed to meet ease-of-use requirements while keeping the system flexible

Main architectural components

Graphical user interface (GUI)

Analytical processing logic

Data-processing logic

‹#›

OLAP Architecture (2 of 2)

‹#›

Relational OLAP

Relational online analytical processing (ROLAP)

Provides OLAP functionality using relational databases and familiar relational tools to store and analyze multidimensional data

Extensions added to traditional RDBMS technology

Multidimensional data schema support within the RDBMS

Data access language and query performance optimized for multidimensional data

Support for very large databases (VLDBs)

‹#›

Multidimensional OLAP

Multidimensional online analytical processing (MOLAP)

Extends OLAP functionality to multidimensional database management systems (MDBMSs)

MDBMS uses proprietary techniques store data in matrix-like n-dimensional arrays

End users visualize stored data as a three dimensional data cube

Grow to n number dimensions, thus becoming hypercubes

Held in memory in a cube cache to speed access

Sparsity: measures density of data held in the data cube

‹#›

Relational versus Multidimensional OLAP

Table 13.12 Relational vs. Multidimensional OLAP
Characteristic	ROLAP	MOLAP
Schema	Uses star schema Additional dimensions can be added dynamically	Uses data cubes Multidimensional arrays, row stores, column stores Additional dimensions require re-creation of the data cube
Database size	Medium to large	Large
Architecture	Client/server Standards-based	Client/server Open or proprietary, depending on vendor
Access	Supports ad hoc requests Unlimited dimensions	Limited to predefined dimensions Proprietary access languages
Speed	Good with small data sets; average for medium-sized to large data sets	Faster for large data sets with predefined dimensions

‹#›

Data Analytics (1 of 4)

Subset of business intelligence (BI) functionality that encompasses a wide range of mathematical, statistical, and modeling techniques with the purpose of extracting knowledge from data

Explanatory analytics: focuses on discovering and explaining data characteristics and relationships based on existing data

Predictive analytics: focuses on predicting future data outcomes with a high degree of accuracy

Data mining focuses on the discovery and explanation stages of knowledge acquisition

Analyzing massive amounts of data to uncover hidden trends, patterns, and relationships; to form computer models to simulate and explain the findings; and to use such models to support business decision making

‹#›

Data Analytics (2 of 4)

‹#›

Data Analytics (3 of 4)

‹#›

Data Analytics (4 of 4)

Predictive analytics focuses on creating actionable models to predict future behaviors and events

Employs mathematical and statistical algorithms, neural networks, artificial intelligence, and other advanced modeling tools to create actionable predictive models based on available data

Used in areas such as customer relationships, customer service, customer retention, fraud detection, targeted marketing, and optimized pricing

‹#›

SQL Analytic Functions (1 of 2)

The ROLLUP extension

Used with GROUP BY clause to generate aggregates by different dimensions

Enables subtotal for each column listed except for the last one, which gets a grand total

The CUBE extension

Used with GROUP BY clause to generate aggregates by the listed columns

Enables you to get a subtotal for each column listed in the expression, in addition to a grand total for the last column listed

‹#›

SQL Analytic Functions (2 of 2)

Materialized views

Dynamic table that contains SQL query command to generate rows and stores the actual rows

Created the first time query is run and summary rows are stored in the table

Automatically updated when base tables are updated

Requires specified privileges

‹#›

Data Visualization (1 of 2)

Process of abstracting data to provide a visual data representation that enhances the user’s ability to comprehend the meaning of the data

Goal is to allow the user to quickly and efficiently see the data’s big picture by identifying trends, patterns, and relationships

The need for data visualization

Ability to zoom in and out, drill down and up, filter, etc. is one of the many advantages of current breed of data visualization tools

Makes it easier to understand data—in particular, large amounts of data

The science of data visualization

Roots in cognitive sciences: how the human brain receives, interprets, organizes, and processes information

Pattern recognition

Spatial awareness

Aesthetics

‹#›

Data Visualization (2 of 2)

Understanding the data

The same data can be presented in multiple ways

In general, there are two types of data:

Qualitative: describes qualities of the data

Nominal or ordinal

Quantitative: describes numeric facts or measures of the data

‹#›

Summary (1 of 2)

Business intelligence (BI) is a term for a comprehensive, cohesive, and integrated set of applications used to capture, collect, integrate, store, and analyze data with the purpose of generating and presenting information to support business decision making

Decision support systems (DSSs) refer to an arrangement of computerized tools used to assist managerial decision making within a business

Operational data is not well suited for decision support

The data warehouse is an integrated, subject-oriented, time-variant, nonvolatile collection of data that provides support for decision making

The star schema is a data-modeling technique used to map multidimensional decision support data into a relational database for advanced data analysis

‹#›

Summary (2 of 2)

Online analytical processing (OLAP) refers to an advanced data analysis environment that supports decision making, business modeling, and operations research

Data analytics is a subset of BI functionality that provides advanced data analysis tools to extract knowledge from business data

Data mining automates the analysis of operational data to find previously unknown data characteristics, relationships, dependencies, and trends

SQL has been enhanced with analytic functions that support OLAP-type processing and data generation

Data visualization provides visual representations of data that enhance the user’s ability to comprehend the meaning of the data

‹#›