Restore DB with Different Name – Limitation

Got this error trying to rename a SSAS 2005 database:
“Backup and restore errors: Restore under different DatabaseId/DatabaseName can not be done for a database with partitions with specific location.”

Apparently, there is a limitation with SSAS 2005 when you try to restore a database with a different name AND you have specified a different folder location for storage. Hopefully this limitation is removed in SQL2008.

I had specified a different location because I ran out of space on my default location.

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