When analyzing data, the MDX query from the pivot table designer is sent as a parameter used to initialize the whole pivot table. In the pivot table, the MDX query is parsed and modified when needed. For instance, the history of the user commands can be used to restore the latest saved state of the pivot table; or the settings of the Hide empty Rows/Columns can be modified.
Ranet OLAP includes an MDX query builder that can parse any MDX expression, including dimensions hierarchy, formulas, crossjoin etc. MDX parser and generator build Document Object Model (DOM) for MDX expression, similar to Abstract Syntax Tree (AST). A Document Object Model (MDX DOM) is a model showing how various elements of the MDX expression are related to each other. An MDX query is built by constructing nodes of a tree. This MDX DOM approach to building a query is very effective.
MDX Query Builder can do the following in Analysis Services:
- Parse and validate an MDX expression/query and present it in an object hierarchy
- Conversely, create an MDX query/expression from object hierarchy
Software controlled generation of MDX queries allows hiding the complexity of MDX from the users and find syntax errors in the MDX statement. A user can write an MDX expression, on which an expression tree will be based.
After that, the MDX query will be generated automatically using the navigation functions (Drill Down/Drill Up, Expand/Collapse ) in the summary table.