Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

274 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Table 7-3 Refreshing aggregated data tables

7.2 Design and develop your ETL application

The discussion so far has highlighted the different data ingest patterns,

objectives, and approaches. At this point you know the principle characteristics

of your ETL application and have an appreciation of what the final ETL

application is to look like.

When designing an ETL application, it is important to follow some basic rules:

򐂰 Design discrete ETL components to process data as soon as possible and as

far as possible through the ETL process rather than waiting for all data to be

available.

򐂰 Fail only rows that cannot be processed and avoid processing valid data more

than once; this reduces I/O.

򐂰 Parameterize your ETL components such that multiple instances of each

component can be run in parallel at peak times to increase your data ingest

rate.

򐂰 Know your recovery position from a data ingest and database perspective at

all points in the process.

SQL Warehousing, a feature of IBM Design Studio that is part of InfoSphere

Warehouse, is a graphical tool that allows you to build and compile an

executable ETL application. InfoSphere Warehouse Administration Console is

used to register, deploy, and schedule the compiled SQW application.

SQW follows the control flow and data flow architecture common to other ETL

tools. An Eclipse-based interface provides a drag, drop, and configure approach

Label Description Refresh time

None Database contains only tables with detail data 0 minutes

MQTs Database contains MQTs that must be refreshed n minutes

Custom

aggregates

Database contains summary or aggregate tables that

are custom-built (that is, not using MQT feature) and

must be updated

n minutes

Tip: Include the need to refresh MQTs or custom aggregate into your ingest

rate calculations and latency objectives.