Adding comments to an XMLA script

So I was trying to run an XMLA script and kept getting errors. Below is the script. You will notice that I used the same commenting as in T-SQL “- -”. Highlighting the XMLA script and executing it worked. So my commenting was wrong. It should be <!- – enter comments here – ->. The same as any XML document. I know for anyone familiar with XMLA or XML would say “DUH!, that is obvious” but it took me some poking to get it to work. I am so used to using the two dashes “- -” which is what is used in both T-SQL and MDX.

- – XMLA script to backup an SSAS database
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<File>C:\Backup\SSAS\Adventured Works DW 20080218.abf</File>
</Backup>

Here is the correct way of commenting:

<!- – XMLA script to backup an SSAS database – ->
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<File>C:\Backup\SSAS\Adventured Works DW 20080218.abf</File>
</Backup>

YTD and Time Dimension

I kept getting the following error when trying to do YTD calculations using MDX on a client cube:

VALUE #Error Query By default, a year level was expected. No such level was found in the cube.
FORMATTED_VALUE #Error By default, a year level was expected. No such level was found in the cube.

Here is the MDX to do a simple YTD:
SUM(YTD([Time].currentmember), [Measures].[Sales])

The MDX is good and works in every other cube including Adventure Works so I had to look deeper. I started changing keys thinking that the client did not set those up properly. I should have looked at the error message more closely because it alluded to what the problem was. It says, “No such level was found in the cube”. Well, I know it’s there because I can see it in the dimension structure. However, looking at the dimension properties, the Year attribute was set to Regular as the Type. Changing the Type to “Years” solved the issue. So that makes sense, the MDX parser was looking for a Year level but could not find it.

Sometimes just figuring out what the error says helps a lot