Author Archives

Mark

Program Manager on the Power BI Customer Advisory Team at Microsoft.

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.

Upcoming Presentation: SQL Saturday 119, Chicago IL May 19th

By Mark on March 27, 2012

I just got the great news that my session, MDX Trek: First Contact has been accepted for SQL Saturday 119 in Chicago on May 19th. I had the honor of attending the past two events there including presenting last year, and I have to say the fine folks of the windy city know how to put on a great event. If you have the means, I highly recommend attending; it’s so choice.

Upcoming Presentation: SQL Saturday 118, Madison WI April 21st

By Mark on March 12, 2012

I am all kinds of delighted to report that my MDX Trek: First Contact session has been accepted for SQL Saturday 118 in Madison, WI. Yay!

Since this is the first SQL Saturday ever in Wisonsin, I must congratulate the organizers of SQL Saturday 118 for boldly going where no SQL Saturday has gone before.

Upcoming Presentation: MADPASS Meeting April 25th

By Mark on February 28, 2012 • ( 2 Comments )

After attending a great SSRS session by Jes Schultz Borland (blog | Twitter) at the 2011 PASS Summit (SQUEE!), I mentioned to Jes that one of my goals for 2012 is to present more. Well, lo and behold, she invited me to present at MADPASS , the PASS chapter in Madison, WI. SQL Community in action. Thanks, Jes. 🙂

I shall be presenting at the MADPASS meeting on April 25th with a brand new topic for me MDX Trek: First Contact. As you can imagine, it’s a Star Trek themed introduction to MDX.

Who’s got two thumbs and is a giant nerd? This guy.

Facing The Challenge: Designing a BI Infrastructure With SQL 2008 (70-452)

By Mark on February 21, 2012 • ( 6 Comments )

On Monday, Feb 6th, I passed exam 70-452, Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008. Hazzah. I am really excited about that, not because I get a shiny new MCITP certification, but because it validates how much I have learned. When I started working with SQL Server about 5 years ago, I started down the DBA track, even earning an MCITP in the SQL Server 2005 DBA track. It was not too long after that when I determined that Business Intelligence was where I really wanted to hang my career hat.

This post isn’t really about that, though. I have a process that I use to learn the material. I then use the exams to measure that learning. The process I have has worked pretty well. Actually, I am 6 for 6 when I commit to this process. Since it seems to work really well for me, I thought I would share it in case it may help someone else as well.

Typically, I like to use the Microsoft Press Self-Paced exam guides. I find them to be a great starting point and cover the key areas of the technology in question. In the case of exam 70-452, as of the time of this writing, there is not a Microsoft Press exam guide for that exam. Therefore, I instead chose Delivering Business Intelligence with Microsoft SQL Server 2008 by Brian Larson. I found it to be a great base for my prep for 70-452. In areas where I felt I needed to go deeper, I turned to Books Online.

My process consists of a few key steps.

1. I go through the text taking notes on the details of each topic. I make sure to make my notes in my own words and avoid taking anything word for word from the text. This helps a lot in that I must process the information more than once as I go.

2. The books I have used have all featured do-it-yourself style exercises during which you put what you learned into practice. I find this really valuable to gain at least some experience, particularly with skills I have never used in real life.

3. I read through the notes several times over, just like studying for test back in school.

It does not seem like much and certainly is not very elaborate. There are no tesla coils involved or anything. But sometimes it is the simple things people overlook.

Since I titled this post “Facing The Challenge,” I figured I should also show some of the faces I used during the process outline above.

Confusion. Some of the content was confusing at first.

Determination. I knew I had to commit myself to understanding all the concepts.

Realization. Ah. NOW I get it.

Blue Steel.

Satisfaction. I passed. I dun learned something.

Well, there you have it.

PASS Summit 2011 Top Ten List

By Mark on October 19, 2011 • ( 3 Comments )

After I come back from an event, whether I presented or not, I like to write up a Top Ten List of my experience. These are not necessarily ranked in order of importance; they were all cool. And there were awesome experiences that could not make the list due to limitations of the number Ten.

Behold!

10. Guidebook App

Mobile phone users (aka anyone with a pulse) were able to use an app called a Guidebook to track the schedule, speakers, exhibitors, etc. This meant that I was able to easily build my own schedule of the sessions I wanted to attend as well as fill out my evaluations online. There were configurable reminders to make sure I didn’t forget something I had put on my schedule. It was also easy to keep track of any updates to the schedule during the event. Especially for a noob like me, this app was awesome.

9. SQLKaraoke

SQLKaraoke is just fun. When you’re not singing, you get to hang out with really cool people. I have a background in Theater and Improvised Comedy, so SQLKaraoke is a total blast for me. Since I’m well…a little different, I actually use SQLKaraoke as a networking tool. I like to make up SQL-related spoofs and even do an impression or two. This time around, I ended up performing The Bangles Eternal Flame as Gilbert Gottfried. Yeah. It was pretty silly. But it ended up being a huge boon in terms of networking. People who I have long followed and respect were introducing themselves to me instead of the other way around. I was “the karaoke guy.” Bottom line: be creative in your networking. Creative use of your strengths can pay off really well for meeting new people.

8. Women In Technology Luncheon – Make Yourself Heard

This was totally a great way to meet girls. <pause>

Seriously though, this is an event really designed to help women continue to gain more traction in the workplace. Things are certainly a lot better than they used to be (my wife just started watching Mad Men), but I think we can all agree that true equality isn’t there yet. I was excited to see there were a LOT of men in there (and not just to meet girls). The wisdom shared by the panel is applicable to everyone. It was just a great experience.

7. Exhibitor Hall

I’m a nerd. I like hardware and software. I like stuff. There was all of that in here. You know the old saying, “run around like a kid in a candy store?” Well, this was more like a mall full of candy stores. Mmmmmmmm.

6. Peoples

A huge part of the Summit is networking. Meeting other people. I met so many awesome SQL professionals from around the world. It was just unreal how friendly people were. First Timers, like myself, were made to feel so welcome. I spent meals, walks (there was a LOT of walking), evenings, some events, just meeting and chatting with great folks. There were too many fantastic people to list.

5. “Preventing the Oh, Poop! Reporting Situation.”

SQL Server MVP Jessica Moss (Blog | Twitter) delivered this session. She demonstrated the out-of-the-box methods that Reporting Services provides for tracking the performance of your reports. This included how to determine that a report returned no rows (CEO’s LOVE that, right?). She also demonstrated a cool meta-report solution (reports about reports) you can use to keep an eye on things. It was cool to see a presentation about Report management to complement the ones about building them.

4. A Little Help From My Friends

My friend, Jes Schultz Borland (Blog | Twitter), gave a session called “Reporting Services 201: From Basic To WOW!” She gave some great tips on report authoring and really explained the Tablix well for anyone that was new to it. She discussed features such as the Tooltip and the NoRows message. These are things that are easily overlooked but can really enhance the user experience when implemented well. Squeeeeee! (translation of “Squee”: Hi, my name is Jes and I presented at the Summit!)

My friend, Doug Lane (Blog | Twitter), gave a session called “Developers Are From Mars, Report Servers Are From Venus.” Mr. Lane gave some valuable tips and tricks on Reporting Services through the lens of a marriage. He included the “mother-in-law always comes along” feature of the extra pages you sometimes get in SSRS and how you can often solve that with the ConsumeContainerWhiteSpace property.

NOTE: This image has nothing to do with this post. But, it’s cool so I put it in anyway.

3. Dr. David DeWitt: “Big Data – What is the Big Deal?”

On Friday, Dr. DeWitt gave his keynote, this time on Big Data. As stated earlier, my background is in Theater and Improvised Comedy. I cannot remember any of my acting classes in involving huge quantities of data. And, never having dealt with VLDBs at all in my career, the concepts related to Big Data were pretty new to me. But here is the thing: I understood this keynote. That surprised me. I expected to get lost pretty quickly. Rather, I was able to follow along due to the excellent way in which Dr. DeWitt explained it. This was not merely a presentation on Big Data, it was also a master class in how to be a great presenter.

2. BISM (BI Semantic Model)

I have done a lot of work with Report Models and enjoy dealing with tools that can enable end users. I am excited to deal with BISM and help people get their own data. Why give people a fish when you can plant a potato? Wait… Yeah. You certainly don’t plant the fish…

1. Power View (Project Crescent)

Along the lines of letting users get their own data is the fabulous Power View (formerly known as Crescent). This thing is friggin cool. That’s totally industry speak, by the way. The idea of Two Clicks To ROI is just a great way to make things easy. Essentially, no feature in Power View is more than two clicks away. It is just so easy to use and gorgeous to behold. I can’t wait to really get in and play with this fabulous tool. Keep in mind that it complements the existing tools; it does not replace any of them.

The PASS Summit was an amazing experience. If you ever get the chance to go, grab onto it with both hands.

Mark Vaillancourt

I drink coffee and know things