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 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.
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:
abhijit,
mdx studio by Mosha contains mdx parser
Post a Comment