I’ll never look the same way at a water closet again. (For the uninitiated, a water closet is a stall in a restroom, toilet, lavatory…you get the idea.) Before I close the door, I will check to make sure that I have an exit strategy. This was perhaps the most important lesson I learned at SQLBits X in London last week. Actually, I never saw an Exit sign anywhere in London – only a sign with the symbol shown in the illustration at right or a sign indicating the Way Out, but having a Way Out strategy doesn’t sound right to my American ears.

Each night of the conference, many of the speakers and attendees gathered in the lobby of the Novotel London West, the venue for the conference. At one point, late in the evening (or perhaps early in the morning), I stepped away to “powder my nose,” and probably didn’t mention it to anyone either. Usually, one doesn’t need to register one’s destination for that sort of activity. It’s not like going for a hike alone in a national forest. So off I went, but when I tried to leave my water closet, the door handle broke off in my hand and I was unable to unlock the door and make my exit.

It’s important for my American readers to understand why this was a problem. The water closet doors are constructed to leave only an inch or two at the bottom and at the top of the stall door and floor-to-ceiling walls on three sides. There was no way to climb or crawl out.

I’m a handy sort of person and could have used a screwdriver to pry open the door (I think), but I had no purse with me. Which wouldn’t have contained a screwdriver even if I did have it, but perhaps something else in my purse would have proven helpful. Nor did I have my phone with me so that I could phone, text, or tweet about my dilemma. I thought, “What would MacGyver do?”

I tried yelling, but no one could hear me. I was in a part of the hotel that few would be walking past. Furthermore, most of the people speaking at or attending SQLBits are men, so I could not count on someone coming to rescue me soon. Fortunately, the story ends well. After about 10 minutes, I heard someone walk in and I said, “I know this sounds weird, but I’m trapped in here, and I was hoping you could open the door from that side.” Silence. And then my door opened! Carmel – Bob Duffy’s (blog | twitter) wife – was my heroine!

Fortunately, the door locking mechanism had a handle on the outside that was still attached and functional. I don’t know what would have happened otherwise. Dismantle the door from the hinges? Chainsaw? I’ll never know. But I will not close a stall door ever again without first checking to make sure that I can get out again.

Other Lessons Learned

Of course, that experience was not the only lesson learned, but it was the most humorous. I knew many of the organizers at SQLBits from my involvement in the SQL community for years, and met others that I had previously only known by name. I learned they really know how to put on an excellent show and I appreciate all the hard work they did to make this a great event. If you ever get a chance to go, please do – you will not regret it.

I was privileged and honored to be accepted as a speaker for a pre-conference session, “A 360-Degree View of SQL Server 2012 Business Intelligence” and for two other sessions. One was “(Way Too Much) Fun with Reporting Services” which I co-presented with my daughter Erika Bakse (blog | twitter) and the other was “So How Does the BI Workload Impact the Database Engine Part I” which I co-presented with Denny Cherry (blog | twitter). Erika and I had a lot of fun showing some “out there” techniques with Reporting Services and will soon be sharing the solution and inviting the community to send suggestions for evolving the solution with more features. Denny and I also enjoyed the reprise of the first half of our PASS Summit 2011 session where we bring BI and DBA subject matter together. These sessions were recorded and will be available on the SQLBits site soon.

What lesson was there in these sessions for me? Words with Friends is not as popular in the UK as it is in the US (at least not with the SQLBits attendees) and that I can always tell my slides from Denny’s slides in a deck because mine have pictures. But he really knows his stuff and can riff at length about database performance issues. Not a surprise there, really – after all, he’s a Microsoft Certified Master.

London Lessons

I added some days before and after the conference to do some sightseeing and learned that London is a very easy town to explore. Once you get a ticket for the Underground, also known as the tube. Everything we wanted to see and do was a short walk from an Underground station in Zones 1 and 2.

Highlights of our tour of London – an absolute treasure trove for a history junkie like me:

	Erika and I had lunch with Denny and his wife Kris at our first English Pub, in view of the Tower of London and appropriately titled The Hung Drawn and Quartered.
Tower of London, where Denny, Kris, Erika and I saw the Crown Jewels, Traitor’s Gate through which prisoners were brought for incarceration (and eventual execution in most cases), and walked through a medieval palace once inhabited by King Edward I (1239-1307).
	Lunch at Ye Olde Cheshire Cheese where Charles Dickens had his favorite seat (where Lara is seated). Pictured from left to right: Kris (Denny’s wife), Erika Bakse, Jennifer Moser, Jen Stirrup, Lara Rubbelke, Buck Woody, Denny Cherry’s forehead, and Chris Webb.
Jen Stirrup took us to see Big Ben and the Houses of Parliament along the River Thames.
	Nearby we saw Westminster Abbey, so we went in for a tour. It has a very rich history, and is the site of two coronations in 1066: King Harold, the last Anglo-Saxon king in England, and William the Conqueror, the first Norman king. Many royal weddings have occurred here, and many members of royal and noble families and other notable persons are buried here.
After the conference, Bill Graziano, Erika and I went to the British Museum, where we explored items from Egypt, including the Rosetta Stone, and from Assyria, Greece, Rome, and Britain. We only scratched the surface of the museum’s contents.
	And Erika and I wrapped up our time in London with a visit to the reconstruction of Shakespeare’s Globe Theatre.

SQL Azure Reporting is still in community preview mode, which means it is still evolving as a product, but that also means it is available for you to explore and evaluate at no additional charge. However, you will need a SQL Azure account, which provides you with cloud-based storage for your databases with or without a Windows Azure account. (With Windows Azure, you can host your own custom applications or run virtual machines in the cloud.) In my webinar, I’m going to show you how easy it is to set up your first SQL Azure database and to add SQL Azure Reporting. Currently, Microsoft is offering a three-month trial, which includes a 1 GB edition of a SQL Azure database, so you can try it for yourself after watching my demonstration.

SQL Azure Reporting does not match SQL Server Reporting Services feature for feature, but it does provide a solid platform that is perfect for certain reporting scenarios. During the webinar, I’ll show you exactly what SQL Azure Reporting can do for you, and I’ll point out its limitations. That way, you can make an informed decision about using it to deliver business intelligence from the cloud.

In one my recent MDX classes, a student asked about automation of MDX queries for the purposes of testing query performance, so today I’ll answer that question and draw your attention to a few resources available.

Get the ASCMD Utility

If you’re running SQL Server 2008 Analysis Services or SQL Server 2008 R2 Analysis Services, you can download  the ASCMD_StressTestingScripts from the Analysis Services Samples page on Codeplex to get the main prize, the compiled ASCMD executable. (This version should also work with SQL Server 2005 Analysis Services, but I haven’t tested it.)

This utility allows you to execute MDX or DMX queries or XMLA commands from the command line. This capability means that you can build out some complex batch files to automate your MDX query testing, along with other repetitive tasks such as database processing. You can view the 2005 version of the ReadMe file to see the syntax as well as various ways that you might you use this utility (if you scroll all the way to the bottom of the page).

The old ReadMe file doesn’t include all of the arguments that are in the latest version of ASCMD. You can download a more current ReadMe document that’s buried deep in the bowels of Codeplex to get more information.

Try a Simple Command

In theory, the stress testing scripts let you execute ASCMD as a single client with single query or multiple queries, or as multiple clients with single query, or as multiple clients with multiple queries.  I wasn’t able to get that piece working as a file seems to be missing from the download, but you can create your own batch files to do similar things. You can get the basic idea by reviewing the RunASCMDParallelStressTest.cmd and RunASCMDSerialStressTest.cmd files that come in the download.

If you don’t want to wade through all that batch file stuff to figure out how to make ASCMD work, let me cut to the chase. You can execute asmcd /? to see the parameters available. For running an MDX query, assuming that you want to capture trace information to see the query duration and key trace events like QuerySubcubeVerbose and GetDataFromCache, you use the following syntax:

 ascmd –S <server\instance> -d <database name> -I <mdx file> -o Output.xml -T <CSV file>

So, for example, to run on my local instance, using a query file found in the Queries subfolder of the download, I would run the following command:

ascmd -S localhost -d "Adventure Works DW 2008R2" -i Queries\Query1-3.mdx
-o Output.xml -T Trace.csv

The Output.xml will contain the query results and a lot of metadata about the cube that you can probably ignore if your goal is to get performance testing data. The Trace file is a pipe-delimited CSV file that contains the same type of trace data that you get when you run a SQL Server Profiler trace for Analysis Services. The trace file gets overwritten on each execution, except as noted below.

You can adjust the level of detail in your trace file by adding the –Tl argument with one of the following values

• High (default) – captures everything.
• Medium – captures everything except ProgressReportCurrent and Notification events.
• Low – captures only events with “End” or “Error”.
• Duration – captures only execution duration and writes one line in the trace file with current time, duration, execution text, database, and server name. If you use this argument, and execute ASCMD multiple times, each execution appends new data to the file.
• Duration-result – captures the same results as when you use “duration” but includes an addition column to store the result of the execution. Each execution appends new data to the file.

Batch It

The ASCMD utility can run one file at a time, but you can put multiple queries into the file using a GO command between the queries. Or you can set up a batch process with a loop to call ASCMD multiple times.

You can leave out the –o argument but you’ll get the output stream on your screen, unless of course you put this into a batch file and use echo off and echo on after executing the ASCMD.

For performance testing, you should also include some additional steps before you run the MDX query to get true baseline performance measurements. Put these steps into an MDX or XMLA script file and use ASCMD to execute them in your batch file before running the MDX query that you’re testing:

• Clear the Analysis Services cache

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ClearCache>
    <Object>
      <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
    </Object>
  </ClearCache>
 </Batch>

• Clear the file system cache.
• Execute the MDX script independently.

SELECT {} ON 0 FROM [Adventure Works]

Learn More About Query Performance Analysis

Now that you’ve captured trace event information, what do you do with it? Here are some resources to help you determine what it means:

• Identifying and Resolving MDX Query Performance Bottlenecks
• Analysis Services 2008 Performance Guide

 Updates

I mirror my blog and received a few comments at SQLBlog.com about other tools for automating MDX query testing that I want to share here:

• Boyan Penev (twitter | blog) reminded me that you can also use the ClearAllCaches() method to clear both SSAS cache and the file system cache.
• Davide Mauri (twitter | blog) recommends BI.Quality for verifying that MDX results match expectations (and more!).
• Jason Thomas (twitter | blog) uses AS Performance Workbench for load testing MDX queries.

The upcoming release of SQL Server 2012 has a lot of new features for business intelligence developers to love. The free preview of Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) does not include the chapter on Analysis Services, but you’ll be able to read the details when the final version of the ebook is released for download in March.

Overall, there are a lot of changes in Analysis Services 2012, and it’s easy to get overwhelmed by the details. So, just as I did for Integration Services 2012 last month, I thought about the key aspects of this release that I would single out as important:

• Business Intelligence Semantic Model
• Tabular mode
• Installation experience of PowerPivot for SharePoint

Business Intelligence Semantic Model (BISM)

Back in Analysis Services 2005, the Unified Dimensional Model (UDM) made its debut.  The UDM was supposed to blur the lines between relational and multidimensional modeling. How well it accomplished that goal could be argued, but I’m not going to take sides on that issue because now it no longer matters. Why? Because BISM replaces UDM and very definitely addresses two styles of modeling: multidimensional and tabular (about which I explain more below).  To get the background, see Microsoft’s vision and roadmap statement describing the benefits at a high level.

Just as many people didn’t really understand that their development in Analysis Services (versions 2005, 2008, or 2008 R2) produced a UDM, they also don’t need to understand that their development in Analysis Services 2012 produces a BISM. Cubes from prior version will upgrade automatically when migrated to Analysis Services 2012.  It still stays multidimensional, and there is no magic button to convert it to tabular.

Lack of a magic button is not that big of a deal. I can’t think of a good reason to convert from multidimensional to tabular in most cases. However, I have heard through the grapevine of some scenarios where queries performed significantly faster against a tabular model as compared to a comparable design in a multidimensional model. It’s probably too early to say whether these performance improvements resulted from an anomaly or a genuine benefit of the VertiPaq engine that the tabular model uses.  For now, I would say the only way to know is to test it yourself with your own data. To do this, you will have to build the tabular model from scratch (unless some clever developer comes up with a tool to do the conversion some day).

Tabular Mode

When you install Analysis Sevices, you must choose from one of three server modes: Multidimensional mode (the one we’ve had since Analysis Services 2005), Tabular mode, and PowerPivot for SharePoint mode. They differ in the way they store data, how they use memory, and the engine they use to retrieve data for queries. Tabular mode uses the same VertiPaq Engine as PowerPivot for SharePoint mode, but as a separate instance and without the same dependency on SharePoint.

You can only store tabular models on a server running a tabular mode instance. If you have ever built a PowerPivot model, you will find that building tabular models is strikingly similar. The difference is that you use SQL Server Data Tools (SSDT), the SQL Server 2012 replacement for Business Intelligence Development Studio that runs as a Visual Studio 2010 shell.

With tabular models, you can go beyond using relational sources to populate the model, using any of the sources that PowerPivot supports:  relational databases, Analysis Services cubes (any mode), Reporting Services reports (as a data feed), Azure DataMarket datasets, ATOM data feeds, Excel files, or text files. This flexibility can significantly speed up development time, and enables you to rationalize development of one-off or limited life-span tabular models, which might not happen if you were limited to multidimensional mode.

Some shortcomings of the tabular model that originated in PowerPivot have been overcome in this release. The following design features are now available in both PowerPivot and tabular models:

• Hierarchical structures, including parent-child hierarchies
• Perspectives
• Key performance indicators
• Aggregations
• Diagram interface for working with tables and relationships

One advantage that tabular models have over PowerPivot models is the ability to partition the data. You can then manage reprocessing of individual partitions manually within SSDT or using scripts that you execute on the tabular mode server.

Another advantage with tabular models is the ability to use role-based security to control what people can see and do. At minimum, you set one role with permissions to allow read access and another role to administer the database. You can optionally create a role to allow users to both query and process the database, or restrict users only to processing. Another aspect of security that you might implement is row-level filtering. For example, you might set up a role that can view only Bikes as a category, but you can also completely block a role from viewing any rows from a particular table.

A third distinctive feature of tabular mode is its DirectQuery mode. You use this mode when you need to retrieve current data (rather than use the cache that tabular mode creates by default) from the source or when you want to query data volumes that are too large to hold in memory. You can use DirectQuery only when using SQL Server 2005 (or later) data. There are some additional limitations with formulas, security, and client tool support that you should understand before choosing this option.

Use the following links to download tabular samples and follow a tutorial:

• http://msftdbprodsamples.codeplex.com/releases/view/55330
• http://msdn.microsoft.com/en-us/library/hh231691(SQL.110).aspx

PowerPivot for SharePoint Installation Experience

Installation and configuration of SQL Server 2008 R2 PowerPivot for SharePoint and SharePoint Server 2010 can be a bit challenging. Curiously, my post on performing these steps on a Windows 7 machine is one of the most popular posts on my blog. It was challenging due to the dependencies on the SharePoint farm, but the new release provides a configuration wizard that greatly simplifies and automates the process. You are not required to use it, but it’s a terrific option if you’re not well-versed in SharePoint administration. If you prefer to use PowerShell, there are SharePoint and PowerPivot PowerShell cmdlets that you can use instead.

 Virtual Event Coming Soon!

And if possible, make some time to learn more at the SQL Server 2012 Virtual Launch on March 7th.

What features are you really looking forward to trying out?

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?