Query Editor

MDX for Analysis Service is written in Sql Server Management Studio(moving forward I will refer it as SSMS). SSMS carries over the same query experience to MDX editor, that it has for SQL. However there are some changes which are specific to Analysis Service.

Begin by launching SSMS, it will display the “Connect to Server” window. On this window change the “Server Type” to “Analysis Services”. Next in “Server Name” provide the name of your server. You will notice that “Authentication” is now locked to “Windows Authentication”. Since Analysis Service only works with windows domain accounts.

MDX for Analysis Service is written in Sql Server Management Studio(moving forward I will refer it as SSMS). SSMS carries over the same query experience to MDX editor, that it has for SQL. However there are some changes which are specific to Analysis Service.

Begin by launching SSMS, it will display the “Connect to Server” window. On this window change the “Server Type” to “Analysis Services”. Next in “Server Name” provide the name of your server. You will notice that “Authentication” is now locked to “Windows Authentication”. Since Analysis Service only works with windows domain accounts. Now click connect, it will open your Analysis Service connection. On your left hand you should see the “Object Explorer”, in case if it is not present there, go to menu-> Edit and click “Object Explorer”

To query cube you have two options,through Cube browser or through MDX query.
Cube browser is a simple UI, that enables the users to drag and drop dimensions and measures. It generates MDX for selection and fetches data to the ui. The user can also add filters to the query. In the image below we have a sample query. Notice the filter on top of the query.

Now lets explore the MDX query editor. In Object Explorer, right click your database, go to New Query, within New Query click MDX.

This opens the MDX query editor. Notice that apart from the left panel, its very similar to its cousin, SQL query editor.

Lets go over some of the important parts of MDX query editor.

  1. Current Analysis Service database that is selected.
  2. Current cube within the database that is selected. A database can have more than one cube.
  3. This contains the available MDX functions. These come in handy while writing MDX
  4. Current measure group. This ensure that only related measures and dimension are displayed.
  5. This shows the Metadata of the cube. However the metadata shown is effected by the selected measure group. In the above picture “ALL” is selected, which enables to display the entire metadata. In case a particular measure group is selected, metadata relevant to that measure group is displayed.

In the picture above we have some of the Measures and Dimensions expanded.

  1. 1 This is a calculated measure, take a look at its icon, its a bit different then the one in 2. A calculated measure is not an actual measure, rather its based on some logic applied to the actual measure.
  2. This is a measure
  3. This is a dimension attribute. Notice that this is placed within a folder. For better visibility you can place attribute of similar nature within a folder. For example if we had emails we could have placed it in this folder.
  4. These are Hierarchies. The first one is user hierarchy. A user hierarchy consists of a multiple attributes. example Country-State-City-District, all these are attributes on their own. The Second one is parent-child hierarchy. This hierarchy consists of a single attribute. For example take an employee table, the column which holds the manager details is an ideal candidate. This column will have the entire employee’s hierarchy for the organization.

Leave a Reply

Your email address will not be published. Required fields are marked *