The upcoming release of SQL Server 2012 includes a major overhaul for Integration Services (SSIS). I write about the changes in SSIS, big and small, in Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) which will be available in March as a free download. Meanwhile, if you really can’t wait that long, you can get a sneak preview of a few chapters beginning February 1.

When writing a book like this which focuses on all the new stuff, it’s easy to get caught up in minutiae. As I begin to wind down the writing process for this book, I thought it would be interesting to step back and consider which enhancements I thought were particularly helpful for the package development process. There are certainly other enhancements that are helpful too, but the following list includes the ones that are special favorites of mine because they address problems that I encounter most often during my own package development or when teaching students about SSIS.

In no particular order, here is my list:

1. Interface overhaul. Business Intelligence Development Studio (BIDS) is now SQL Server Data Tools (SSDT), but that’s not the important part. Inside SSDT, the Toolbox window now includes a Favorites folder into which you can place components that you use regularly. I find that people can spend a lot of time hunting through the list of available components, so this feature can really help you out when there’s only a handful of components that you use in every package. There are several other changes to the interface, such as zoom control, and icons to specify whether a component succeeded or failed during package execution that improve the development experience as well.
2. Shared Connection Managers. The idea of building something once for reuse many times is not new, and yet until now you’ve had to add the same connection managers to package after package, which turns into a rather tedious task if a change was required to each package’s connection manager. Now you can set up a connection manager once and reference it in multiple packages. As an added bonus when you use a Cache Connection Manager that you share between a parent and child package, the two packages share the same cache which optimizes performance for lookups against the same source in both packages.
3. Undo and Redo. How many times have you made a change to a package and then instantly regretted it? I bet it’s happened at least once! Fortunately, the Undo button is now enabled so you can reverse that change, and the Redo button is available also in case you change your mind afterwards.
4. Resolve References Editor. Managing metadata in the data flow pipeline is a concept with which many beginners struggle, especially when they make a change early in the data flow that has a ripple effect across the downstream components and causes metadata errors.  Now SSIS includes a Resolve References Editor that you can use to quickly resolve the mapping of input and output columns between components. You can use it to check that columns are mapped to one another properly, and also to see which columns remain unmapped.
5. Variable scope. Here’s another commonly encountered problem. How many times have you added a variable only to realize later that you inadvertently assigned its scope to a task rather than to the package? Your only option was to delete the variable and add a new one while taking care to set the scope correctly. The new SSIS creates new variables at the package scope by default, and allows you to move an existing variable to a different scope.
6. Left() function. In previous versions, you can use the Right() function or the Substring() function to work with portions of  a string, but there was no Left() function until the SQL Server 2012 release. Hooray!
7. ReplaceNull() function. A very common task in data warehousing is to replace NULL values with some value. This new function just simplifies that task.
8. Project deployment model and parameters.Working with SSIS projects and deployment is a completely new way of setting up packages to work in a new environment in SQL Server 2012, whether test or production. When you combine this deployment model with the use of parameters, you use parameters instead of configuration files to set run-time values for expressions. Most people didn’t understand what to do with configuration files, so this will be a welcome change indeed for that group. Even if you did work successfully with configuration files, you will likely still find it easier to set up parameters than configuration files.

You can download the upcoming ebook to get more details about the items in my list above. Specifically, Chapter 6 covers Integration Services and is available now in the first draft of the ebook. And come back to visit this blog often – I’ll be providing more details in the coming weeks about each of these items that space did not permit me to include in the ebook.

Meanwhile, you might also like these resources about SSIS and the upcoming release:

• The SSIS product development team provides posts from time to time to elaborate on various aspects of the product. You can use the Denali tag on the blog site to focus on the the new features of 2012.
• Jamie Thomson has a comprehensive list of changes with screen shots, which after all are worth more than a thousand words!
• Jamie also posted an excellent walk-through of the level of detail that package execution can log in the SSIS catalog.
• Last, but not least, Jamie has published a reporting pack that helps you monitor logging in the SSIS catalog.

I think my list of favorite enhancements is heavily biased by my experiences this week working with a new SSIS 2008 r2 developer. Perhaps you have a different set of favorites. If so, share your thoughts! Which SSIS features do you like best in the SQL Server 2012 release?

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!