Introduction and Definitions

This site will primarily focus on MDX used with Microsoft SQL Server Analysis Services 2005. Most examples are for SQL Server Analysis Services 2005, but some will work for earlier and later editions. The site will be updated as new editions of Analysis Services are released. The Pages that aren't not linked in the navigation bar, have not yet be completed. All Pages should be completed by the end of December 2008.

For those just starting with MDX, some definitions are provided below:

MDX stands for Multidimensional Expressions Language. MDX is a query language that is used to interact and perform tasks with multidimensional databses (also called: OLAP Cubes). The MDX language was originally developed by Microsoft in the late 1990s, and has been adopted by many other vendors of Multidimensional Databases.

A Multidimensional Database (also called: OLAP Cubes) is a method of storing data in a multidimensional form, generally for reporting purposes. In Multidimensional databases, data (measures) are categorized by Dimensions. Multidimensional databases are often pre-summarized across dimensions to drastically improve query time, over traditional Relational Databases. Microsoft's Multidimensional Database product is called SQL Server Analysis Services. The most recent version of Analysis Services was released in 2005.

A Dimension is data warehousing concept, that groups information by subject or category. One example of a dimension is a Date Dimension that contains: year, month, week, day, etc. Another example of a dimension is a Geography Dimension that contains: Continent, Country, Region, City. In Multidimensional Databases, Dimensions are usually provided with several hierarchies.

Hierarchies are logical relationships between attributes in a dimension. [In Example #1] Year has a direct relationship with Quarter, in that a Quarter is always contained within one specific year. Month also has the same direct relationship in that one specific Month is always contained within one specific Quarter. [In Example #2] Quarter is not included in the hierachy, however Year does have a direct relationship with Month, in that one specific month is always contained within a specific year.

Example Hierarchy #1 - Date Dimension

Hierarchy Structure	Hierarchy with Members
Year     Quarter     Month     Date	2006 2007 2008 Qtr 1 2008 Jan 2008 Feb 2008 Feb 1 2008 Feb 2 2008 Feb 3 2008 ... Mar 2008 Qtr 2 2008 Qtr 3 2008 Qtr 4 2008

Example Hierarchy #2 - Date Dimension

Hierarchy Structure	Hierarchy with Members
Year     Month     Date	2006 2007 2008 Jan 2008 Feb 2008 Feb 1 2008 Feb 2 2008 Feb 3 2008 ... Mar 2008

Example Hierarchy #3 - Geography Dimension

Hierarchy Structure	Hierarchy with Members
Continent     Country     City	Asia Europe North America Canada Mexico United States Boston Chicago Las Angeles New York ... South America

Each Step in a hierachy is called a Level. Looking at Example #1, the first level is [Year], second level is [Quarter], third level is [Month], and so on....

A Measure is numeric fact used to quantify something. Example: The number of Sales: 2,000 (The value '2,000' is a measure). Measures are all members of the Measures Hierachy.