MDX Query Design for Data Extraction

MDX Query Design

The following section discusses the MDX Query design for extracting the data into a CSV file for Multi Dimensional data. The 2 main Dimensions with hierarchies are Time ( Year/Quarter/Month) as the hierarchy and Product ( Market / Product / Package ) as the Hierarchy. These 2 dimensions would be present in any data extract.

This is divided into 2 sections. Section 1 is for extracting the Hierarchy data for time dimension and Section 2 is for extracting the Hierarchy data for the Product dimension.

Section 1 – MDX for Time Hierarchy

If the user chooses the data extract to include data in the time dimension at the most granular level – so that it has – Year, Quarter and Month – the MDX for the Time Dimension part would look like

SELECT

NON EMPTY {Descendants([Time.Time].[All Members], 3)} ON COLUMNS,

The integer 3 above indicates that the data should be returned at the most granular level – with all the 3 levels of the Time dimension included.

For this Case :

When the data is to be extracted at the quarter level – the MDX :

SELECT

NON EMPTY {Descendants([Time.Time].[All Members], 2)} ON COLUMNS,

The above MDX means – that we need data for only 2 levels in the Time Dimension starting with data for All Years.

Section 2 – When extracting data for Product Dimension Hierarchy

Case 1

Assume the user has selected the Market ( which is from the Product Dimension ) to be ALLERGY and the user wants to get data for all the Products and Packages which come under the Market – ALLERGY.

MDX for the above choice would look like below

SELECT

… on Columns,

NON EMPTY {Descendants([Product].[ALLERGY], [Product].[package_name])} ON ROWS

FROM [Cube]

WHERE ([Measures].[MeasureName])

Results of the above

Case 2 :

Assume that the user has selected the ALLERGY Market from the Product Dimension and wants to get the data values at the Product Level.

MDX for the above would be like this

SELECT

.. ON COLUMNS,

NON EMPTY {Descendants([Product].[market_name].[ALLERGY], [Product].[product_name])} ON ROWS

FROM [Cube]

WHERE ([Measures].[MeasureName])

Result

Case 3 :

The use case where the user selects the Product Dimension and wants to get the data extract – for all Products and all Packages under them – the MDX Query would look like :

SELECT

.. ON COLUMNS,

NON EMPTY {Descendants([Product.Product_Package].[All Members], [Product.Product_Package].[package_name])} ON ROWS

FROM [Cube]

WHERE ([Measures].[TRx])

Results:

Case 4 :

The use case where the end user wants to get a data extract for all Packages under a specific Product – called – RESTASIS – the MDX query would look like :

SELECT

.. ON COLUMNS,

NON EMPTY {Descendants([Product.Product_Package].[RESTASIS], [Product.Product_Package].[package_name])} ON ROWS

FROM [Cube]

WHERE ([Measures].[TRx])

Explanation

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s