SQL BI

Use of SQL Prompt / SQL Refactor with non-admin user

If you use SQL Prompt v3 and/or SQL Refactor like me (they are very useful), you will appreciate this hint on how to correctly enable SQL Server Management Studio add-in menus on a non-admin user account.

You have to run this command within each non-admin account you want to use with (read this thread to get more info):

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe /setup

I am sure I will need this post in a few months, when I will setup my new notebook...

Clarification on Non Empty Behavior property in SSAS 2005 SP2

I just read this updated paper on MDX performance hints. The section Calculation Non Empty Behavior is very interesting because it clarified me some doubt I had using the Non Empty Behavior property for a measure.

The lesson I learnt is that it is better to specify only one measure (or one single tuple) in Non Empty Behavior, instead of a list of measures. The paper suggests that a different (optimization?) behavior is implemented in SP2 against previous versions, but I didn't found a detailed reference about applied changes. If someone has info, please let me know, thanks!

Scope of Named Set in MDX

I recently noted a behavior of named sets (specifically, the resolution order) that can be not so intuitive.

When you define a named set on the cube, it has the same behavior of a session-scoped named set (the one you create with CREATE SESSION SET statement into a MDX session).

When you define a query-scoped named set, apparently you use the same expression, but you obtain a different behavior.

A session-scoped named sets is resolved before the SELECT statement. A possible WHERE clause doesn't have effects on the SET expression. If you want to define the top 10 customers, a session-scoped named set gives you an absolute classification.

A query-scoped named set is solved inside the SELECT statement. If a WHERE clause is present, it restricts the context of the named set evaluation. In other words, the named set is resolved after the WHERE clause. If you use the same top 10 customers expression you could have used for a session-scoped named set, it would have a relative meaning in this form (i.e. defining the top 10 customers only for the products you filtered in the WHERE clause).

I would like to thank Octoni Simbolon that pointed me to this issue.

Analysis Services Hardware Tuning

There is a new paper on project REAL available: this one cover the hardware class tuning for an Analysis Services database of hundreds of Gb.

My first impression is good: it is useful to have a reference for benchmark analysis.

Wrong calculation for unary operator with many-to-many relationships

I just posted this bug on connect.microsoft.com - there is an issue when you use unary operator in a parent-child dimension combined with a many-to-many relationship.

The problem is that the '+' operator on non-leaf nodes seems to not consider the children value and bypass the intermediate measure group. Everything works well with the '-' operator. It sounds like an optimization that has a side-effect...

I found the bug thanks to a post on www.sqlbi.eu forum, that is starting to get feedback from people who are using the models described in the Many-to-Many Revolution paper.

Exam 70-445 TS: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance

Two days ago I did the 70-445 exam in beta (TS: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance). I cannot talk about the exam content, but I think I can talk about the exam in general.

As usual, an exam allows you to study many topics you don't cover with your daily tasks. For example, the exam cover some aspects of the delivery of a solution (SSIS/SSAS/SSRS) that needs some study, because even if you have years of experience, you may have not encountered all possible scenarios, especially if you work for a single company (a consultant may have seen several customers scenarios).

The difference between TS (Technology Specialist) and PRO (Professional) versions of the exams should be this: TS are oriented to implementation and delivery of a solution, while PRO are dedicated to design topics. I will test the beta of 70-446 exam (that is the PRO version of the same thing...) in a few weeks. The preparation guide talks about deploying and optimizing SSAS solution, topic that is not covered by the 70-445 exam (even if it cover the development of a SSAS solution).

As usual, the validity of an exam based on automatic test is limited, but I think that who has real-world experience should not have many problems passing these exams. The major difficult may be that you have to cover a large set of products (SSIS/SSAS/SSRS) while in my experience it's easier to find people who are more experienced on a single topic (in particular, the wider distance is between who develop SSRS reports and who builds SSIS packages).