Database Reference
In-Depth Information
pressure on the primary OLTP system while ensuring that the reporting
needs are being met. It allows you to customize the tables being used by
the reporting system to combine the data sets, thereby satisfying the
queries being run in the most efficient way possible. Again, this means in-
curring overhead to store data that is already being stored, but often the
trade-off is worthwhile in terms of performance both on the OLTP system
and the reporting system.
Now let's look at OLAP systems, which are used primarily for decision
support and reporting. These types of systems are based on the concept of
providing a cube of data, whereby the dimensions of the cube are based
on fact tables provided by an OLTP system. These fact tables are derived
from the OLTP versions of data being stored in the relational database.
These tables are often denormalized versions, however, and they are opti-
mized for the OLAP system to retrieve the data that eventually is loaded
into the cube. Because OLAP is outside the scope of this topic, it's enough
for now to know that if you're working on a system in which OLAP will be
used, you will probably go through the exercise of building fact tables that
are, in some respects, denormalized versions of your normalized tables.
When identifying entities that should be denormalized, you should rely
heavily on the actual queries that are being used to retrieve data from these
entities. You should evaluate all the existing join conditions and search ar-
guments, and you should look closely at the data retrieval needs of the end
users. Only after performing adequate analysis on these queries will you be
able to correctly identify the entities that need to be denormalized, as well
as the attributes that will be combined into the new entities. You'll also
want to be very aware of the overhead the system will incur when you de-
normalize these objects. Remember that you will have to store not only the
rows of data but also (potentially) index data, and keep in mind that the
size of the data being backed up will increase.
Overall, denormalization could be considered the final step of the nor-
malization process. Some OLTP systems have denormalized entities to im-
prove the performance of very specific queries, but more than likely you
will be responsible for developing an additional data model outside the ac-
tual application, which may be used for reporting, or even OLAP. Either
way, understanding the normal forms, denormalization, and their implica-
tions for data storage and manipulation will help you design an efficient,
logical, and scalable data model.
Search WWH ::




Custom Search