When speaking about OLAP in business intelligence, we can identify a lot of tools which help use business information more profitable. Multidimensional Expressions, or MDX, is a query language used to extract OLAP cube metadata for analysis and reporting. Thus, any business analyst working with OLAP systems is no stranger to MDX queries. In order to provide its users with the best opportunities for working with MDX queries, Ranet OLAP has a built-in MDX query builder (generator).
Functions of Ranet OLAP MDX Query Builder
Ranet OLAP MDX Query Builder allows generating effective MDX queries of various types, depending on the user’s skills and business needs. It is especially helpful when you need to do complex ad hoc data analysis.
Users can save the layout of the report as a template and later use the templates for generating MDX queries in C#. The templates allow defining tuples that will be included into axes and strings of the query. The axes can use various hierarchies and hierarchy levels from the cube metadata. It is possible to create sets of dimension members (tuples), custom calculations and filters (see examples in figures below). It offers engineers vast opportunities when developing BI solutions.
The query builder can parse any MDX query that includes dimension hierarchies, formulas, crossjoins, etc. When a query is parsed, a model similar to abstract syntax tree is generated, showing how various elements of the MDX expression are related to each other, in a tree-like form.
Apart from parsing a query and presenting it in the hierarchical form, the MDX query generator is used to create an MDX query from the hierarchy. Thus, the MDX query parser allows making any report interactive, even the ones created using custom MDX queries.
MDX Query Builder Purpose
Ranet OLAP MDX Query builder serves a dual purpose, depending on the user.
On the one hand, it allows tech-savvy users to work directly with MDX expressions. Engineers can create custom MDX queries, generate them using templates, modify parsed queries and generate them anew according to the DOM model. Besides, it additionally validates user’s MDX expressions and helps to quickly find syntax errors.
On the other hand, it helps non-technical users to work with reports by providing a user-friendly interface that hides the complex process of MDX query generation. Users can manage the process by using simple navigation commands (Expand and Collapse, Drill Down, etc.).
Below are several examples of MDX queries in Ranet OLAP (based on Ranet OLAP Silverlight ver.).
We hope this post was useful. Feel free to contact us or comment below if you have questions about Ranet OLAP MDX query builder.