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.