sybase update >joe shaffner >regional technical manager >[email protected]

Sybase Update

>Joe Shaffner>Regional Technical Manager>[email protected]

Agenda

> The Challenges of Data Warehousing

> Sybase’s Approach

> Recent Proof Points

> Customer Examples / Success Stories

Agenda

> Introduction

> Traditional Approaches to Data Warehousing

> Sybase Approach

> Data Archiving

Data to Information to Knowledge

> There is no shortage of information but there is a shortage of useful information

> Data• enables an enterprise to record an event

> Information• enables an enterprise to respond to an event

> Knowledge• enables an enterprise to anticipate an event

The Dimensions of Liquidity

Flow

“The data is there, I just can’t access it, can’t get

to it.”

“The data is locked up in silos.”

Quality

“I don’t always have the right data to make a

decision”

“The data isn’t presented in the right context that

I can use.”

Speed

“I need the information in minutes

not months.”

“I have to wait until I’m back in the office to

get data.”

BI System Purposes and Functions

DM Review, April 2001

More ad hoc use than ever!

Why Do You to do Data Warehousing on the Fly?

> Saving Money and time is what its all about now.

> Decision cycles have significantly compressed

• Need answers now, not tomorrow.

• Each answer creates a new question

• Questions are ad hoc by nature

> Project timelines to build a data warehouse have been reduced

Lots of Queries, Lots of Users, lots of Data

- - Q u e r y d i s p l a y s s e r v i c e t y p e s b y m o n t h

- - V a l i d v a l u e s a r e 1 - 1 2 f o r m o n t h _ k e y

s e l e c t m o n t h _ k e y , s e r v i c e _ k e y , c o u n t ( * )

f r o m t e l c o _ f a c t s

w h e r e m o n t h _ k e y = 1

g r o u p b y m o n t h _ k e y , s e r v i c e _ k e y

;

- - A l o o k a t c u s t o m e r s w h o h a v e t h e f o l l o w i n g s e r v i c e

- - c a l l w a i t i n g , c a l l e r i d , a n d v o i c e m a i l b y f i s c a l p e r i o d

- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8

s e l e c t s e r v i c e _ k e y , f i s c a l _ p e r i o d , c o u n t ( * )

f r o m t e l c o _ f a c t s T , m o n t h M

w h e r e T . m o n t h _ k e y = M . m o n t h _ k e y

a n d s e r v i c e _ k e y = 4

g r o u p b y f i s c a l _ p e r i o d , s e r v i c e _ k e y

o r d e r b y f i s c a l _ p e r i o d , s e r v i c e _ k e y

;

- - f e m a l e C u s t o m e r s i n M a s s a c h u s e t t s t h a t d o n o t h a v e

- - c a l l e r i d

s e l e c t d i s t i n c t ( C . c u s t o m e r _ k e y ) , C . c u s t o m e r _ f i r s t _ n a m e ,

C . c u s t o m e r _ l a s t _ n a m e , C . p h o n e _ n u m b e r

f r o m r e s i d e n t i a l _ c u s t o m e r C , s e r v i c e , t e l c o _ f a c t s

w h e r e C . c u s t o m e r _ k e y = t e l c o _ f a c t s . c u s t o m e r _ k e y

a n d t e l c o _ f a c t s . s e r v i c e _ k e y = s e r v i c e . s e r v i c e _ k e y

a n d c a l l e r _ i d _ f l a g = ' N '

a n d s t a t e = ' M A '

a n d c u s t o m e r _ g e n d e r = ' F '

;

- - F i n d p r o s p e c t s f o r v o i c e m a i l b a s e d o n t h e c r i t e r i a

- - t h a t c u s t o m e r s w i t h c a l l w a i t i n g a n d c a l l e r i d a r e

- - g o o d p r o s p e c t s f o r c a l l W a i t i n g

s e l e c t s t a t e , c o u n t ( * )

f r o m t e l c o _ f a c t s T , s e r v i c e S , r e s i d e n t i a l _ c u s t o m e r C , m o n t h M

w h e r e T . s e r v i c e _ k e y = S . s e r v i c e _ k e y

a n d T . c u s t o m e r _ k e y = C . c u s t o m e r _ k e y

a n d T . m o n t h _ k e y = T . m o n t h _ k e y

a n d c a l l _ w a i t i n g _ f l a g = ' Y '

a n d c a l l e r _ i d _ f l a g = ' Y '

a n d v o i c e _ m a i l _ f l a g = ' N '

a n d s t a t e i n ( ' N Y ' , ' N J ' , ' P A ' )

a n d f i s c a l _ p e r i o d = ' Q 1 '

g r o u p b y s t a t e

;

- - F i n d c u s t o m e r s t h a t h a d I S D N s e r v i c e i n F e b r u a r y 1 9 9 8

s e l e c t c u s t o m e r _ l a s t _ n a m e , c u s t o m e r _ f i r s t _ n a m e

f r o m r e s i d e n t i a l _ c u s t o m e r R , t e l c o _ f a c t s T , s e r v i c e S , m o n t h M

w h e r e M . m o n t h _ t e x t = ' F e b r u a r y '

a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '

a n d M . m o n t h _ k e y = T . m o n t h _ k e y

a n d S . s e r v i c e _ k e y = T . s e r v i c e _ k e y

a n d R . c u s t o m e r _ k e y = T . c u s t o m e r _ k e y

;

- - L o o k a t t h e l o c a l c a l l m i n u t e s t o s e e i f t h e y

- - h a v e i n c r e a s e d a f t e r a d d i n g c a l l w a i t i n g

s e l e c t f i s c a l _ p e r i o d , c o u n t ( * ) , s u m ( l o c a l _ c a l l _ m i n u t e s )

f r o m r e s i d e n t i a l _ c u s t o m e r R , t e l c o _ f a c t s T , s t a t u s S , m o n t h M

w h e r e S . c a l l _ w a i t i n g _ s t a t u s = ' A d d e d '

a n d s t a t e = ' O H '


a n d S . s t a t u s _ k e y = T . s e r v i c e _ k e y


g r o u p b y f i s c a l _ p e r i o d

o r d e r b y f i s c a l _ p e r i o d

;

- - L o o k a t t h e c a l l u s a g e f o r c u s t o m e r s w i t h c a l l w a i t i n g

- - " s e r v i c e t y p e 2 " c o m p a r e d w i t h c u s t o m e r s w i t h b o t h c a l l

- - w a i t i n g a n d C a l l e r i d f o r Q 4 f o r c u s t o m e r s i n C A

s e l e c t f i s c a l _ p e r i o d , T . s e r v i c e _ k e y , s u m ( l o c a l _ c a l l _ m i n u t e s ) ,

s u m ( l o c a l _ c a l l _ c o u n t ) , c o u n t ( * )

f r o m t e l c o _ f a c t s T , r e s i d e n t i a l _ c u s t o m e r C , s e r v i c e S , m o n t h M

w h e r e T . c u s t o m e r _ k e y = C . c u s t o m e r _ k e y

a n d T . s e r v i c e _ k e y = S . s e r v i c e _ k e y

a n d T . m o n t h _ k e y = M . m o n t h _ k e y


a n d T . s e r v i c e _ k e y i n ( 0 2 , 0 3 )

a n d s t a t e = ' C A '

g r o u p b y f i s c a l _ p e r i o d , T . s e r v i c e _ k e y

;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;







;



- - i . e . Q 1 , Q 2 , Q 3 , Q 4 f o r y e a r = 1 9 9 8







;


- - c a l l e r i d









;















;





a n d M . y e a r = 1 9 9 8

a n d S . i s d n _ f l a g = ' Y '




;












;














;

Traditional Row Based DBMS

> Designed to support On-line transaction processing (OLTP)

> GOOD at

• getting data in quickly

• assuring referential integrity

> Not GOOD at:

• Getting data out quickly

• Supporting ad hoc queries

> Requires table scans

• Storing data efficiently

> Requires many indexes

> May require pre-aggregations

> Have been retro-fitted to support data warehouses

> Still run into to the same “old” limitations

Examples:

MSSQLSybase ASEOracle 8.1DB2

Traditional Data Warehouse Databases

> Were designed for Data Warehousing

> Are good at

• Ad Hoc Analysis

• Supporting many users

• Loading data

> But are;

• Challenging to implement

• Costly to Maintain

• Expensive to Own

• May not scale well as users are added.

Examples:

Red BrickTeraData

The Sybase Approach

ASIQ – What is it?

> Adaptive Server IQ is a Relational DB System

> developed in 1993

> employs patented bitwise indexes for fast query response

> Adaptive Server IQ was designed specifically for Data Warehousing

> Has the look and feel of a typical RDBMS

> But under the covers it bears little resemblance

> 2,500 Customers Worldwide

> Mission Critical applications

> VLDB

>IQ customers grew by 125% last year

Traditional Solution - Parallelism

> 800 Bytes per row

> 16K Page Size

> 20 Million Rows

> 500,000 I/O’s Needed

> Parallelism won’t solve this problem

> More Hardware, slightly faster

> Only 1 Query can run at a time

> Very expensive and inflexible to ad-hoc queries

Date Store State Class Sales

3/1/96 32 NY A 63/1/96 36 MA A 93/1/96 38 NY B 53/1/96 41 CT A 113/1/96 43 NY A 93/1/96 46 RI B 33/1/96 47 CT B 73/1/96 49 NY A 12

Calculate average sales for

“A” stores in New York

Calculate average sales for

“A” stores in New York

ASIQ Architectural Summary


3/1/96 32 NY A 6

3/1/96 36 MA A 9

3/1/96 38 NY B 5

3/1/96 41 CT A 11

Read Full Row


3/1/96 32 NY A 6

3/1/96 36 MA A 9

3/1/96 38 NY B 5

3/1/96 41 CT A 11

3/1/96 43 NY A 9

Read Relevant Columns

Bitmap for "Yes" or "No"

row-id YES NO

1 0 1

2 0 1

3 0 1

4 1 0

Read BitmapBitmap structures are built on all fields

Bitmaps further reduce the amount of data read

Small number of bits rather than entire field

ANDing and ORing bitmaps is very efficient with today’s processors

Note that even vertically stored data is not read

Data structure storage and manipulation is transparent to applications and administrators. New values are handled automatically.

Distinctive Advantages of IQ

Storage Efficiency 20-50% of raw data vs. 300-900%

Query Efficiency 10-100x faster than traditional RDBMS

Database Loading Load & Index while reading

Scalability Billions of rows, thousands of users, hundreds

of nodes 3 million internet users

Disk Input-Output Efficiency 60x less I/O than traditional DBMS

Simple to Administer DBA load is 25% of traditional DBMS Any schema (Multidimensional, etc.)

IQ Data Store“IQ deserves the attention of ANYONE evaluating data warehouse DBMS options.”Rich Finkelstein,Performance Computing Inc.

Gartner Measurement - Amount of Detailed DataManaging Large Amounts of Detailed Data

LOAD

LOAD

INPUT DATA:

1TB-Source: flat files,

ETL, replication,ODSBase table(FP):0.2-0.5TB

Indexes: 0.05-0.3TB

Aggr/Summ: 0-0.1TB

Summaries

Aggregates

1-2 TB

Indexes

0.5-3TB

Base table

(“RAW data”)

(no indexes)

0.9-1.1TB

2.4-6TB

IQ Multiplex Conventional DBMS

Same INPUT Data: “Conventional DW” is 3x-10x larger than IQ-M DW

0.25 -0.9 TB

Source: Meta Group 2/98

Elements of TCO for DW

Consulting25%

Hardware45%

Software30%

“Over a 5 year period the cost of managing a data warehouse is typically 3X the initial budget.”

Hardware consumes a large portion of the budget

BI Users: How Many Is That??

ASIQ Multiplex – What Is It?

> Version of ASIQ

> Purpose is to Extend ASIQ Scalability

> Extends Single Database Access across Multiple Computer Nodes

> Allows Mixing of Unix and NT Nodes

> NOT an MPP Solution

> Much Simpler Implementation

> Much Simpler Management

You build your Warehouse …

IQ

It’s successful !It’s very successful

!

It’s too successful

!!!!!!

At some pointAt some pointyou hit the wallyou hit the wall

with performancewith performance

ASIQ Multiplex – What Is IT?

> Version of ASIQ

> Purpose is to Extend ASIQ Scalability

> Extends Single Database Access across Multiple Computer Nodes

> NOT an MPP Solution

> Much Simpler Implementation

> Much Simpler Management

IQ Multiplex Configuration

Each IQ Node has its own: - CPU’s - Local Temp Space (Disk) - Memory - Catalog

All data and indexes are stored in the shared IQ database, which is on fiber channel or EMC type storage systems

Individual nodes canbe different configurations(CPUs, memory, disk)

IQ

UnixServer

IQ

Unix

UnixServer

IQ

Unix

IQ Multiplex functions

UnixServer

IQ

Unix


UnixServer

IQ

Unix


Scaling to More Users or More Data


UnixServer

IQ

Unix

IQ

UnixServer

IQ

Unix

UnixServer

IQ

Unix

VLM UnixServer

IQ

Unix

IQ

Write NodeRead Only Nodes

UnixServer

IQ

Unix

UnixServer

IQ

Unix

UnixServer

IQ

Unix

No Data RedistributionNo Change in Schema

Replication of Catalog for LoginsVery little I/O contention

(1/10 of Oracle Parallel Server)

Read Only Nodes

EnterpriseEnterprise

EntrepreneurialEntrepreneurial

VLDBVLDB

Web Portals

Adaptive Server IQ Multiplex

Exclusive Prepackaged Scalability

Adaptive Server IQ Multip

lex

10’s of Users10’s of Users



Adaptive Server IQ Index Types:

> Low-Fast(LF) This index is ideal for columns that have a very low number of unique values (under 1000)

• Example: Gender, Yes/No, State

> High_Group (HG) Typically used to process equality and group by operations on high-cardinality data (recommended for more than 1000 distinct values)

> High_Non_Group (HNG) Add an HNG index when you need to do range searches.

• The number of unique values is high (greater than 1000)

• You don't need to do GROUP BY on the column

> Compare (CMP) A Compare (CMP) index is an index on the relationship between two columns

Adaptive Server IQ Index Types:

> Containment (WD) This index allows you to store words from a column string of CHAR and VARCHAR data.

• Use a WD index for the fastest access to columns that contain a list of keywords (for example, in bibliographic record or Web page).

> The Date (DATE), Time (TIME), and Datetime (DTTM) Three index types are used to process queries involving date, time, or datetime quantities:

• A DATE index is used on columns of data type DATE to process certain queries involving date quantities.

• The TIME index is used on columns of data type TIME to process certain queries involving time quantities.

• The DTTM index is used on columns of data type DATETIME or TIMESTAMP to process certain queries involving datetime quantities.

IQ Hardware and Software Partners

>PLATFORMS

> Sun Solaris

> HP - UX

> IBM AIX (Simplex)

> NT

> LINUX 32 bit

> WEB ANALYSIS APPL.

> Compudigm

> Web Trends

> Informatica

> CRM ANALYSIS APPL.

> Industry Warehouse Studio *

> ADVANCED VISUALIZATION

> Compudigm

> ETL / DATA MOVEMENT

> Informatica *

> Sybase Replication Server

> WEB REPORTING APPL.

> Actuate e.Reporting Suite

IQ Hardware and Software Partners

>ANALYSIS TOOLS

> Business Objects

> Cognos

> Brio

> Micro Strategy

> Easy Ask

> Whitelight

> Hummingbird

> ANALYSIS TOOLS

> MS Access

> SAS

> Group 1

> MineSet (SGI)

> PowerDesigner *

> Warehouse Control Center *

> PowerBuilder *

OLAP Tools And IQ: A Beautiful Thing

> Demonstrates IQ well

> OLAP Tools do ad hoc queries

> OLAP Tools can bring a traditional RDBMS to its knees.

> OLAP tools can be used by large numbers of people

> The success of a Data Warehouse is largely determined by its acceptance by end users

> If the OLAP tool becomes popular

• More data will be requested

• More users

Late Breaking News

The Sybase Approach

References: Amount of Detailed Data Nielsen Media Research

Business Issues• Leader in TV ratings business• 5 to 10 years of TV viewer history• Cost and logistics becoming costly • Need same data on multiple databases to scale• Difficult to deliver new services to customers

Results• Sun/Sybase delivered Reference Architecture• 35 TB benchmark and best practices guide• 12 TB detailed input data in production• Fast access and data load• Linear scalability to 108 CPUs• Architected for 100s of TB on Hitachi SNA

“The big advantage of the Sun Sybase Reference Architecture is that it provides the advance knowledge that this solution will work.”

“We are able to deliver one data warehouse for all our applications, at one-third the storage of conventional technologies, while seeing performance gains as advertised with IQ Multiplex.”

Kim RossCIO Nielsen Media Research

References: Query Complexity U.S. DOT: Bureau of Transportation Statistics

Business Issues• Congressional legislation required

consolidated, single-point of access to all transportation statistics

• Needed to deliver over the Web• Over 250+ databases of source data

Results• 2.5TB of detailed input data compressed to 1

TB• Query complexity with 18-way joins• Reduced data gathering time • Easy linkages across many data sets allows

new insights on transportation safety• The new website is aimed at transportation

researchers and analysts• Website gets 15,000 hits per day

Sybase IQ reduced loading and indexing from 30 minutes to 2.5 to 3 minutes. Query speeds were 20 – 50 times faster than Oracle. Time to add a column was reduced from 4 hours with Oracle to 15 minutes with IQ.

Jeff ButlerAssistant Director, Office of Statistical ComputingDepartment of TransportationBureau of Transportation Statistics

BizRate gains Economical DataManagement with Sun and Sybase

Together, Sun and Sybase have created a solution that packs an extraordinary amount of data processing and analytical power into a small footprint that represents a realistic investment for small and mid-sized firms.

Sybase’s tight architectural integration with Sun technology provides us with the assurance we need that the technology foundation of our data warehouse will scale to meet our growing needs in the future.

Henri Asseily, Chief Technology Officer and Founder of BizRate.com

Business Issues

> Delivers analysis of internet utilization

> Leading online customer survey producer

> Cost and logistics becoming unwieldy

> Microsoft SQL Server could not scale

> Simplify data deliver and analysis for sellers

Business Results

> Manage 15 million customer data sets

> Tight integration through Reference Architecture

> Scaleable solution that will grow

> Delivered on Sun Fire V880

http://www.bizrate.com/marketplace/index__de_id--299.html

References: Amount of Detailed Data & Query Complexity Internal Revenue Service

Business Issues• Analysis virtually impossible• Lost productivity • Loss of potential billions in revenue • VLDB management

Results• 10+ TB detailed input data (10 yrs of taxpayer

records) fits in 5 TB of storage• Query complexity with 14-way joins• Average 120 ad-hoc analysis users• Modeled entire population of commercial tax

returns• Supports advanced analysis like data mining• Revenue protection & fraud detection• ROI of $250 Million• Portal-enabled

“The primary technology challenge was to build a system that could manage such large volumes of dataand yet was sufficiently open to facilitate queries from variousoff-the-shelf products.

We selected Sybase ASIQ as the data-management server, based onits strength with decision supporttype queries.”

Jeff KmonkManager, Office of ResearchCompliance Research DivisionInternal Revenue Service

References: Amount of Data and # of Concurrent Users NC: Dept. of Health and Human Services

Business Issues• System required to provide information for

federally mandated reports. Also used for fraud detection for USDA and food stamp programs

Results• Now serving 1,200 users on 4.5TB, to grow to

28TB• Recently used to uncover $3.5 million in Medicaid

claims saving• Approx. $18 million in storage savings

Non-disclosure reference

References: Amount of Detailed Data & Number of Users & Complex Data Models American Express Global Fraud Detection

Business Issues• Unable to perform advanced analysis of fraud

patterns for credit card transactions with competing solutions due to performance issues, query complexity limitations

• Needed solutions to handle 700-column table to describe every transaction, at least one year of transactions online

Results• Advanced fraud analysis possible for last 4 years• Over 1,600 users worldwide• 6TB of input data; 10 billion records (last 13 months) of

credit card transactions online• All fraud managers worldwide use IQ Multiplex system

in AZ• Over 90% of database is fraud detection information

Non-disclosure reference

References: Query Complexity Bank of Montreal

Business Issues• Identify and retain most profitable customers• Increase effectiveness of marketing programs• Attract new customers• Access to multiple information systems and

“touch points” • Cutting edge technology and architecture

Results• 1 Terabyte data warehouse• Avg. query complexity with 18-way joins• IRR over 100%• Average credit card volume up 59%• Average credit card balances up 129%• Market share up 60 basis points• System up in 4 months• Component architecture enables flexibility

“We felt you should develop the data warehouse component by component because that allowsyou to apply what you learn.”

“Sybase was truly committed toensuring that we used technology in a way that really impacted the business.”

Carl A. TouchieSr. ManagerElectronic Financial ServicesBank of Montreal

Distinctive Advantages of IQ

> Query Efficiency

> 10-100x faster than traditional RDBMS

> Database Loading

> 600+ million records/300+ GB of data per week, 2+ billion rows a month

> Scalability

> Billions of rows, thousands of users, hundreds of nodes

> 3 million internet users

>Storage Efficiency

> 20-70% of raw data vs. 300-900%

>Disk Input-Output Efficiency

> 60x less I/O than traditional DBMS

>Simple to Administer

> DBA load is 25% of traditional DBMS

> Any schema (Multidimensional, etc.)

Recent Customer Examples

Overview for Large Southern Bank

October 2nd, 2003

Sybase IQ Proof of Concept

Your Requirements

> Reduce EMC Storage costs

> Simple and fast implementation

> Faster query speeds

Who is using IQ today for Archiving?

> Nielsen Media

Loaded 10 years of detailed TV viewer patterns from mainframe archive.

http://syberstatic.sybase.com/bid/pdfsforweb/nielsen_research.pdf

> U.S. Internal Revenue Service

Keeps years’ worth of all U.S tax returns on disk.

http://syberstatic.sybase.com/bid/pdfsforweb/iq_ss_l01105.pdf

> EMI Music Germany

Stores 10 years of historical data on and delivers responses to ad-hoc queries within seconds.

http://syberstatic.sybase.com/bid/pdfsforweb/emi_ss.pdf

> North Carolina Department of Health and Human Services

Recently used to uncover $3.5 million in Medicaid claims saving. Serving 1,200 Approx. $18 million in storage savings

> 2 TB of input/raw data, IQ-M: 1.5 TB, Recently used to uncover $3.5 million in Medicaid claims saving> Approx. $18 million in storage savings

TIME LINE

DATE

AUGUST

SEPTEMBER3rd

8th

15th16th

OCTOBER1st2nd

TASK

Initial IQ Presentation

Scoping Meeting

Pre POC Preparations

Installation, configuration, loadLoad completionTabulation of Results(Two Days!)

Complete data movementRun sample queriesPresentation

TODAY

Stumbling Blocks

1. Dr Watson prevented final table load on day 1

2. Special Characters in data found in extract prevented load completion

Current Environment

BCP

ARCHIVESOURCE

BCP

ARCHIVE

POC Environment

P3Tradsssss_2000(16M Rows)

Xxx History_2001(16M Rows)

GOOBERSun E 6500

20-way7GB Ram

IQ Clients

BCP

OrFROMASE

ODBC

JDBC

OPEN

CL

SYBASE IQ

P3ssssss_2002

VIEW

CREATED

Ssss _hist_Archive(89M Rows)

The Results - Storage

The Results - Queries

QUERY ASE Sybase IQ1. SELECT firmNumber, acctNumber, settleDate, tradeDate,

sideCode,buySellCode,cusipNbr, secType, quantity,

price, principalAmt,grossCommission, tradeProfit,

secFee,NetAmount, frontAcctNbr,contraAcctNbr1,

tradeInterest,TBASettleDate, entryDate, sourceProgram

FROM dbo.P3TradeHistory_2001

WHERE ( sideCode = 'F' ) AND

( cusipNbr = '9128273L4' )

49:58.7 0:40

2. SELECT firmNumber ……

FROM P3TradeHistory_2001

WHERE acctNumber = '94207206' and sideCode = 'F'

43:43.6 0:08


FROM dbo.P3TradeHistory_2002

WHERE ( sideCode = 'F' ) AND

-- ( cusipNbr = '9128277KW' )

(cusipNbr = '9128273L4')

53:05.9 0:07.1


FROM P3TradeHistory_2002

WHERE acctNumber = '94207206' and sideCode = 'F'

53:13.6 0:07

Storage Costs Analysis

Ref Wachovia Cost Analysis.xls

Your Requirements…… The Results

Reduce EMC Storage costs = demonstrated

Simple and fast implementation = three days for four tables

Faster query speeds = all

Worlds Largest ISP

POC3 Environment

SAN

EMC SYMM

SYBASE IQV 12.5ESD 9

1.6 GB RAM(800 MB Temp800MB Main)

RED HAT3.0

Advanced Server(2.4.21-9.Elsmp)

14 RAW DEVICES

1-5 TEMP 8GB Each5-14 DATA 32GB each

Data staging Area 100GB

HP DL 7408 CPU2.7GHz-2MB cache4GB RAM

Bulk Loading Results

Serial Execution

Table NameTable Width

Num Columns

Raw Data Size (MB)

Rows Loaded Pass 1 Pass 2

Total Time MB/Sec Rows/Sec GB/Hour

MEMBER 344 35 54,359.44 150,000,000 1,055 174 1,229 44.23 122,050 155.50MEMBER_SUB 82 8 39,052.96 450,000,000 883 537 1,420 27.50 316,901 96.69

Aggregate Throughput 123.97

Parallel Execution

Table NameTable Width

Num Columns

Raw Data Size (MB)

Rows Loaded Pass 1 Pass 2

Total Time MB/Sec Rows/Sec GB/Hour

MEMBER 344 35 54,359.44 150,000,000 1,116 267 1,383 39.31 108,460 138.18MEMBER_SUB 82 8 39,052.96 450,000,000 1,086 660 1,746 22.37 257,732 78.63Start Time 15:47:13 Aggregate Throughput 104.95Stop Time 16:28:09 Wallclock Throuhgput 133.71Elapsed 00:40:56

Load Times (Seconds)

Load Times (Seconds)

155 GB Per Hour Achieved!A Five-Fold improvement

Data Explosion Factorsv.s RAW vs. Row-based RDBMS

LOAD

LOAD

INPUT DATA:

88 GB

110+GB

> IQ Multiplex > Row-Based DBMS

FULLY INDEXED

~45 GBDATA

101 GB

> RAW Data

DATA

88 GB

INDEXES

22 GB

Aggregates

???Materialized Views

What we demonstrated in POC 3

Great Price/Performance combination

> Compression of RAW data (~ 50% of input fully indexed)

> Inexpensive/high-performance Hardware

> High Performance on a 8-way Linux Blade (Compared to UNIX)

IQ working on HP’s new 32 bit Linux Box

• Improved performance over 4 way

• No software issues/downtime

Demonstrated Hyper-threading vs. non-Hyper-threading

• IQ shows 15% increase with Hyper-Threading ‘on’

Ease of implementation

• Install and configuration of HW and SW in hours

• Net POC time a single day

• Used existing data model and data types with no tweaking

Ease of Administration

• No P&T on Box, OS or Storage

• All indexing done during load

IQ Write NodeHP DL7408 CPUs32 GB RAM

IQ Reader Nodes4 CPU Bladesfrom Egenera

StorageCurrently EMCbut not limited

Proposed Architecture

SourceDBSource

DBSourceDBSource

DB

RepServer

RepConnect

PossibleTarget

DB

Flat Files

Sybase IQClientsQueriesReports

Load ProcessOr ETL Tool

sybase update >joe shaffner >regional technical manager >[email protected]

Documents