Five Things SSAS Should Drop

The trend started with a thread entitled Five Things SQL Server Should Drop a few months back. Naturally, that post triggered a similar response in SSIS by Jamie Thomson and Todd McDermid and in SSRS by David Leibowitz. Nearly three months later and there still doesn’t exist a similar meme for SQL Server Analysis Services (SSAS), at least not one that comes back in Google or Bing. So here to complete the set are five things I believe should be dropped from SSAS.

LookupCube and NonEmptyCrossJoin MDX functions

I realize that these MDX functions exists for backward support. The NonEmptyCrossJoin function was a kludge when it was first introduced. All of its functionality has been incorporated into the NonEmpty function in the latest versions of SQL Server. Its very existence needs to be eradicated from everyone’s memory. The LookupCube function is just plain scary. Though I guess it may have some problems that are resolvable with its use, if you have to use it, it’s probably signifying a much larger design problem that would be better addressed through the use of linked measure groups. The use of the LookupCube function in the calculations tab of a cube is one way to degrade the performance of an otherwise well-designed cube.

Cell Level Security

Another way to absolutely destroy the performance of an otherwise well-designed cube is through the use of cell level security. Why? Use of cell-level security disables the global cache which usually handles the most frequently requested queries. But, how else do you handle security requirements that need to be addressed by OR conditions? (i.e. Users need to be able to see Company X or all accounts that begin with 9.) Rather than use cell-level security, the use of a many-to-many relationship is a much better option. Though M2M relationships have their own issues, the use of a M2M relationship in this situation will involve a relatively small intermediate measure group that will usually be highly performing, at least much more highly performing than the use of cell-level security. And also, since the requirements are handled by a relational table, much more maintainable also.

Unknown Member

Unknown members were created to handle situations where NULLS exist for a dimension reference in a related fact table. Since I don’t believe a data warehouse should allow NULLs, I don’t like this property at all. Instead, if you have a missing dimension reference in a fact table, replace that instance with a pointer to a ‘Not Available’ member in your ETL. Doing so follows best practices also. I know I’ll likely not ever use this property. Even if I inherit a poorly designed warehouse that does have NULLs, I would correct these references in the DSV or server based views if left with no better option.

Server Time Dimension

Again, I don’t like that this feature is even available. It just leads to bad practices and misunderstandings. A well-designed data warehouse should have a well-formed date dimension that can easily and gracefully address ever changing analytical requirements. The use of a server-based date dimension will not.

Named Queries in the Data Source View

Named queries in the DSV aren’t necessarily evil. However, heavy use of named queries in the DSV could transform a well-formulated solution into an unmaintainable mess. The logic addressed by named queries is better addressed by incorporating into database engine views, or better yet, in the ETL and storage engine if that logic is complex.

I’m sure that this post, if anyone actually reads it, will be disagreeable to some.

Later, Martin