Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
because of the additional adding and dropping
of indices and maintaining the index
database.
When should the databases be reorganized?
- When 5 percent of the records are inserted
or deleted.
Removing fragmentation improves performance.
9.
Extract/Transform/Load
Design
Remember that the more data elements you include
in the scope, the more transformations will need to
be coded, and the longer the ETL process will run.
Let your transformation requirements drive the
selection of an ETL tool, not marketing hype from
the vendors.
Create the load files for all BI target databases for
the same load period at the same time from the
same ETL process. Loading one BI target database
only to turn around and read it again to extract,
transform, and load another BI target database is
too time consuming and unnecessary.
Share one ETL process and staging area. Do not
allow each data mart to have its own ETL process
because that produces stovepipe systems. While a
staging area can be decentralized (running different
ETL functions on different platforms), the only valid
reasons for decentralizing are due to different types
and locations of source files and source databases,
as well as the functions, capabilities, and licensing
terms of the ETL tool. Nevertheless, the ETL staging
area should be managed as one logical unit.
Include data quality metrics and reconciliation totals
in the ETL process design for every program that
moves or manipulates data.
Don'ts
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
581/631
Don't develop the ETL process flow without the
assistance and participation of the database
administrator. Database administrators often know
about tricks of the trade that can help streamline
the ETL process flow.
Never limit the ETL process to the technical
conversion rules. ETL is much more than converting
the data type and length of your source data
structures to your target data structures. It also
includes transformation logic for business data
domain rules and business data integrity rules.
Don't automatically delete rows from the BI target
databases after discovering that an operational
record was deleted from a source file or source
database. Develop business rules for the ETL
process, which specify when to propagate
operational record deletions into the BI target
databases and when not to propagate them.
Don't overlook aggregations and summarizations for
the data marts. These need to be incorporated
toward the end of the ETL process flow.
No matter how advanced the ETL tool is, don't rely
on it to know exactly how to populate the BI target
databases by following standard conversion rules.
Only the business people and IT understand the
business rules that are buried in the source data
and in the operational programs.
Tips and Rules of Thumb
About 80 percent of ETL work is in the "T"
(transform). It is the most complicated process of
any BI decision-support application. Be sure to
allocate enough time for designing the ETL
processes. The success of your BI initiative may
depend on it.
At a minimum, transformations should reconcile
different expressions of the same data from different
sources. Transformations should also enforce
business data domain rules and business data
integrity rules.
The biggest challenge in determining the correct
transformation and cleansing specifications is
finding people who understand the origin and
history of the source data. Do not limit yourself to
the business people. Look for programmers who
have been around for many years and who know the
history of some of the source files and source
databases.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
582/631
If loading past history is not required with the initial
implementation of the BI application, try to
postpone building the historical load process until
the next release. That will reduce the scope of the
project and speed up delivery of the BI application
release.
Turn off referential integrity during the ETL load
cycle, and turn it back on after the load has
completed to allow the DBMS to find referential
integrity violations. Look for tables in "check
pending."
Drop all indices during the ETL load cycle, and
recreate them after the load has completed.
10. Meta Data
Repository Design
Review and expand (if necessary) the meta data
repository design with every BI project. Be sure
your design can accommodate expansion.
Evaluate off-the-shelf meta data repository
products, which have the capability to integrate
business meta data with technical meta data. It may
be easier and faster to install a product than to
build custom software, especially when the schedule
is tight.
Follow up with client references provided by vendors
when licensing (buying) a meta data repository
product. Ask the vendors' clients what the vendor
products cannot do and what they don't like about
the products.
Design a reusable interface between the meta data
repository and other tools (CASE, ETL, OLAP, report
writers, other access and analysis tools)
Provide a context-sensitive online help function for
meta data. This feature is invaluable to business
people, who will use it to help them navigate not
only through the meta data repository but also
through the BI decision-support environment as a
whole.
Don'ts
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
583/631
Don't shortcut the meta data repository design by
modeling only the most common meta data entities.
When building a customized solution, design it to be
fully functioning, even though you may not
implement all of the features at once.
Don't fail to automate the integration process of
linking the business meta data with the technical
meta data. Linking (relating) those two types of
meta data manually is a labor-intensive process.
Don't forget to include meta data components for
capturing metrics, such as data reliability factors,
reconciliation totals, and load statistics.
Don't even consider any meta data repository
products that do not satisfy your mandatory meta
data requirements.
Don't forget to evaluate the vendors in addition to
their products. Vendor stability is important for
sustaining a meta data repository solution.
Tips and Rules of Thumb
Since the meta data repository is a database, many
tips and rules of thumb that are applicable to
database design are also applicable to meta data
repository design.
Start with a central meta data repository database
because distributed meta data repositories and
XML-enabled meta data solutions are more difficult
to build and maintain.
Start with an entity-relationship design rather than
an object-oriented design. They are easier to
implement and easier to comprehend.
About 90 percent of direct access to the meta data
repository will be from the business people and only
about 10 percent from the technicians. Meta data
helps business people understand the meaning of
the data, the quality of the data content, where the
data came from (source system), and how to use it.
Therefore, spend time on designing an easy-to-use
access interface to the meta data repository.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
584/631
11.
Extract/Transform/Load
Development
Run as many ETL programs in parallel as possible to
cut down on runtime. Since the ETL staging window
at most large organizations is very short (often only
a few hours per night), be prepared to run your ETL
process over several nights.
Produce reconciliation totals for record counts,
domain counts, and amount counts. These totals
should be stored in the meta data repository for
every load cycle.
Produce a detailed error accounting for source data
that failed the edit rules and was rejected, as well
as for source data that failed the edit rules but was
accepted. Errors should be categorized, such as
number of missing values, number of domain
violations, number of business rules violations, and
so on.
Perform rigorous testing with formal test plans, test
cases, and expected test results.
Involve the business representative in writing the
test cases and expected test results.
Use a stress test simulation tool to project estimated
performance numbers before running an actual
performance (stress) test with real data.
Use peer reviews or XP programming techniques for
quality control.
Don'ts
Developers should not test their own code;
however, they can test the code of other developers.
Don't limit the business representative and the
subject matter expert to testing only the access and
analysis portion of the BI application. Be sure they
are also involved in testing the ETL process.
Don't consider integration or regression testing
completed until all programs in the ETL process run
as expected from beginning to end. In other words,
don't just test individual modules until they are
error free, but test and retest the entire job stream
until it runs error free.
Never skip testing because you think you can fix
problems in the next release. If the BI target
databases are not loaded properly with correct data,
the BI application is of no use to the business
people.
Don't expect to cleanse the source data in the
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
585/631
operational systems. The operational systems staff
actually expect their dirty data to run successfully!
In many cases, modifying an operational system is
not cost-effective. On the other hand, old practices
and bad habits that produce these data quality
problems should be addressed. Ask your business
sponsor to make the owners of the operational
systems and the business executives aware of the
cost and effort it takes to cleanse their bad data for
the BI decision-support environment. Some general
data quality standards should be implemented
across the entire organization to avoid perpetuating
data quality problems.
Tips and Rules of Thumb
Organizations devote close to 80 percent of the BI
project time to back-end efforts, including labor-
intensive data cleansing. Although tools can help
with assessing the extent of data quality problems
in the operational systems, they cannot magically
turn bad data into good data.
Cleansing data is a time-intensive and expensive
process. Analyze, prioritize, and then choose your
battles since cleansing 20 percent of the enterprise
data may solve 80 percent of the information needs.
About 80 percent of the data transformation effort is
spent on enforcing business data domain rules and
business data integrity rules, and only about 20
percent of the effort is spent on technical data
conversion rules.
The most common symptoms of dirty source data
are data inconsistencies and overuse of data
elements, especially in old flat files, where one data
element can explicitly be redefined half a dozen
times or can implicitly have half a dozen different
meanings.
Why use automated software tools for data
transformation? Data-profiling tools can significantly
shorten the time it takes to analyze data domains.
ETL tools can perform data type and length
conversions and code translations in minutes, rather
than hours when done manually. However, note that
writing data-cleansing algorithms is still a manual
effort and must be performed before the ETL tool
can be utilized.
The ETL process will run into fewer problems if
extensive source data analysis is performed ahead
of time. Source data rules are usually discovered
proactively during requirements gathering, data
analysis, and meta data repository analysis. They
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
586/631
are discovered reactively during prototyping,
application development, ETL development, and
when loading the BI target databases during
implementation.
12. Application
Development
Let the business people define what types of
analyses they will perform. Let their analysis needs
drive the choice of tools to be deployed. And, let the
requirements of the tools define the schema in
which the data should be stored.
Keep abreast of the latest BI tools. Standards and
tools are evolving constantly, and new tools and
features leapfrog each other. The features that used
to clearly delineate OLAP strengths and weaknesses
(for example, indexing approaches, size barriers
and scalability, API standardization) have become
less important as tools mature and add features.
Determine and communicate the strengths and
weaknesses of each access and analysis tool,
whether it is a report writer, an OLAP tool, or
another query tool.
Consider selecting a product suite that combines
querying, reporting, and analysis capabilities for
multidimensional analysis. Querying, reporting, and
analysis are interrelated, interactive, and iterative
and can be implemented with one tool. A business
person should not have to switch between tools for
different types of analyses, such as from the "what"
analysis (query and reporting) to the "why" analysis
(OLAP).
Don'ts
Don't start with every possible dimension to satisfy
every conceivable multidimensional query. Keep in
mind that the more dimensions you have, the bigger
the database, the more granular the facts, the
longer it will take to precalculate the facts, the
longer the ETL process will run, and the longer it
will take to run reports and queries.
Don't plan to store every possible computation and
ratio (fact) just because you can.
Don't forget to stress test the access and analysis
components of the BI application. OLAP tools have
their limitations, and you must find those limitations
early in order to adjust your design before moving
the BI application into production.
Avoid having completely different production and
development platforms. In many organizations, the
development platform is very "rich" in resources,
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
587/631
but the production machines are very "meager." As
a result, performance is outstanding during
development and terrible in production.
Tips and Rules of Thumb
Find out how many dimensions the business people
commonly use for slicing and dicing.
- Two or three dimensions are easily grasped.
- Four dimensions require training and
practice for comprehension (the fourth
dimension is usually the time dimension).
- Five and six dimensions get difficult to
comprehend and to use effectively.
- Seven should be the maximum number of
dimensions used.
In order to optimize performance, store and use
precalculated facts for slicing-and-dicing analysis. A
simple query should be able to run in less than 5
seconds. Business people are not interested in
knowing about the delay in response time because
of the sizes of the tables or because of inefficient
indexing. They expect reasonable performance,
even for complex queries.
The complexity of queries run against the BI target
databases generally breaks down as follows:
- About 80 percent of the queries are simple.
- About 20 percent of the queries are
complex.
The breakdown of the complexity of reports run
against the BI target databases is generally the
opposite of that for queries.
- About 20 percent of the reports are simple.
- About 80 percent of the reports are complex.
Knowledge workers and business analysts executing
simple queries are like farmers—they harvest their
crops on a regular basis. In order to get better
performance, prebuild tables to satisfy these
routines.
Knowledge workers and business analysts executing
complex queries are like gold miners—they dig
many mines before they strike gold, so they need a
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
588/631
powerful platform to let them look for their data and
manipulate it.
13. Data Mining
Plan to incorporate data mining into the BI decision-
support environment. Although data mining can use
data from operational files and databases, BI target
databases usually provide the only place where data
has been cleansed and consolidated across
functional boundaries.
Work with sales or marketing groups to bring in
data mining technology. These groups are the most
likely groups to understand the business value of
data mining and may champion the data mining
effort.
Spend some time and effort researching data
mining and knowledge discovery techniques and
methods before selecting products or hiring
consultants. The technology itself takes various
approaches and employs high-level applied
mathematics, advanced statistics, and artificial
intelligence. Since few vendors offer a full range of
data mining techniques and methods, most will be
biased toward whatever is used in their products.
Hire consultants specializing in data mining to help
set up your data mining environment and to help
interpret the data mining results. Data mining is
difficult; it requires a statistical background to
interpret data mining results.
Start the data mining efforts with realistic
expectations. Setting expectations too high may
result in disappointment. It is commonly known that
your satisfaction depends on your expectations.
Don'ts
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
589/631
Don't forget to work with other departments to find
additional potential applications for data mining.
Sales and marketing are not the only departments
that can benefit; credit risk, manufacturing,
acquisition, billing, and human resources can also
utilize data mining.
Don't get fooled by software vendors who claim to
have data mining capabilities in their products,
when their tools are just query tools that require the
business analyst to be the analytical engine.
Don't believe software vendors who say that data
mining does not offer any real advantages.
Don't assume that you must have a BI decision-
support environment in order to implement data
mining. Also, don't assume that every BI target
database will be a suitable source for data mining.
Don't run data mining directly against operational
files and operational databases. The performance
impact on the operational systems would be
enormous.
Tips and Rules of Thumb
Beware of using operational data for data mining.
Operational data is often full of duplicates,
inconsistencies, and errors. Using this data could
throw off your data mining results.
Before building an analytical data model you have
to prepare the data by classifying the variables.
Variables could be discrete or continuous,
qualitative or quantitative. If you find variables that
have missing values, either eliminate the variables
or replace the missing values with "most likely"
values.
Compare your data mining results with industry
statistics on a regular basis. Industry statistics are
established periodically by using very large samples
of data.
When using data mining, one or more of the
following marketing opportunities should be realized
in order to cost-justify the activity:
- Cross-selling should be enabled or enhanced
as a result of data mining efforts.
- A large percentage of customers should be
retained. These customers would have left if a
defection pattern had not been noticed
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
590/631