1308EN_MDX with Microsoft SQL Server Analysis Services 2008 R2 CookbookAn interesting confluence of events this week takes me both back to the past as I ponder the future! What gives? Well, the recent release of Tomislav Piasevoli’s (blog|twitter) new book, MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook, reminded me of my first meeting with him at a PASS Summit event a few years ago. Alas, I don’t remember which event because – I confess – I’m getting old and my brain just doesn’t store details like that anymore. Not when it’s full of more important tidbits more necessary to my daily work! If I had to guess, I think it was the first event that Erika attended with me, which means it would have been in 2008 but it could have been 2009, and definitely not 2010. How’s that for narrowing it down? [Edit: Tomislav reminded me that we actually met at the 2008 PASS Summit but we talked about the Axis function at the 2009 PASS Summit Birds of a Feather.]

Regardless of which year it was, Tomislav was seated at table during a Birds of a Feather lunch talking to several people about MDX, and he made a comment about the Axis function which I thought was very interesting. To be honest, I don’t remember what he said specifically, but it intrigued me enough to jot it down to pursue at a later time. It turned out not to be helpful for whatever MDX problem I was faced with at the time, but I kept the note to myself in my smartphone, which transferred over to the next two smartphones I’ve owned since then. My experience with MDX is that there are a handful of MDX functions that we who are inclined to write MDX use quite often. For an interesting, but entirely unscientific, analysis of function popularity, see this 2005 post from Mosha Pasumansky, “What are the popular MDX functions?” Although the post is a bit dated, I think it’s still valid. When I look at the top list of functions, I do indeed use many of those functions frequently (although NonEmptyCrossJoin is deprecated and I now use the NonEmpty function instead).

This year, I finally found a situation where I found the Axis function to be useful. Basically, this function gives us a way to “see” what’s currently on rows or columns. The situation facing me was  to come up with a way to create a calculation based on values found in two columns in a pivot table. More specifically, each column could be a different time period. So the calculation might be comparing Q1 and Q2 of a particular year, or it might be Q1 of one year and Q2 of another year. But we would not know in advance which two time periods would be involved. I built a calculation to count how many date members were on the columns axis and return a null if there were more than two, otherwise to do the math. I’ll provide the details for this in a separate post, but there are other scenarios in which this function is useful as Tomislav explains in this SQLBits session (recording freely accessible, slides and samples downloadable): Universal calculated measures in MDX queries.

That encounter with Tomislav is just one example of many that highlights the value of attending the PASS Summit. It’s not just what you learn by attending the sessions, but also what you learn in casual conversations with experts. Opportunities abound for these conversations, whether at a Birds of a Feather table or in the hallway or at one of the many evening events. (Hint: Asking questions during SQLKaraoke is probably not a good time.) People who are at PASS – both speakers and non-speakers alike – are there because they’re passionate about what they do. If you plan to go and it’s your first time, the best advice I can give is Don’t Be Shy! Talk to someone, anyone, everyone! You won’t regret it.

It’s only a few more short weeks until Erika and I head out to this year’s summit. I’ve got to finalize my demos for my preconference workshop on MDX, DAX, and DMX and for my half-day session with Denny Cherry, So How Does the BI Workload Impact the Database Engine?. I think all I need to do is show up for my panel session, Are You a Linchpin?

Meanwhile, I’m going to be reading Tomislav’s new book to see what other gems he has to share, and I’ll post a review when I finish the book. Stay tuned!