Input :-


What is the Use of this MDX formatter ?

This question comes to mind when you are using this for the first time. what is the purpose of this ?
I’ll explain.
When you need to merge/join data from a relational database with a OLAP (multidimensional) database then you need to create a linked server. That linked server will be linked with the OLAP db. Here you write a MDX QUERY (it is a parameterized query) to get the data from the OLAP db with the OPENQUERY T-SQL function through linked server. Now that data is most probably stored in a temporary table which is in turn joined with the desired relational table.

If you want to get this MDX query which is stored most probably in a T-SQL variable, with proper indentation and parameter values substituted, you can do any of the following :-
1) Issue a select statement as ‘select @mdx’. The result is a MDX query not properly formatted and also not understandable.
2) You can take the MDX query as it is and substitute the parameter values manually.
3) Use the print statement.

2nd step is time consuming.
3rd step will show the mdx query upto a certain limit.
1st step when used with this MDX formatter can be time-saving actually.

For Example :-

Below exists some T-SQL statments with MDX query in a variable :-

Declare @mdx varchar(max)

set @mdx = ‘
SELECT {
[Measures].[Reseller Sales Amount]
} ON COLUMNS,
Descendants (
[Geography].[Geography].[Country].&[United States] ,
[Geography].[Geography].[State-Province]
) ON ROWS
FROM
[Adventure Works]

when a select statment ‘select @mdx’ is executed, following output is generated

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS, Descendants ( [Geography].[Geography].[Country].&[United States] , [Geography].[Geography].[State-Province] ) ON ROWS FROM [Adventure Works]

To format this mdx query (above query is not parameterized), we can use MDX formatter.

3 Thoughts on “MDX Formatter

  1. site is excellent. beneficial for starters and I’ll recommend to my friend who is working on it.

  2. Gaurav on April 29, 2014 at 10:07 am said:

    Is it possible to use your service which formats the MDX query via some web service or restful service. Do you have any API for the same ?

Leave a Reply

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

*

* Copy This Password *

* Type Or Paste Password Here *

2,892 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>