PASS Summit 2012 Day 2

Unfortunately, I accidentally slept through this morning’s Keynote address. I was sorry to have missed it, but I think I needed the bit of extra sleep. I’ve been putting in a lot of hours each day, both on my OLAP project, and soaking in all that the PASS Summit has to offer, so it was nice to get an extra hour of sleep. I wish I could say that I woke up to the sun streaming in through the windows, but I don’t think I’ve seen the sun once this week. Nobody at the PASS Summit seems to mind, though – myself included. Clearly we’re a sheltered people.

I attended some great sessions today. My favourite was a session on the ColumnStore index, delivered by Klaus Aschenbrenner. As I noted yesterday, the access to high-level expertise is energizing and inspiring, and Klaus’ session was no different. I went in to the session knowing just about nothing about ColumnStore indexes, and came out wanting to try them out. From what I gathered, they’re a new type of non-clustered index suitable for Fact tables in data warehouses. The indexes work on the column level, rather than the row level, so the level of compression that they can achieve is impressive, and it parlays into ridiculous query speed. Klaus took us through an exploration of the storage and memory footprint, the execution plan, restrictions and optimizations, and a highlight reel of ‘watch this’ moments where the ColumnStore index ripped through 2 billion records of data in seconds.

I also attended a Power Pivot for MOLAP session, and got to see the coveted tool in action, popping a multidimensional cube to life. I spoke with a Microsoft Engineer at the end of the session, and he hinted that it might be released as early as December. He did caution that “the QA team will kill us if we announce an official date”, so I guess they’re not quite ready for release yet.

Buffet Innovation

Lunch was dynamite once again (salad, beans, tomatoes, mashed potatoes and salmon), and while standing in the buffet line, I thought of a great idea: a buffet fly-over. It would be something similar to the fly-overs that you see watching golf on TV – a camera that swoops over the hole and gives the viewer a sense of how it plays (hazards, fairways, optimal lies, etc). The challenge at the front end of the buffet line is knowing what lies ahead, and how to ration the real estate on your plate. A buffet fly-over video playing on a loop at the front of the line would solve that.

Okay – back to reality.

Securing the Cube

I spent most of this evening experimenting with the various ways of securing cube data. I was really impressed by how flexible the security model is, and how easy it was to implement.

I started with the following goal in mind: within the Product dimension of my cube, allow suppliers to see only their data. SQL Server Analysis Services implements this with the concept of Roles, and Dimension Security, in tandem with Windows users and groups.

So, the first thing I did was create some Windows users on my laptop, to mimic product suppliers, so I could test things out with them. Having spent most of my creative energy conceiving of the buffet fly-over, there wasn’t much left in the well for clever supplier company names. So we’re stuck with the following: Dee Licious, Yumm Inc, Choc Inc, Sugarize, and GumGum.

New Windows Users

With just a few clicks in SSDT, I created a new role for the Dee Licious company, and mapped it to the Windows user that I created. That was the first point to really understand: Windows users are mapped to SQL Server Analysis Services Roles, and Roles take it from there – defining who gets to see what Dimension Members and Cells.

From there, all I had to do, to ensure that the DeeLicious user could only retrieve results for its products was configure the Dimensions Data tab inside the Role designer. It was as simple as selecting the appropriate Dimension (DimProduct), and then deselecting all other suppliers members from the SupplierCompanyNameEN attribute. When I flipped over to the Advanced tab, the MDX required to implement the security was revealed, presumably so that I could edit it to implement something more complex (no thanks).

Dimension Security

I used the cube browser to make sure that I had done everything properly (there’s a button in the toolbar that lets you change the security context), and was pleased to see just one supplier member show up for the attribute when I queried it:

Just DeeLicious

But I wasn’t done there.

I decided to see if I could implement the concept of a category manager: an Analysis Services role that was restricted to seeing data only for the particular categories they managed.

I went back to the Computer Management console and created some Windows groups (Chocolate, Gum, and Snack), and dropped my fictitious users into them, in the following way:

Windows Groups Representing Product Categories

Back in SSDT, I deleted my existing role, and created three new ones: Chocolate, Gum, and Snack. I mapped each role to the Windows group with the same name (i.e. the Chocolate.role to the Chocolate Windows group, etc.). I then changed the properties inside the Dimension Data tab to only allow the role to have access to the attribute member of the same name.

Just Chocolate

I repeated the step for my two other roles: Gum, and Snack.

This design pattern works for the following reason: a Windows user can be part of more than one role inside a MOLAP solution. Per BOL: “If a Microsoft Windows user or group belongs to multiple database roles, the effective permissions for the user or group are additive across all database roles (a union of permissions).” In my setup, Windows users were scattered across different groups, and those groups mapped to roles in a one-to-one relationship. So, Analysis Services would retrieve the correct Windows group based on the role, and then pull the correct users out of that group. (If this sounds convoluted, I apologize.) The main difference here is that you are effectively managing dimension security by managing Windows users and groups, instead of within the MOLAP solution’s roles, where you would have to modify and re-deploy the solution.

To test it out, I switched over to the Cube Browser, and changed the security context to the DeeLicious user, which is a member of both the Chocolate and Gum groups, but not the Snack group. I dragged some measures onto the results pane, set my date dimension filter, took a deep breath, and dragged the Category (Confection Type Major Desc) and Company (Supplier Company Name EN) attributes over, too.

It worked – all I saw were the Chocolate and Gum categories.

Success!

I had Songza running in the background and at the moment when I realized that it was working, Aaron Copland’s “Fanfare for the Common Man” came on. It was the perfect song, both in title and tune. (Give it a listen next time you need a soundtrack for a dramatic moment.)

“Fanfare for the Common Man”, Aaron Copland

Analysis Services goes even deeper with Dimension Security, and also offers the concept of Cell Security, but this common man had had enough security for one night, so that will be saved for another day.

Tomorrow is the last day of the PASS Summit, and also the last day of my project; I fly back to Toronto on Saturday morning. I’ve got mixed feelings about winding it all down. I’ll blog tomorrow about how I’m going to proceed from here, and share some final thoughts on the experience as a whole.