Transcript DB2

®
IBM Software Group
IBM Business Intelligence
Carolina Yalamova
NDB Ltd.
Page 1
IBM Software Group | DB2 Information Management
Page 2
IBM Software Group | DB2 Information Management
Anatomy of Business Intelligence Solutions
Data
Discovery
Business Actions
Customer retention
programs
Transaction
Customer segmentation
Operational
Demand & forecasting
Demographic
Affinity merchandising
Where to advertise
Lifestyle
Product & customer
profitability
Maximize store layout
Financial
Economic
Government
Associations
Optimize mailing list
Fraud patterns & profiles Cross-product marketing
Improved risk profiles
Funds allocation
Page 3
IBM Software Group | DB2 Information Management
Why Should Customers Do BI projects Now?
%
30
Average ROI = 431%
25
% of
Clients
20
Achievin
g This
15
ROI
10
5
1-50
51-100 101-500 501-1000 1000+
% ROI Achieved
Source: IDC,The Financial Impact of Business Analytics study, Nov. 2002
Page 4
IBM Software Group | DB2 Information Management
End-User's productivity
0
10
20
30
40
10
20
30
40
C ollec t D ata
Analy s e D ata
D etermine D ec is ion
Plan Sc enario
Ac t on Plan
0
P ercent of Time S pent on A ctivity
W ithout BI Applications
W ith BI Applications
Source: Gartner Group
Page 5
IBM Software Group | DB2 Information Management
Business Intelligence Architecture
Operational
Databases
Production
Extract, transform,
merge, load
ETL
metadata
Clean it up
Consolidate
Enterprise
Data Warehouse
A single
version of the
truth
Line of Business
Data Marts
Slices of data
for departments
End User
Analytic Tools
End user
Delivery &
analysis
Data
Mining
Query &
Reporting
Spatial
Page 6
IBM Software Group | DB2 Information Management
MOLAP, HOLAP, ROLAP
Page 7
®
IBM Software Group
IBM Business Intelligence
Software Overview
Page 8
IBM Software Group | DB2 Information Management
The DB2 Framework for Business Intelligence
SQL XML PMML
Administration
Data Mining
OLAP
ETL
Scalability
DB2
Information Integration
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
Page 9
IBM Software Group | DB2 Information Management
IBM Products Mapping
Operational
Databases
Production
Extract, transform,
merge, load
ETL
metadata
DB2 ESE
DB2 Query Patroller
DB2 Cube Views
DB2 DPF
Enterprise
Data Warehouse
Line of Business
Data Marts
End User
Analytic Tools
DB2 WH Center
DB2 WH Manager
DB2 II
DB2 OLAP Server
Data
Mining
Query &
Reporting
Spatial
Intelligent Miner
Spatial Extenders
Office Connect
QMF
Alphablox
Page 10
®
IBM Software Group
IBM Business Intelligence
DB2
Page 11
IBM Software Group | DB2 Information Management
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
Page 12
IBM Software Group | DB2 Information Management
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
Page 13
IBM Software Group | DB2 Information Management
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
Oracle Server
DB2 Server
MQT_1
Table 1
Table 2
MQT
2
Table 3
Oracle
Table 1
Page 14
IBM Software Group | DB2 Information Management
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
Page 15
®
IBM Software Group
IBM Business Intelligence
OLAP Server
Page 16
IBM Software Group | DB2 Information Management
DB2 OLAP Server
Analyzer, Brio,
or
Windows Client
DB2
DB2
OIS
source data
DB2
OLAP
Server
High
concurrency
option
Partitionin
g option
Multi-dimensional Cubes
SQL interface
Essbase API
MS Excel macros
Currency conversions
Page 17
IBM Software Group | DB2 Information Management
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
Page 18
IBM Software Group | DB2 Information Management
DB2 OLAP Server- Benefits
Benefit
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
Page 19
®
IBM Software Group
IBM Business Intelligence
DB2 Warehouse Center
/
DB2 Warehouse Manager
Page 20
IBM Software Group | DB2 Information Management
ETL - Data Warehouse Center / DB2 Warehouse Manager
Page 21
IBM Software Group | DB2 Information Management
DB2 Data Warehouse Manager and Center
DB2 Data Warehouse Center
W PS v1.2 - The Cutting Edge
File
Back
Address:
Tool Hel
Edit View s
p
Forwar
Searc
x
d
Stop Refresh Home h
Histor
y
Mail
Prin
t
http://the_call_center/customer/l ookup
07/02/2001
19:29:20
home
ƒ DBA administration console
ƒ Access to most RDBMS's & flat files
ƒ Schedules and monitors database tasks
ƒ 150+ prebuilt data transformations
ƒ Parallel extracts & transforms
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
Internet
DB2 Warehouse
Center
DB2 Warehouse Manager adds:
extract, transform, load,
schedule, administrate
ƒ Extracts & transformations on remote servers
via agents
ƒ Information Catalog end user metadata
DB2
3m
IBM
DB2 Warehouse Manager
ETL agents
Information
Catalog
Metadata
Page 22
IBM Software Group | DB2 Information Management
DB2 Warehouse Manager - Benefits
Benefit
SQL/Java extracts & Leverage existing programming skills -- nothing proprietary
Standards based transforms are portable to applications
transformations
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
Page 23
®
IBM Software Group
IBM Business Intelligence
Query Patroller
Page 24
IBM Software Group | DB2 Information Management
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
Flexible result set options
ƒ Queue handling of reports returned
Interoperates with end user tools
SQL 14
SQL 15
SQL 21
SQL 23
answers
ƒ Charge Back & Trend Statistics
answer sets
queued
queues
explains
Graphical tools to Administer and
Report
Query
Patroller
DB2
Optimizer
Data Warehouse
Page 25
IBM Software Group | DB2 Information Management
Query Patroller Benefits
Benefit
Manages SQL traffic Ensure smooth use of RDBMS resources, maximizing throughput,
and making best use of server; prevents unnecessary server
into DB2
upgrades
SQL query result set let's users submit queries and pick up results later without locking
up the workstation
"caching"
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
Page 26
®
IBM Software Group
IBM Business Intelligence
Office Connect
Page 27
IBM Software Group | DB2 Information Management
Office Connect
Client
Browser
HTTP
Server
HTTP
Tomcat
Apache
Application
Databases
Servlet Engine
Tomcat
WebSphere
JDBC/
HTTP
Office Connect
Servlet
JDBC
Web Edition
Design time
repository
HTML
sources
ODBC
Microsoft Excel - Sales forecast.txt
demo pg6
File Edit View Insert Format Tools Data Window Essbase Help
A
Z
ABC
A
1
2
B
C
D
Profit
Actual
Cola
Jan
Feb
100%
E
Mar
F
Apr
Arial
G
May
H
Jun
Jul
J
Aug
K
Sep
L
Oct
I
B
10
I
M
Nov
N
Tot
Dec
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
Page 28
IBM Software Group | DB2 Information Management
Office Connect - Benefits
Benefit
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
Uses Microsoft Excel as
Basic reporting capability easy to learn and share with
Query Tool
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
Page 29
®
IBM Software Group
IBM Business Intelligence
DB2 Cube Views
Page 30
IBM Software Group | DB2 Information Management
DB2 Cube Views = an OLAP Accelerator
OLAP servers
ROLAP
Servers
Query &
Reporting Applications
Tools
DOLAP
Servers
Business Business
Functions Functions
&
&
Operators Operators
BI Tools
Applications
Business Functions &
Operators
Net present value, allocations,
write back, etc.
MOLAP Engines
DB2 Cube Views
DB2
Page 31
IBM Software Group | DB2 Information Management
What Are DB2 Cube Views?
Wizard to design & generate cube views
ƒ Wizard for cube definition, metadata modeling, and optimization
MQT
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
Page 32
IBM Software Group | DB2 Information Management
Cube Models & Cubes
Page 33
IBM Software Group | DB2 Information Management
Bridges
Simple interface
ƒ Stored Procedure and XML document
Bridge into and out of DB2
ƒ Enables end to end flow of dimensional metadata
XML
1
XML
DB2
1
Hyperion
Cube Views
BUSINESS
OBJECTS
DB2 Stored
Procedure
Page 34
IBM Software Group | DB2 Information Management
Aggregates are required for OLAP
MQT's for
ƒ Faster cube load
ƒ Faster drill through reports
ƒ Faster hybrid analysis
ƒ Faster Ad-hoc analysis
ƒ Faster reporting
rows & columns
Data loading
DB2 OLAP
Server
Hybrid
Analysis
Cube Build
Drill through
Query Results
materialized query tables
Page 35
IBM Software Group | DB2 Information Management
Performance Advisor
Administrator
Aurora Model
Catalog Tables
Base Tables
OLAP Metadata
.
Model Information
Time & Space constraints
Optimization hints
Statistics
Query history
Data Samples
MQT's
Page 36
®
IBM Software Group
IBM Business Intelligence
Data Mining
Page 37
IBM Software Group | DB2 Information Management
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
Page 38
IBM Software Group | DB2 Information Management
IBM Data Mining Products

DB2 Intelligent Miner Modeling

DB2 Intelligent Miner Scoring

DB2 Intelligent Miner Visualization

DB2 OLAP Miner
Page 39
IBM Software Group | DB2 Information Management
Data Mining Techniques
Discovery Clustering (Demographic and Neural)
? What logical groups exist?
Associations
? What are the linkages among events?
Prediction 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?
Page 40
IBM Software Group | DB2 Information Management
Data Mining Functions in DWE
Analyst defines
model and
runs the
analysis …
Model applied
real time in
applications
XML
… using
data from
the data
warehouse
IM Modeling
XML
Model can be
viewed
standalone or
over the web
IM Visualization
DB2 OLTP,
DW or
ODS
IM Scoring
Page 41
IBM Software Group | DB2 Information Management
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
Page 42
®
IBM Software Group
IBM Business Intelligence
DB2 Data Warehouse Edition
Page 43
IBM Software Group | DB2 Information Management
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
Page 44
IBM Software Group | DB2 Information Management
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
Page 45
IBM Software Group | DB2 Information Management
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
CPU
15.0
10 users
2.5
DB2 Intelligent Miner Scoring V8.1
Office Connect Professional Web Edition V4.0
DB2 Warehouse Center V8.1
CPU
Total of unit list prices
DWE Std Edition Price
Total Savings for one CPU / single server
0
92.5
CPU
23.75
$68.75
Page 46
IBM Software Group | DB2 Information Management
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
Page 47
IBM Software Group | DB2 Information Management
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
Page 48
IBM Software Group | DB2 Information Management
DB2 Data Warehouse Enterprise Edition
Competitive List Prices
Per CPU prices
DB2 DWE
Oracle 10G
SQL Server
Teradata
RDBMS
*
$40K
Cluster partitions/RAC
*
$20K
ETL
*
$5K/seat
Data mining
*
$20K
Query management
*
*
*
Information Integration
*
none
none
none
Table partitioning
*
Yukon
n/a
MS-Excel connectivity
*
*
none
OLAP
*
$20K
*
none
n/a
$20K
n/a
n/a
Web App server
Data loaders
1 CPU costs
16 CPUs list price
$20K
$10K
*
*
*
$50K
$135K
$800K
$2085K
* = Included
n/a = not applicable
$77K
none
*
*
** $77K
limited
$32.7K
** $22.7K
*
$40K*
$20K
n/a
$249K
$1895K
** = per server or site
Page 49
IBM Software Group | DB2 Information Management
DB2 Data Warehouse Standard Edition
Competitive List Prices
Per CPU prices
DB2 DWE ***
Oracle 10g Std
Ed.
RDBMS
*
$15K
ETL
*
$5K/seat
Data mining
*
MS-Excel connectivity
OLAP
Web App server
Data loaders
SQL Server
Teradata
$20K
$50K
*
** $77K
none
limited
$32.7K
*
*
*
none
*
none
*
none
n/a
n/a
n/a
*
$10K
*
*
** $40K
1st CPU costs
$15K
$30K
$20K
$199K
4 CPUs List Price
$60K
$120K
$80K
$447.8K
* = 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
Page 50
®
IBM Software Group
DB2
Page 51
IBM Software Group | DB2 Information Management
Alphablox Acquisition July 2004
 What is Alphablox
 Company in Mountain View, California
 Develops & sells a platform for building customized analytic applications
 Strong sales into OLAP applications
Page 52
IBM Software Group | DB2 Information Management
Component-Based Architecture
 Rapid and open application development platform
 Pre-built components for analytic and alerting functionality
 Components for efficient customization
J2EE Framework …ties it all together
Page 53
IBM Software Group | DB2 Information Management
Alphablox Categories (Analytics)
Grid Blox
Presentation
&
Manipulation
Chart Blox
Spreadsheet Blox Forms Blox
Present Blox
Reporting Blox
Page Blox
Toolbar Blox
Data Access
Data Blox
MDBQuery Blox
Stored procedures Blox
Infrastructure
Status Blox
Repository Blox
Page 54
®
IBM Software Group
IBM Business Intelligence
Why IBM for BI
Page 55
IBM Software Group | DB2 Information Management
Mean 5 Year Cost of Licenses and Support
Thousands
Cumulative Software CCO
$8,467
$7,056
$5,645
$4,234
$2,822
$1,411
$0
1
2
IBM DB2
3
Year of Operation
NCR Teradata
4
5
Oracle
Page 56
IBM Software Group | DB2 Information Management
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
Page 57
IBM Software Group | DB2 Information Management
Solutions for Your Industry
Page 58
IBM Software Group | DB2 Information Management
100
Performance
Go With a Winner: DB2 BI
#1
MetaSpectrum
Feb 2004
LEADER
80
IBM
CHALLENGER
Teradata
Sybase
Netezza
Oracle
Microsoft
HP
60
Sand Comp
aq
40
20
FOLLOWER
0
0
20
40
60
Feb 2004
80
100
Presence
#1
“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
“DB2 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
Page 59