Microsoft Office Tutorials and References
In Depth Information
The really interesting part of this section is not in the dashboard itself but in the fact that
when you convert a PivotTable into formulas, you can use data coming from PowerPivot as
the first layer of a more complex workbook, which uses the original data to provide values,
indicators, and other information that can be later processed with the full power of Excel.
using Complex Queries as Linked Tables
Let us suppose that you have an OLAP query that produces interesting results that you want
to import to PowerPivot. If the results are already present in the query, you know that you can
easily reproduce the query in MDX using the designer and directly import data into PowerPivot.
Nevertheless, PivotTables are frequently used as the source of data for more complex or
customized calculations, which might be not handled by the OLAP cube.
In Figure 8-23, you can see a simple PivotTable that shows quantity and sales for a product
category (Bikes) divided into months. We added two columns that compute the contribution
of every month to the year total as a percentage. You can see this example in the companion
FIguRE 8-23 An Excel worksheet that mixes OLAP queries and Excel calculations.
Now, let us suppose that you want to import this data to PowerPivot. Obviously, because the
worksheet is not the result of a simple OLAP query, you cannot import the values directly to
PowerPivot by using the MDX query designer. You have no way to express the complexity of
the Excel computations in an MDX query by using just the built-in query designer.