After being forced to deliver sour grapes in my last few columns – thanks to some unexpectedly negative experiences with Oracle and DB2, not to mention the lack of new features in MDX and Windows 8 – I am  pleased to have positive news to share. On May 12 I was fortunate to be able attend the first SQL Saturday held in Rochester, which outdid my own expectations, not to mention those of the organizers.

                The event was largely due to the efforts of the new president of the Professional Association of SQL Server (PASS) Rochester chapter, Matt Slocum, who recently reinvigorated the group by scheduling some face-to-face meetings between local DBAs. I joined that group at some point during the last couple of years, but was forced to miss the only meeting due to poor health, as usual, so the first meeting our new president scheduled in late April was my first opportunity to meet DBAs who are actually working in the field. After years of studying SQL Server on my own, it was relief to meet people who didn’t react with a blank stare when I mentioned terms like optimistic concurrency or phantom updates; Matt Slocum and I actually shared a few laughs over SQL Injection jokes, like Bobby Tables and this clever license plate. About ten people attended that meeting and another a few days before SQL Saturday, when a few of our local PASS members helped out by stuffing information packets for the attendees. Many of the same people also volunteered to man booths, act as proctors and help out in other ways, but I wasn’t available to chip in due to time constraints on Saturday itself.

                They needed all the manpower they could get, given that the crowd at the Rochester Institute of Technology (RIT) exceeded the expectations of the organizers. I haven’t checked back with Matt yet to get any final figures, but I heard that the unofficial attendance number by early afternoon was above 250. The day was divided into five one-hour sessions, in which speakers discussed topics related to their particular fields of expertise in six classrooms, for a total of 30 sessions to choose from. In addition, a few of the vendors who had information booths set up in the main foyer gave presentations on their products during the lunch break.[1] Most of the attendees seemed content to spend the hour swapping SQL Server stories over the catered lunch, which was inexpensive but didn’t taste like it. After waiting years to discuss SQL Server with others, it was a bit of a shock to meet scores of people who worked with it on a daily basis. I simply couldn’t find enough time in the day to discuss everything I wanted to with the DBAs there, whose eyes sometimes lit up when I brought up particular topics to them during the breaks between sessions or during lunch in the main foyer. For example, I ran into a developer from a local business intelligence company called iVEDiX and asked him a question, which quickly snowballed out of control into a lot of interesting speculation on such topics as the future of BI, data mining and similar topics, before we ran out of time in the break between sessions. Golisano Hall seemed to be filled with similar face-to-face chats between DBAs on every conceivable aspect of SQL Server, which left me with the impression that SQL Saturday has a bright future in Rochester, where the local PASS chapter plans to make it an annual event.

                The brightest spot of the day for me came when I won a free book in one of the sessions, basically for asking too many of the right questions. There was no shortage of interesting sessions to attend, including presentations by local PASS members Chris Crane and Rob Schoedel, as well as a couple by SQL Server “rock star” Grant Fritchey. Given that I can’t yet bilocate[2], I chose to attend the sessions most closely related to SQL Server Analysis Services (SSAS), MDX and data mining, the areas of SQL Server I’m most interested in. My first stop was Tim Costello’s excellent presentation “Dimensional Design Primer: What You Need to Know.” As soon as I walked in the classroom door, I learned things I was never introduced to a year ago when I was studying for my MCITP certification in SSAS. Of particular interest were Costello’s caution against overuse of parent-child relationships and his discussion of Accumulating Snapshots, which I hadn’t heard of before. In William Pearson III’s presentation on “Overcoming Barriers and Avoiding Mistakes with BI” I also learned a lot about such interrelated topics as dealing with end users in BI, planning for SSAS projects and gathering requirements, none of which I have done professionally yet. Since this is a weak spot for me, I intended to learn as much as I could as quickly as possible, but I must have asked the right questions, because Pearson rewarded me with a ticket for a free book. I had to leave before the fourth session, long before a series of raffles in which expensive prizes were being given away, so I kept telling the other local PASS members that I would jinx myself and win, but would have to forfeit the prizes since I wouldn’t be there to collect. No one’s notified me yet that I forfeited the iPad or other goodies, but if so, I was still happy to walk away with my free copy of Kalen Delaney’s mammoth $60 tome SQL Server 2008 Internals. I could have chosen from about a dozen other titles available at the booth where I redeemed my ticket (including the last copy of a book by Joe Celko, which was tempting) but I’ve been impressed with Delaney’s brilliance before and figured her book might come in handy on the job someday.

            It might even be useful in helping me understand the internal workings of DMX and Microsoft’s data mining algorithms, which is an area I’d like specialize in. One of the surprising things about the conference was the realization that little is known about the data mining tools built right into SQL Server, which seem to be drastically underused. Almost all of the seats were taken at the three BI sessions I attended, mainly by local DBAs and developers, some of whom clearly outclassed me in subjects like dimensional design and MDX. It was refreshing to see so much interest in BI topics, but I didn’t meet anyone who was familiar with the data mining aspects of SQL Server at all. At times I found myself rushing to explain to others just how much value they can add to BI, in my usual garbled way, magnified by lack of sleep; I’m not exactly known for my verbal skills, even on a good day. At a few points, I remember mentioning to people that my series of six blog posts beginning with An Informal Compendium of SSAS Errors, Part 1: Introduction is really the only centralized source on the planet that explains some of the errors we encounter in SSAS, particularly with the data mining language, DMX. Attending the conference convinced me more than ever that I ought to specialize in that area, not only because it is the area of SQL Server that interests me the most, but that there is a crying need for anyone to spread any knowledge of the topic at all. Part of the reason that these valuable tools are neglected is because they are difficult to work with, even though Microsoft designed them quite well (the lack of documentation on error messages excepted, of course). It really helps to know MDX before working with DMX, both of which ultimately depend on T-SQL at least to populate the cubes from relational data sources. It can be quite helpful to know T-SQL first, but that foreknowledge can lead to some misunderstandings when applied to MDX. As Pearson said in the third session I attended, “Getting Started with MDX,” there is a subtle difference between the two languages, in that MDX is used to locate points on a cube. T-SQL is similar, but its primary purpose is not to determine a position in two-dimensional space, at least not in the same sense that MDX identifies positions in multidimensional hyperspace. In an hour, Pearson explained MDX much more succinctly that I ever could in a day. In fact, I doubt it could be explained any more efficiently and clearly. MDX is not difficult to learn because it is badly designed (unlike a lot of other computer languages in use today, whose syntax is unnecessarily complex) but because the subject matter is inherently intricate. Identifying points in hyperspace is simply an irreducibly complex problem. Pearson lamented that Microsoft let the inventor of MDX, Moshe Pasumansky, jump ship to Google, and I agree with him. Pasumansky designed a language so efficient that even Microsoft’s competitors had to jump on the bandwagon and adopt it themselves. From my limited vantage point, it seems that Microsoft has left all of its competitors in the dust as far as BI goes, especially since the potential of DMX and data mining hasn’t even been touched yet.  Unless I finally manage to get my copy of SQL Server Developer Edition 2012, I plan to get back to data mining topics in my next few blog posts, perhaps in the form of a few tutorials. Perhaps I will post on some of the interesting and quite practical uses I have put it to already. The first SQL Saturday in Rochester was filled with highlights for me, with the only drawback being that I had to leave early for an early Mother’s Day-slash-double-birthday party.[3] The biggest lesson I took away, however, is that the data mining field is even more wide open than I thought, because the excellent tools Microsoft has integrated into SSAS really aren’t getting as much press as they should.