Stacia Misner : PASS, MDX

Back to the Future with MDX and PASS

smisner — Fri, 09 Sep 2011 20:22:00 GMT

An 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 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!

Creating a Linked Server for Analysis Services

smisner — Tue, 30 Nov 2010 23:30:00 GMT

I think I’ve finally recovered (in more ways than one!) from PASS Summit 2010 in Seattle a mere few weeks ago. I seem to be eternally plagued with computer problems at every conference I attend, but always in different ways. Accordingly, I have devised different strategies for anticipating and coping with problems. I foiled this last disaster by having my slides and demos duplicated on Erika’s laptop which I had to call into action literally seconds before the session began due to a hard drive failure, but missed one little detail which was actually a last minute bonus addition to my session on Analysis Services Stored Procedures (ASSP). So it wasn’t the end of the world, but it was a forehead slapping moment!

As part of that session, I told a story of how I came to appreciate the use of ASSP for a client project in which I had to dynamically construct MDX queries on the fly using T-SQL stored procedures and pass the resulting query into an OPENQUERY function. The problem that I had in that situation was the limitation imposed by the OPENQUERY function – I had to write my MDX query in 8000 characters or less! Erika and I worked out all kinds of ways to live within that constraint before we tried ASSPs, but ultimately the complexity of these queries kept pushing over the query string over the limit and thus began our adventures with ASSP. I’ll add a post on this technique to my series on Using Dynamic MDX in Reporting Services soon. (The first post describes the use of StrToMember() and StrToSet() and the second post shows how to use MDX in a dynamic OLE DB query.

I had a few extra minutes of time in my session, so I thought I’d show the audience “the rest of the story” by demonstrating how I could use the OPENQUERY function with the ASSP that I created earlier in the session. I had the OPENQUERY working on my laptop just a few minutes before the session started, but in preparing Erika’s laptop, I didn’t think about setting up a linked server to Analysis Services in advance. No worries – I’ll just do it live! Well, my memory failed me on how to fill out the New Linked Server dialog box in Management Studio, so I asked the audience if anyone knew what to do and the reply I got was, “It’s a mystery!” (It took me a few times to realize that’s what they said – I was suffering from the “SQL plague” and my ears were really plugged up!)

Nothing a little search engine won’t resolve, right? Well, I turned up a few hits (like the Books Online entry Adding a Linked Server), but nothing was particularly helpful for interpreting which parameters are really needed and which are optional – especially when I was on the spot! So my post today is my contribution to resolving the mystery. Of course, now that I’m offstage and working on my replaced hard drive and with a much less congested head, I was able to set up a linked server quite quickly.

To set up a linked server for Analysis Services, follow these steps:

Connect to the Database Engine in Management Studio.
Expand Server Objects, right-click Linked Servers, and click New Linked Server.
Define a name for the linked server, and set the following options as shown in the screenshot below:

Provider: Microsoft OLE DB Provider for Analysis Services 10.0
Product name: MSOLAP.4
Data source: (the name of your server)
Catalog: (the name of the Analysis Services database)

To test the linked server, you can write a query like this:

select * from openquery(AdventureWorksOLAP, 'select [Measures].[Sales Amount] on columns from [Adventure Works]')

A couple of things to note - This query is executed as a database engine query, not as an MDX query. The first argument of the OPENQUERY is the name of your linked server.

PASS Summit 2010: Epilogue

smisner — Mon, 15 Nov 2010 21:16:00 GMT

Another PASS Summit has come and gone, and a good time was had by all. The song at the opening keynote sums up the experience quite nicely, "Simply the Best." My favorite quote of the week comes from Andy Leonard (blog| twitter) who tweeted on November 9, "#sqlpass is a family reunion. :{>" Oddly enough, Andy is one of the few people that I DIDN'T get to see last week, but not for lack of trying. Sorry, Andy!

As an independent consultant, I don't get to interact regularly with my peers, so I love the reunion aspect of PASS. And now that I've started following people on Twitter, my universe of colleagues has expanded even more, and I was delighted to meet the people behind the avatars.

The joy of PASS is not limited to those of us who have attended for many years. As I was sitting in the airport last Friday, I recognized an attendee who was a first-timer and asked about his experience. He replied that it exceeded expectations. He was so anxious to get back to work to put what he learned into practice. He felt that the value of the knowledge that he is bringing back to the office from PASS was so much greater than the price of the conference. Now that's a ringing endorsement if I ever heard one.

There is so much more that could be said about PASS, and many in the community have already posted their thoughts on Twitter and on their blogs. There has been quite a stir in the business intelligence community this past week about the future of Analysis Services, about which I will comment in a future post. Today, however, I will focus on a few follow-up comments and links to resources related to activities in which I participated last week.

Getting Started In Blogging And Technical Speaking

(Photo courtesy of Brent Ozar)

Who had control of this very interactive session? You'll have to buy the PASS Summit DVD to find out!

I waited patiently for my turn to speak. With a room full of speakers, getting your own turn can be a challenge! I heard a few attendees express concern that they didn't know what to write about, or that someone else has covered the topic. My response to this concern is that everyone has a unique take on a topic, and that's why T-SQL Tuesday is such a great way both to learn and to contribute. T-SQL Tuesday was started by Adam Mechanic (blog | twitter) and gives everyone with an opinion (and who doesn't have one?) or a specific experience to add to the topic. You can see the first invitation to T-SQL Tuesday here to see how it all started and the most recent T-SQL Tuesday posts hosted by Paul Randal (blog | twitter). The best way to keep tabs on who's hosting the next round is to follow the #TSQL2sDay hash tag on Twitter.

Unfortunately, I didn't get to hang out for the entire presentation, because I had to move on to the…

Women in Technology Panel

I was honored to participate as a panelist for this year's Women in Technology luncheon. It was well attended, and I heard so many positive comments after the event from both men and women. I drew inspiration from my fellow panelists as well as the stories shared with me by other women attending PASS this year. You can view a recording of the event here if you're a registered member of PASS (which is free to join).

It's so difficult to say what the right answer is for increasing the numbers of women in technology. The numbers are diminishing at a deplorable rate (as I discussed in a previous post). It seems to me that to foster change we need to start laying the groundwork with our children. By "our children", I mean society in general, not me specifically, although I have tried do my part! On the one hand, I don't recommend forcing children into a career path that they can't embrace enthusiastically. On the other hand, I believe that one reason that girls don't pursue technology as an option is lack of exposure to the possibilities. Lynn Langit (blog | twitter) is a role model for showing kids (not just girls!) how to explore these possibilities through www.teachingkidsprogramming.org. Check it out!

Along these lines, I proposed that maybe - as great a community as PASS is - we should collectively think about what we can do for our kids. Someone tweeted that I suggested we should bring our daughters to SQLSaturday, but actually I wondered aloud if we could do something similar to SQLSaturday that focused on the kids (and not just girls). Maybe we could get some sponsors to help, too?

Demystifying MDX in Reporting Services

In this session, I explained some of the nuances of working with MDX in Reporting Services. I have posted my demo reports here.

In addition, you might want to refer back to some of my recent posts about using dynamic MDX in Reporting Services: Part 1 and Part 2.

If you're not familiar with MDX, I presented Session 07: Intro to MDX for 24 Hours of Pass: Summit Preview which you can view if you have a free PASS membership. MDX is not going away any time soon, contrary to recent rumors, so invest some time learning it if you plan to work with real Analysis Services cubes, which will continue to have their place in the BI stack for several years to come.

Real World Analysis Services Stored Procedures

This topic drew a larger audience than I expected as it's a fairly specialized topic. For years, I never needed to use Analysis Services Stored Procedures (ASSP), avoiding it because folklore said so due to performance hits. However, some things just can't be done any other way and I ran into such things this past year. To date, I haven't found much written about ASSP other than BOL, but you can find some excellent examples to download at CodePlex. Plus I've uploaded the very simple (non-production-ready) C# example that I used in my session demonstration for you to peruse.

What's Next?

So now that I've completed all the training and Webcasts and conference sessions that I've been focused on the last couple of months (with one exception - Delivering Information with Reporting Services, a free Webcast at 12 pm Pacific on Wednesday, November 17), I plan to get back to a more regular blogging schedule. There are certainly plenty of topics on my "to do" list!

24 Hours of Pass Summit Preview: Introduction to MDX

smisner — Wed, 15 Sep 2010 23:16:14 GMT

Many thanks to everyone who attended my session today, Introduction to MDX. There was so much more I wanted to say, but the constraint of an hour required me to be very selective and, as you might have noticed, to sacrifice the demos. After all, how exciting can it be to watch me execute a query? I thought it more important to provide you with the key concepts that you can use to get started.

When I first learned MDX, I didn't learn it the way that I explained it today. Consequently, I struggled for a long time until the proverbial light bulb turned on in my head about how to think about the language. I hope that my explanation saves you some of the grief that I experienced, although I can't promise that you aren't going to have (and lose) a few wrestling matches as you build up your MDX muscles.

As promised, I have sample queries that illustrate the concepts that I discussed today. Before you can run the queries, I assume that you have both a SQL Server 2008 R2 database instance and an Analysis Services instance. You will need to download and install the AdventureWorks 2008 R2 sample database. Then open the Intro to MDX solution included in the zip file and deploy the project to create the simple cube for the queries. Then open the MDX files in SQL Server Management Studio. You can run the entire script, and thereby execute multiple queries, or run one at a time by highlighting the query text as described in the header of each MDX file. These queries and the SSAS solution might work with earlier versions of SQL Server, but is untested.

Download the zip file containing the queries and SSAS solution here.

If you plan to attend the PASS Summit 2010 in Seattle, be sure to see my session, Demystifying MDX in Reporting Services, where I explain how to use MDX with Reporting Services. There are a few differences from the query structure that I described today that you'll need to know.