IBM Business Intelligence

basesprocketData Management

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

113 views

IBM Software Group

®


Page
1

Carolina Yalamova

NDB Ltd.

IBM Business Intelligence


IBM Software Group | DB2 Information Management

Page
2

IBM Software Group | DB2 Information Management

Page
3

Anatomy of Business Intelligence Solutions


Transaction


Operational


Demographic


Lifestyle


Financial


Economic


Government


Associations

Data

Discovery


Customer segmentation


Demand & forecasting


Affinity merchandising


Product & customer
profitability


Fraud patterns & profiles

Business Actions


Customer retention
programs


Where to advertise


Optimize mailing list


Maximize store layout


Cross
-
product marketing


Improved risk profiles


Funds allocation

IBM Software Group | DB2 Information Management

Page
4

Source: IDC,The Financial Impact of Business Analytics study, Nov. 2002

% ROI Achieved

1
-
50

51
-
100

101
-
500

501
-
1000

1000+

30

25

20

15

10

5

%

% of
Clients
Achievin
g This
ROI

Average ROI = 431%

Why Should Customers Do BI projects Now?

IBM Software Group | DB2 Information Management

Page
5

End
-
User's productivity

Source: Gartner Group

IBM Software Group | DB2 Information Management

Page
6

Business Intelligence Architecture

Operational
Databases

Extract, transform,

merge, load

Line of Business

Data Marts

Enterprise

Data Warehouse

End User

Analytic Tools

Query &
Reporting

Data
Mining

Spatial

metadata

ETL

A single
version of the
truth

Slices of data
for departments

End user

Delivery &
analysis

Clean it up

Consolidate

Production

DTG

IBM Software Group | DB2 Information Management

Page
7

MOLAP, HOLAP, ROLAP

IBM Software Group

®


Page
8

IBM Business Intelligence



Software Overview


IBM Software Group | DB2 Information Management

Page
9

Information Integration

DB2

Administration

Data Mining

OLAP

ETL

Scalability

SQL XML PMML

The DB2 Framework for Business Intelligence

Build BI function into the database, as part of an integrated BI
platform, exposed solely through open, standard interfaces, and
collaborating with partners for other layers of the BI architecture

IBM Software Group | DB2 Information Management

Page
10

IBM Product
s

Mapping

Operational
Databases

Extract, transform,

merge, load

Line of Business

Data Marts

Enterprise

Data Warehouse

End User

Analytic Tools

Query &
Reporting

Data
Mining

Spatial

metadata

ETL

DB2 ESE

DB2 Query Patroller

DB2 Cube Views

DB2 DPF

DB2 OLAP Server

Intelligent Miner

Spatial Extenders

Office Connect

QMF

Alphablox

DB2 W
H Center

DB2 W
H Manager

DB2 II


Production

DTG

IBM Software Group

®


Page
11

IBM Business Intelligence


DB2

IBM Software Group | DB2 Information Management

Page
12

Fast Data Access
-

Multidimensional Clustering


Data is organized on disk based on dimensions


Eg. store data on disk by Region and Year



Queries skip portions of table that we know


don’t qualify


i.e. no need to scan 1997 data if query is asking for
2000



Data automatically clustered


Never need to recluster data (i.e. no REORG)



Customer Quote:


“The new multidimensional data clustering capability
has
improved performance of our most complex
queries by up to 30 times

while removing the need
for additional reorganization.”


Ken Ross, Senior Vice President and CTO, Thomson
West

IBM Software Group | DB2 Information Management

Page
13

Multi
-
Dimensional Clustering Advisor


The MDC advisor feature of the DB2 Design Advisor recommends MDC
clustering dimensions, including coarsifications on base columns in order to
improve workload performance


This includes potentially recommending generated columns that define coarsification of
dimensions.


Cost benefit analysis includes impact of MDC on Insert/Update/Delete activity
against dimensions (which may cause records to move cells).


Cost benefit analysis also models the potential negative effect of MDC caused
by table expansion.


The MDC advisor has a goal to select MDC solutions that result in a moderate
table expansion

Benefit: Competitive Advantage, Customer Requirement

IBM Software Group | DB2 Information Management

Page
14

Oracle Server

DB2 Server

Fast Data Access
-

Materialized Query Tables


Caches results for faster data access


Cache automatically maintained as base data is modified


Queries automatically rerouted to use MQTs that qualify



MQTs can cache data from other platforms


Including Oracle, Microsoft and other data sources

Table 1

Table 2

Table 3

Oracle

Table 1

MQT_1

MQT
2

IBM Software Group | DB2 Information Management

Page
15

Materialized Query Table Advisor


An advisor to recommend creation (and removal) of Materialized Query Tables
(formerly known as AST's in DB2) based on submitted SQL workload


Very difficult to choose the correct MQTs to create without the help of this wizard


Much more complex than deciding which indexes to create


Includes enhancements to the index wizard


allow indexes and MQTs to be recommended together (including recommendations for
indexes on MQTs).


"db2advis" command with added flags to allow index and MQT recommendations


This feature procedure will also be accessible using a GUI interface.



Benefit: Competitive Advantage, Customer Requirement

IBM Software Group

®


Page
16

IBM Business Intelligence



OLAP Server

IBM Software Group | DB2 Information Management

Page
17

DB2 OLAP Server



DB2

source data

Multi
-
dimensional Cubes

DB2

OLAP

Server

DB2

OIS

Partitionin
g option

High
concurrency
option

SQL interface

Essbase API

MS Excel macros

Currency conversions

Analyzer, Brio,
or

Windows Client

IBM Software Group | DB2 Information Management

Page
18

DB2 OLAP Server v8 Summary


Hybrid Analysis
--

cubes with performance of DB2 OLAP with
transparent link to highly scalable relational detail data


Integration Server


Parallelism

ƒ
Load, Calc, Export


OLAP Miner
--

end user tool to discover unknown trends and
opportunities


Enterprise Services for high availability

ƒ
load balancing,

ƒ
connection pooling,


ƒ
clustering

ƒ
failover capabilities


Administration Services

-
central management of OLAP Servers from
anywhere in the enterprise

IBM Software Group | DB2 Information Management

Page
19

DB2 OLAP Server
-

Benefits

Best in class OLAP


Easy to build summaries and hierarchies to match corporate structure &
goals


Navigate complex data dimensions fast to make complex business decisions


wide range of ISV support

Business Calculations


Rich library of functions makes it easy to define advanced & sophisticated
business logic and relationships

Hybrid analysis


Summaries end & detail data begins where YOU want it to


Match details & summaries to usage patterns for productivity


Drill down & through is seamless for end users

Distributed cubes


Create virtual linked cubes for unified view of the business


Provides load balancing & removes single point of failure

DTG

Benefit

IBM Software Group

®


Page
20

IBM Business Intelligence



DB2 Warehouse Center

/

DB2 Warehouse Manager

IBM Software Group | DB2 Information Management

Page
21

ETL
-

Data Warehouse Center / DB2 Warehouse Manager

IBM Software Group | DB2 Information Management

Page
22

DB2 Data Warehouse Manager and Center


DB2 Data Warehouse Center

ƒ
DBA administration console

ƒ
Access to most RDBMS's & flat files

ƒ
Schedules and monitors database tasks

ƒ
150+
prebuilt
data transformations

ƒ
P
arallel extracts & transforms


DB2 Warehouse Manager adds:

ƒ
Extracts & transformations on remote servers
via agents

ƒ
Information Catalog end user metadata

Information
Catalog

DB2 Warehouse Manager

DB2 Warehouse
Center

extract, transform, load,
schedule, administrate

I nt er net

Fi l e

Edi t

Vi ew

Hel
p

Tool
s

Back

Forwar
d

Stop

Home

Searc
h

Refresh

x

Hi stor
y

Mai l

Pri n
t

Addr ess:

ht t p://the_call_center/customer/lookup

07/02/2001 19:29:20

WPS v1.2
-

The Cutt ing Edge

home

1 cust
-
nbr

2 acct
-
code

3 first name

4 last name

5 street

6 city

7 zip

8 country

1 cust
-
number

2 acct_type

3 F
-
name

4 L
-
name

5 street

6 city

7 postal_code

8 country

DB2

3m

Metadata

IBM

ETL agents

IBM Software Group | DB2 Information Management

Page
23

DB2 Warehouse Manager
-

Benefits

DTG

Benefit

SQL/Java extracts &
transformations


Leverage existing programming skills
--

nothing proprietary


Standards based transforms are portable to applications


Reduced training costs now & in future

Parallel extracts &
transforms


High performance gets you through the processing "night"


Scalability to match your workload

ETL Server agents


Processing at the source spreads the workload, avoids
bottlenecks, and saves hardware costs


No licenses costs for agents on servers saves money

150+ transforms


Pre built transforms cover majority of tasks so you don't have to
write or maintain as much software
-

less code, less cost


Accelerates programmer productivity

IBM Software Group

®


Page
24

IBM Business Intelligence



Query Patroller


IBM Software Group | DB2 Information Management

Page
25

Query Patroller


Intercept query from the client


Governs & Throttles Queries

ƒ
Ask DB2 the cost in resources

ƒ
Queue SQL based on user's priority

ƒ
Release SQL to run


Graphical tools to Administer and
Report

ƒ
Charge Back & Trend Statistics


Flexible result set options

ƒ
Queue handling of reports returned


Interoperates with end user tools

Data Warehouse

answers

explains

SQL 14

SQL 15

SQL 21

SQL 23

Query

Patroller

DB2

Optimizer

answer sets

queued

queues

IBM Software Group | DB2 Information Management

Page
26

Query Patroller Benefits

Benefit

Manages SQL traffic
into DB2


Ensure smooth use of RDBMS resources, maximizing throughput,
and making best use of server; prevents unnecessary server
upgrades

SQL query result set
"caching"


let's users submit queries and pick up results later without locking
up the workstation


answer sets can be shared between users reducing need to run
the query once per user

Historical analysis
reports


analyze trends to forecast when upgrades may be needed


analyze patterns to detect problems and minimiz

Prioritize user
queries


ensure time critical business tasks get done


minimize performance impact of long running queries, ensuring
uses with short, quick reports get through

IBM Software Group

®


Page
27

IBM Business Intelligence


Office Connect


IBM Software Group | DB2 Information Management

Page
28

Office Connect

repository

Application

Databases

HTML
sources

JDBC/
HTTP

Web Edition

Design time

ODBC

JDBC

HTTP

Client
Browser

Servlet Engine

Tomcat

WebSphere

Office Connect
Servlet

Tomcat

Apache

HTTP
Server

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Profit

Actual

Cola

2

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Tot

3

New York

262

245

259

276

295

357

370

368

312

247

236

271

3498

4

Florida

367

340

366

397

434

547

564

563

465

348

324

390

5105

5

Virginia

68

63

67

73

78

99

104

104

86

64

63

73

942

demo pg6

Microsoft Excel
-

Sales forecast.txt

B

100%

10

F
ile
E
dit
V
iew
I
nsert
F
ormat
T
ools
D
ata
W
indow
E
ssbase
H
elp

ABC

A

Z

Arial

I

IBM Software Group | DB2 Information Management

Page
29

Office Connect
-

Benefits

Uses Microsoft Excel as
Query Tool


query tool already installed on 95% of desktops so no
additional user fees for tools


user is already skilled in use of XL so no training costs


Basic reporting capability easy to learn and share with
others


usable by professionals who don't have a lot of
computer skills

Web Based Server


can scale up to handle 1000s of users with minimal
administration costs

Repository for
Spreadsheets


lets users share query spreadsheets over the intranet
or internet

DTG

Benefit

IBM Software Group

®


Page
30

IBM Business Intelligence



DB2 Cube Views

IBM Software Group | DB2 Information Management

Page
31

DB2 Cube Views = an OLAP Accelerator

DB
2

DB2 Cube Views

Applications

Query &
Reporting
Tools

ROLAP

Servers

DOLAP

Servers

MOLAP Engines

Net present value, allocations,

write back, etc.

Business Functions &
Operators

Applications

BI Tools

Business
Functions
&
Operators

Business
Functions
&
Operators

OLAP servers

DTG

IBM Software Group | DB2 Information Management

Page
32

What Are DB2 Cube Views?


Wizard to design & generate cube views

ƒ
Wizard for cube definition, metadata modeling, and optimization





Multi
-
dimensional metadata inside DB2

ƒ
Import/export utilities for metadata exchange
a objects, relations

ƒ
described in XML for ease of use

ƒ
Used to generate the Materialized Query Table(s)



Application Programming interface

ƒ
SQL & XML based interfaces from DB2 to tools & applications

ƒ
CLI, ODBC, JDBC, stored procedures

ƒ
OLAP Web services

MQT

IBM Software Group | DB2 Information Management

Page
33

Cube Models & Cubes

IBM Software Group | DB2 Information Management

Page
34

Bridges

1



XML

DB2 Stored
Procedure

DB2

Hyperion

B
USINESS

O
BJECTS

1



XML


Simple interface

ƒ
Stored Procedure and XML document


Bridge into and out of DB2

ƒ
Enables end to end flow of dimensional metadata

Cube Views

IBM Software Group | DB2 Information Management

Page
35

materialized query tables

rows & columns

Query Results

Data loading

Hybrid
Analysis

Cube Build

Drill through

DB2 OLAP
Server


MQT's for

ƒ
Faster cube load

ƒ
Faster drill through reports

ƒ
Faster hybrid analysis

ƒ
Faster Ad
-
hoc analysis

ƒ
Faster reporting

Aggregates are required for OLAP

IBM Software Group | DB2 Information Management

Page
36

Performance Advisor

.


Aurora Model

OLAP Metadata

Base Tables

Administrator

Catalog Tables

Time & Space constraints

Optimization hints

Model Information

Data Samples



Statistics

Query history

MQT's

IBM Software Group

®


Page
37

IBM Business Intelligence



Data Mining

IBM Software Group | DB2 Information Management

Page
38

What is Data Mining?

Data Mining summarises a set of methods to analyse
very large data sets,which discover, from the
innumerable amounts of detail data, new and unknown
structures and patterns then present them such that
they efficiently support the decision process to provide
business advantage.

or... finding out useful things in your data that you
didn't know!

"The
secret

to business is

knowing

something that someone else doesn't."


Aristotle Onassis

IBM Software Group | DB2 Information Management

Page
39



DB2 Intelligent Miner Modeling



DB2 Intelligent Miner Scoring



DB2 Intelligent Miner Visualization




DB2 OLAP Miner

IBM Data Mining Products

IBM Software Group | DB2 Information Management

Page
40

Data Mining Techniques


Clustering
(Demographic and Neural)

?
What logical groups exist?


Associations

?
What are the linkages among events?




Classification

?
Can I determine which category an event will fit?

?
What are the major factors that influence the
occurence of the event?


Regression

?
How much?

Prediction

Discovery

IBM Software Group | DB2 Information Management

Page
41

IM Modeling

IM Visualization

IM Scoring

Model applied
real time in
applications

Analyst defines
model and
runs the
analysis …

… using
data from
the data
warehouse

XML

Model can be

viewed
standalone or
over the web

XML

DB2 OLTP,
DW or
ODS

Data Mining Functions in
DWE

IBM Software Group | DB2 Information Management

Page
42

Data Mining with IM DB2 Functions


Preprocess your data

?
With any SQL statement, Warehouse transformers,
create VIEWs


Use
IM Modeling

SQL types and methods

?
Define mining parameters


Call
IM Modeling

stored procedure

?
Do the mining run


Call
IM Visualization

?
Visualize and analyse the resulting mining model


Use
IM Scoring

function in SQL or Java bean

?
Apply the model to new data

IBM Software Group

®


Page
43

IBM Business Intelligence



DB2 Data Warehouse Edition


IBM Software Group | DB2 Information Management

Page
44

What is DB2 Data Warehouse Edition?


A strategy for ensuring more BI function is easily available to
more customers

ƒ
Simplify: Make it easy to use, easy to buy, easy to understand


A collection of engines and tools to build data warehouses and
analytic applications:

ƒ
DB2 UDB Data Warehouse
Enterprise

Edition


ƒ
DB2 UDB Data Warehouse
Standard

Edition

ƒ
DB2 UDB Data Warehouse
Base

Edition


A pricing strategy to remove cost as a barrier to BI success


The foundation for
Information On Demand

ƒ
via the Real Time data warehouse

IBM Software Group | DB2 Information Management

Page
45

Data Warehouse
Standard
Edition

What Is in the Box?

Value

DB2 UDB Workgroup Unlimited

Query Performance

DB2 Cube Views

OLAP summary reporting

DB2 Intelligent Miner Modeling

Mine the DB for patterns

DB2 Intelligent Miner Visualization

Visualize the patterns

DB2 Intelligent Miner Scoring

Score consumer accounts based on the
buying behavior patterns detected

Office Connect Professional Web Edition

Query & reporting via XL spreadsheets

DB2 AlphaBlox

As easy as A,B,C,D,E

DB2 Warehouse Center

Extract, transform and load production
data into DB2

IBM Software Group | DB2 Information Management

Page
46

DB2 Data Warehouse
Standard

Edition Pricing

metric

Unit price $K

DB2 UDB Workgroup Unlimited V8.1

CPU

7.5

DB2 Cube Views V8.1

CPU

7.5

DB2 Intelligent Miner Modeling V8.1

server

45.0

DB2 Intelligent Miner Visualization V8.1

server

15.0

DB2 Intelligent Miner Scoring V8.1

CPU

15.0

Office Connect Professional Web Edition V4.0

10 users

2.5

DB2 Warehouse Center V8.1

CPU

0

Total of unit list prices

92.5

DWE Std Edition Price

CPU

23.75

Total Savings for one CPU / single server

$68.75

DTG

IBM Software Group | DB2 Information Management

Page
47

Data Warehouse

Enterprise
Edition

What Is in the Box?

Value

DB2 UDB
Enterprise

Server Edition

Query Performance

DB2 Cube Views

OLAP summary reporting

DB2 Intelligent Miner Modeling

Mine the DB for patterns

DB2 Intelligent Miner Visualization

Visualize the patterns

DB2 Intelligent Miner Scoring

Score consumer accounts based on the
buying behavior patterns detected

Office Connect
Enterprise Web

Edition

Query & reporting via spreadsheets

DB2 Warehouse Manager Standard
Edition

Extract, transform and load production
data into DB2

DB2 AlphaBlox

As easy as A,B,C,D,E

DB2 Query Patroller

Manage queries for throughput

DB2 Data Partitioning Feature

Scalability via scale
-
out (clusters)

DB2 Information Integrator Standard
Edition (limited to ETL only)

Access to heterogeneous data for DB2
Warehouse Manager

IBM Software Group | DB2 Information Management

Page
48

DB2 Data Warehouse
Enterprise

Edition Pricing

metric

Unit price $K

DB2 UDB Enterprise Server Edition V8.1

CPU

25.0

DB2 Cube Views V8.1

CPU

7.5

DB2 Intelligent Miner Modeling V8.1

server

45.0

DB2 Intelligent Miner Visualization V8.1

server

15.0

DB2 Intelligent Miner Scoring V8.1

CPU

15.0

Office Connect Enterprise Web Edition V4.0

CPU

9.2

DB2 Warehouse Manager V8.1

CPU

7.5

DB2 Query Patroller V8.1

CPU

7.9

DB2 Data Partitioning Feature V8.1

CPU

7.5

DB2 Information Integrator Standard Ed. V8.1

CPU

ETL restricted use

Total of unit list prices

CPU

139.6

DWE Price

CPU

75.0

Total Savings for one CPU & single server

$64.6

DTG

IBM Software Group | DB2 Information Management

Page
49

DB2 Data Warehouse
Enterprise

Edition
Competitive List Prices

Per CPU prices

DB2 DWE

Oracle 10G

SQL Server

Teradata

RDBMS

*

$40K

$20K

$77K

Cluster partitions/RAC

*

$20K

none

*

ETL

*

$5K/seat

*


** $77K

Data mining

*

$20K

limited

$32.7K

Query management

*

*

*

** $22.7K

Information Integration

*

none

none

none

Table partitioning

*

$10K

Yukon

n/a

MS
-
Excel connectivity

*

*

*

none

OLAP

*

$20K

*

none

Web App server

n/a

$20K

n/a


n/a

Data loaders

*

*

*

$40K*

1 CPU costs

$50K

$135K

$20K

$249K

16 CPUs list price

$800K

$2085K

n/a

$1895K

DTG

* = Included n/a = not applicable ** = per server or site

IBM Software Group | DB2 Information Management

Page
50

DB2 Data Warehouse
Standard

Edition
Competitive List Prices

Per CPU prices

DB2 DWE ***

Oracle 10g Std
Ed.

SQL Server

Teradata

RDBMS

*

$15K

$20K

$50K

ETL

*

$5K/seat

*

** $77K

Data mining

*

none

limited

$32.7K

MS
-
Excel connectivity

*

*

*

none

OLAP

*

none

*

none

Web App server


n/a

$10K


n/a

n/a

Data loaders

*

*

*

** $40K

1st CPU costs

$15K

$30K

$20K

$199K

4 CPUs List Price

$60K

$120K

$80K

$447.8K

DTG

* = Included n/a = not applicable ** = per server or site *** = 4 CPU maximum

Note: Oracle 10G Std One Edition has 2 CPU limit for $5K/CPU

IBM Software Group

®


Page
51

DTG

DB2

IBM Software Group | DB2 Information Management

Page
52

Alphablox Acquisition July 2004


What is Alphablox


C
ompany in Mountain View, California


Develops & sells a platform for building customized analytic applications


Strong sales into OLAP applications

DTG

IBM Software Group | DB2 Information Management

Page
53

Component
-
Based Architecture


Rapid and open application development platform


Pre
-
built components for analytic and alerting functionality


C
omponents for efficient customization

J2EE Framework …ties it all together

IBM Software Group | DB2 Information Management

Page
54

Alphablox Categories (Analytics)

Infrastructure

Data Access

Presentation
&
Manipulation

Grid Blox

Chart Blox

Toolbar Blox

Spreadsheet Blox

Present Blox

Reporting Blox

Forms Blox

Page Blox

Repository Blox

Status Blox

Data Blox

Stored procedures Blox

MDBQuery Blox

DTG

IBM Software Group

®


Page
55

IBM
B
usiness
I
ntelligence


Why IBM for BI

IBM Software Group | DB2 Information Management

Page
56

Mean 5 Year Cost of Licenses and Support

IBM Software Group | DB2 Information Management

Page
57

When you think about BI Vendor, consider IBM's
Business Intelligence Values


Market leader

in BI



Best of breed

BI solutions



End
-
to
-
end

BI solutions



Long term

Strategic partner




Safe

secure choice



Lowest TCO



Most innovative HW and SW technology


IBM Software Group | DB2 Information Management

Page
58

Solutions for Your Industry


IBM Software Group | DB2 Information Management

Page
59

Go With a Winner: DB2 BI

“DB
2

UDB

for

Unix

and

Windows

is

a

very

capable

platform,

easily

equal

to

or

better

than

any

other

in

this

market

based

on

scalability,

flexibility,

and

price/performance
.


Feb

2004

“IBM DB2 8.1 hit the Bull’s
-
Eye.
Analytic SQL is good; OLAP is
comprehensive, open, and flexible;
and data mining is powerful.”

Patricia Seybold Group,

Nov 2003

#1

Performance

0

20

40

60

80

100

Teradata

IBM

Oracle

Microsoft

Sybase

Netezza

Sand

Presence

HP
Comp
aq

20

40

60

80

100

0

LEADER

FOLLOWER

CHALLENGER

Feb 2004

MetaSpectrum

Feb 2004

#1