Then one day I received an email explaining that I had been mentioned by SQL_Joker on Twitter and could I make some time for a phone conversation to talk about a potential business opportunity? Well, that email got my curiosity piqued about this Twitter business, so I set up an account so that I could see for myself what the fuss was all about, and the rest – as they say – is history!

1. Get help with a problem. Whether you’re independent like me, working in a small shop, or out of resources in a big organization, there are people out there who might be able to help. Sometimes weird things happen and you need a fresh perspective for troubleshooting. Or you’ve been asked to start working with a different aspect of SQL Server and need a nudge in the right direction. With SQL Server professionals around the globe, someone out there is probably able to help. Don’t worry if no one knows who you are and isn’t following you – that will come with time. All you need to do is compose a tweet and add a hashtag at the end of your request – like #sqlhelp, #ssashelp, #ssishelp, or #ssrshelp. If you need more that 140 characters, then break your tweet into 2 parts and add 1/2 and 2/2 to each part. Before you tweet, you can use the search feature in Twitter to see tweets using these hashtags and learn what kinds of questions get answered. Obviously, it’s not the best way to get help for complex problems.
2. Learn new things. Technology keeps changing and it can be challenging to stay current. As you learn who’s who in the SQL Server community, you can follow them to get breaking news, or thoughts about trends, or resources of interest. Don’t know who to follow? Start by watching who answers questions for the #sqlhelp and other hashtags, click the name, and click the Follow button. If you like what they tweet, do nothing but continue to watch the tweets. If you don’t like what they tweet, you can always Unfollow later. If you explore Twitter more deeply, you can also see who a person follows and who follows them. You might see some names you recognize as conference speakers, bloggers, and authors of magazine articles and books. Cast a wide net.
3. Develop friendships with people who share common interests. Those of us in the SQL Server community have SQL Server in common. Often, the way we met one another was at conferences and that’s the only time we interacted from year to year. But with Twitter, we can continue the conversation. And with more conversation, the more we get to know one another and the friendships develop. And for those who can’t get to a conference, they can participate vicariously as many of us tweet interesting things that we hear and see. I’ve had Twitter exchanges with many people long before I met them, and some whom I’ve never met. So don’t feel like you have to know someone personally before you engage. Just be polite and friendly. Ask questions or offer up something you’ve learned. Before long, if you play nicely, you’ll be amazed at how you’ve been assimilated into the family.
4. Have a laugh. We work hard and take our jobs seriously. Well, most of us do! But, seriously, we need a break from time to time, even if just for a few minutes. Every now and then, someone will come up with a topic and people begin to riff on that. You’ll see things like movies or books with names adapted to something meaningful and humorous only to those in the SQL Server community. You’ll just have to keep your eyes peeled to see what I mean.
5. Work the network. As you get to know people on Twitter, you’ll find that you can get help in other ways besides the technical stuff. Maybe you need to find a job, or maybe you know of a job opening at your company. Twitter is a great way to share the need and someone might be able to help. I’ve heard lots of great stories of people getting connected in this way. Of course, having relationships built first is important. But this network of ours is not just about jobs. If you’re traveling some place new, or thinking about buying some new gadget, or need inspiration for a new way to fix something for dinner, someone in the community has an opinion that you might find useful.

If you’ve been following my recent series on MDX, which began with Location, Location, Location, you have learned some important concepts, but this series was merely an introduction and there is so much more to learn. To help you continue building your skills, here are a variety of resources that I recommend that you peruse.

Books

• SQL Server 2008 MDX Step by Step, Bryan C. Smith, C. Ryan Clay, Hitachi Consulting (Microsoft Press, 2009). This book is useful for beginners, and leads you through the key concepts of MDX as the name implies…step by step. Although it’s written for SQL Server 2008, you will find it useful for SQL Server 2008 R2 as well and, although I haven’t tested it, it should work for SQL Server 2012.
• MDX with SQL Server 2008 R2 Analysis Services Cookbook, Tomislave Piasevoli (Packt Publishing, 2011). I really like this book, as you might surmise from my review, and picked up a few tips myself. However, you’re not going to start learning MDX with this book. I recommend that you first read the Step by Step or some of the online resources first to get the most value from it.
• MDX Solutions, 2^nd edition. George Spofford, Sivakumar Harinath, Christopher Webb, and Dylan Hai Huang (Wiley, 2006). Although this book is focused on SQL Server 2005, much of it still applies to 2008 and later versions as the language remains largely intact. However, I must say this is not a book for beginners. There is lots of useful information here, but you will find it more useful if you already have a good grasp of the basics.
• Practical MDX Queries for Microsoft SQL Server Analysis Services 2008, Art Tennick (McGraw-Hill, 2010). I haven’t read this book yet, but it’s in my queue to read this year. If you’ve read it, I invite you to add a comment to this post with your impression.

 Online Resources

• Stairway to MDX Series, I have long recommended Bill Pearson’s (@Bill_Pearson) online writings, such as MDX Essentials at Database Journal, and of course must heartily recommend his latest endeavor at SQL Server Central.
• MDX Studio. This resource is not one designed to teach you MDX, but I include it as a tool that you can use to improve your MDX as it is a tool that you can use to identify problems  in your query and to capture statistics for your query if you’re attempting to improve performance. Mosha, the developer of this tool, also developed an online version for formatting and analysis.

Blogs

• Mosha Pasumansky is one of the architects responsible for the MDX language, so what better resource could you ask for? He’s no longer at Microsoft, but fortunately his legacy persists at SQLBlog.com with many posts dedicated to providing insights into MDX.
• Chris Webb (@Technitrain) is a coauthor of the second edition of the MDX Solutions mentioned above and writes frequently about  MDX. He has some very creative ideas that I have found useful in some of the more bleeding edge projects I have worked on.
• Another terrific MDX online resource is Marco Russo (@MarcoRus). Marco and Chris Webb (along with Alberto Ferrari) co-authored Expert Cube Development with Microsoft SQL Server 2008 Analysis Services which I recommend if you want to learn more about developing the cubes that we use as a source for MDX queries.
• Boyan Penev (@BoyanPenev) also writes on SSAS topics that you might find helpful for improving your MDX skills
• Greg Galloway has some interesting studies on MDX at his blog.
• SSAS-info, run by Vidas Matelis (@VidasM) is a great aggregation site that includes MDX topics.
• And of course, I have my own series which will expand over time, so keep checking back here for everything I write on MDX.

Whitepapers/Best Practices

• Identifying and Resolving MDX Query Performance Bottlenecks. MDX performance is partly dependent on the choices you make when constructing the query and partly dependent on the cube structure. This whitepaper explains how to determine where to focus your attention for resolving query performance issues.
• Analysis Services 2008 Performance Guide. Here you’ll find some good information about avoiding MDX functions that cause performance problems. You can use this whitepaper in conjunction with the performance bottleneck whitepaper to ferret out the problems you might be experiencing with slow queries.
• Analysis Services Query Performance Top 10 Best Practices. As you build your MDX skills, it’s a very good idea to keep best practices in mind. This is  a nice quick reference to have handy.

After an intermission for the holidays, I’m resuming my series of posts that provide an introduction to MDX. This series began with Location, Location, Location, and continued through A Gentle Introduction to Sets. At this point, you should have a good idea of the basic structure of a query using objects in an Analysis Services cube. In this post, I’m going to explain how you can expand your options by adding objects at query time.

You might do this during cube development to test out calculation syntax before adding it to the cube, or you might do this only when you need to run a one-time query to answer a specific question or test the cube. You can even use this technique for Reporting Services reports or with  other tools. But I have to recommend that, wherever possible, you should put calculations inside a cube which would render moot what I’m about to show you.  Why? So you don’t have to reinvent the wheel each time you need a calculation. One of the reasons we build cubes is to centralize business logic, which includes calculations. That said, some calculations can’t go in the cube. Whatever your reason, there will likely come a time that you’ll need a calculation added to your query, so the point of this post is to prepare you for that time.

We’ll start with an analogy in Excel, using a percent of total calculation as shown below. The formula consists of a numerator to get the sales amount on the current row and a denominator that references the total sales amount in cell B2. We use the terms relative reference and absolute reference to describe the values in the numerator and the denominator, respectively. So for Road-250 Black, 44 on row 4, we have a formula =B4/$B$2, where B4 is a relative reference and $B$2 is the absolute reference. As we paste the formula into rows 2, 3, and 5, the formula changes to =B2/$B$2, =B3/$B$2, and B5/$B$2.

We can get similar behavior in an MDX query by using tuples. I can create one tuple as an expression (the term we use in Analysis Services that corresponds to a formula in Excel) for the numerator, and a second tuple as an expression for the numerator. Let’s create a query that has a calculation for the numerator and denominator separately, and then a third calculation for the final calculation so that we can see the results independently.

First, I like to create the query with placeholders, just to get the query structure straight before I introduce anything else that might compromise the query as I add complexity. Not that this example is complex, but it’s a good habit to develop. Start simple, and build up from there. It’s too easy to mess yourself up with a stray comma or parenthesis. Note that we add in each expression in a WITH clause that precedes the SELECT statement, and that we add the expressions as measures. Then we can use them just like a measure that comes from the cube in the SELECT statement.

WITH
MEMBER [Measures].[Numerator] AS NULL
MEMBER [Measures].[Denominator] AS NULL
MEMBER [Measures].[Pct of Total] AS NULL
SELECT
{[Measures].[Sales Amount],[Measures].[Numerator],[Measures].[Denominator],[Measures].[Pct of Total]}
ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [Adventure Works]

Now let’s update the numerator expression by replacing NULL with [Measures].[Sales Amount]. You can see below that the query result simply repeats the sales amount value from the first column in the second column. Let’s review what we know about tuples from Location, Location, Location. When the query resolves the tuple for the second column, the current measure – [Measures].[Numerator] – doesn’t really exist in the cube, but its expression redirects to [Measures].[Sales Amount] which then gets combined with the category member on each row to produce a tuple which in turn resolves as a value retrieved from the cube. Just like the tuples for each value in the first column. This tuple is like the relative reference we saw in the Excel example – Sales Amount varies according to the row.

Next let’s update the denominator expression by replacing NULL with a tuple expression: ([Measures].[Sales Amount], [Product].[Category].[All Products]). This time the query results shown below repeat the tuple value that we see at the intersection of the first column and first row. When the query resolves the tuple for the third column, both the measure in the tuple and the category member come from the expression. Thus, we get a constant value for each row in the third column. This tuple is like the absolute value in the Excel example.

So let’s ponder tuple behavior in query results again for a moment. Simply put, a tuple resolves as by combining the current column member, the current row member, and whatever members are in the WHERE clause (aka the slicer). If a member on rows, on columns, or in the WHERE clause is a calculated member, then whatever member the calculated member references in the expression will be overridden in the current row, column, or slicer member. If the expression is complex – that is, composed of operations on multiple tuples – then the contents of each tuple is evaluated independently. Thus, in the percent of total calculation, we have the numerator changing dynamically as row members change but the denominator remaining constant.

Putting the pieces together, we get the final query as shown below. Notice the FORMAT_STRING property added to the Pct of Total expression to get a percentage format. It’s not necessary for Numerator or Denominator because the use of Sales Amount directly (rather than as part of a computation) results in the use of the currency format string as defined for the measure in the cube.

WITH
MEMBER [Measures].[Numerator] AS [Measures].[Sales Amount]
MEMBER [Measures].[Denominator] AS ([Measures].[Sales Amount], [Product].[Category].[All Products])
MEMBER [Measures].[Pct of Total] AS [Measures].[Numerator] / [Measures].[Denominator], FORMAT_STRING='Percent'
SELECT
{[Measures].[Sales Amount],[Measures].[Numerator],[Measures].[Denominator],[Measures].[Pct of Total]}
ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [Adventure Works]

Although my recent series was of MDX posts, ending with A Gentle Introduction to Sets, was intended merely to be an introduction to MDX, and not a full-fledged course, I have a few more things to share with you! I had hoped I could wrap it up last week. As it turns out, I needed the entire week to finish a chapter for the Introducing Microsoft SQL Server 2012 book.

Compounding the delay is the fact that I’m currently in my remote Alaskan home and the Internet situation is still getting ironed out. It has taken me over 20 minutes just to get this simple little explanation post put out there for you. (It’s PAINfully slow, but at least I have a beautiful view while I wait!) Besides, you should be doing something else as the year winds down! I promise there will be more to come. Either as soon as my Internet speeds up or when I get back to the mainland – whichever comes first!

Wishing you a very Happy New Year!

Welcome to my third post in a series of topics on MDX for SQL University. If you’re just now joining me, you should check out the first post, Location, Location, Location, and my previous post, Writing Simple Queries, to get oriented. I’m going to continue building on the concepts introduced in the earlier posts by showing you how to use functions to create a set to use on an axis.

Many times you’re interested in seeing values for all of the members of a particular attribute hierarchy. For example, in this query, we can see sales for all categories and for all years, including a grand total for categories and years and also years that have no sales.

SELECT
[Date].[Calendar Year].Members
 ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

When you use the Members function with a dimension and hierarchy, you get everything listed in the Members folder:

What if you don’t want the All Periods member to show up? Well, you could create a set like this {[Date].[Calendar].[CY 2005], [Date].[Calendar.[CY 2006], …} but that would be tedious, and it wouldn’t automatically include new members that get added to the dimension over time. Instead, you can use the Members function with the dimension, hierarchy, and level reference to skip over the All Periods like this:

SELECT
[Date].[Calendar Year].[Calendar Year].Members
 ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

Here we have a case of “more is less.” When we use a level reference, such as we do with the expression [Date].[Calendar Year].[Calendar Year].Members, we are providing more specific instructions to the Members function so that we can get less data. Using a level reference works with both attribute hierarchies (the ones with two levels only) and user-defined hierarchies (the ones with multiple levels and a pyramid-shaped icon).

One more little change to this query might make it better. If we don’t want to see CY 2009 and CY 2010 columns when they are empty, we add the NON EMPTY keyword in front of the set definition like this:

SELECT
NON EMPTY [Date].[Calendar Year].[Calendar Year].Members
 ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

What if we want to see the category sales broken down not only by year, but also by sales territory? We can combine sets on an axis using the CrossJoin function like this:

SELECT
NON EMPTY [Date].[Calendar Year].[Calendar Year].Members
 ON COLUMNS,
NON EMPTY
CrossJoin([Product].[Category].Members,
	[Sales Territory].[Sales Territory Group].[Sales Territory Group].Members)
ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

The CrossJoin function takes only two arguments, but you can use that expression as one argument in a second CrossJoin function to create a grouping of three dimension hierarchies on an axis. There are several ways to get the crossjoin effect which might be easier to read when you start adding more dimension hierarchies to the mix. One is the use of a “polymorphic operator” such as an asterisk * like this:

[Product].[Category].Members *
[Sales Territory].[Sales Territory Group].[Sales Territory Group].Members

The other is to create a tuple set like this:

([Product].[Category].Members,
[Sales Territory].[Sales Territory Group].[Sales Territory Group].Members)

I know I said in a previous post that a tuple is like a coordinate to a cell, and it appears in the above example that I’m violating that rule by using sets in a tuple. But Analysis Services resolves this expression as a set of tuples by taking every member of the first set (the product category members) to create a tuple with every member of the second set (sales territory group members).  That set gets placed on the rows axis and the set of calendar year members gets placed on the columns axis, and then the tuple coordinates for each intersection of a row and a column produces a new tuple definition that generates a result, such as $709,947.20 for the pseudo-tuple (All Products, Europe, CY 2005).

There are many more functions that we can use to generate sets to control what we see on rows or columns. More than I can cover in this post. (In fact, there are entire books that cover this topic. In a future post, I’ll provide some recommendations.) For now, I’ll leave you with one more function to add to your repertoire. This time we want to see only those product categories and sales territory combinations that have sales greater than $1 million in CY 2008, but we want to see their sales for all years. To do this, we use the Filter function. This should satiate your desire to use a WHERE clause in your query, which I told you in my previous post does not work like a filter as it does in a T-SQL query.

SELECT
NON EMPTY [Date].[Calendar Year].[Calendar Year].Members
 ON COLUMNS,
Filter(
	([Product].[Category].[Category].Members,
		[Sales Territory].[Sales Territory Group].[Sales Territory Group].Members),
	([Measures].[Sales Amount],[Date].[Calendar Year].&[2008])  > 1000000)
ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

The Filter function takes two arguments. The first is the set that you want to filter. And the second is a Boolean expression that determines which members will be in the set that the Filter function produces. In this case, I used a tuple expression to focus on the 2008 sales.  If I had used only Sales Amount, the filter would have produced a set of category/territory combinations having total sales for all years greater than $1 million.

The key concept to take away from this post is that sets go on rows and columns, and that we can control very precisely which members are in those sets by using functions and tuple sets. A very common function to use is the Members function, and it’s also common to use NON EMPTY to rid ourselves of rows or columns that contain only null values.