Analysis Services Samples

Microsoft includes with SQL Server 2005 samples that illustrate techniques useful for DBAs, developers, and business intelligence (BI) professionals. The SQL Server Analysis Services (SSAS) samples are especially useful because they perform many functions that aren't otherwise available in SQL Server Management Studio (SSMS) or the SQL Server Business Intelligence Development Studio (BIDS).

In addition, the SQL Server development team made a conscious decision to distribute all of the utilities in the samples as source code, not executables. That means you'll need to compile the utilities by using either Visual Studio or the .NET Framework 2.0 software development kit (SDK) before you'll get a usable binary file. Although this requirement might seem like an obstacle, it's actually a positive feature of the samples because you can customize the utilities without going through Microsoft.

The Microsoft team would love to hear your ideas for improvements to the utilities. You can submit your comments through the SQL Server Connect page at https://connect.microsoft.com/SQLServer.

Once you've installed the samples, look in C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services and its subfolders. You'll find SSAS utilities that exploit specific product features. Here are several that you should add to your toolkit. (All the utilities listed here except SyncAdvWorksPartitions are in C#.)

ActivityViewer. This utility shows the current connections, sessions, locks, and traces running on any SSAS server that you connect to. It's similar to the Current Activity view in SSMS, except this utility works for SSAS rather than the relational engine. You can use Activity Viewer to kill a process.

AMOBrowser. This utility lets you directly interact with an SSAS server and all of its objects by using the Analysis Management Objects (AMO) object model. The AMOBrowser works like the SSMS Object Browser; it lets you directly tinker with the schema of an SSAS cube and its fact tables, dimensions, and properties.

AMOAdventureWorks. This tool recreates part of the SSAS AdventureWorks database by using AMO, which is nifty because you might want to use AMO to build cubes programmatically rather than in the BIDS GUI. Most experienced enterprise DBAs strongly encourage you to perform operations like this one in a script as an administrative best practice. Scripts provide many benefits over using the GUI, such as deeper knowledge of the platform, the ability to run multiple scripts concurrently, easier automation, and repeatability.

BackupAndRestore. This utility describes how to use AMO to back up and restore cubes programmatically. You'll find this feature handy for backup automation.

DisplayObjectNames. This tool uses AMO to display object names. I think of this one as sort of the "hello world" script for AMO. The purpose of this utlility is to help you learn how to use AMO.

SyncAdvWorksPartitions. This sample SQL Server Integration Services (SSIS) package (located in C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ SyncAdvWorksPartitions) synchronizes the AdventureWorks database with the SSAS cube. You can download this sample at http://msdn2.microsoft.com/en-us/library/ms161545.aspx

Some details about how to compile and run the samples are included in two SQL Server Samples HTML files that ship with the samples. Aside from that, the only documentation is in the root file of each utility's Readme file.

If you have a programming bent, I encourage you to develop your own variations of the samples and share them with the community at the Tool Time discussion forum at http://www.sqlmag.com/go/tooltime. If you're not a programmer, download the samples, compile them, and add them to your tool kit. They'll definitely help you be more efficient.