Database Reference
In-Depth Information
You can find the example file for this chapter on this topic's companion Web site at
On the Web
What Is an OLAP Database
and What Can It Do?
The dominant database type in most organizations is the OLTP (online transaction processing) data-
base. Indeed, most of you are probably working some form of an OLTP database. This type of database
typically contains many tables, each table usually contains multiple relationships with other tables,
and records within any given table can be routinely added, deleted, or updated.
Although OLTP databases are effective in gathering and managing data, they typically don't make for
effective data sources for reporting. There are three main reasons for this:
➤
Complexity:
The large number of tables and relationships that can exist in an OLTP database
can leave you wondering exactly which tables to join and how the tables relate to each other.
➤
Volume:
OLTP databases normally contain individual records; lots of them, too. In order to
create any number of aggregate reports and views, you would have to run views that group,
aggregate, and sort records on-the-fly. The sheer volume of data in the database could very
well inundate you with painfully slow reporting.
➤
Consistency:
By its very nature, the records in a transactional database are ever-changing.
Building a reporting solution on top of this type of database will inevitably lead to inconsis-
tent results from month to month, or even from day to day.
Some organizations avoid these woes by building their reporting solutions on top of OLAP data-
bases. OLAP databases are data islands that are isolated from the hustle and bustle of transactional
databases. An OLAP database can help alleviate these problems in the following ways:
➤
Structured data:
In an OLAP database, all of the relationships between the various data
points have been predefined and stored in
cubes
. These cubes contain the hierarchical struc-
tures that allow for the easy navigation of available data dimensions and measures. With this
configuration, you no longer have to create joins or try to guess how one data table relates
to another. All of that complexity is taken care of behind the scenes, leaving you free to
develop the reports you need.
➤
Predefined aggregations:
The data in an OLAP database is not only organized, but it is
aggregated. This means that grouping, sorting, and aggregations are all predefined in OLAP
databases. In addition, OLAP databases make heavy use of indexes; a technique that allows a
database to search for records more efficiently. All of this amounts to reporting solutions that
are optimized to provided the reports you need as quickly as possible.
Search WWH ::
Custom Search