Monday, January 26, 2009

Analysis Services MDX Templates Exploration

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

No comments: