MDX parser and generator similar to Abstract Syntax Tree (AST)

I was looking out for a utility / tool / class which can do following in Analysis Services 2005:

1. Parse & validate an MDX expression/query and present it in a object hierarchy
2. Vice-versa, create an MDX query / expression from object hierarchy

The above functionaity can be used in an application where the complexities of the MDX needs to be kept away from the users and it would be helpful in programatically managing the mdx queries.

Per Analysis Services book by Melomed, Gorbach et all, Analysis Services itself handles it where it parses an MDX request. First, it analyzes the MDX statement, finds syntax errors, and produces a tree-based data structure called an abstract syntax tree (AST). An AST has operators, such as MDX functions as inner nodes, and operands, such as function arguments as leaf nodes. In the second parsing phase, Analysis Services traverses an AST, resolves the names of the objects referenced in the query, and validates the signatures of the functions. This phase produces an expression tree. Each node on the expression tree is an object that knows how to calculate itself. It also has a hook by which it can plug in to the calculation execution plan.

For example, if you send the below MDX query to Analysis Services, it will produce the AST shown:

A Simple MDX Query

SELECT
Order(
          Filter(
               [Customer].[Customers].[Country].members,
               [Measures].[Unit Sales].Value >1000
          ),
          [Measures].[Unit Sales],
          BDESC
    )
    ON COLUMNS
   FROM [Warehouse and Sales]



In the first parsing phase, Analysis Services produces an AST.


clip_image002

In the second parsing phase, Analysis Services traverses an AST, resolves the names of the objects referenced in the query, and validates the signatures of the functions. This phase produces an expression tree. Each node on the expression tree is an object that knows how to calculate itself. It also has a hook by which it can plug in to the calculation execution plan. For the sample query, Analysis Services generates the expression tree as below:

During the second parsing phase, Analysis Services performs the semantic analysis and produces an expression tree.

clip_image004

After creating the expression tree, Analysis Services is ready to move to the query resolution phase.

I was wondering if the above functionalities can be exposed to outside application where they can convert an MDX to abstract syntax tree and vice versa.
Mondrian seems to have such class hierarchies exposed which seems to be programmatically exploitable. (http://mondrian.pentaho.org/api/mondrian/mdx/MdxVisitor.html)

If anyone knows approach for SQL 2005 or earlier, please let me know. If I come to know something, sure would post it here.

1 comment:

Sam X said...

abhijit,
mdx studio by Mosha contains mdx parser

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll