Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
A star schema has two, and only two, levels: the fact table and a series of single-
level dimension tables. Fact tables have the following characteristics:
A fact table represents a critical business event (a business activity or
transaction, such as a sale or a claim).
The facts are the quantifiable aspects of the business event; that is, they are
columns in the fact table.
A fact table links to its related dimension tables (business objects, such as
customer or product).
A fact table has a long composite key comprised of the primary keys of the
related dimension tables (which are foreign keys in the fact table).
A number of highly redundant fact tables may exist for a given subject area.
Each fact table could contain a different aggregation level of the same data. For
example:
- Sales facts by store by region by date
- Sales facts by product by store by date
- Sales facts by customer by region by date
Fact tables are long and narrow: the tables have an immense number of rows
(long), but there are relatively few columns in the tables (narrow).
Dimension tables have very different characteristics.
Dimension tables are business objects, which represent the different
perspectives from which the facts in a fact table can be viewed and analyzed.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
261/631
Dimension tables usually have a one-attribute primary key.
Dimension tables are denormalized, which means that data belonging together
from a specific business perspective, such as a roll-up hierarchy, is grouped
together into one table. This produces some redundant data values, which is
acceptable in this design schema.
Dimension tables are short and wide: the tables have relatively few rows
(short), but there are many columns in the tables (wide).
Whenever possible, dimension tables should be shared by the fact tables
(conformed dimensions).
One dimension is always a time dimension with attributes describing the
timestamp, such as calendar year, quarter, season, fiscal period, or accounting
period. Some other examples of common dimension tables are customer,
product, policy, sales representative, region, and store.
Most multidimensional DBMSs effectively deal with the optimization of large multi-
table JOINs. One method for determining whether the DBMS is resolving the query
efficiently is to look at the optimized plan for the query. For example:
If the fact table is the last table JOINed, this is an indicator of optimization. If
the fact table appears to be somewhere in the middle, or even somewhere
toward the beginning, the DBMS may not be resolving the JOIN optimally,
unless it uses more sophisticated JOIN algorithms.
If the DBMS does not use Cartesian product JOINs, the DBMS may take the
qualifying row keys and apply them against a composite fact table index, or it
may apply them via an index intersection against multiple fact table single-
column indices.
In either case, verify that your DBMS is executing multidimensional queries in the
most efficient manner since your performance depends on it.
The star schema is the most popular database design schema for BI applications for
a variety of reasons.
It yields the best performance for trend analysis queries and reports that
include years of historical data.
It provides maximum flexibility for multidimensional data analysis.
It is supported by most of the relational DBMS vendors with modifications to
their DBMS optimizer.
Its simplicity makes complex data analysis much less difficult than with a
standard normalized design. It is much easier to ask questions such as the
following:
- Which insurance broker is giving us the most or the least lucrative
business?
- What are the most frequently occurring types of claims from this
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
262/631
insurance broker?
- When are these claims occurring?
The preceding questions are typical drill-down questions (asking for more detailed
data) and typical roll-up questions (asking for more summarized data).
The Snowflake Schema
A snowflake schema is a variation of a star schema, except in a snowflake the points
of the star radiate into more points, as shown in Figure 8.3.
Figure 8.3. Snowflake Schema
In snowflake schemas, the levels of the hierarchies in the dimension tables are
normalized, thereby increasing the number of tables. Table 8.2 lists the advantages
and disadvantages of snowflake schemas.
Table 8.2. Advantages and Disadvantages of Snowflake
Schemas
Advantages
Disadvantages
The size of the dimension tables is
reduced and data value redundancy is
avoided because parent-child
hierarchies are no longer collapsed.
The increased number of tables
may adversely affect query
performance because of the
necessary additional JOINs.
Application flexibility is increased.
Database maintenance effort is
increased because there are more
tables to maintain.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
263/631
[ Team LiB ]
Physical Database Design
Because BI applications usually require operational detailed data as well as
summarized data, and because they often need to store some or all of that data
redundantly, the size of some BI target databases can be enormous. Databases
approaching or exceeding one terabyte of data are called very large databases
(VLDBs). Designing VLDBs is a big challenge, and the day-to-day chores of
maintaining these VLDBs are demanding. Many difficult physical design decisions
need to be made, and some highly effective performance enhancements need to be
used. The following sections present some suggested guidelines.
Implementation Options
Almost every DBMS lets the database administrator choose from a number of
implementation options. Give considerable attention to selecting the right options
when implementing a BI target database. It takes experience to know which
combination of options will meet the desired performance level. Implementation
decisions include the following:
How much free space to choose
How much buffer space to declare
How large to set the blocksize
Whether to use any compaction technique
Physical Dataset Placement
Another basic issue that affects performance is the placement of the datasets.
Methods for achieving fast response include combinations of:
Storing frequently referenced data on fast devices.
Storing different aggregation levels on different platforms. For performance
reasons, it may be necessary to store aggregate data on distributed midrange
servers while keeping detail data on the mainframe.
Striping disks in an interleaved fashion to optimize input/output (I/O) controller
usage. Using lots of small disks instead of a few large disks, separating those
disks onto separate controllers, and writing the data across devices increases
I/O throughput.
Placing datasets in a way that lengthy seeks are avoided when possible.
Selecting address and search schemes that require few seeks, preferably only
one per retrieval.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
264/631
Running multiple operations in parallel.
Also consider whether to separate indices from data and put them on separate disks.
Partitioning
Ensure that tables are partitioned effectively across multiple disks. This is
particularly important for VLDBs where fact tables can reach several hundred
gigabytes. Partitioning allows the data of one "logical" table to be spread across
multiple physical datasets. The physical data distribution is based on a partitioning
column, which is most commonly date. Since a partitioning column must be part of
the table's primary key, the partitioning column cannot be a derived column, and it
cannot contain NULL values. Partitioning enables you to back up and restore a
portion of a table without impacting the availability of other portions of the same
table that are not being backed up or restored.
Clustering
Define cluster table requirements, and physically co-locate related tables on the disk
drive. Clustering is a very useful technique for sequential access of large amounts of
data. Clustering is accomplished through clustering indices that determine in which
sequential order the rows in the tables are physically stored in the datasets. Ideally,
you want to cluster the primary key of each table to avoid page splits, that is, to
make sure that new rows inserted into the tables will be stored sequentially on the
disk according to the columns in their clustering index. Using this technique can
dramatically improve performance because sequential access of data is the norm in
BI applications. When the rows of a table are no longer stored in the same order as
its clustering index (data fragmentation), performance will suffer and the table has
to be reorganized.
Indexing
There are two extreme indexing strategies, neither of which is advisable: one
strategy is to index everything, and the other is to index nothing. Instead of veering
to these extremes, index those columns that are frequently searched and that have a
high distribution in values, such as Account Open Date. Do not index columns that
have a low distribution in values, such as Gender Code.
Once you have decided which columns to index, determine the index strategy to use.
Most DBMSs provide several access methods to choose from, either sequential access
or direct access using any of the following well-known indexing algorithms:
B-tree
Hash
Inverted file
Sparse
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
265/631
Binary
Consult with your DBMS vendor to choose the most optimum access method
(indexing algorithm) for the DBMS product you are using.
Reorganizations
Occasionally you will need to reorganize the databases because incremental loads
will fragment the datasets over time, and inserted rows will no longer be stored in a
logical sequence. This fragmentation may result in long data retrieval chains, and
performance can drop off significantly. Most DBMSs provide reorganization routines
to rearrange the fragmented database in order to reclaim space occupied by deleted
data or to move records from overflow areas into free space in prime data areas.
The basic activities involved in reorganizing a database are to copy the old database
onto another device, reblock the rows, and reload them. This is not a trivial effort for
BI target databases. The good news is that all DBMSs can perform a partial
reorganization routine on database partitions, which is another reason for the
database administrator to partition the BI target databases.
Backup and Recovery
Since software and hardware may fail, it is necessary to establish backup and
recovery procedures. DBMSs provide utilities to take full backups as well as
incremental backups. Many organizations are under the misguided impression that
the BI target databases can always be recreated from the original source data. They
neglect to realize that it may take a very long time to recreate the BI target
databases if they have to rerun all the initial and historical extract/transform/load
(ETL) programs—assuming the original source files are still available.
Disaster recovery is also an issue for BI applications. If the backup tapes or
cartridges are destroyed during a disaster, it could be difficult to recreate your BI
target databases, and it could take a very long time (if recovery is possible at all).
For this reason, many companies choose to store their database backups in remote
locations.
Parallel Query Execution
To improve the performance of a query, break down a single query into components
to be run concurrently. Some DBMS products offer transparent parallel execution,
which means you do not need to know how to break down a query into components
because the DBMS does it for you. Performance is greatly increased when multiple
portions of one query run in parallel on multiple processors. Other applications of
parallel query execution are loading tablespace partitions, building indices, and
scanning or sorting tables. Parallel processing is a very important concept for BI
applications and should be considered whenever possible.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
266/631
[ Team LiB ]
Database Design Activities
The activities for database design do not need to be performed linearly. Figure 8.4
indicates which activities can be performed concurrently. The list below briefly
describes the activities associated with Step 8, Database Design.
Review the data access requirements
The database administrator must review the data access and analysis
requirements (reports, queries), which were analyzed and finalized during Step
6, Application Prototyping. He or she also has to review the prototyping results
with the application lead developer to help determine the most appropriate
design schema for the BI target databases.
1.
Determine the aggregation and summarization requirements.
Before committing to the final design schema for the BI target databases, the
database administrator needs to finalize the data aggregation and
summarization requirements with the business representative and the
application lead developer. Pay close attention to aggregation and
summarization explosion and to data explosion in general. Business people
often ask for data "just in case" they will need it some day, and then they rarely
use it, if ever.
2.
Design the BI target databases.
The widespread claims that all BI applications are only about multidimensional
analysis and multidimensional reporting are not true! For example, some
financial analysts (statisticians) reporting to the CFO or the CEO will
emphatically state their requirements similar to this: "I need to be able to ask
any question of any detailed data in any way. Don't try to box me into any
predetermined reporting patterns. I have none!" These analysts need total ad
hoc flexibility against historical detailed data and are always willing to give up
performance, even if it means that their queries will run for hours or overnight.
Although these types of analysts are definitely in the minority, they do exist,
and you must take their data access requirements into consideration. Therefore,
while the designs of most of your BI target databases will be based on a
multidimensional schema, some will be based on an entity-relationship schema.
Database designs are documented as physical data models.
The data access requirements and the data aggregation and summarization
requirements will determine the most appropriate database design. If there are
obvious reporting patterns or if the requirements ask for slice and dice analysis
capabilities, then the most appropriate database design is a multidimensional
one. If there are no reporting requirements and if the business analysts insist
that they need ad hoc access to their detail data, then the most appropriate
design is the entity-relationship design, which is more normalized with few or
no aggregations or summaries.
These are not the only two design schemas applicable for BI target databases.
For some types of access and analysis requirements, a hybrid design may be
3.
4.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
267/631
the most appropriate.
Design the physical database structures.
Clustering, partitioning, indexing, and appropriately placing the datasets are
the four most important characteristics of physical database design. The
database administrator should cluster the most frequently used tables in order
to reduce the disk arm movement. He or she must also determine where to
place the datasets and how to partition tables across multiple disks. Finally, he
or she has to select an index strategy.
4.
Build the BI target databases.
The physical databases are built when the data definition language (DDL) is run
against the DBMS. The database administrator uses the DDL to describe the
database structures (e.g., storage groups, database partitions) to the DBMS.
Database security is established when the data control language (DCL) is run
against the DBMS. In standard relational databases, security is imposed at the
table or view level. Because of the dimensional nature of BI target databases,
the capability to drill down into detail data, sometimes across databases,
presents an often-overlooked security risk.
Grant database authority either to individuals or to groups into which
individuals have been assigned. Managing security on an individual level can
quickly become a maintenance nightmare, which is why most organizations
prefer to set up group identifiers (group IDs). Each group ID is granted some
form of create, read, update, delete (CRUD) access to the tables. An audit trail
can then show which specific "user ID" under which group ID accessed the
database. If there is a breach of security, the "infiltrator" can often be located
through this audit trail.
5.
Develop database maintenance procedures.
Once the database goes into production, it will be important to set aside time
for taking database backups or reorganizing fragmented tables. Therefore,
establish procedures to address database maintenance functions.
6.
Prepare to monitor and tune the database designs.
Once the BI application is implemented, the BI target databases have to be
monitored and tuned. The best database design does not guarantee continued
good performance, partly because tables become fragmented and partly
because actual usage of the BI target databases changes over time. Monitor
performance of queries at runtime with a performance-monitoring utility that
has diagnostic capabilities. It does not help to know that performance has
degraded without knowing the causes. Diagnosing performance problems is
usually much harder than discovering them.
7.
Prepare to monitor and tune the query designs.
Since performance is such a challenge on BI applications, you must explore all
tricks of the trade to address this problem. Parallel query execution is one of
those tricks that could boost query performance.
8.
Figure 8.4. Database Design Activities
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
268/631
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
269/631
[ Team LiB ]
Deliverables Resulting from These Activities
Physical data model
The physical data model, also known as the logical database design, is a
diagram of the physical database structures that will contain the BI data.
Depending on the selected database design schema, this diagram can be an
entity-relationship diagram, a star schema diagram, or a snowflake diagram. It
shows tables, columns, primary keys, foreign keys, cardinality, referential
integrity rules, and indices.
1.
Physical design of the BI target databases
The physical database design components include dataset placement, index
placement, partitioning, clustering, and indexing. These physical database
components must be defined to the DBMS when the BI target databases are
created.
2.
Data definition language
The DDL is a set of SQL instructions that tells the DBMS what types of physical
database structures to create, such as databases, tablespaces, tables, columns,
and indices.
3.
Data control language
The DCL is a set of SQL instructions that tells the DBMS what types of CRUD
access to grant to people, groups, programs, and tools.
4.
Physical BI target databases
Running (executing) the DDL and DCL statements builds the actual BI target
databases.
5.
Database maintenance procedures
These procedures describe the time and frequency allocated for performing
ongoing database maintenance activities, such as database backups, recovery
(including disaster recovery), and database reorganizations. The procedures
should also specify the process for and the frequency of performance-
monitoring activities.
6.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
270/631