Microsoft Office Tutorials and References
In Depth Information
If you need to create dynamic sets, you need to directly query the Sandbox cube and leverage
the full power of MDX. It is not an easy task, and to get the best from it, you need to know the
MDX language. Nevertheless, it exploits an analytic power that cannot be reached by using
the standard PowerPivot user interface.
The Sandbox OLAP cube contains one dimension and one fact table for each PowerPivot
table. The dimensions contain the fields used to perform slicing, whereas the fact tables
contain the values that can be aggregated. In our example, you have a DimProduct fact
table, which lets you count the products (count is a measure that can be shown in cells),
and you have a DimProduct dimension, which contains the attributes used to slice data.
With a bit of knowledge of MDX, you can define a new set called Top 10 products with this
[Measures].[Sum of OrderQuantity]
We do not have space in this topic to explain MDX in detail, so for the sake of this example
it is enough to understand that the TopCount function call returns the first 10 products after
having sorted them by OrderQuantity.
The real power of this feature is that you do not know in advance which these 10 products will be.
You ask the OLAP cube to compute them and to return the set of those 10 products, whichever
they will be.
You can now create a PivotTable and select this set to discover which these products are, as you
see in Figure 8-38.
FIguRE 8-38 The top 10 products set at work.