Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
backup, the data for all days in the partition being backed up would not
be available.
- A big drawback of this strategy is that if the table is partitioned by a
date column for backup purposes (which means it is clustered by the date
column), it cannot be clustered in any other way for access purposes. This
can affect performance when running the reports and queries, unless
database parallelism is used.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
431/631
[ Team LiB ]
Monitoring the Utilization of Resources
You must continuously monitor the utilization of various resources in a BI decision-
support environment, especially the utilization of computers, networks, and
personnel. If any one of these resources is neglected, it may potentially become a
bottleneck for the BI applications.
Computer Utilization
Computer utilization includes the central processing unit (CPU), input/output (I/O)
channels, random access memory (RAM), direct access storage devices (DASDs) or
disk drives, and other related hardware. These devices should be dedicated for the
BI decision-support environment and should not be shared by other applications.
Since utilization of these devices increases over time, they should be monitored.
It is vitally important to use appropriate monitoring and
alert utilities that will detect any resource problem and
sound an alarm in case of an actual or pending system
failure or resource shortfall. Selecting an appropriate
monitoring utility is especially critical in a distributed
environment.
Network Utilization
Network utilization may be a relatively minor issue for the day-to-day execution of BI
applications, but it may be a big issue for the ETL process. One large
telecommunications organization found out that even with access to the latest and
greatest communications technologies, it had insufficient bandwidth to transmit its
source data from the mainframe to the database server to populate its BI target
databases in a timely fashion.
When monitoring your network utilization, also consider the following points:
Bandwidth may be a serious problem for BI applications that frequently involve
several levels of drill-down access into detailed data. Queries after queries may
be executed by many people from many locations, each potentially returning
huge amounts of data across the network.
If bandwidth is an ongoing problem, one potential solution is to move toward a
distributed BI implementation and away from a centralized strategy. However,
if a lot of communication and integration among the distributed BI target
databases is required, this solution could make the situation worse.
Personnel Utilization
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
432/631
A BI decision-support environment is a high-maintenance environment. This is
especially true during the initial development and deployment of the first few BI
applications. It is also true to the extent that manual processes are used during or
after the ETL cycles to validate and analyze load statistics produced by the ETL runs.
A BI decision-support environment requires dedicated support from a number of IT
personnel, such as:
Application developers
Data administrators
Database administrators
Hardware and operating system specialists
Middleware specialists
Network administrators
Good technicians are often hard to find. Many technicians want to work on BI
applications, especially the multidimensional ones, because these challenging
applications involve new technologies (and thus they look good on a résumé).
However, because the operational systems still need to be supported— since they are
more mission-critical than BI applications—a "tug of war" over available staff is often
the result.
This is especially true for database administrators who already have a full plate
supporting the operational systems. Some senior database administrators have to be
released from those duties to become the chief designers and managers of the BI
target databases. These database administrators are responsible for designing,
building, monitoring, tuning, and maintaining the BI target databases and, to some
extent, the database access calls from the BI application programs and access and
analysis tools (e.g., writing passthrough queries). In addition, they have to be
concerned with monitoring and managing the increasing data volumes and
increasing database usage, which will require even more of their time. Redistributing
the workload of database administrators is a culture shift and an issue in many
organizations that perceive the database administrator's function as nothing more
than running DBMS maintenance procedures on production databases.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
433/631
[ Team LiB ]
Growth Management
By conservative estimate, the data in a BI decision-support environment doubles
every two years. The good news is that the cost per query for most BI decision-
support environments goes down with properly managed growth. The bad news is
that the overall cost climbs, assuming that more and more business people use the
BI decision-support environment as time progresses. The three key growth areas to
watch are data, usage, and hardware.
Growth in Data
Growth in data means not only adding new rows to the tables but also expanding the
BI target databases with additional columns and new tables. Adding new columns to
a dimension table is not as involved as adding new dimension tables to an existing
star or snowflake schema, which usually requires the following:
Unloading the fact table
Adding another foreign key to the fact table to relate to the new dimension
table
Recalculating the facts to a lower granularity (because of the new dimension)
Reloading the fact table
BI target databases need a large amount of disk space, with workspace and indices
taking up as much as 25 to 40 percent of that space. In the relational world, the data
is only a fraction of the overall database size; a major portion of it is index space.
Indexing is required to provide better response time when enormous volumes of data
are read.
When calculating space requirements, it might be prudent
to use the standard engineering maxim: Calculate how
large the BI target databases will be (including indices),
and then triple those numbers.
As data volumes increase, there needs to be a plan to aggregate and summarize the
data as it ages. Business analysts rarely require the same level of granularity for
very old data as they do for recent data. Therefore, the level of granularity should
decrease with a moving calendar. For example, assume the business people want to
store ten years of historical data. They require monthly summary data by
department for two years but are satisfied with monthly summaries by region for the
remaining eight years. Before a new month is loaded into the BI target database, the
department-level data for the 24th month is summarized into regional totals and
rolled off into another fact table so that the 23rd month becomes the 24th month,
the 22nd month becomes the 23rd month, and so on.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
434/631
The following list contains some of the new technologies available to support the
massive data volumes and the analysis capabilities of these huge databases:
Parallel technologies
Multidimensional databases
New indexing technologies
Relational online analytical processing (ROLAP) tools
Distributed database maintenance tools and utilities
Growth in Usage
Another key growth area is usage. Organizations that have built successful BI
applications have often uncovered a pent-up need for information throughout the
organization. This need translates to more business people using the existing BI
applications and asking for new ones. The number of business people accessing the
BI target databases can easily double or triple every year, which drives up growth in
usage exponentially. Since different business people want to see different data and
look at it in different ways, they want to slice and dice the data by new business
dimensions, which increases the data volume. Although the data volume is a far
more critical factor in determining processor requirements, the number of people
accessing the BI target databases is equally important.
Technicians view growth in usage as something negative. Managers, however, think
of growth in usage as something positive, as long as there is a return on investment
(ROI). Purchasing new hardware or updating existing hardware to handle the growth
may not be a concern if the organization is making a sizable profit due to better
decision-making capabilities with the BI applications. Therefore, growth in usage
may mean that the BI strategy is working.
BI target databases are by nature read-only and grow-
only. Therefore, the key is to stop trying to conserve disk
space if the BI applications and BI data are helping the
organization make a profit.
Growth in Hardware
Given the information about the growth in data and the growth in usage, it should be
obvious that scalability of the BI hardware architecture is key. But before you plan
five years ahead, remember that the hardware cost is only one part of the total BI
cost. Look at a planning horizon of 12 to 24 months; it is best to start small but also
plan for long-term growth. Consider the following factors.
Keep in mind the capacity threshold of your BI platform. If you exceed that
capacity, you have to add more processors, I/O channels, independent disk
controllers, and other high-speed components to keep the BI decision-support
environment at an acceptable level of performance.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
435/631
Of all the BI hardware, the BI server platform is the most important. When
ordering new hardware of any kind, there must be enough lead time to have
the equipment delivered, tested, and prepared for the development and
production environments.
Parallel technology is an absolute must for VLDBs. The ability to store data
across striped disks and the ability to have multiple independent disk
controllers play an enormously important role in the performance of processes
running against the BI target databases.
The Transmission Control Protocol/Internet Protocol (TCP/IP) is appropriate for
most hardware platforms. TCP/IP is rapidly becoming a standard for scalability,
growth considerations, and multiplatform environments.
Consider the advantages of a data mart approach with separate BI target
databases. This approach permits scalability in smaller and less expensive
increments.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
436/631
[ Team LiB ]
Implementation Activities
The activities for implementation do not need to be performed linearly. Figure 15.5
indicates which activities can be performed concurrently. The list below briefly
describes the activities associated with Step 15, Implementation.
Plan the implementation.
Set the implementation date and make sure that all the resources needed for
the implementation will be available. Depending on the progress you have
made, the lessons you have learned, and the difficulties you have encountered,
you may want to roll out the BI application to the business community in
phases. Start with a small group of business people, learn from the experience,
and modify your approach if necessary (e.g., increase the time for training or
change the security measures) before making the BI application available to
more people.
If the BI application has any organizational impact, prepare to make those
organizational changes (e.g., business process improvement changes or shifted
roles and responsibilities).
1.
Set up the production environment.
In most large organizations, strict procedures have to be followed to prepare
the production environment.
– Set up the production program libraries (ETL, application, meta data
repository).
1.
– Create the production databases (BI target databases, meta data
repository database).
2.
– Grant appropriate access authority on the production databases.3.
– Grant appropriate access authority to developers, operations staff, and
business people to execute programs from production program libraries.
4.
– Write operating procedures for the operations staff with instructions for
running the ETL process, as well as the regularly scheduled application
report jobs.
5.
– Prepare a reference guide for the help desk staff and the business people
with instructions on how to use the BI application.
6.
– Determine production security levels for all components of the BI
application.
7.
2.
Install all the BI application components.
Move all ETL programs, application programs, and meta data repository
programs to their respective production libraries.
3.
Set up the production schedule.4.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
437/631
All ETL programs, application report programs, and meta data repository
programs that will run on a regular basis have to be set up on the job
scheduler. The ETL job schedule has to include the meta data programs that are
part of the ETL process (e.g., capture load statistics, reconciliation totals, data
reliability factors).
4.
Load the production databases.
Load the BI target databases by running the initial load process, followed by
the historical load process. Also load the meta data repository with meta data
from your various meta data sources such as spreadsheets, computer-aided
software engineering (CASE) tool, ETL tool, and online analytical processing
(OLAP) tool.
5.
Prepare for ongoing support.
Establish a schedule for on-call emergency support. Schedule regular backups
as well as occasional database reorganizations for all production databases.
Plan to use the DBMS-provided utilities for these database maintenance
activities. In addition, plan to monitor performance, growth, usage, and quality
as part of the ongoing database maintenance activities. Periodically review and
revise capacity plans for processors, disk storage, network, and bandwidth.
6.
Figure 15.5. Implementation Activities
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
438/631
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
439/631
[ Team LiB ]
Deliverables Resulting from These Activities
Production ETL program library
All fully functioning ETL programs and scripts should reside in the production
ETL program library.
1.
Production application program library
All fully functioning access and analysis programs and scripts should reside in
the production application program library.
2.
Production meta data repository program library
All fully functioning meta data repository programs and scripts should reside in
the meta data repository program library.
3.
Production BI target databases
The data definition language (DDL) and data control language (DCL) SQL
statements are run in the production environment to build the production BI
target databases. The ETL initial load process and the ETL historical load
process are run to populate these production BI target databases.
4.
Production meta data repository database
The DDL and DCL statements for the meta data repository are run in the
production environment to build the production meta data repository database.
The meta data migration programs and tool interface programs are run to
populate the production meta data repository database with business meta
data, technical meta data, and ETL meta data (load statistics, reconciliation
totals, data quality metrics) from the initial load and historical load processes.
5.
Production documentation
Production documentation for the BI application includes:
- Operating procedures for operations staff covering the ETL process and
all scheduled application report jobs
- Reference guide for the help desk staff and the business people with
instructions on how to use the BI application
6.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
440/631