MDX Queries for PowerPivot

MultiDimensional eXpressions (MDX) is a query language designed to obtain data from cubes or multidimensional databases. MDX can be used to query data from SSAS cube or a PowerPoint for SharePoint. We’ll examine such key components of MDS as

  1. Cube Dimensions
  2. Measures
  3. Crossjoin()
  4. Navigation Trees
  5. Ranges
  6. Sorting and Filtering
  7. KPIs
  8. User Hierarchy
  9. Attribute Hierarchy

MDX is not only specific to Microsoft technologies. It is also used to extract data from SAP Business Warehouse Cubes or Hyperion Essbase.

PowerPivot has built in MDX query design window that lets you construct MDX query via GUI. In order to write your first MDX query, you need to start up Table Import Wizard by selecting From Analysis Services as your data source.

Some description
Pic 1: MDX query designer

We used SSAS as our data source. You may choose to use it as well. However, it requires installation of SQL Server Analysis Services on your database server and access permissions to SSAS cubes.

The basic MDX query that you can easily construct looks like this

SELECT {Measures.Amount} ON COLUMNS,

 {Date.Calendar.Month .members}

ON ROWS

FROM MyDatabase

Another example of MDS is CROSSJOIN() function. It is used in more sophisticated queries which utilize multiple dimensions and measures.

SELECT {Measures.Amount} ON COLUMNS,

{CROSSJOIN({Date.Calendar.Month .members} ,

CROSSJOIN({Product.Categories.Category.members})})}

ON ROWS

FROM MyDatabase

A Navigation functions with MDX is another useful addition. There is on particular function that is called Descendants that is being used to flatten the result set

SELECT {Measures.Amount} ON COLUMNS,

{DESCENDANTS(Product.Categories.Category.members ,

Product.Subcategories.Subcategory.members }

ON ROWS

FROM MyDatabase

A Range Query used with colon operator. It needs both members to belong to the same attribute hierarchy.

SELECT {Measures.Amount}  ON COLUMNS,

{Date.Calendar.Month .June: Date.Calendar.Month .July }  ON ROWS

FROM MyDatabase

Sorting and Filtering example is self explanatory

SELECT {Measures.Amount} ON COLUMNS,

{ORDER({Date.Calendar.Month .members} ,

{Product.Categories.Category.members},DESC)}

ON ROWS

FROM MyDatabase

 

SELECT {Measures.Amount} ON COLUMNS,

{FILTER({Date.Calendar.Month .members} ,

{Product.Categories.Category.members}=”Sport”)}

ON ROWS

FROM MyDatabase

Featured pages

Sharepoint

Learn SharePoint integration with PowerPivot and start utilizing enterprise grade software for comp…

Excel

Learn how to use PowerPivot in Excel and gain insights into your data. PowerPivot for Excel helps …

SQL

Learn about little known PowerPivot tips and tricks and make your Excel PowerPivot Business Intell…

Intelligence

Learn PowerPivot Business Intelligence capabilities and perform BI tasks on large data sets using …