Database Reference
In-Depth Information
A word about MDX
When you are using a PivotTable with an OLAP cube, you're sending the OLAP database MDX (multi-
dimensional expressions) queries. MDX is an expression language that is used to return data from
multidimensional data sources (that is, OLAP cubes).
As your OLAP PivotTable is refreshed or changed, subsequent MDX queries are passed to the OLAP
database. The results of the query are sent back to Excel and displayed through the PivotTable. This
is how you can work with OLAP data without a local copy of a pivot cache.
When building calculated measures and calculated members, you need to utilize MDX syntax. This is
the only way the PivotTable can communicate your calculation to the back-end OLAP database.
The examples in this chapter use basic MDX constructs to demonstrate the functionality found in
Excel 2013. If you need to create complex calculated measures and calculated members, you need to
learn MDX.
That said, the topic of MDX is robust and beyond the scope of this topic. If after reading this section,
you want to learn more about MDX, consider picking up MDX Solutions: With Microsoft SQL Server
Analysis Services 2005 and Hyperion Essbase by George Spofford (John Wiley & Sons); an excellent
guide to MDX.
Creating calculated measures
A calculated measure is essentially the OLAP version of a calculated field. A calculated measure cre-
ates a new data field based on some mathematical operation that uses the existing OLAP fields.
FigureĀ 13-13 shows an OLAP PivotTable containing products along with their respective quantities
and revenues. It needs a measure that calculates average sales price per unit.
FigureĀ 13-13: To show average sales price per unit, you need a calculated measure.
 
Search WWH ::




Custom Search