Business Intelligence and Data Warehouses

Business Intelligence and Data Warehouses

Objectives

In this chapter, you will learn: • How business intelligence provides a

comprehensive business decision support framework

• About business intelligence architecture, its evolution, and reporting styles
• About the relationship and differences between operational data and decision support data
• What a data warehouse is and how to prepare data for one

Database Systems, 10th Edition 2

Objectives (cont’d.)

• What star schemas are and how they are constructed
• About data analytics, data mining, and predictive analytics
• About online analytical processing (OLAP) • How SQL extensions are used to support

OLAP-type data manipulations

Database Systems, 10th Edition 3

The Need for Data Analysis

• Managers track daily transactions to evaluate how the business is performing
• Strategies should be developed to meet organizational goals using operational databases
• Data analysis provides information about short- term tactical evaluations and strategies

Database Systems, 10th Edition 4

Business Intelligence

• Comprehensive, cohesive, integrated tools and processes – Capture, collect, integrate, store, and analyze

data

– Generate information to support business decision making

• Framework that allows a business to transform: – Data into information

– Information into knowledge

– Knowledge into wisdom Database Systems, 10th Edition 5

Business Intelligence Architecture

• Composed of data, people, processes, technology, and management of components
• Focuses on strategic and tactical use of information
• Key performance indicators (KPI) – Measurements that assess company’s

effectiveness or success in reaching goals

• Multiple tools from different vendors can be integrated into a single BI framework

Database Systems, 10th Edition 6

Database Systems, 10th Edition 7

Business Intelligence Benefits

• Main goal: improved decision making • Other benefits

– Integrating architecture

– Common user interface for data reporting and analysis

– Common data repository fosters single version of company data

– Improved organizational performance

Database Systems, 10th Edition 8

Business Intelligence Evolution

Database Systems, 10th Edition 9

Database Systems, 10th Edition 10

Business Intelligence Technology Trends

• Data storage improvements • Business intelligence appliances • Business intelligence as a service • Big Data analytics • Personal analytics

Database Systems, 10th Edition 11

Decision Support Data

• BI effectiveness depends on quality of data gathered at operational level
• Operational data seldom well-suited for decision support tasks
• Need reformat data in order to be useful for business intelligence

Database Systems, 10th Edition 12

Operational Data vs. Decision Support Data

• Operational data – Mostly stored in relational database – Optimized to support transactions representing

daily operations

• Decision support data differs from operational data in three main areas: – Time span

– Granularity

– Dimensionality

Database Systems, 10th Edition 13

Database Systems, 10th Edition 14

Decision Support Database Requirements

• Specialized DBMS tailored to provide fast answers to complex queries
• Three main requirements – Database schema

– Data extraction and loading

– Database size

Database Systems, 10th Edition 15

Decision Support Database Requirements (cont’d.)

• Database schema – Complex data representations – Aggregated and summarized data – Queries extract multidimensional time slices
• Data extraction and filtering – Supports different data sources
• Flat files • Hierarchical, network, and relational databases • Multiple vendors

– Checking for inconsistent data Database Systems, 10th Edition 16

Decision Support Database Requirements (cont’d.)

• Database size – In 2005, Wal-Mart had 260 terabytes of data in

its data warehouses

– DBMS must support very large databases (VLDBs)

Database Systems, 10th Edition 17

The Data Warehouse

• Integrated, subject-oriented, time-variant, and nonvolatile collection of data – Provides support for decision making
• Usually a read-only database optimized for data analysis and query processing
• Requires time, money, and considerable managerial effort to create

Database Systems, 10th Edition 18

Database Systems, 10th Edition 19

Data Marts

• Small, single-subject data warehouse subset • More manageable data set than data

warehouse • Provides decision support to small group of

people • Typically lower cost and lower implementation

time than data warehouse

Database Systems, 10th Edition 20

Twelve Rules That Define a Data Warehouse

Database Systems, 10th Edition 21

Star Schemas

• Data-modeling technique – Maps multidimensional decision support data

into relational database

• Creates near equivalent of multidimensional database schema from relational data
• Easily implemented model for multidimensional data analysis while preserving relational structures
• Four components: facts, dimensions, attributes, and attribute hierarchies

Database Systems, 10th Edition 22

Facts

• Numeric measurements that represent specific business aspect or activity – Normally stored in fact table that is center of star

schema

• Fact table contains facts linked through their dimensions
• Metrics are facts computed at run time

Database Systems, 10th Edition 23

Dimensions

• Qualifying characteristics provide additional perspectives to a given fact
• Decision support data almost always viewed in relation to other data
• Study facts via dimensions • Dimensions stored in dimension tables

Database Systems, 10th Edition 24

Attributes

• Use to search, filter, and classify facts • Dimensions provide descriptions of facts

through their attributes • No mathematical limit to the number of

dimensions • Slice and dice: focus on slices of the data cube

for more detailed analysis

Database Systems, 10th Edition 25

Attribute Hierarchies

• Provide top-down data organization • Two purposes:

– Aggregation

– Drill-down/roll-up data analysis

• Determine how the data are extracted and represented
• Stored in the DBMS’s data dictionary • Used by OLAP tool to access warehouse

properly

Database Systems, 10th Edition 26

Star Schema Representation

• Facts and dimensions represe

 

nted in physical tables in data warehouse database

• Many fact rows related to each dimension row – Primary key of fact table is a composite primary

key

– Fact table primary key formed by combining foreign keys pointing to dimension tables

• Dimension tables are smaller than fact tables • Each dimension record is related to thousands

of fact records Database Systems, 10th Edition 27

Performance-Improving Techniques for the Star Schema

• Four techniques to optimize data warehouse design: – Normalizing dimensional tables

– Maintaining multiple fact tables to represent different aggregation levels

– Denormalizing fact tables

– Partitioning and replicating tables

Database Systems, 10th Edition 28

Performance-Improving Techniques for the Star Schema (cont’d.)

• Dimension tables normalized to: – Achieve semantic simplicity – Facilitate end-user navigation through the

dimensions

• Denormalizing fact tables improves data access performance and saves data storage space
• Partitioning splits table into subsets of rows or columns
• Replication makes copy of table and places it in different location

Database Systems, 10th Edition 29

Data Analytics

• Subset of BI functionality • Encompasses a wide range of mathematical,

statistical, and modeling techniques – Purpose of extracting knowledge from data

• Tools can be grouped into two separate areas: – Explanatory analytics

– Predictive analytics

Database Systems, 10th Edition 30

Data Mining

• Data-mining tools do the following: – Analyze data – Uncover problems or opportunities hidden in

data relationships

– Form computer models based on their findings – Use models to predict business behavior

• Runs in two modes – Guided

– Automated

Database Systems, 10th Edition 31

Database Systems, 10th Edition 32

Predictive Analytics

• Employs mathematical and statistical algorithms, neural networks, artificial intelligence, and other advanced modeling tools
• Create actionable predictive models based on available data
• Models are used in areas such as: – Customer relationships, customer service,

customer retention, fraud detection, targeted marketing, and optimized pricing

Database Systems, 10th Edition 33

Online Analytical Processing

• Three main characteristics: – Multidimensional data analysis techniques – Advanced database support

– Easy-to-use end-user interfaces

Database Systems, 10th Edition 34

Multidimensional Data Analysis Techniques

• Data are processed and viewed as part of a multidimensional structure
• Augmented by the following functions: – Advanced data presentation functions

– Advanced data aggregation, consolidation, and classification functions

– Advanced computational functions

– Advanced data modeling functions

Database Systems, 10th Edition 35

Advanced Database Support

• Advanced data access features include: – Access to many different kinds of DBMSs, flat

files, and internal and external data sources

– Access to aggregated data warehouse data

– Advanced data navigation – Rapid and consistent query response times

– Maps end-user requests to appropriate data source and to proper data access language

– Support for very large databases

Database Systems, 10th Edition 36

Easy-to-Use End-User Interface

• Advanced OLAP features are more useful when access is simple
• Many interface features are “borrowed” from previous generations of data analysis tools – Already familiar to end users

– Makes OLAP easily accepted and readily used

Database Systems, 10th Edition 37

OLAP Architecture

• Three main architectural components: – Graphical user interface (GUI) – Analytical processing logic

– Data-processing logic

Database Systems, 10th Edition 38

OLAP Architecture (cont’d.)

• Designed to use both operational and data warehouse data
• In most implementations, data warehouse and OLAP are interrelated and complementary
• OLAP systems merge data warehouse and data mart approaches

Database Systems, 10th Edition 39

Database Systems, 10th Edition 40

Relational OLAP

• Relational online analytical processing (ROLAP) provides the following extensions: – Multidimensional data schema support within the

RDBMS

– Data access language and query performance optimized for multidimensional data

– Support for very large databases (VLDBs)

Database Systems, 10th Edition 41

Multidimensional OLAP

• Multidimensional online analytical processing (MOLAP) extends OLAP functionality to multidimensional database management systems (MDBMSs) – MDBMS end users visualize stored data as a 3D

data cube

– Data cubes can grow to n dimensions, becoming hypercubes

– To speed access, data cubes are held in memory in a cube cache

Database Systems, 10th Edition 42

Relational vs. Multidimensional OLAP

• Selection of one or the other depends on evaluator’s vantage point
• Proper evaluation must include supported hardware, compatibility with DBMS, etc.
• ROLAP and MOLAP vendors working toward integration within unified framework
• Relational databases use star schema design to handle multidimensional data

Database Systems, 10th Edition 43

Database Systems, 10th Edition 44

SQL Extensions for OLAP

• Proliferation of OLAP tools fostered development of SQL extensions
• Many innovations have become part of standard SQL
• All SQL commands will work in data warehouse as expected
• Most queries include many data groupings and aggregations over multiple columns

Database Systems, 10th Edition 45

The ROLLUP Extension

• Used with GROUP BY clause to generate aggregates by different dimensions
• GROUP BY generates only one aggregate for each new value combination of attributes
• ROLLUP extension enables subtotal for each column listed except for the last one – Last column gets grand total
• Order of column list important

Database Systems, 10th Edition 46

The CUBE Extension

• CUBE extension used with GROUP BY clause to generate aggregates by listed columns – Includes the last column
• Enables subtotal for each column in addition to grand total for last column – Useful when you want to compute all possible

subtotals within groupings

• Cross-tabulations are good candidates for application of CUBE extension

Database Systems, 10th Edition 47

Materialized Views

• A dynamic table that contains SQL query command to generate rows – Also contains the actual rows
• Created the first time query is run and summary rows are stored in table
• Automatically updated when base tables are updated

Database Systems, 10th Edition 48

Summary

• Business intelligence generates information used to support decision making
• BI covers a range of technologies, applications, and functionalities
• Decision support systems were the precursor of current generation BI systems
• Operational data not suited for decision support

Database Systems, 10th Edition 49

Summary (cont’d.)

• Data warehouse provides support for decision making – Usually read-only

– Optimized for data analysis, query processing

• Star schema is a data-modeling technique – Maps multidimensional decision support data

into a relational database

• Star schema has four components: – Facts, dimensions, attributes, and attribute

hierarchies Database Systems, 10th Edition 50

Summary (cont’d.)

• Data analytics – 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

• Predictive analytics – Uses information generated in the data-mining

phase to create advanced predictive models

Database Systems, 10th Edition 51

Summary (cont’d.)

• Online analytical processing (OLAP) – Advanced data analysis environment that

supports decision making, business modeling, and operations research

• SQL has been enhanced with extensions that support OLAP-type processing and data generation

Database Systems, 10th Edition 52

• Database Systems: Design, Implementation, and Management Tenth Edition
• Objectives
• Objectives (cont’d.)
• The Need for Data Analysis
• Business Intelligence
• Business Intelligence Architecture
• PowerPoint Presentation
• Business Intelligence Benefits
• Business Intelligence Evolution
• Slide 10
• Business Intelligence Technology Trends
• Decision Support Data
• Operational Data vs. Decision Support Data
• Slide 14
• Decision Support Database Requirements
• Decision Support Database Requirements (cont’d.)
• Slide 17
• The Data Warehouse
• Slide 19
• Data Marts
• Twelve Rules That Define a Data Warehouse
• Star Schemas
• Facts
• Dimensions
• Attributes
• Attribute Hierarchies
• Star Schema Representation
• Performance-Improving Techniques for the Star Schema
• Performance-Improving Techniques for the Star Schema (cont’d.)
• Data Analytics
• Data Mining
• Slide 32
• Predictive Analytics
• Online Analytical Processing
• Multidimensional Data Analysis Techniques
• Advanced Database Support
• Easy-to-Use End-User Interface
• OLAP Architecture
• OLAP Architecture (cont’d.)
• Slide 40
• Relational OLAP
• Multidimensional OLAP
• Relational vs. Multidimensional OLAP
• Slide 44
• SQL Extensions for OLAP
• The ROLLUP Extension
• The CUBE Extension
• Materialized Views
• Summary
• Summary (cont’d.)
• Slide 51
• Slide 52

PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!