Microsoft Office Tutorials and References
In Depth Information
understanding OLTP and Data Marts
If you think that many of the preceding topics were pretty complex, well, you are definitely
right! The task of creating a data model is taxing. Many decisions need to be made, and it
is normal to be missing some of the information you need. Creating a model is more an art
than a science: it requires a lot of experience and foresight. Nevertheless, a good data model
is the key to producing good analysis, and this is why we believe that a good PowerPivot
analyst needs to understand data modeling.
As you have already discovered, there are at least two different ways to look at a data model:
■ Technical view This is a very compact and normalized way to look at data. This is the
best way to model data when it needs to be used by software. Normalized data models
are used by OLTP software, which is—more or less—the software that handles
everyday tasks for a company. Handling such models is not your task—there are technicians
who do it all day long. Nevertheless, you will probably need to read data this way some
time or another.
■ User view This is a highly denormalized view of data, much simpler to query and to
analyze. As a side effect, it contains a lot of redundancy, generates bigger databases,
and is very hard for software to update. Denormalized data models are normally present
when the company has a data warehouse—that is, a database that contains all the data
about the company in a structured way, useful for querying.
The task of building a data warehouse usually involves several years of work of highly
specialized technicians, whose task is to build both the data warehouse model and the
software systems that daily ill it with the information found in the various OLTP systems
around the company.
This is not a topic about data warehouse modeling, so we are not interested in a complete
discussion about how to model a data warehouse. Instead, we would like to give to you the
feeling of what a data warehouse is and the differences between querying an OLTP system,
as you did up to now, and querying a data warehouse. We are using the data warehouse built
on top of AdventureWorks. But, before we look at the examples, it is worth spending some
time describing the difference between an operational database and a data warehouse,
which requires us to describe how a data warehouse is composed.
Data Marts, Facts, and Dimensions
The AdventureWorks data warehouse follows the Kimball methodology and is made up of
tables called facts and dimensions . Facts and dimensions are tied together into business units
that are called, in Kimball’s terminology, data marts . Let us quickly review what facts and