Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
[ Team LiB ]
Roles Involved in These Activities
Application lead developer
The application lead developer and the database administrator should review all
lessons learned during the prototyping activities. The application lead developer
should help the database administrator determine which queries and reports
can be executed in parallel and what type of security is needed.
Data administrator
The data administrator should provide the logical data model and the meta data
to the database administrator. The logical data model and the meta data will be
helpful to the database administrator when he or she designs the BI target
databases. This is true even if a multidimensional database design schema was
chosen because the entities and relationships on the logical data model are the
perfect starting point for designing conformed dimensions and normalized
snowflake dimensions.
Database administrator
The database administrator has the primary responsibility for database design.
He or she needs to know the access paths, weigh the projected data volumes
and growth factors, and understand the platform limitations. He or she must
create and run the DDL and DCL to build the physical databases. In addition, he
or she is responsible for choosing the most appropriate implementation options.
Database administrators, not programmers, should
design databases. Database design usually is—and
should be—part of the job description for database
administrators because it requires special product-
specific training on the DBMS optimizer.
ETL lead developer
The ETL process is dependent on the database design. The ETL lead developer
should be involved in the database design activities in order to stay informed
about any database design changes that will affect the ETL process or the ETL
programming specifications.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
271/631
[ Team LiB ]
Risks of Not Performing Step 8
Tables are not flat files in a database, and they are not just a different way to
casually store some data. Relational DBMS engines are based on intricate internal
sets of rules. These rules must be understood and followed. Organizations hire
database administrators to do just that. However, too often programmers who are
not intimately familiar with the internal workings of their DBMS engines are allowed
to design the BI target databases, and they design them poorly. This could have a
catastrophic effect on performance. In fact, it could kill the BI application, if not the
entire BI decision-support initiative.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
272/631
[ Team LiB ]
Bibliography and Additional Reading
Atre, Shaku. Distributed Databases, Cooperative Processing and Networking. New
York: McGraw-Hill, 1992.
———. Data Base: Structured Techniques for Design, Performance, and Management,
Second Edition. New York: John Wiley & Sons, 1988.
Bischoff, Joyce, and Ted Alexander. Data Warehouse: Practical Advice from the
Experts. Upper Saddle River, NJ: Prentice Hall, 1997.
Bontempo, Charles J., and Cynthia Maro Saracco. Database Management: Principles
and Products. Upper Saddle River, NJ: Prentice Hall, 1996.
Bruce, Thomas A. Designing Quality Databases with IDEF1X Information Models. New
York: Dorset House, 1992.
Corey, Michael J., and Michael Abbey. Oracle Data Warehousing: A Practical Guide to
Successful Data Warehouse Analysis, Build, and Roll-out. Berkeley, CA: Osborne
McGraw-Hill, 1996.
Hackney, Douglas. Understanding and Implementing Successful Data Marts. Reading,
MA: Addison-Wesley, 1997.
Hoberman, Steve. Data Modeler's Workbench: Tools and Techniques for Analysis and
Design. New York: John Wiley & Sons, 2001.
Inmon, William H. Building the Data Warehouse. New York: John Wiley & Sons,
1996.
Inmon, William H., Claudia Imhoff, and Greg Battas. Building the Operational Data
Store. New York: John Wiley & Sons, 1996.
Kimball, Ralph, and Richard Merz. The Data Webhouse Toolkit: Building the Web-
Enabled Data Warehouse. New York: John Wiley & Sons, 2000.
Kimball, Ralph, Laura Reeves, Margy Ross, and Warren Thornthwaite. The Data
Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and
Deploying Data Warehouses. New York: John Wiley & Sons, 1998.
DBMS-specific optimization rules obtainable from the DBMS vendors:
IBM: http://www.ibm.com/support/us
Oracle: http://www.oracle.com
NCR Teradata: http://www.teradata.com/library/default.asp
Microsoft SQL Server: http://www.microsoft.com/sql/default.asp
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
273/631
[ Team LiB ]
Chapter Nine. Step 9:
Extract/Transform/Load Design
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
274/631
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
275/631
Chapter Overview
This chapter covers the following topics:
Things to consider about extract/transform/load (ETL) design
Common BI implementation strategies, such as data marts, operational data
stores, enterprise data warehouses, and Web warehouses
How to reformat, reconcile, and cleanse the source data for three different sets
of ETL programs: initial load, historical load, and incremental load
Various approaches for extracting data from the operational source files and
source databases
Typical source data problems encountered during transformation, such as
duplicate primary keys, inconsistent data values, different data formats, and
embedded process logic
Load considerations, such as referential integrity and indexing
The source-to-target mapping document, the process flow diagram, and the
staging area
Eight steps to follow when evaluating ETL products and vendors
Brief descriptions of the activities involved in ETL design, the deliverables
resulting from those activities, and the roles involved
The risks of not performing Step 9
Things to Consider
Tools
Have we selected an ETL tool, or are we writing the ETL programs
from scratch?
Will the ETL tool run on the platform where the source files are? On
a separate server?
Do we have a separate data-cleansing tool? Will we run it before or
during the ETL process?
Do we have an efficient sort utility?
ETL Staging
How big is our ETL staging window? How many hours per night?
Per week? Do we have a smaller window at month-end because of
other month-end processes? How much smaller?
Can we fit our ETL process into those windows or will we have to
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
276/631
run over several days or nights?
How many source data elements do we have to extract? And how
many source files and source databases do we have to access?
ETL Process Flow
How many programs can we run in parallel to shorten the ETL
runtime?
How long will the initial load take? Have we prototyped it?
How long will it take to load the historical data? How many years of
history do we need to load?
Do we know how long the incremental loads will run?
Should we insert rows or use the database management system
(DBMS) load utility?
Should we use a third-party load utility to speed up the process?
Do we already have a third-party load utility, or do we need to buy
one?
When and how will the data be archived? On disk? On tape? Do we
have to write archival programs at this time or can we postpone that
additional programming effort to a future release?
Performance Considerations
How would ETL load performance be affected if we left referential
integrity (RI) turned on?
How high would the risk of data corruption be if we turned RI off?
How much RI checking do we want to perform in the ETL
programs?
Reconciliation
At how many points in the ETL process do we need to count input
and output records?
Are the record layouts and database structures different on the old
historical files and databases than they are on the current files and
databases? How do we reconcile them?
Do we need to reconcile changed codes? Reused and redefined
fields?
How many data elements do we have to reconcile? How many
codes? How many amounts?
Will dirty data be rejected? How will that be reflected in the
reconciliation totals?
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
277/631
Will the load counts and reconciliation totals be stored as meta
data?
Quality Metrics
How will data quality errors be counted? What data quality metrics
do we need to compile in the programs?
Will we store those metrics as meta data in the meta data
repository or print them in a report?
Source data for the BI applications will come from a variety of platforms, which are
managed by a variety of operating systems and applications. The purpose of the ETL
process is to merge data from these heterogeneous platforms into a standard format
for the BI target databases in the BI decision-support environment, as shown in
Figure 9.1.
Figure 9.1. Heterogeneous Data Sources
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
278/631
[ Team LiB ]
Implementation Strategies
There are several types of BI decision-support implementation strategies with every
conceivable combination of BI target databases (e.g., operational data store and
enterprise data warehouse; Web warehouse and data marts; exploration warehouses
and data mining databases; data marts and operational marts [oper marts]). By far
the most popular implementation strategy is a data mart environment.
Regardless of which implementation strategy is selected, there is a right way and a
wrong way to implement it. The wrong way is to build a collection of stand-alone BI
target databases, each with its own independent ETL process. This approach will not
produce an integrated and reconciled BI decision-support environment because
creating separate ETL processes is no different than developing traditional stovepipe
decision-support systems.
The right way to implement a chosen strategy is to build a BI decision-support
environment in which all BI target databases are integrated and reconciled. When
building this environment, it is critical to perform the common data transformations
for all BI target databases only once and to reconcile these data transformations
back to the operational source files and source databases. This will demonstrate the
validity of the data in the various BI target databases. It is also important to
reconcile all the data across the different BI target databases in order to
demonstrate the data consistency among the various BI target databases. Both
reconciliation processes are best accomplished with a coordinated ETL effort for all BI
target databases, as illustrated in Figure 9.2.
Figure 9.2. Integrated BI Implementation Strategy
The most important ETL rule for an integrated BI
implementation strategy is to share one coordinated ETL
process. This is what differentiates BI from a traditional
decision-support approach.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
279/631
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
280/631