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

Moving SSAS 2005 database to another drive

I just got asked how to move a SSAS 2005 database from one drive to another. The default data folder is the C: drive when you install SQL 2005. Usually, the data drive that system admins assign is the D: , F: , etc drive.
So how do you move a SSAS 2005 database? Well, apparently you can’t move individual databases, you have to move the entire data folder to another drive. You also have to change the server property DataDir to the new location.
The steps are outlined here.
It would have been nice to allow moving individual databases like we do in regular SQL with detach/attach or restore to a new location.

Get Rid of those Distinct Count Blues

Do you have the case of the distinct count blues? Too many rows in your fact table to do a distinct count? Are your resources overwhelmed by the distinct count process?

No worries…SQL2005 to the rescue! Take advantage of the many-to-many relationship capabilities of SQL2005 to do counts on your dimension table instead of the usual distinct count on your fact table. Read more about the many-to-many distinct count from Marco Russo’s white paper. Thanks Marco for your insights.
With this alternative method, you gain maximum performance.
Implementing is easy too:
1. Create a measure group on your dimension (e.g. Customer, Product) with a simple row count and not a distinct count
2. Define the M-M relationships in your Dimension Usage
And that’s it!

I am so excited about SQL2005 and how you can now solve such business problems which were almost impossible in SQL2000.

Many-to-Many in SQL 2005

Anyone who has built cubes in SQL 2000 and had to deal with the issue of many-many relationships knows how difficult it was to deal with that.
Enter SQL 2005 and the all problems are solved. Really, it is that much easier to handle many-many scenarios in 2005. The implementation is very straightforward. You have your “bridge tables” in your database structure and all you need to do is set them up in the Dimension Usage in SQL 2005 Cube Designer (Visual Studio). Of course, you have to know your structure and data but the rest is easy.
Read more about it here from a white paper on many-to-many by Marco Russo.

Curse of the distinct count

Distinct counts are a pain in SQL AS.

DISTINCT COUNT
AS2k – based on the dimension structure.
AS2k5 – based on hierarchy and attribute structure. This allows the distinct count measure to be calculated on much larger volumes of data.
Some issues: distinct counts are typically calculated at run-time – i.e. at the moment the user asks the question.

OPTIMIZE DISTINCT COUNTS
The basic run down of these distinct count optimization techniques are to:
· SQL2005 – Create a separate measure group for each distinct count measure
. SQL2000 – Create a separate cube for each distinct count
· Create custom aggregations
· Create a partitioning strategy for your OLAP cube allowing yourself to “distribute” the data.

In my experience, larger data sets of distinct counts are very slow to process even when creating separate cubes. Unless maybe you create partitions. Have not done much with custom aggregations. That’s on my list to do.

More about distinct counts here from Denny Lee.
More on building aggregations manually here from Chris Webb.

Also, look at the Microsoft SQL Server 2000 Analysis Services Performance Guide
for Storage Design Wizard, Aggregation Usage, and Cube Editor.

Analysis Services 2005 Performance Guide

Analysis Services 2005 Performance Guide is now available from the MSDN downloads. Good stuff on performance including: distinct counts, partitioning, many-many, etc.

How can SQL Server 2005 help me evaluate and manage indexes?

The Microsoft SQL Server Advisory Team has a good article on indexes:

http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

They include very useful performance tuning queries. SQL Server 2005 has what they call DMV (Dynamic Management Views) that lets you do diagnostics and tuning. This was not available in SQL 2000. This article answers the following questions:

(1) How can I find out whether my indexes are useful? How are they used?
(2) Do I have any tables or indexes that are not used (or rarely)
(3) What is the cost of index maintenance vs. its benefit?
(4) Do I have hot spots & index contention?
(5) Could I benefit from more (or less) indexes?

Worth a bookmark.

SQL Server 2005 System Views Map

Download the SQL Server 2005 System Views here.

Use it to get familiar with internal workings of SQL Server 2005.

Found this from SQL-Server-Performance.com.