Tag Archives: SQLBits X

SQLBits X PreCon SSAS Deep Dive Summary – Part 1

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 architecture
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.

ΑΩ – Biggest take-away

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.

SQLBits X – My almost final agenda

As the agenda for SQL Bits X was released, I had my take on what sessions I wanted to attend.
Now, two full days ahead, I’ve managed to almost pin down all the sessions I am going to immerse into.

Thursday: “Deep dive into Analysis Services” session by Akshai Mirchandani (LinkedIn)
Friday: Keynote, CDC by Allan Mitchell (blog|twitter), “SSIS in SQL Server 2012, Part 1: Developer Enhancements” by Matt Masson (blog|twitter), SSAS Load testing by Bob Duffy (blog) and finally SSAS Security by Chris Webb (blog|twitter)
Saturday: SSIS Dev Part 2., Keynote, then of to either Alex Whittles (blog|twitter) Loading dimensions in DW OR Extended Events by Bob Beauchemin , Performance investigations by Akshai Mirchandani (LinkedIn), Declarative Database Development by Gert Drapers and finally Deep dive into DW in SQL Server 2012 by Dejan Sarka (blog)

It has been really hard to decide what sessions to attend, and I might even change my mind on a few of them… So much knowledge to gain (must get the recordings if any are released).

I have been going over the agenda several times in these past two months and I must say that the SQLBits guys have done one *biiip* of a fine job putting this schedule together.

“What should I do?” – Brett Favre

The full agenda for SQLBits X is now published (link), and it looks like it’s going to take some tough choices to find out which sessions I’m going to attend. I know my coworker Rasmus Reinholdt (LinkedIn) is going to attend the “Putting tabular models into production” session by Cathy Dumas (blog) where I will see the “Deep dive into Analysis Services” session by Akshai Mirchandani (LinkedIn), but where to go Friday and Saturday?

Query Tuning by Adam Machanic (blog|twitter), Big Data by Thomas Kejser (blog) and CDC by Allan Mitchell (blog|twitter) sessions fight for the kickoff, then of to the hands down winning session “SSIS in SQL Server 2012, Part 1: Developer Enhancements” by Matt Masson (blog|twitter) and if its half as good as it sounds to be, I’ll take the 2nd session as well. After lunch some nasty cuts have to made once again; either T-SQL candy by Mladen Prajdić (blog|twitter) or SSAS Load testing by Bob Duffy (blog). I’m leaning towards the latter.
The last session on Friday will be the hardest to pick; SSIS Catalog by Jamie Thomson (blog|twitter), SSAS Security by Chris Webb (blog|twitter) or SSIS Unit Testing by John Welch (blog|twitter)

Saturday morning starts off with either Hierarchies by Markus Ehrenmüller-Jensen (blog) or SSIS Dev Part 2. Then of to either Alex Whittles (blog|twitter) Loading data in DW, T-SQL Perfomance Tips by Milos Radivojevic (blog) or Query Tuning by Adam Machanic. After that Im probably of to Performance investigations by Akshai Mirchandani, followed by windowing functions by Fabiano Neves Amorim (blog|twitter) and finally Deep dive into DW in SQL Server 2012 by Dejan Sarka (blog)

I know the venue is not until late March, but still, so many options, so many good sessions…

And if you we’re wondering about the title, have a look at this: http://www.youtube.com/watch?v=POyFvDgV2cU