Analysis Services Query Performance Top 10 Best Practices

I came across this little gem which I haven’t noticed so far. A nice summary of the 10 things you should do to optimize the UDM query performance. The best one is kept for last – scale out when you can no longer scale up. So true, if I could only convince customers to do so J

Protect UDM with Dimension Data Security

SQL Magazine published the first part of my article Protect UDM with Dimension Data Security in its July issue. This article explains the fundamentals of dimension data security. I am working on making the article available for public access (as of now, it requires subscriber-level access). The article code can be downloaded from the publisher’s website and from my website.

I am currently writing the second part (tentatively named Protect UDM with Dynamic Dimension Security) whose focus is dynamic dimension security. It will present two implementation approaches for implementing dynamic dimension security which are harvested from a real-life project: factless fact table and integrating with external security service.

Trust Relationships

If you use VS.NET deployment to deploy your SSAS project from one domain to another, you may get the following obscure error message on deploy.


The trust relationship between the primary domain and the trusted domain failed.


This may happen even if your workstation and the deploy server are on the same domain. The most likely reason for this error is that you have added Windows groups or users from the old domain as members to SSAS role definitions. To fix the error clear the role membership list.

Aggregate Rows and SP2

Back to my When (null) is not Null post, there is a undocumented breaking change in SQL Server 2005 SP2 which you should be aware of. In response to the customer demand, SP2 treats aggregate rows as detail rows.  As a result, if the report doesn’t use the Aggregate() function, the report will show all rows. For those using the Aggregate function, there will be no change.

TechEd 2007 US Memoirs

I am back from an one-week vacation to Clearwater, Florida followed by TechEd 2007 US in Orlando. Overall, nothing earth – shattering on the BI arena from TechEd 2007. Product groups are between releases so most of the content was SQL Server 2005-based with a few sneak previews of some of the good stuff to come in Katmai.


My presentation attracted a fairly large crowd. Almost 500 folks joined my presentation “Applied Microsoft Reporting Services 2005” which was scheduled for the first breakout slot after the keynote on Monday. Thank you! I’ve made the source code and slides available for download on my website. Courtesy to Marco Russo, I’ve also uploaded a couple of photos to capture the moment of glory.


Now that I can open my mouth about some of the cool features coming up in the Katmai release of Reporting Services that were officially announced, here are the most important ones:



  1. Standalone report designer – both the VS.NET Report Designer and the Report Builder will share the same full-featured designer. Note that “standalone” doesn’t mean “embeddable”. While a post-Katmai release may bring us a designer which can be hosted in a custom application this will probably not happen in Katmai. Sigh…

  2. Tablix region – The table and matrix regions unite and give a birth to a brand new region called tablix. This versatile control can handle it all. By changing a few properties, the tablix can morph itself into a table, crosstab, or list report! The tablix region will enable reports that were not possible or required hacks in the past to get around the matrix limitations, such as stacked columns in a crosstab reports, multiple subtotals, and multiple pivot columns. I wish I had this baby a year ago…

  3. RDL Object Model – The Katmai release will ship with public RDL Object Model. This is a great news for developers (myself included) that need to generate RDL programmatically.

  4. Acquisition of the Dundas RS controls – MS acquired the Dundas chart, gauge, and map for Reporting Services. This will sure spice up the report graphical experience and position MS to compete better with other report vendors.

  5. Acquisition of the Soft Artisans Office Writer technology – This will let business users embed reports in Microsoft Word and Excel documents.

  6. Many designer enhancements – HTML inline support, better designer experience, export to Word 2000-2007, etc.

I hope I haven’t missed something important. The first Katmai CTP build to include some of the new RS enhancement is due in July.


Nothing too exciting on the SSAS side of things as the main team of the next release is “don’t rock the boat”. It will focus mainly on improving the server performance, manageability, and robustness.