Microsoft Office Tutorials and References
In Depth Information
Querying the Data Warehouse
To see why we recommend querying the data warehouse instead of the OLTP database, we
now show you some queries made against the AdventureWorks data warehouse. You can
compare these with the previous queries.
Let us say that you want to perform an analysis of customers, products, geography, and time.
You have already seen that only one of these entities (customers) is stored in several distinct
tables in the OLTP, so you can easily imagine how complex it might be to load four of them
plus the fact tables. If you have a data warehouse available, you need to import only the
tables shown in Table 4-2 into PowerPivot.
TABLE 4-2 Tables to load from the data warehouse.
Dimension containing all the products.
Dimension containing all the customers.
Dimension containing the territories where AdventureWorks sells items.
Time dimension. We will cover time dimension later in the topic.
Fact table, containing the sales made over the Web.
Fact table, containing the sales made by resellers.
You can already appreciate, from this simple list of tables to import, that their number is
surprisingly low when compared with the number of tables that you would have needed to load
if you had tried to perform the same kind of analysis directly from the OLTP system.
Moreover, because relationships are easily apparent in a star schema data model, PowerPivot
can detect them during the loading process. You can create a report for the sales of a specific
model of product to people who have a specific occupation, slicing data by year and
geography, with just a few clicks.
You can see such a report in Figure 4-29, and although we do not provide here a complete
description of how to build it, we strongly recommend that you try to reproduce it on your
personal computer to get a real feeling of how easy it is. If you want to see the final sample
report for this scenario, you can find it in the companion workbook CH04-05-DataMart.xlsx.
FIguRE 4-29 A query against the data warehouse provides great reports in just a few clicks.