seven signs you need a data warehouse

7 Signs You Need a Data Warehouse

Heath KathSr. Data Access Consultant

Mike StegemanSr. Data Access Consultant

Today’s speakers:

Welcome!

Today’s Agenda

• Why use a data warehouse on IBM i?

• 7 signs you need a data warehouse

• SEQUEL Data Warehouse

Why Use a Data Warehouse on IBM i?

How does a data warehouse boost yourbusiness intelligence strategy?

Sales Data

Budgets

Customers, Inventory, Financials

Industry Data

Why would you need more than just a query tool?• You have multiple application databases• The database is complex and not well designed• Your data contains errors• Your reporting needs are complex • You have many query users

What’s real? Data is growing, data is becoming more complex, and users need and want more reliable information.

Why Use a Data Warehouse on IBM i?

Multiple Application Databases

Sales (DB2 for i5/OS)

BI Reporting

Financials (DB2 for i5/OS)

POS System (SQL Server) Purchasing (Oracle)

You Need a Data Warehouse: Sign 1

You Need a Data Warehouse: Sign 2

CUSTNO CUSTNAME1001 John Smith1002 Mary Jones1003 Chris Anderson1004 David Perry

Customer File - USCUSTNO CUSTNAME

1001 Harry Potter1002 Jeremy Carr1003 Penny Hayes1004 Debbie Thornton

Customer File - Canada

CUSTID CUSTNAMAA234 Julie JohnsonAA235 Fred HunterAB670 John SmithBD309 Alan Jordan

Customer File - CanadaCUSTNO CUSTNAME

1001 John Smith1002 Mary Jones1003 Chris Anderson1004 David Perry

Customer File - US

Files (tables) are the same, but different…

Multiple instances of the same table, with duplicate key values

Or different versions of the same entity with incompatible data types

Poor performance

You Need a Data Warehouse: Sign 3

100M rows

Changing dimensions

You Need a Data Warehouse: Sign 4

100 Smith & Jones Electrical Small Retailer Jenny Brown

100 Smith & Jones Electrical Major Retailer Rob McAdam

100 Smith & Jones Electrical Major Retailer Jenny Brown

2012

2013

2014

You Need a Data Warehouse: Sign 5

Data errors

• Failed joins• Invalid dates• Missing

values

Difficult dates

• Date format• Multiple fields

for Year, Month, Day

• Field types

Hidden meanings

• Second character of column X means…

• If column Y = ‘C’ then Z * -1

• If this…then…

Data challenges

You Need a Data Warehouse: Sign 6

A chaotic reporting environment!

Sales FinancialsPurchasing

GL Summary(Excel)Summary Sales by

Customer/Brand

Profitability Extract

Summary Sales by Region

Purchasing extract

(MS Access)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Joe downloads this manually via Client Access every

Monday...except when he’s on vacation or out with the

flu!

Mary wrote this extract. She left last

year and no-one knows how it works.

The Net Sales calculation in this

extract is different to Mary’s.

No one has yet realized that this is loaded

incorrectly. The auditors will be the first to discover

the problem.

These reports

don’t bala

These reports don’t balance

with each other. No one trusts

this report.John spends 5 days

every month generating this and massaging the numbers until he thinks

it is correct.

Poor data quality

For example:• Property assessment incorrectly changed to $400M • Property tax revenue of $8M was included in the county budget• County had a huge revenue shortfall, resulting in lots of cuts• The school district forced to return $2.7M

You Need a Data Warehouse: Sign 7

Just because of

ONE bad data

value!

Quick Poll…

Why do you need a Data Warehouse?

Multiple databases

Inconsistent data

Complexity

Without a Data Warehouse…

• 96% of data marts require change in the first year • 75% of independent data marts do not survive > 2 years• 60% of companies abandon their BI investment within 5 years

SOLUTION: SEQUEL Data Warehouse

SEQUEL Data Warehouse

ETL Tool

DataManagement

ManageDevelopment

IBM iEnvironment

MetadataRepository

DataWarehouse

SEQUEL Data Warehouse

Development Client

SEQUEL Data Warehouse

SALES PURCHASING FINANCIALS

O P E R A T I O N A L S Y S T E M S

SEQUEL Implemented Against Operational Data

Simple Implementation—Without SDW

Front End Tools Implemented Against DW/DM Tables

Data Warehouse/Data Marts

O P E R A T I O N A L S Y S T E M S

SALES PURCHASING FINANCIALS

Simple Implementation—With SDW

SEQUEL Data Warehouse: Data Access

+ Non-DB2 Data Sources

SEQUEL Data Warehouse on IBM i

Oracle, MS SQL, MySQL, Sybase

XML FilesText Files (fixed length or delimited)

MS Excel Salesforce Apache Hive, Impala

Share Information withSEQUEL Data Warehouse

Key to formatting and distribution of data

Browser Access!

Share information withSEQUEL Web Interface

Data Warehouse

A solution to your business needs

SEQUEL is offering exciting spring promotions. We’d love to tell you about them!

Interested? Give your sales rep a callat 800-328-1000.

Special Promotions—Just for You

Heath KathSr. Data Access Consultantheath.kath@helpsystems.com

Mike StegemanSr. Data Access Consultantmike.stegeman@helpsystems.com

www.helpsystems.com/sequel

We are ready for your questions!

Thank You for Joining Us Today!

Website: www.helpsystems.com/sequel

Phone: 800-328-1000 or+1 952-933-0609

Email: mike.stegeman@helpsystems.comheath.kath@helpsystems.comsupport.sequel@helpsystems.com