Microsoft Office Tutorials and References
In Depth Information
The really (and we mean really ) tireless reader might try to reproduce the same report by
means of directly querying the OLTP system. Believe us, he would need to spend more time
doing that exercise than reading the rest of this topic.
PowerPivot and the Data Warehouse
If the only strength of PowerPivot was the fact that you can build your own reports
directly on the data warehouse, the very same purpose could be reached by simply
connecting to an Analysis Services OLAP cube, which is probably already present in
the data warehouse infrastructure.
Nevertheless, in PowerPivot, this report can be the starting point of your analysis. You
can easily add more information by loading it from other tables that you already have
in other databases or that reside in simple Excel workbooks where, for example, you
might think out the corporate budget.
By using the current data warehouse data model and adding your personal new data, you
can build a new data model over the existing data warehouse, adding information and
intelligence where you need it. We believe that when a company begins using PowerPivot,
the quality of the reports rise. In fact, data that is not yet in the data warehouse can be
used immediately by analysts who, in turn, can give good advice to the BI specialists, who
can then create a better integration of data in the data warehouse.
In our view, PowerPivot does not replace the corporate data warehouse. PowerPivot
gives analysts the ability to extend the existent data warehouse and reach levels of
analysis never seen before.
In the rest of this topic, you query both the OLTP and the Data Warehouse databases,
depending on the kind of analysis you want to perform. In later chapters, you integrate
the data warehouse with your personal data.
Discovering Advanced Types of Relationships
Although standard relationships (that is, relationships between two tables based on a single
column, which holds the same value in both tables) are the most popular in relational
databases, some other types of relationships can be used to model information and are not so
easy to detect and model.
We now discuss some of these other types, using the AdventureWorks data warehouse
as an example.