Social Networks

Multidimensional expressions (MDX), a highly functional expression syntax for querying multidimensional data in Microsoft SQL Server OLAP Services. MDX was introduced by Microsoft with Microsoft SQL Server OLAP Services in around 1998, as the language component of the OLE DB for OLAP API.

Introduction

What is MDXMicrosoft SQL Server OLAP Services provides an architecture for access to multidimensional data. This data is summarized, organized, and stored in multidimensional structures for rapid response to user queries. Through OLE DB for OLAP, a PivotTable Service provides client access to this multidimensional online analytical processing (OLAP) data. For expressing queries to this data, OLE DB for OLAP employs a full-fledged, highly functional expression syntax: multidimensional expressions (MDX).

MDX data types

Scalar: Scalar is either a number or a string.

Dimension/Hierarchy: Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions- they are assumed to be mutually independent. A dimension will contain some members (see below) organized in some hierarchy or hierarchies containing levels.

Level: Level is a level in a dimension hierarchy.

Tuple:  Tuple is an ordered collection of one or more members from different dimensions.

Set:  Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft’s implementation.

Other data types: Member properties are equivalent to attributes in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query.

Example

The following example queries the Adventure Works cube. This example returns the order quantity of each product and the percentage of order quantities by category. TheCoalesceEmpty function ensures that null values are represented as zero (0) when formatting the calculated members.

WITH 
MEMBER [Measures].[Order Percent by Category] AS
CoalesceEmpty
( 
([Product].[Product Categories].CurrentMember,
Measures.[Order Quantity]) / 
(
Ancestor
( [Product].[Product Categories].CurrentMember, 
[Product].[Product Categories].[Category]
), Measures.[Order Quantity]
), 0
), FORMAT_STRING='Percent'
SELECT 
{Measures.[Order Quantity],
[Measures].[Order Percent by Category]} ON COLUMNS,
{[Product].[Product].Members} ON ROWS
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].[Calendar Year].&[2003]}

Example:

Provide the total sales and total cost amounts for the years 1997 and 1998 individually for all USA-based stores (including all products). We are asked, moreover, to provide the information in a two-dimensional grid, with the sales and cost amounts (called measures in our data warehouse) in the rows and the years (1997 and 1998) in the columns.

--MDX01-1:  Basic Query
SELECT
{[Time].[1997],[Time].[1998]}ON COLUMNS,
{[Measures].[Warehouse Sales],[Measures].[Warehouse Cost]}  ON ROWS
FROM Warehouse
WHERE  ([Store].[All Stores].[USA])

MDX_Dimention_Example

Refer Below links for more reading –

http://en.wikipedia.org/wiki/MultiDimensional_eXpressions

http://www.databasejournal.com/features/mssql/article.php/10894_1495511_2

None found.

Leave a Reply

Your email address will not be published. Required fields are marked *

Allowed Tags:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>