Month: July 2012

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


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

Figure 2


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


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


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


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


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


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.