Using SQL Server Management Studio 2005 to Build MDX Queries

You can use the graphical environment in SQL Server Management Studio 2005 (SSMS) to build MDX queries against cubes. This example shows how to build the following query:

Select [Ship Date].[Calendar Year].MEMBERS on  Columns,    [Product].[Dim Product].CHILDREN on RowsFrom [AdventureWorksDW]Where [Measures].[Sales Amount]

After you’ve set up a cube in Microsoft Visual Studio 2005, deploy it to your local SQL Server database. When the cube is on your local database, you can build a query against it. In SSMS 2005, go to the upper-left side of the toolbar and click the Analysis Services MDX Query button (the third button from the left). A prompt asks you to define your database connection. After you’ve referenced your local server, the query pane opens, as shown in Figure A.

Next, look for the Template Explorer pane to the right of the query pane. If it isn’t visible, click View, Template Explorer to display it. The default view in Template Explorer is a series of templates for creating T-SQL commands. However, by clicking the Analysis Services MDX Query button, you’ve switched this view to display Analysis Services Templates, as Figure A shows.

In the Template Explorer, click MDX template, Queries. As you can see in Figure A, the first item in the Query list is the Basic Query. Drag the Basic Query from the Template Explorer into the query pane to display the Basic MDX Query definition. Update this definition by using the metadata for your sample cube; this is displayed in the Metadata tab to the left of the query pane. First you’ll create columns and then you’ll create rows.

Use the metadata in the list to define the columns based on the years dimension. Click Ship Date, and then Ship Date. Calendar Year. To add this definition to the Basic MDX Query, click the location in the template place holder where you want it to go and then drag the Ship Date. Calendar Year attribute to that location in the query. In Figure A you can see the text [Ship Date]. [Calendar Year] at the top of the query. While this code would build, it probably wouldn’t produce the specific results you want. For more precise results, you need to further define what you want from this attribute.

For this example, I’ll introduce two keywords, the first of which is MEMBERS. The MEMBERS designation tells the query engine that you want a column for the top summation for this attribute as well as for each of the items that make up this attribute. In this case, you’ll get a summation column for all of the years and a column for each year. After [Ship Date]. [Calendar Year], append .MEMBERS prior to the on Columns text in the query as shown in Figure A.

Next, follow the same steps to create the rows. I’ve entered the Product dimension into the query and used the Dim Product attribute. I’ve also specified that I want only the children of the Rows attribute. The only difference between the CHILDREN and MEMBERS keywords is the summation. Because I’m looking only at the child attributes that make up this dimension, the query engine won’t return a row representing the top-level query.

The remaining portions of the query are straightforward. The From clause names the location the data is retrieved from; in an MDX query it’s usually the name of the cube. In this case, you’ll populate the From clause by dragging AdventureWorksDW from the Cube Metadata pane and dropping it into the query pane next to the From clause. Next, you’ll populate the Where clause, which defines the data that will be applied. The Where clause needs a data element that’s measured by dimensions. In the Cube Metatdata pane, the only measure defined for this sample cube is Sales Amount, so you’ll drag and drop Sales Amount to populate the Where clause. Here’s the final query:

Select [Ship Date].[Calendar Year].MEMBERS on  Columns,    [Product].[Dim Product].CHILDREN on RowsFrom [AdventureWorksDW]Where [Measures].[Sales Amount]

Click the Execute button on the SSMS toolbar to test this query. The results are displayed in the bottom half of the Query pane, as Figure A shows.