WP_Data Management Best Practices

W H I T E P A P E R

www.persistent.com

The Kimball DW/BI systemarchitecture focuses on the following components:

—

Backroom area

or ETL (Extract -Transform – Load) area

pages 119-133). This area performs four major

operations:

—

Extracting

the data from the sources,

—

Performing

cleansing and conforming

transformations,

—

Delivering

dimensional models to the presentation server and

—

Managing

the ETL process and back room environment (job scheduling and monitoring,

recovery/restart capabilities, versioning, and pipelining/parallelizing).

An ETL tool may be built homegrown or may be used off-the-shelf, with the key parameters being the

productivity support, usability and metadata driven approach. The data stores in this layer deal with process

history, staging tables, master tables, reference tables, audit tables and error tables.

—

Presentation server

(

pages 133-141), also called the target DW server, is where the data complying to a

logical dimensional model and queryable by the front room applications is stored. It is organized by business

process, is atomically grained, is tied together by the enterprise data warehouse bus architecture. In general,

because of the large datasets involved in most organizations, a second layer is necessary for summary level

queries to return in real time (or at least, a reasonable amount of time): the aggregation layer, where data is pre-

aggregated during the load process as opposed to being aggregated on the fly by analytic queries.

Implementation choices for the aggregation layer include the same (or another) relational database as the one

storing the atomically grained dimensional models, or an OLAPengine.

The management features are those that govern the life of the data warehouse, which include performance

monitoring, securing and auditing access to the server, backing up and recovering content, physical database

maintenance and automated systemoperation.