While looking in SQL Server Management Studio for something else entirely, I stumbled upon the Template Explorer. This window provides Analysis Services templates for querying data mining structures (DMX), querying cubes (MDX), and performing DDL (XMLA). I took a deeper look into the “MDX Queries” templates.
To view the Template Explorer, select the View menu > Template Explorer option. To see the Analysis Services template, select the Analysis Services cube option at the top of the window. Double-clicking any of the templates listed will then open a new query window containing the selected query. For example, the Basic Query will show the following:
Select <row_axis, mdx_set,> on Columns,
<column_axis, mdx_set,> on Rows
From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>
You have two choices at this point in time: selecting the Query menu > Specify Values for Template Parameters option or replacing the phrases enclosed by angle brackets manually. The former choice opens a dialog box with all parameters listed to allow you to fill in the correct value; however, you will need to type in the full hierarchical structure by hand. If you’re anything like me, this is bound to cause a typo and a few frustrating minutes of letter-by-letter comparison. I prefer to modify the query directly by dragging the measure or dimensional attribute/hierarchy to my query window. Then I don’t need to worry about mistyping anything.
While these templates will not teach you how to write MDX, they are an easy way to save yourself some typing or looking up a particular keyword that you have forgotten. Looking over the XMLA queries, they appear to be more useful, as I am forever looking up the exact syntax for a particular XMLA query.
Versions: SQL Server 2005/2008