Microsoft Office Tutorials and References
In Depth Information
Note The careful reader should have noticed that all the efforts we have made up to now to
create a data model for the AdventureWorks database, are—in reality—in the direction of a data
warehouse built with data marts. This is not accidental: the rationale behind it is that data marts
are by far the best data model to query. Therefore, if we already have a data mart built by our IT
department, it is far better to query it than to try to reproduce our personal data mart.
Moreover, we figure that most of the reporting needs in the company are already fulfilled by
the data warehouse. When you get your data from there, chances are better that personal
reports will be comparable with the corporate ones. If, on the other side, you create your
personal reports from scratch, you are in danger of producing computation that differs from
that produced by the data warehouse.
Last, but not least, is the fact that by directly querying the OLTP system, we rely on its data
model to remain the same over time. Although people building the data warehouse know
that their data structure is queried by analysts, OLTP programmers normally do not think
that someone is poking around at their data, and so they feel free to change data structure
as their needs change, as long as their software works. However, if the underlying OLTP data
model changes, you discover it only when someone refreshes your reports for the first time.
Chances are that you would need to perform a lot of work to re-create the data model in
Which Database Is the Best to Query?
Deciding whether is it better to load data from the OLTP system or the data warehouse is
not easy. Using the data warehouse, you receive data already cleaned up and organized,
which reduces the hard work of creating your personal data model. On the other hand,
data is already organized, so you cannot model it as you would like because a lot of
operations have been already carried out.
If you have a data warehouse, we suggest that you start using it. If this is enough to
satisfy your reporting needs, you have completed your work with a minimum of effort.
If, on the other hand, you still lack some information, you should import new data into
PowerPivot tables and try to relate it with the data warehouse facts and dimensions.
Directly querying the OLTP should be seen as a very last resort because the work of
creating a clean data model from an OLTP database is not easy. You could spend most
of your time cleaning the model, reducing the time you might prefer to dedicate to
analyzing the data.