Month: April 2012

Upcoming Presentation: I-380 PASS May 8th

By Mark on April 30, 2012

I am quite happy to say that I will be giving my MDX Trek: First Contact presentation to I-380 PASS on May 8th. Thanks to Ed Leighton-Dick ( b | t ) for the invitation.

SQL Saturday Madison (118) Top Ten List

By Mark on April 25, 2012

I have a habit of posting Top Ten Lists after SQL events. I hereby continue said habit. As always, these are not really in any particular order. And also, as always, there were cool things that could not make this list due to limitations of the number Ten. A huge thank you to all the fine humans who worked so hard to make this happen and the sponsors for dropping some coins in the mighty fountain of learning.

Behold, my Top Ten list for the SQL Saturday (SQLSat118) this past weekend in Madison, WI.

10. Carpooling

When I indicated that I was headed out to SQLSat118, a fellow member of PASSMN messaged me on Twitter to ask if I wanted to carpool. That fine fellow was Mr. Mike Donnelly ( b | t ). I didn’t know Mike much at all. We had just seen each other at PASSMN meetings, and such. It turns out that he didn’t try to sell me to any drug cartels or anything. He is a very cool guy and I had a lot of fun driving with him and hanging out with him in and around the event itself.

9. New Presenter

Mr. Donnelly was giving his first full length presentation at SQLSat118. It was the first session of the day for me, called SSIS: Figuring Out Configuring. He talked about the use of xml configuration files in SSIS 2008 R2 and also discussed some of the new configuration options in SSIS 2012. He also demonstrated how to easily convert a solution from “package deployment” to “project deployment” in SSIS 2012. Mike really did a fine job. I honestly could not tell that he was really new to presenting as he stayed relaxed and had fun with it. Bravo, Mike.

8. SSIS 2012 New Features

Ever since I attended my first SQL Saturday (in Chicago) a few years ago and met Norman Kelm ( b | t ), I have made it a point to attend his SSIS sessions. I find his knowledge of SSIS to be impressive. This time around he discussed new features in SSIS 2012. There was some great discussion in that session, for sure.

7. Minimal Processing For Large Cubes

I attended this session by Ross McNeely ( linkedin ) being a bit of a newbie to SSAS. I am really glad I did. Ross has created a very cool framework that assesses each partition in your cube to determine the what type of processing it needs. Partitions that can be processed using Process Add are done that way. Only partitions that NEED Process Update get that more intense treatment. I haven’t had to deal with any processing headaches yet, but I found his framework to be really flexible and robust. Great session.

6. Performance Monitoring and Tuning in SSAS

I attended this session by mighty Steve Hughes ( b | t ) directly after my own session (which I will get to later). This was a bit of an advanced session, particular with my being pretty new to SSAS. Steve’s relaxed and fun delivery style is always a good time, though. And I didn’t come away empty handed. I learned how to monitor when MDX queries are hitting the cache and when they’re not, letting me know what queries need a little help. But the biggest thing I learned in that session was that I have a lot to learn before I can even begin approach the level of experienced SSAS folks like Steve. But don’t tell him I said that; I’ll never hear the end of it…

5. Data Visualizations in Reporting Services

I spent the last session of the day with MVP Stacia Misner ( b | t ) and Reporting Services. Indicators, Data Bars, and Sparklines, Oh My. Stacia demonstrated the configuration of each of these as well as maps. She also covered some visualization design practices, including what NOT to do. Can you say 3D? I agree totally that 3D visualizations defy the basic tenet of a report: Above All, Show The Data. It’s great to learn from experts like Stacia that I don’t get to see very often.

4. Life is Like a Box Of Chocolates

While hanging out a bit before the speaker dinner on Friday night, the great Erin Stellato ( b | t ) opened up a box of chocolates and offered me one. I immediately popped it into my mouth all in one piece. Apparently, that was a breach of protocol. I learned that you MUST bite the chocolate in half and share info about what kind of chocolate it is. I had never encountered this law of the universe. From now on, whenever I have myself a chocolate, I shall think of Erin (and furtively pop the whole dang thing into my mouth, probably).

3. Coffee

We don’t have Dunkin Donuts here in Minnesota. Having grown up in New England where they are EVERYWHERE, I miss their coffee big-time. On the drive back, Mike and I hit a DD just outside of Madison. So good. (sigh)

2. SQL People

I have said this before and I will say it again. The people of the SQL community are JUST AWESOME. I spent a lot of time hanging out with really excellent folks. Just sitting around chatting with people I respect and like was just such a great part of the experience. If you find yourself attending a SQL Saturday or other similar event, I IMPLORE you to go introduce yourself to people. You won’t regret it.

1. MDX Trek: First Contact

I have to mention my own session here. I have been overwhelmed with how well-received my MDX Trek: First Contact session was. The comments I received on the evaluations, on Twitter, and face to face, have all been amazingly positive. I worked very hard on preparing that presentation, so the fact that it has had such an impact is just really uplifting. Thanks to everyone who attended.

Upcoming Presentation: PASS DW/BI Virtual Chapter May 9th

By Mark on April 9, 2012

I am elated to announce that I will be delivering my MDX Trek: First Contact presentation on May 9th (at 4PM Central Time) to the PASS Data Warehouse and Business Intelligence Virtual Chapter. I have presented to local groups and at SQL Saturdays before (with a few more coming in the next two months) but never for such a geographically diverse audience. I am really excited for this opportunity and hope it will be a great step in establishing myself as a quality presenter in the SQL community.

Using Bogus Measures in SSRS Parameter Available Values Lists

By Mark on April 3, 2012 • ( 2 Comments )

I am currently working on a project involving many reports against an Analysis Services 2008 R2 cube that we implemented for the client. The available values lists for the report parameters are largely just lists of members of various dimension attribute hierarchies from the cube. Since we are not going to actually use any measures in our available values lists, we decided it was best if we didn’t even hit any measure groups at all. Why waste resources retrieving facts we don’t even want, right?

Let’s take our example from the Adventure Work DW 2008 R2 SSAS database, which contains the Adventure Works cube.

Suppose we have a report parameter that allows the user to select from the list of Product Categories.

We could start by just putting the Product Categories on the COLUMNS axis, thereby specifying only the items we actually want. Since all queries must include the COLUMNS axis, we have to start there.

-- Query 1
SELECT [Product].[Product Categories].children ON COLUMNS 
FROM [Adventure Works]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Query 1 produces the following results:

With this query, we are hitting a measure group even though we didn’t specify one, in this case the Reseller Sales measure group, to retrieve the default measure, Reseller Sales Amount. Not only that, but we have the Product Categories pivoted and would have to un-pivot them to use them in an available values list. That’s not the best way, for sure.

We could just go ahead and resign ourselves to retrieving a measure and specify the Reseller Sales Amount measure since we’re going to get it anyway.

-- Query 2
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

Query 2 produces the following results:

This is less than spectacular as well. While we actually have our Product Categories on the Rows where we want them, we are still hitting the measure group for no reason. We have already seen that we will get the default measure even if we don’t specify one. So, let’s specify one that isn’t in a measure group at all.

In the following query, we declare a new measure called Bogus that is just a constant, “1” and nothing else.

-- Query 3
WITH MEMBER [Measures].[Bogus] AS "1"

SELECT [Measures].[Bogus] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

Query 3 produces the following results:

This query still puts the Product Categories on the rows where we want them. Hazzah. And, since we specified a Measure, even a Bogus one, we didn’t need to hit any measure groups, not even for the default measure. Double Hazzah.

So, when constructing available values lists, follow the lead of modern political discourse in the United States: Don’t muddy things up with facts. Oh snap!

Surprise SQL Server 2012 Feature: “It’s Like Siri….For SQL Server.”

By Mark on April 1, 2012 • ( 2 Comments )

In conjunction with the official release of SQL Server 2012 today, Microsoft has announced a surprise feature that was kept under wraps for three years. Casper Marley, Senior Program Manager for Microsoft’s newly announced Whimsical Products division put it this way, “It’s like Siri…for SQL Server.” “Siri,” Apple’s intelligent assistant that ships with the IPhone 4S, responds audibly not only to voice commands, but questions as well. He then quickly put his IPhone back in his pocket as several members of the Windows Phone team strolled by the well-windowed conference room in which this interview took place.

“Here is one example which integrates with the Query Optimizer,” he said, excitedly sitting up straighter in his chair. He then went on to explain that this new feature, when detecting your tuning has resulted in a plan that is ten or more times more performant than the previous plan for that query, will play a .wav file that sounds like the sizzling of bacon on a griddle. “It’s our way of providing the immediate feedback that you’re really cookin’ now.”

“Another example,” explained Marley, “has been plugged into Resource Governor. When a user is affected by RG, they will now get an audible “No resources for you!” He sat back and chuckled at the brilliance of intertwining comedy and databases.

One feature of this new tool is somewhat controversial. “We all know what happens when you shrink a database, right?” Marley then sipped his coffee for several seconds, clearly building suspense. “Well, when you actually execute a SHRINK on a database, your screen will go dark for a moment. During that time, you will hear high pitched meows and some thumps. Then a well-known, bearded SQL Server expert will appear on the screen, a single tear on his cheek.” He then explained that this expert will then hold up a small furry object and say, “YOU SEE!? See what you did!?” How that made it passed QA, I will never know.

There are some Easter Eggs in there as well. Marley turned to his laptop and said, “Watch this one.” He opened a query window and executed a differential backup of a database. The screen got fuzzy for a moment and then an alien humanoid appeared. Below this alien were the words, “All your differential base are belong to us.”

“I’ll show you one last feature, “ Marley said, turning back to his laptop. He opened a new query editor window and wrote a query against a table that gets a lot of writes. Then, to my astonishment, he put a NO LOCK hint on that table. As soon as he executed his query, there was a loudly audibly “YOU FOOL!” as shouted by Gilbert Gottfried on a famous episode of Hollywood Squares.

Marley, clearly delighted with himself, closed the lid of his laptop and stood to shake my hand, signaling the end of the interview. “We’ll let the users find the rest.”

Well, there you have it. It only remains to see what the SQL community will make of these changes.

Mark Vaillancourt

I drink coffee and know things