Chapter 5 Business Intelligence

makeshiftklipInternet and Web Development

Oct 31, 2013 (3 years and 5 months ago)

57 views

Chapter 5

Business Intelligence

Databases and Information
Management

Database Approach


Database


Paper Form


Phonebooks


Index cards


Paper files


Database


Digital Form


Automation


Table Relationships


Reporting

Small Organizational File Method

Customer
Files

Inventory
Files

Orders

Files

Product

Files

Customer

History

Reorder

Files

Table Relationships


One
-
to
-
One


One
record & only
One
corresponds to exactly one
record



One
-
to
-
Many


Each record in Table A may have
many linked records in Table B,
However, may have only one
corresponding record in Table A



Many
-
to
-
Many


Cardinal Sin


Database Mangement System


Only supports one
-
to
-
many
relationships

M2M

One : One

One to Many

Order Processing


Digital Form

Web

Server

Application
Server

Sales
Database

Sandra Crain

Sandra

Crain’s

ORDER
INPUT


PROGRAM

TRANSLATION

HTML TO SQL

Database

Load

Into Tables

Order
Database

Received

Database

Server

RECIEVES

AND SENDS

I.E. RECEIPT

Dedicated Computer

Customer
Reports

Madison Flagship Embossed Buff Croc Caroline Dowel

$798.00

Product Number: 18661

Elegant crocodile embossing finished with a soft metallic
wash


a limited edition classic with exquisite detailing

Madison Gathered Leather Abigail Shoulder Bag

$898.00

Product Number: 18603

A graceful shape and spacious interior make this new shoulder
silhouette a great everyday bag with luxuries leather

Madison OP Art Metallic Outline Hailey

$398.00

Product Number: 18241

Day
-
to
-
Evening elegance meets luxurious practicality on a new
crossbody in luminous OP Art fabric with a satin finish

Select

Select

Select

First Name:

Last Name
:


Address:

Username
:


City:

Password
:


State:

Age
:


Zip:

Gender


Email
:


Phone:

Credit Card

Type:

Credit

Card Number:

Credit Card Expiration Date:

Crain

F

Midland

Texas

79701

HusbandMoney@g.com

432
-
514
-
7982

Visa Gold Card

5866
-
4400
-
3900
-
1573

Mar 07, 2013

BigSpender

●●●●●●●

40

Sandra


Submit

124 Money Street

Madison Flagship Embossed Buff Croc Caroline Dowel

$798.00

Product Number: 18661

Elegant crocodile embossing finished with a soft metallic wash


a limited edition classic with exquisite detailing

First Name:

Last Name
:


Address:

Username
:


City:

Password
:


State:

Age
:


Zip:

Gender


Email
:


Phone:

Credit Card

Type:

Credit

Card Number:

Credit Card Expiration Date:

C_L_Name

C_Gender

C_City

C_State

C_Zip

C_Email

C_Phone

Credit_Card_T

Credit_Card_N

Credit_Card_E

C_Username

C_Passwd

C_Age

C_F_Name

C_Address_1

Prod_ID = 18661

Description = Madison Flagship Embossed Buff Croc Caroline
Dowel

W_Price

= $414.96

R_Price

= $798.00


Customer Spreadsheet View


Relational Database


Groups of data are organized


Has a relationship to other files


Tables are constructed of both
Rows & Columns


Customer_ID

C_F_NAME

C_L_NAME

C_ADDRESS_1

C_CITY

C_STATE

C_ZIP

C_EMAIL

25361

Sandra

Crain

124 Money St

Midland

TX

79701

HusbandMoney@g.com

23411

Amber

Garlock

254 Easy St

Houston

TX

57931

BigBucks@h.com

61234

Lacey

Harris

875 Diamonds LN

Dallas

TX

47983

RichGirl@b.com

Customer Information

ENTITY

Attributes

Represents:

Person, Place, or Thing

Represents:

Attributes describes the
Entity

Two
-
Dimensional Tables

Columns

Rows

Also Known as:

Records or Tuples

Represents:

A Intersection of a Row
and Column

Fields

Primary Key

Represents:

UNIQUE INDENTIFIER

identify a row in a Table

Product Database Spreadsheet

Prod_ID

Supplier_ID

Description

W_Price

R_Price

18661

20010

Madison

Flagship Embossed

$414.96

$798.00

18603

20010

Madison Gathered Leather

$466.96

$898.00

18241

20010

Madison OP Art Metallic Outline Hailey

$206.96

$398.00

PRODUCTS

Represents:

UNIQUE INDENTIFIER

Identify a Row in a Table

Primary Key

Foreign Key

Represents:

Identifies two or more
columns

Customer Information

Customer_ID

C_Frist_Name

C_Last_Name

C_Address_1

C_City

C_State

C_Zip

Process

Prod_ID

Supplier_ID

Description

W_Price

R_Price

18661

20010

Madison

Flagship Embossed

$414.96

$798.00

18603

20010

Madison Gathered Leather

$466.96

$898.00

18241

20010

Madison OP Art Metallic Outline Hailey

$206.96

$398.00

Order_
ID

Order_Date

Net_Amt

Tax

Total_Amt

4001

10/17/11

$798.00

8.5

$865.83

2525

09/14/11

$898.00

8.5

$974.33

1022

05/16/11

$398.00

8.5

$431.83

Order_ID

Prod_ID

Prod_Quan

4001

18661

1

2525

18603

1

1022

18241

1

Supplier_ID

S_NAME

S_Street

S_CITY

S_State

S_Zip

20010

Coach

12 Coach ST

New York

NY

66359

20111

C.
Louboutin

2020 1
st

AVE

New York

NY

66885

34012

Gucci

197 Wall

St.

Manhattan

NY

45782

PRODUCT

LINE_ITEM

ORDER

SUPPLIER

Primary Key

Foreign Key

Primary Key

Primary Key

Processing

Customer
Information

Customer_ID

C_Frist_Name

C_Last_Name

C_Address_1

C_City

C_State

C_Zip

C_Email

C_Phone

Credit_Card_T

Credit_Card_N

Credit_Card_E

C_UserName

C_Passwd

C_Age

C_Gender

ORDER

Order_ID

Customer_ID

Order_Date

Net_Amount

Tax

Total_Amount

Inventory

Prod_ID

Quan_in_Stock

Sales

Order Lines

Item_Line

Order_ID

Prod_ID

Description

Quantity

Unit_Price

1
-
1

M
-
1

1
-
M

Process

Customer

Customer_ID

C_Frist_Name

C_Last
_Name

C_Address_1

Order_Lines

Item_Number

Order_ID

Prod_ID

Description

Quantity

Unit_Price

ORDER

Order_ID

Customer_ID

Order_Date

Net_Amount

Tax

Total_Amount

SELECT
CUSTOMER.C_First_Name
,
C_Last_Name
,
ORDER.Order.Date
,
ORDER.Total_Amount
, ORDER_
LINES.Description


FROM CUSTOMER, ORDER, ORDER_LINES


WHERE
CUSTOMER.C_F_Name

= Sandra,
CUSTOMER.C_L_Name

= Crain; AND
ORDER.Total_Amoumt

> 100;

ORDER BY ORDER_DATE DESC



RUN

Order_Date

C_First_Name

C_Last_Name

Total_Amount

Description

02/12/2013

Sandra

Crain

$865.00

Madison

Flagship Embossed

03/06/2013

Sandra

Crain

$974.33

Madison Gathered Leather

03/01/2013

Sandra

Crain

$431.83

Madison OP Art Metallic Outline Hailey

Object
-
Oriented Databases


Pictures


Images


Video


Programs

/*

Java Hello World example.

*/

public class
HelloWorldExample
{

public static void main(String
args
[]){

/*

Use
System.out.println
() to print on console.

*/

System.out.println
("Hello World !");

}

}

/*

OUTPUT of the above given Java Hello World
Example would be :

Hello World !

*/

Data Warehouse

Data Warehouse


What it enables


Stores


Consolidates


Standardize


Better Decisions


We can apply


Analytical tools


Graphical Reporting


What we cannot do


Alter information

The Data Warehouse

Extract

Transform

* Cleanse

* Load

Data

Ware
-
house

Information

Directory

Data
Access &
Analysis

Operational
Data

Customer

Data

Manufacturing

Data

Historical

Data

External

Data

Internal Data Sources

Transactional Databases

External Data Sources

Extract

Packaged Applications

Legacy Systems

Programs



OLAP


Data Mining


Queries/Reporting

Transform

Uses Rules to combine Data
with other Data


Cleanse/Scrubbing

Correcting Data

Ensure Consistency


Load

The process of writing Data
into Target Database

Data

Mart

Data Mart


Decentralized
warehouses


Lower Cost


Batch processes


Corporate Office

East Region

West Region

Online Analytical Processing (OLAP)


What is OLAP


A interactive navigational tool
to assess data, and perform
calculations of the fly


Multi
-
Dimensional analytical
Queries


Considered a Business
Intelligence tool


View historical data using time
-
stamps


Aggregate data to the smallest
granular


Enables Real
-
Time Reporting


Executive Support Systems

ESS


Creates Multiple Views of
Data in Relational Databases


Top Software Providers

Microsoft Corporation

Hyperion Solutions Corporation

Cognos

Business Objects

MicroStrategy

SAP AG

Cartesis SA

Applix

Infor

Oracle Corporation

Data Mining


Uncover Patterns


To predict future
behavior


Associations (Sam’s)


If you buy one item, we
can predict other
purchase


Sequences (Percentage)


Classification (Dillard’s)


Dividing customers into
groups


Pink/Red Party


Clustering


The discovery of groups


demographics

A Review of OLAP

The End