Intro To MDX Decathlon–The Basic MDX Query (2/10)

In the previous post in this series, Intro to MDX Decathlon – Cube Space: The Final Frontier (1/10), we learned the terminology we use when talking about cubes and cube space. We also gave a little primer on a helpful way to imagine the cube space while working with MDX.

In this post, we will start looking at the basic structure and syntax of MDX queries. But first, let’s set the stage by showing how to get to the MDX Query Designer in SQL Server Management Studio (SSMS).

Note: Since the source code formatting plug-in I use with Live Writer does not have a specific setting for MDX (there is one that does, but it has other behavior I really don’t like), you will notice that it will seem to make strange choices in what gets highlighted in different colors. Essentially, it will put T-SQL keywords in blue, even if they are in the middle of an identifier. Just be aware that the highlighting of keywords in SSMS MDX query editor will differ from my source code snippets for MDX.

Opening The MDX Query Editor in SQL Server Management Studio

The sample queries in this series will use the cube I created for my MDX Trek: First Contact presentation. You can find a link to it in the Introduction post for this series, Intro To MDX Decathlon – Introduction.

To open the MDX query editor in SSMS, just follow these steps.

  1. Open SSMS.
  2. Connect to your Analysis Services instance.
  3. Right-click on the SSAS database containing the cube you want to query.
  4. Choose New Query.
  5. Choose MDX, as shown in Figure 1.

Figure 1

image

This will take you to the MDX query designer, which should resemble Figure 2.

Figure 2

image

The first pane on the left, marked with the 1 in Figure 2, is the Object Explorer for the SSAS instance. You can see that my StarTrekDW SSAS database has been expanded to see the cubes it contains. It has just the one, called Borg. What else could I call my cube in a Star Trek themed presentation? The Borg cube has one Measure Group, Episode, and three Dimensions: Date, Season, Series.

The second pane, marked with the 2 (clever, don’t you think?), shows the metadata of my cube. You can see once again the the Episode Measure Group, this time along with its single Measure, Episode Count. You can also see my Dimensions again.

The third pane, brilliantly marked with a 3, is the query window where you write your MDX.

You will notice a lot of brackets in MDX you see out in the wild. Essentially, the same rules apply to MDX that apply to T-SQL regarding when brackets are required. A key here is the use of spaces in object names. I, for one, can’t stand having spaces in the names of objects in the core database engine. I don’t ever create objects with spaces in their names. But cubes are designed for users, not DBAs and developers. As such, objects in cubes will very often have spaces in their names as it makes them easier to read. It is common practice, therefore, to use brackets as a matter of course so that you don’t have to worry about it.

There are three different styles to use when referencing dimension members in your MDX. Sometimes the situation dictates which of the options are available.

Attribute Hierarchy Member (Most Complete)

[Dimension].[Attribute Hierarchy Name].[Member]

Example from Borg cube:

[Series].[Series Name].[Star Trek: The Original Series]

.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; }

.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; }

 

Attribute Hierarchy Member (by UniqueName)

[Dimension].[Attribute Hierarchy Name].&[Member UniqueName]

Note that the UniqueName of a member corresponds to it ID. For example, in DimSeries in my data warehouse, the Star Trek: The Original Series row has an ID value of 1. In the cube, that translates into the UniqueName of the member in the Series dimension. So, the following example will produce the same result as the example above.

Example from Borg cube:

[Series].[Series Name].&[1]

.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; }

 

Attribute Hierarchy Member in Dimension With Only One Attribute Hierarchy

[Dimension].[Member]

If there is only one Attribute Hierarchy in a Dimension, it is not necessary to specify the Attribute Hierarchy.

Example from Borg cube:

[Series].[Star Trek: The Original Series]

.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; }

.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; }

User Hierarchy Member

[Dimension].[User Hierarchy Name].[Hierarchy Level].[Member]

Example from Borg cube:

[Date].[Date Hierarchy].[Calendar Year].[1966]

.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; }

.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; }

 

Bring on the MDX

It’s time to dive into MDX query syntax with Figure 3.

Figure 3

image

You will notice in Figure 3 that the basic MDX query has some similarities to the basic T-SQL query. You see it starts with a SELECT clause, includes a FROM clause and also has an optional WHERE clause. You my be tempted to start thinking about MDX as kind of a dialect of T-SQL. I implore you not to do that. It will only lead to pain and frustration. The behavior of MDX, while sharing some keywords with T-SQL, is a very different animal.

MDX queries will feature a SELECT clause that will determine what cube cells will be returned. Within the SELECT clause, there are query axes. I show two query axes in Figure 3, Columns and Rows. The vast majority of MDX queries you will encounter in the wild will have these two axes and no more. In fact, SQL Server management studio supports only these two query axes. Wait. More axes? Yes. MDX supports up to… wait for it… <pause for effect> 256 query axes. Two hundred fifty-six. Yup. I can’t even fathom such a thing. Believe me, from my perspective, two axes are plenty for now.

The 256 query axes are numbered from 0 to 255. The first five axes also each have an alias you can use in place of the number. For this intro, we’ll just stick with the first two axes. Axis 0 can be referenced using the alias Columns. Axis 1 can be referenced using the alias Rows. Just for clarity, my samples will use the aliases Columns and Rows rather than 0 and 1.

Columns Axis

The first axis we will look at is the Columns axis. That is noted in Figure 3 as the <<expression>> ON COLUMNS. That tells the MDX engine to put the information specified in that expression across the top of our result set, rather like the columns one might see in an Excel spreadsheet. If your query has only one axis, that axis will be Columns. You cannot use any other axis until you have specified the Columns axis.

SELECT
      [Measures].[Episode Count] ON COLUMNS
FROM [Borg]

.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; }

 

Result:

image

Since we only specified the Columns axis, we don’t have anything in the rows to provide context for the Episode Count value returned. What we end up with is the total Episode Count across the entire cube. I’ll explain in the next post in this series how that is working behind the scenes.

Rows Axis

The Rows axis is specified using <<expression>> ON ROWS, as shown in Figure 3. This corresponds to the rows of your result set, just like the rows one might see in an Excel spreadsheet. This is the second possible axis. If you will have two axes in your query, you will have Columns and Rows. You cannot specify anything for Rows until you have specified Columns.

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]

.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; }

 

Result:

image

In this result, we place the [Star Trek: The Original Series] member on the rows, resulting the cell that contains Episode Count for just that series.

WHERE Clause (Slicer Axis)

The WHERE clause is specified in Figure 3 using the [ ] notation common to SQL Server documentation, marking it as optional. Your MDX query need not have a WHERE clause at all.

The WHERE clause in MDX is often referred to as the Slicer Axis. Since other key parts of the MDX query are axes, I suppose it only makes sense to a lot of people to refer to this as one. What is important to remember here is that the WHERE clause is not a true axis like Columns and Rows are. An axis in MDX has a direct impact on the structure of the result set that is returned. The Slicer does not. Rather, it affects the contents of the cells that are returned.

Before we implement the Slicer, let’s start by returning more rows so that the demonstration is clearer.

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].members ON ROWS
FROM [Borg]

.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; }

Result 1:

image

In this result, we specified that we wanted every member of the [Series Name] attribute hierarchy on the rows by using the .members function (which will be explained in a later post in this series). Notice that we have a total of 6 rows and 1 column. Now let’s add the Slicer.

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].members ON ROWS
FROM [Borg]
WHERE [Date].[Date Hierarchy].[Calendar Year].[1966]

.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; }

Result 2:

image

Notice now that we still have 6 rows and 1 column. The structure is exactly the same, just the contents of the cells are different. This is very different from the WHERE clause in T-SQL that can have a direct effect on the rows returned in the result.

Wrapping Up

In this post, we showed how to get into the MDX query editor in SSMS and went over the basic syntax and structure of MDX queries. In the next post, we’ll dig into a very important concept for working with MDX: the tuple.

Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10)

Since this series is an introduction to MDX (Multidimensional Expressions), let’s start off with a few definitions. The following terms form the foundation of our discussion of MDX and Cube Space.

Measure: A value used to asses a business process or event. Measures are typically numeric and often map to fields in your fact table. These values are frequently aggregated in your queries. Examples are Sales Amount and Order Count.

Measure Group: A container into which related Measures are organized. These typically map to fact tables. Examples are Internet Sales or Reseller Sales.

Dimension: A collection of information used to categorize Measure values. These form the BY or the FOR EACH of the questions your queries are meant to answer. An example would be a Publisher dimension. Dimensions usually map to a Dim table.

Attribute: A row or field in a Dimension. Attributes are organized into hierarchies. Examples include a Publisher Name in a Publisher dimension.

Member: A distinct value within an attribute’s domain. An example would be the [Random House] in a Publisher Name attribute.

Hierarchy: A structure in which dimension attributes/members are organized at different levels relating to each other. An example would be a Date hierarchy in which Dates roll up to Months, Months to Quarters, and Quarters to Years.

Attribute Hierarchy: A hierarchy composed of a single attribute. Figure 1 shows an example.

Figure 1

image

User Hierarchy: A hierarchy composed of more than one attribute. Figure 2 shows an example.

Figure 2

image

Cube Space

The Cube Space is a very different place from your relational database environment. It requires a different mindset to navigate. Rather than Boolean logic of whether field values meet the criteria within various filters, the Cube Space is all about intersections of dimensions in the N-Dimensional space. The “N” in this term just stands in for the number of dimensions you have, including the Measures dimension itself. I find keeping this in mind to be hugely helpful when I am dealing with MDX queries and scripts.

Figure 3 shows a one-dimensional space; essentially, a number line. This is Dimension A.

Figure 3

image

If we want to refer to a particular location within Dimension A, we can just use the name of that location. For example, in Figure 3, location (A3) is highlighted.

We can add another dimension to our space, creating the 2-dimensional space shown in Figure 4.

Figure 4

image

In this space, we can reference a particular location using the coordinates (A3,B5). This names a particular intersection of Dimension A and Dimension B.

Figure 5 shows the effect of adding a third dimension, Dimension C.

Figure 5

image

In this space, we reference locations using a 3-part coordinate, (A3,B5,C7). So far so good.

I have seen explanations of the cube space using the familiar X,Y and Z dimensions you may recall from Geometry class. You may recall that the X Axis is the Horizontal Axis. Y is the Vertical. Z then comes into the picture referring to Depth, adding that third dimension. All 3 are necessary for picturing a physical Cube. Going down this path can help you picture an Analysis Services cube with 3 dimensions as a physical object you can hold in your hand. The problem I have with this model is that SSAS Cubes will typically have more than 3 dimensions. Now, I challenge you to picture a physical object that has 4 dimensions that you can hold in your hand. How about 5? You can see pretty quickly that this model of picturing a physical object breaks down REALLY quickly. Thus, to me, it is not a good way to go.

I prefer to leave physics out of the whole thing. I don’t picture an object I can hold or than can exist within the bounds of Physics. Rather, I picture an SSAS Cube as just a collection of intersecting number lines, like in the figures I have shown so far, with each number line being a Dimension. By using this more linear style way of thinking, picturing the cube space with more than 3 dimensions is easy. Figure 6 demonstrates this pretty well.

Figure 6

image

You can see in Figure 6 that adding the fourth dimension, Dimension D, is no big deal at all. It works just fine. Figure 7 takes it even further.

Figure 7

image

Here we have six dimensions and it still works.

So, in this first post of my MDX Decathlon series, my main point is that by picturing the Cube Space as just a collection of intersecting number lines, rather than the physical cube that is often suggested, it helps keep things from getting too complicated too quickly. It was when I figured this out that MDX started to click for me. I got it. Believe me, I still have a lot to learn about MDX, but this foundation is really important.

Intro To MDX Decathlon–Introduction

For quite some time now, I have had an idea in the back of my head right next to some useless trivia about Sherlock Holmes. A blog series with ten parts, called a Decathlon, would be really cool. Particularly with the Olympics Games in London having just started, now is a great time to get this going. I have ideas for more Decathlons, but I figured I would start with one on MDX.

I have taken content from my MDX Trek: First Contact presentation and broken it up into 10 easily digestible chunks. The source data for this can be found here. The list below will serve as the map to each post. I will update it as each new post is released.

1. Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10)
2. Intro To MDX Decathlon – The Basic MDX Query (2/10)
3. Intro To MDX Decathlon – The Trouble With Tuples (3/10)
4. Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10)
5. Intro To MDX Decathlon – .members Function (5/10)
6. Intro To MDX Decathlon – Hierarchy Navigation Basics (6/10)
7. Intro To MDX Decathlon – NON EMPTY and CROSSJOIN (7/10)
8. Intro To MDX Decathlon – LEAD() and LAG() Functions (8/10)
9. Intro To MDX Decathlon – PARALLELPERIOD() Function (9/10)
10. Intro To MDX Decathlon – PREIODSTODATE() Function (10/10)

Professional Development Plans: Turning Your Job Into a Career, Part 2

Long ago in a galaxy far far away, I blogged about my Professional Development Plan (PDP). That really was a long time ago: May of 2010. Wow. I want to provide an update on my own PDP and reiterate their value. I also have a few tips to share.

Updates From Last Time

1. One of my PDP goals at that time, as I was really starting to delve deeper into Business Intelligence, was to pass the 70-448 Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. I did that. I have since achieved my MCITP in 2008 BI by also passing 70-452 PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008. I work for a consulting firm. Certifications are an important part of Microsoft Partnership criteria for our organization. So, certifications certainly do matter here. They also help me by serving as a measurement of my learning. I know there are people who feel that certifications have no value whatsoever since some people have learned how to game the system and get certified with little to no effort or experience. I think it is short-sighted and unfair to dismiss their value. People have learned how to steal cars relatively easily, but that does not invalidate car ownership. Now, I’m not saying that certs are everything by any means. A cert is no guarantee of aptitude, but neither is it meaningless.

2. I explained my personal goals of establishing myself as an expert and being an active member in the SQL Community. I had also listed the MVP Award as a goal. As I had explained in my previous PDP post, the MVP itself was not truly a goal, but rather would a possible side-effect of achieving my personal goals of community support and expertise. Since the MVP is an award, and not something with a definitive roadmap, having it as a goal is silly. I have removed that. Don’t get me wrong, it would be cool. But MVP is something that happens to you, not something you go get in a definitive fashion.

New Goals

Sextants and Compases1. It was back in May of 2010 that I fully decided to pursue Business Intelligence as a career path. I have learned a lot since then and have been on an engagement dealing directly with SSAS since December. I am loving it and learning more every day. I am also learning about Master Data Services (there will likely be a presentation/blog series on this in the future). I have started learning MDX and even presented on it several times. My goal is to keep forging ahead with the SQL 2012 BI tools including Power View (which is just friggin awesome) and the new SSAS Tabular Model. There’s just so much to learn. Dig it!

2. Blogging. Yeah. I have on my PDP a goal to blog twice per month. Well. Yeah. Not doing so hot on that one. I am doing better than I have in that past, but many of my posts of late have been about upcoming presentations. Those are important to me, but I promise I have some cool content coming. I am planning some blog series and I also have another writing project that I am working on that I am not ready to discuss yet.

3. Presenting. I have had on my PDP for some time that I want to present at least 3 times per year. Well. I have knocked this one out of the park. I have already done more presenting in 2012 than in the previous years of my career combined. And I intend to keep it up. I don’t think I am being overconfident when I say that presenting is something I have a talent for. Absolutely loving it helps a lot, too.

4. PASS Regional Mentor. I spent 3 years on the executive board of the Minnesota chapter of PASS, PASSMN. I have taken a few years off from that and now I am anxious to get back into community involvement. I would like to try something different this time around. My favorite part of community involvement is helping people and evangelizing SQL Server the SQL Community. RM seems a great fit for that. Thus, earlier this week, I let SQLPASS HQ know of my desire to become a PASS Regional Mentor. I am looking forward to hearing back about interviewing for the role. I am really excited about the possibility and feel that I could do a great job.

PDP Tips

Road Signs1. My original PDP post was in May 2010. It is now July of 2012. More than two years. In that time, my maintenance of my PDP has been less than awesome. I have also had periods in there, some long ones, actually, where no PDP progress was made at all. This brings me to my first tip: Cover Your Nut. That is an old Sales adage. It means, above all, do at least enough to pay your bills and maintain the status quo. There were periods where it took everything I had just to do my daily work and extra stuff just didn’t happen. There will be times when going above and beyond is just too far above and beyond what you are able to do. I urge you to be OK with that at the time. Doing more than you can handle is not a sustainable behavior; it will just lead to burnout. That said, get back to your PDP and making steps toward your goals when you can.

2. Find a mentor. Your mentor does not have to be someone older or a wizard or anything. It really can just be someone you discuss your PDP and your goals with. Having someone like that can help you stay on track and even offer advice on steps to take, etc. My own SQL Server consulting career started when Lara Rubbelke (b|t) hired me at Digineer. Lara was a great mentor for me. When she moved on to Microsoft, direct mentorship became a bit more difficult, although it is still great to chat with her about my career every now and then when we can. The person filling the role of my mentor today is Jason Strate (b|t). His knowledge of SQL Server is really impressive and he has a willingness and a knack for helping people out. I get a lot of good advice from him on blogging, presenting, and networking, as well. When it comes to more Business Intelligence focused topics, Dan English (b|t) has helped me out as well. After seeing my MDX Trek: First Contact presentation, he emailed me some valuable feedback that helped me polish it up. All of the people mentioned above have great expertise and are more than willing to share.

3. The best PDP related tip I can give is this: HAVE A PDP. It does not have to be anything especially rigid or come with drill sergeant. But working toward turning your job into a career is a lot easier when you know what you want that career to look like.

I’m going to try to be better about keeping my PDP updated. I will also make sure my next PDP update blog post doesn’t take over two years to happen.

SQL Saturday Chicago (119) Top Ten List

As is my custom following a SQL event, I hereby release my SQL Saturday Chicago Top Ten List. And, as always, these are in no particular ranking order and some really cool things could not make this list due to limitations of the number Ten.

10. 6 of 1…

This year, like last year, I rode with some really great folks. Jason Strate (b|t) drove, and brought his awesome wife Sarah Strate (b|t) who volunteered at the PASS table. Jason brought fellow Digineerians Chris Fish (t), Joe Tempel (t), Eric Strom and my very own self, whose blog you now read with rapt joy. I have to say, I was looking forward to the drive with such fine humans just as much as anything. I had a lot of fun talking and laughing with them on the trek. And Jason once again proved he is a successful driver as we didn’t crash even once.

9. Speaker Dinner

The speaker dinner on Friday night was at Dave and Buster’s. There was pool and shuffle-board and general laughing and hanging out. These are always fun and just build up even more excitement for the event itself.

8. Un-Hacking My Blog

I found out Thursday night, just before heading to bed that my blog had been hacked. My home page was replaced with an announcement of the hack and some political statements of varying levels of popularity in different parts of the world. Given that the timing was such that I was about to present at a SQL Saturday, typically resulting in a bit of a bump in traffic to my blog, and the fact that it is about the time that the PASS Summit Program Committee could be looking over my blog as a factor in whether to accept a session from me, I must confess that I uttered a few expletives. But at the same time, it was really quite funny and became a bit of a running joke throughout the weekend. Anyway, after the speaker dinner on Friday night, Eric Strom helped me out in un-hacking it. To the credit of the hacker, it was not malicious. I didn’t lose anything. All they did was replace my home page. So, recovery was quite simple.

7. My MDX Trek: First Contact Session

I gave my MDX Trek: First Contact presentation during the first slot of the day. Once again, I was overwhelmed by the great folks in Chicago and the feedback I received. It means a lot when something I worked so hard on ends up really helping people understand a complex topic. So many people have told me that my way of explaining MDX and the cube space just made it click for them. Helping people learn is such a huge part of my aspirations for my career. Knowing that I am accomplishing that is just awesome.

By the way, if there is a topic you want to learn about, write a presentation on it. You do not need to be an expert to present on a topic. You learn a lot by putting these things together.

6. Performance Tuning 2012 SSIS Data Loads

Hope Foley (b|t) gave a presentation on the awesomeness that is SSIS 2012. I found out after from Hope that she did this presentation as a challenge to herself since she did not do a lot of SSIS. If you want to learn something, present on it. Hope did a great job and set an awesome example of the “present to learn” ideal.

5. Kama Sutra of SSIS: A Guide to Loving ETL

In this fine session, Bill Fellows (b|t) showed some great tips and tricks for using SSIS well. Even dealing with a bit of a hardware failure just before the session, Bill did a great job giving a fun and informative presentation. Not only that, but he gave out candy. I like candy.

4. SQL Server 2012 Column Store Index

I knew very little about Column Store indexes before this fine session by Kevin Boles (t). Kevin did a great job explaining how they work and how they apply to data warehousing. He discussed the ups and downs of using them. He then demonstrated how they work and the awesomesauce they can bring. I definitely need to dig into these more.

3. Advancements in CDC in SSIS 2012

In this presentation by Mike Donnelly (b|t), I learned how new features of SSIS 2012 make CDC just awesomely easy. Mike did a great job explaining how to use them and showing how easy they are to use. Mike has only presented a few times before, but it doesn’t really show. He really does a fine job like someone who has presented many times before.

2. After Party

SQL Saturday after parties I have been to have been just a total blast. This one was no exception. As usual for Chicago, this one featured SQL Karaoke. What a blast this is. Hanging out and laughing and some people singing, and other people doing something that could NEVER EVER be confused with singing. Just awesome.

1. SQL People

I hung out with some people this time around that I never really spent time with before. A few of us sat chatting until almost 3am. Yeah. That’s the awesomeness that is the SQL community.

 

SQL Saturdays are just awesome. I implore you to make an effort to go to one if you haven’t before. It still amazes me how welcoming people are. The phrase “SQL Family” is totally accurate.

Dynamic SSRS Chart Data Label Visibility

The other day, there was a question posted on the #ssrshelp hash tag on Twitter. The question was whether SSRS would allow you to show data labels for a chart, but ONLY on the last item in a series. For example, if you have months ranging from January to December, can you show the data label only on December’s value?

You can indeed. Here is how you do it.

Let’s start with a basic data set query (the source does not matter since were are hardcoding everything here.

SELECT     '2011-01-01' AS ValDate, 1 AS Val
UNION ALL
SELECT     '2011-02-01' AS ValDate, 2 AS Val
UNION ALL
SELECT     '2011-03-01' AS ValDate, 1.5 AS Val

 

We’ll call it Dateset1. It returns the following:

image

Let’s see a chart based on this data with the data labels turned on.

image

This is basic stuff. Now, let’s add the necessary items to hide all of the data labels except the one for 2011-03-01.

First, we’ll add a new Report Variable. Click on Report and choose Report Properties.

image

We create a new variable called MaxDateVal. We then click on the Expression button, shown in the rectangle above.

image

Don’t worry about the red underline here. Click OK when you’re done.

Now we head over to the Visible property of the Chart Series data labels, shown below in the rectangle near the bottom.

image

Click on the property value and choose Expression.

image

Enter the following expression.

image

Click OK and then run the report.

image

Notice only the MAX date value, 2011-03-01, has a data label now.

Have fun.