Appendix J - Business Intelligence Systems - Bond Wetherbe

levelsordΔιαχείριση Δεδομένων

20 Νοε 2013 (πριν από 3 χρόνια και 4 μήνες)

86 εμφανίσεις

Business
Intelligence Systems

Appendix J

DAVID M. KROENKE and DAVID J. AUER

DATABASE CONCEPTS,
6
th

Edition

All rights reserved. No part of this publication may be reproduced,
stored in a retrieval system, or transmitted, in any form or by any
means, electronic, mechanical, photocopying, recording, or otherwise,
without the prior written permission of the publisher. Printed in the
United States of America.

Copyright ©
2013
Pearson Education, Inc.


Publishing as Prentice Hall

Chapter Objectives


Learn the basic concepts of business
intelligence (BI) systems


Learn the basic concepts of reporting
systems and data mining



KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
3

Heather Sweeney Designs Review:

Database Design

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
4

Heather Sweeney Designs Review:

HSD Database Diagram in SQL Server 2012


KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
5

Business Intelligence Systems


Business intelligence (BI)
systems are information
systems that


Assist managers and other professionals in the analysis of
current and past activities and in the prediction of future
events.


Do not support operational activities, such as the recording
and processing of orders.


These are supported by transaction processing systems.


Support management assessment, analysis, planning and
control.


BI systems fall into two broad categories:


Reporting systems

that

sort, filter, group, and make
elementary calculations on operational data; and


Data mining applications

that perform sophisticated
analyses on data that usually involve complex statistical
and mathematical processing.

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
6

The Relationship Among
Operational and BI Applications

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
7

Figure J
-
1: Characteristic of Business Intelligence Systems

Characteristics of Business
Intelligence Applications Review

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
8

Characteristics of a

Data Warehouse

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
9

Figure J
-
2: Components of a Data Warehouse

Problems with Operational Data


“Dirty Data”


Example: “G” for Gender


Example: “213” for Age


Missing Values


Inconsistent Data


Example: data that has changed, such
as a customer’s phone number

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
10

Problems with Operational Data
(Continued)


Nonintegrated Data


Example: data from two or more
sources that need to be combined


Incorrect Format


Example: time data in hours when
needed in minutes


Too Much Data


Example: An excess number of
columns

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
11

ETL Data Transformation


Data may need to be transformed for
use in a data warehouse


Example:


{CountryCode


CountryName}


“US”


“United States”


Example:


Email address to Email domain


joe@somewhere.com



“somewhere.com”

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
12

Characteristics of a

Data Mart

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
13

Figure J
-
3: Data Warehouses and Data Marts

Enterprise Data Warehouse
(EDW) Architecture


Combines the data warehouse
structure and the data mart
structures shown above


Expensive to create, staff, and
operate


Smaller organizations use subsets of
the EDW architecture.

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
14

Dimensional Database Review


A non
-
normalized database structure
used for data warehouses


May use slowly changing dimensions


Values change infrequently


Phone Number


Address


Use a Date or Time dimension

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
15

Star Schema Review

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
16

HSD
-
DW Star Schema Review

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
17

Conformed Dimensions

and the Extended HSD
-
DW Schema

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
18

Figure J
-
4: Dimensional Databases and the Star Schema

The RFM Score Report

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
19

Figure J
-
5: The RFM Score Report

Reporting Systems

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
20

Figure J
-
6: Components of a Reporting System

Reporting Systems:

RFM Analysis


RFM Analysis

analyzes and ranks
customers according to purchasing
patterns:


R

= Recent (most recent order)


F

= Frequent (how often an order is made)


M

= Money (dollar amount of orders)


Customers are sorted into five groups,
each containing
20%

of the customers.


Each group is given a numerical value:


1

= Top 20%


2, 3, 4

= Each 20% in between top and
bottom 20%


5


= Bottom 20%

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
21

Reporting Systems:


Report Characteristics

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
22

Figure J
-
7: Report Characteristics

Reporting Systems:


Report System Functions


Report Authoring:


Connect to data sources


Create the report structure


Format the report


Report Management:


Defines who receives what reports
when and by what means


Report Delivery:


Push reports or allow them to be pulled

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
23

OLAP and Data Mining


OnLine Analytical Processing (OLAP)

is
a technique for dynamically examining
database data.


OLAP uses arithmetic functions such as Sum
and Average.


Discussed in Chapter 8


Data Mining

is a mathematically
sophisticated technique for analyzing
database data.


Data mining uses mathematical and statistical
techniques .

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
24

OLAP


OLAP systems produce an
OLAP
report
, also know as an
OLAP cube.


The OLAP report uses inputs called
dimensions.


The OLAP report calculates outputs
called
measures.


Excel PivotTables

can be used to
create OLAP reports.

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
25

Data Mining Applications:

The Convergence of the Disciplines

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
26

Figure J
-
8: Convergence of Disciplines for Data Mining

Data Mining Applications:

Popular Data Mining Techniques

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall


Cluster
analysis

Identifies
groups of
entities that have similar characteristics


Decision tree
analysis

Classifies
entities
into groups based on past history


Logistic
regression

Produces
equations
that offer probabilities that certain events
will occur


Neural
Networks

Complex
statistical
prediction techniques


Market Basket
Analysis

Determines
patterns of associated buying behavior

J
-
27

Data Mining Applications:

Cluster Analysis I

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
28

Figure J
-
9:

The Microsoft Excel 2010 with the Microsoft SQL Server 2012 Data Mining Add
-
ins

Data Mining Applications:

Cluster Analysis II

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
29

Figure J
-
10: The Cluster Analysis Results

Data Mining Applications:

Cluster Analysis III

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
30

Figure J
-
11: The Cluster Analysis Results (cont’d)

Data Mining Applications:

Market Basket Analysis

KROENKE and AUER
-

DATABASE CONCEPTS (6th Edition)
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall

J
-
31

Figure J
-
12: A Market Basket Analysis Example

Business Intelligence Systems

End of Presentation on Appendix J

DAVID M. KROENKE and DAVID J. AUER

DATABASE CONCEPTS,
6
th

Edition