Reposted from Jens Vestergaard's blog with the authors permission.

As an exercise for myself I will be writing fairly short post on the session I attended at SQLBits X´. I will begin with the training day where I went for the Deep Dive into SSAS.

The only bad thing there was to say about the SSAS Deep Dive precon by Akshai Mirchandani, was that he had so much material, that he had to skip some. Besides that it was a fantastic whole-day session.

The base of the material was taken from the SSAS Maestro program, and from several of the white papers and guideline documents out there. See a complete list by Vidas Matelis (blog|twitter) here.

General view of SSAS

For starters Akshai took the crowd through the major parts contributing to the execution of an incoming query. The main parts are illustrated on the following diagram:

MDX Query Execution

What this diagram doesn’t show, is the change made in the 2012 edition, to better serve processing and querying by allowing a split of the two on separate thread pools. The 2012 edition will have two (2) thread pools; one for process jobs and one for IO. Read more info about this new feature here.

Dimension Design 1on1

This was followed by a best practise walk-through on designing dimensions in a cube.
Some of the highlights were:

• Simple Cubes => Faster
• String AttributeKey => BAD
• Dummy attributes
• Natural Hierarchies
• String Store (4 GB limit)

The “dummy attribute” construction was new to me, and among the note taking and listening I lost the general concept. But I believe it had to do with dimensions where no immediate relationship could be made. Anybody reading this, please feel free to fill in the big blanks.

Akshai then continued on to give a brush up on Parent-Child (PC) Hierarchies in Analysis Services. Here he wanted to debunk some of the myths out there in regards to size and usage. In general the guidelines he gave was that one or two PC Hierarchy is acceptable in a cube. In SQL Server 2005 there was a guideline saying that when ever a dimension with a PC Hierarchy has more than 250K members, it was advised to convert this hierarchy into a natural hierarchy. This can be done by using the Parent-Child Dimension Naturalizer found at CodePlex. Akshai stated that the 250K members limit was not advised in the SQL Server 2008 edition and forward, but when performance issues arrises it could be a solution to convert a PC Hierarchy.

In regards to Many-To-Many (M2M) dimension relationships Akshai had an interesting notion on compression. Luckily BIDS Helper has a build in feature that generates the rather complex SQL needed for this operation. The technique is described in this white paper.
In general Akshai recommended BIDS Helper and highlighted several of the tool features.

The maybe biggest take away from the part on dimensions was to always check if you really need the AttributeHierarchyEnabled to be true on an attribute. There is much to gain by going through your attributes disabling those you only need as detail and not in hierarchies. See this TechNet article on the subject. 

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.