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?

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.