MDX Query Builder in Ranet OLAP

1. Introduction
2. OLAP in BI
3. Functions of Ranet OLAP MDX Query Builder
4. MDX Query Builder Modes
5. MDX Query Builder Purpose
6. Who Is the User?

Introduction

OLAP cubes are query based constructions created for business data analyzing. It is possible to examine various performance metrics such as sales, profits across dimensions available, for instance, territory, time, demographics, and others.

OLAP in BI

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, cross joins, 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 Modes

MDX query builder supports operation in the two modes according to the user’s background and skills.

• Design Mode

Design mode of the MDX query builder is set up by default. In this mode, an MDX query is generated automatically by the settings of the design area fields.

• Custom Query Mode

Custom query mode exploits the user’s MDX query which can be generated manually without any alterations brought about by the system.

The modes of the MDX query builder define the rules of creating the MDX query to the data source of OLAP. The button switching the modes may have two states:

Icon	Mode	Description
	Design Mode	The mode informs the user that an MDX query is generated automatically by the settings of the choice panel areas.
	Custom Query Mode	The mode informs the user that the choice panel is unavailable to alter. The MDX query is supposed to be generated manually by the user (a programmer, a cube administrator, an analyst) or copied from another application. Under this mode, there is an opportunity to utilize the full capacity of the MDX language so that to gain the most efficient solution for the report building.

A double click of the left mouse button to the button of the Design mode changes its current state into the opposite. If the current state is custom query mode then the user will receive a request warning of the loss of the alterations in the MDX query generated manually. And if the user confirms the action, the MDX request will be updated automatically according to the settings of the choice panel areas whereas the very choice panel will become available for editing.

Remember:

If the query had been generated or edited manually by the user in the tab bar in the bar of the report or in the header of the MDX query area there will be a pictogram displayed:

A simple example below demonstrates the MDX builder in the custom query mode:

Areas Designation:

Area	Description
1	A group of commands running the display of the MDX query area and defining the mode of the pivot table design. The first button runs the display of the MDX query, the second one defines the design mode.
2	The MDX query area which is designed for the view and editing of the MDX query. The button ‘Generate MDX’ allows a user to automatically formulate an MDX query according to the settings of the choice panel areas.
3	The choice panel displays the settings of the pivot table areas. In the custom query mode, it is unavailable for the user’s alterations.
4	Reports tab bar The shortcut of the tab displays a pictogram informing that the pivot table is built based on a custom MDX query.

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.).

 

 

Who Is the User?

The use of the MDX query builder is available for those who already have experience in multidimensional expressions generating. Otherwise, it is possible to have a predefined MDX query which can be tested and used. I a person has no expertise in MDX, they are able to exploit Excel in order to receive Analysis Services Data. After getting connected to a database or server, a user can see which cubes are available in Excel. They can import all the measures, dimensions, and hierarchies to be able to query and part of data through the PivotTable Fields list.

Learn more about the functionality the MDX query builder offers in online demos for HTML and Silverlight.

We hope this post was useful. Feel free to contact us or comment below if you have questions about Ranet OLAP MDX query builder tool.