Understanding SSAS in SSMS and SQL Server Profiler

We covered quite a bit of ground in the preceding five chapters—everything from the key concepts of business intelligence to concepts, languages, processes, and modeling methodologies. By now, we’re sure you’re quite ready to roll up your sleeves and get to work in the Business Intelligence Development Studio (BIDS) interface. Before you start, though, we have one more chapter’s worth of information. In this chapter, we’ll explore the ins and outs of some tools you’ll use when working with Microsoft SQL Server Analysis Services (SSAS) objects. After reading this chapter, you’ll be an expert in not only SQL Server Profiler but also SQL Server Management Studio (SSMS) and other tools that will make your work in SSAS very productive. We aim to provide valuable information in this chapter for all SSAS developers—from those of you who are new to the tools to those who have some experience. If you’re wondering when we’re going to get around to discussing BIDS, we’ll start that in Chapter 7.

Core Tools in SQL Server Analysis Services

We’ll begin this discussion of the tools you’ll use to design, create, populate, secure, and manage OLAP cubes by taking an inventory. That is, we’ll first list all the tools that are part of SQL Server 2008. Later in this chapter, we’ll look at useful utilities and other tools you can get for free or at low cost that are not included with SQL Server 2008. We mention these because we’ve found them to be useful for production work. Before we list our inventory, we’ll talk a bit about the target audience—that is, who Microsoft thinks will use these tools. We share this information so that you can choose the tools that best fit your style, background, and expectations.

SQL Server 2008 does not install SSAS by default. When you install SSAS, several tools are installed with the SSAS engine and data storage mechanisms. Also, an SSAS installation does not require that you install SQL Server Database Engine Services. You’ll probably want to install SQL Server Database Engine Services, however, because some of the tools that install with it are useful with SSAS cubes. SQL Server 2008 installation follows the minimum-installation paradigm, so you’ll probably want to verify which components you’ve installed before exploring the tools for SSAS. To come up with this inventory list, follow these steps:

1. Run Setup.exe from the installation media.

2. On the left side of the Installation Center screen, click Installation and then select New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation on the resulting screen.

3. Click OK after the system checks complete, and click the Install button on the next screen.

4. Click Next on the resulting screen. Then select the Add Features To An Existing Instance Of SQL Server 2008 option, and select the appropriate instance of SQL Server from the list. After you select the particular instance you want to verify, click Next to review the features that have been installed for this instance.

Note in Figure 6-1 that some components are shared to the SQL Server 2008 instance, but others install only when a particular component is installed. As mentioned in previous chapters, SQL Server 2008 no longer ships with sample databases. If you want to install the AdventureWorks OLTP and OLAP samples, you must download them from CodePlex. For instructions on where to locate these samples and how to install them, see Chapter 1.

Figure 6-1. Installed features are shown on the Select Features page.

After you’ve verified that everything you expected to be installed is actually installed in the particular instance of SSAS, you’re ready to start working with the tools. A variety of tools are included; however, you’ll do the majority of your design and development work in just one tool—BIDS.

For completeness, this is the list of tools installed with the various SQL Server components:

• Import/Export Wizard. Used to import/export data and to perform simple transformations

• Business Intelligence Development Studio. Primary SSAS, SSIS, and SSRS development environment

• SQL Server Management Studio. Primary SQL Server (all components) administrative environment

• SSAS Deployment Wizard. Used to deploy SSAS metadata (*.asdatabase) from one server to another

• SSRS Configuration Manager. Used to configure SSRS

• SQL Server Configuration Manager. Used to configure SQL Server components, including SSAS

• SQL Server Error and Usage Reporting. Used to configure error/usage reporting—that is, to specify whether or not to send a report to Microsoft

• SQL Server Installation Center. New information center, as shown in Figure 6-2, which includes hardware and software requirements, baseline security, and installed features and samples

• SQL Server Books Online. Product documentation

• Database Engine Tuning Advisor. Used to provide performance tuning recommendations for SQL Server databases

• SQL Server Profiler. Used to capture activity running on SQL Server components, including SSAS

• Report Builder 2.0. Used by nondevelopers to design SSRS reports. This is available as a separate download and is not on the SQL Server installation media.

  

  Figure 6-2. The SQL Server Installation Center

A number of GUI tools are available with a complete SQL Server 2008 installation. By complete, we mean that all components of SQL Server 2008 are installed on the machine. A full installation is not required, or even recommended, for production environments. The best practice for production environments is to create a reduced attack surface by installing only the components and tools needed to satisfy the business requirements. In addition, you should secure access to powerful tools with appropriate security measures. We’ll talk more about security in general later in this chapter, and we’ll describe best practices for locking down tools in production environments. For now, we’ll stay in an exploratory mode by installing everything and accessing it with administrator privileges to understand the capabilities of the various tools.

Because BIDS looks like the Visual Studio interface, people often ask us if an SSAS instance installation requires a full Visual Studio install. The answer is no. If Visual Studio is not installed on a machine with SSAS, BIDS, which is subset of Visual Studio, installs. If Visual Studio is installed, the BIDS project templates install inside of the Visual Studio instance on that machine.

The core tools you’ll use for development of OLAP cubes and data mining structures in SSAS are BIDS, SSMS, and SQL Server Profiler. Before we take a closer look at these GUI tools, we’ll mention a couple of command-line tools that are available to you as well.

In addition to the GUI tools, several command-line tools are installed when you install SQL Server 2008 SSAS. You can also download additional free tools from CodePlex. One tool available on the CodePlex site is called BIDS Helper, which you can find at http://www.code-plex.com/bidshelper. It includes many useful features for SSAS development. You can find other useful tools on CodePlex as well. We’ll list only a couple of the tools that we’ve used in our projects:

• ascmd.exe. Allows you to run XMLA, DMX, or DMX scripts from the command prompt (available at http://www.codeplex.com/MSFTASProdSamples)

• SQLPS.exe. Allows you to execute Transact-SQL via the Windows PowerShell command line—mostly used when managing SQL Server source data for BI projects.

As we mentioned, you’ll also want to continue to monitor CodePlex for new community-driven tools and samples. Contributors to CodePlex include both Microsoft employees and non-Microsoft contributors.

Baseline Service Configuration

Now that we’ve seen the list of tools, we’ll take a look at the configuration of SSAS. The simplest way to do this is to use SQL Server Configuration Manager. In Figure 6-3, you can see that on our demo machine, the installed instance of SSAS is named MSSQLSERVER and its current state is set to Running. You can also see that the Start Mode is set to Automatic. The service log on account is set to NT AUTHORITY\Network Service. Of course, your settings may vary from our defaults.

Figure 6-3. SQL Server Configuration Manager

Although you can also see this information using the Control Panel Services item, it’s recommended that you view and change any of this information using the SQL Server Configuration Manager. The reason for this is that the latter tool properly changes associated registry settings when changes to the service configuration are made. This association is not necessarily observed if configuration changes are made using the Control Panel Services item.

The most important setting for the SSAS service itself is the Log On (or service) account. You have two choices for this setting. You can select one of three built-in accounts: Local System, Local Service, or Network Service. If you do not do that, you can use an account that has been created specifically for this purpose either locally or on your domain. Figure 6-4 shows the dialog box in SQL Server Configuration Manager where you set this. Which one of these choices is best and why?

Figure 6-4. The SSAS service account uses a Log On account.

Our answer depends on which environment you’re working in. If you’re exploring or setting up a development machine in an isolated domain, or as a stand-alone server, you can use any account. As we show in Figure 6-3 and Figure 6-4, we usually just use a local account that has been added to the local administrator’s group for this purpose. We do remind you that this circumvention of security is appropriate only for nonproduction environments, however.

SQL Server Books Online contains lots of information about log-on accounts. You’ll want to review the topics “Setting Up Windows Service Accounts” and “Choosing the Service Account” for details on exactly which permissions and rights are needed for your particular service (user) account.

We’ll distill the SQL Server Books Online information down a bit because, in practice, we’ve seen only two configurations. Most typically, we see our clients use either a local or domain lower-privileged (similar to a regular user) account. Be aware that for SSAS-only installations, the ability to use a domain user account as the SSAS logon account is disabled. One important consideration specific to SSAS is that the service logon account information is used to encrypt SSAS connection strings and passwords. This is a further reason to use an isolated, monitored, unique, low-privileged account.

Service Principal Names

What is a Service Principal Name (SPN)? An SPN is a particular type of Domain Name System (DNS) record. When you associate a service account with SSAS at the time of installation, an SPN record is created. If your SSAS server is part of a domain, this record is stored in your domain DNS database. It’s required for some authentication scenarios (particular client tools). If you change the service account for SSAS, you must delete the original SPN and create a new SPN record for DNS You can do this with the setSPN.exe tool available from the Windows Server Resource Kit.

Here’s further guidance from SQL Server Books Online:

• “Service SIDs are available in SQL Server 2008 on Windows Server 2008 and Windows Vista operating systems to allow service isolation. Service isolation provides services a way to access specific objects without having to either run in a high-privilege account or weaken the object’s security protection. A SQL Server service can use this identity to restrict access to its resources by other services or applications. Use of service SIDs also removes the requirement of managing different domain accounts for various SQL Server services.

• A service isolates an object for its exclusive use by securing the resource with an access control entry that contains a service security ID (SID). This ID, referred to as a per-service SID, is derived from the service name and is unique to that service. After a SID has been assigned to a service, the service owner can modify the access control list for an object to allow access to the SID. For example, a registry key in HKEY_LOCAL_MACHINE\SOFTWARE would normally be accessible only to services with administrative privileges. By adding the per-service SID to the key’s ACL, the service can run in a lower-privilege account, but still have access to the key.”

Now that you’ve verified your SSAS installation and checked to make sure the service was configured correctly and is currently running, it’s time to look at some of the tools you’ll use to work with OLAP objects. For illustration, we’ve installed the AdventureWorks DW2008 sample OLAP project found on CodePlex, because we believe it’s more meaningful to explore the various developer surfaces with information already in them. In the next chapter, we’ll build a cube from start to finish. So if you’re already familiar with SSMS and SQL Server Profiler, you might want to skip directly to that chapter.

SSAS in SSMS

Although we don’t believe that the primary audience of this book is administrators, we do choose to begin our deep dive into the GUI tools with SSMS. SQL Server Management Studio is an administrative tool for SQL Server relational databases, SSAS OLAP cubes and data mining models, SSIS packages, SSRS reports, and SQL Server Compact edition data. The reason we begin here is that we’ve found the line between SSAS developer and administrator to be quite blurry. Because of a general lack of knowledge about SSAS, we’ve seen many an SSAS developer being asked to perform administrative tasks for the OLAP cubes or data mining structures that have been developed. Figure 6-5 shows the connection dialog box for SSMS.

Figure 6-5. SSMS is the unified administrative tool for all SQL Server 2008 components.

After you connect to SSAS in SSMS, you are presented with a tree-like view of all SSAS objects. The top-level object is the server, and databases are next. Figure 6-6 shows this tree view in Object Explorer. An OLAP database object is quite different than a relational database object, which is kept in SQL Server’s RDBMS storage. Rather than having relational tables, views, and stored procedures, an OLAP database consists of data sources, data source views, cubes, dimensions, mining structures, roles, and assemblies. All of these core object types are represented by folders in the Object Explorer tree view. These folders can contain child objects as well, as shown in Figure 6-6 in the Measure Groups folder that appears under a cube in the Cubes folder.

Figure 6-6. Object Explorer lists all SSAS objects in a tree-like structure.

So what are all of these objects? Some should be familiar to you based on our previous discussions of OLAP concepts, including cubes, dimensions, and mining structures. These are the basic storage units for SSAS data. You can think of them as somewhat analogous to relational tables and views in that respect, although structurally, OLAP objects are not relational but multidimensional.

Data sources represent connections to source data. We’ll be exploring them in more detail in this chapter and the next one. Data source views are conceptually similar to relational views in that they represent a view of the data from one or more defined data sources in the project. Roles are security groups for SSAS objects. Assemblies are .NET types to be used in your SSAS project—that is, they have been written in a .NET language and compiled as .dlls.

The next area to explore in SSMS is the menus. Figure 6-7 shows both the menu and standard toolbar. Note that the standard toolbar displays query types for all possible components—that is, relational (Transact-SQL) components, multidimensional OLAP cubes (MDX), data mining structures (DMX), administrative metadata for OLAP objects (XMLA), and SQL Server Compact edition.

Figure 6-7. The SSMS standard toolbar displays query options for all possible SQL Server 2008 components.

It’s important that you remember the purpose of SSMS—administration. When you think about this, the fact that it’s straightforward to view, query, and configure SSAS objects—but more complex to create them—is understandable. You primarily use BIDS to create OLAP objects. Because this is a GUI environment, you’re also provided with guidance should you want to examine or query objects. Another consideration is that SSMS is not an end-user tool. Even though the viewers are sophisticated, SSMS is designed for SSAS administrators.

How Do I View OLAP Objects?

SSMS includes many object viewers. You’ll see these same viewers built into other tools designed to work with SSAS, such as BIDS. You’ll also find versions of these viewers built into client tools, such as Microsoft Office Excel 2007. The simplest and fastest way to explore cubes and mining models in SSMS is to locate the object in the tree view and then to right-click on it. For cubes, dimensions, and mining structures, the first item on the shortcut menu is Browse.

We’ll begin our exploration with the Product dimension. Figure 6-8 shows the results of browsing the Product dimension. For each dimension, we have the ability to drill down to see the member names at the defined levels—in this case, at the category, subcategory, and individual item levels. In addition to being able to view the details of the particular dimensional (rollup) hierarchy, we can also select a localization (language) and member properties that might be associated with one or more levels of a dimension. In our example, we have elected to include color and list price in our view for the AWC Logo Cap clothing item. These member properties have been associated with the item (bottom) level of the product dimension.

Figure 6-8. The dimension browser enables you to view the data in a dimension.

The viewing options available for dimensions in SSMS include the ability to filter and implement dimension writeback. Writeback has to be enabled on the particular dimension, and the connected user needs to have dimension writeback permission to be permitted to use this action in SSMS.

In addition to being able to view the dimension information, you can also see some of the metadata properties by clicking Properties on the shortcut menu. Be aware that you’re viewing a small subset of structural properties in SSMS. As you would expect, these properties are related to administrative tasks associated with a dimension. Figure 6-9 shows the general dialog box of the Product dimension property page. Note that the only setting you can change in this view is Processing Mode. We’ll examine the various processing modes for dimensions and the implications of using particular selections in Chapter 9.

Figure 6-9. The Dimension Properties dialog box in SSMS shows administrative properties associated with a dimension.

In fact, you can process OLAP dimensions, cubes, and mining structures in SSMS. You do this by right-clicking on the object and then choosing Process on the shortcut menu. Because this topic requires more explanation, we’ll cover it in Chapter 9. Suffice it to say at this point that, from a high level, SSAS object processing is the process of copying data from source locations into destination containers and performing various associated processing actions on this data as part of the loading process. As you might expect, these processes can be complex and require that you have an advanced understanding of the SSAS objects before you try to implement the objects and tune them. For this reason, we’ll explore processing in Part II of this book.

If you’re getting curious about the rest of the metadata associated with a dimension, you can view this information in SSMS as well. This task is accomplished by clicking on the shortcut menu option Script Dimension As, choosing Create To, and selecting New Query Editor Window. The results are produced as pure XMLA script. You’ll recall from earlier in the book that XMLA is a dialect of XML.

What you’re looking at is a portion of the XMLA script that is used to define the structure of the dimension. Although you can use Notepad to create SSAS objects, because they are entirely based on an XMLA script, you’ll be much more productive using the graphical user interface in BIDS to generate this metadata script. The reason you can generate XMLA in SSMS is that when you need to re-create OLAP objects, you need the XMLA to do so. So XMLA is used to copy, move, and back up SSAS objects. In fact, you can execute the XMLA query you’ve generated using SSMS. We take a closer look at querying later in this chapter.

Now that you’ve seen how to work with objects, we’ll simply repeat the pattern for OLAP cubes and data mining structures. That is, we’ll first view the cube or structure using the Browse option, review configurable administrative properties, and then take a look at the XMLA that is generated. We won’t neglect querying either. After we examine browsing, properties, and scripting for cubes and models, we’ll look at querying the objects using the appropriate language—MDX, DMX, or XMLA.

How Do I View OLAP Cubes?

The OLAP cube browser built into SSMS is identical to the one you’ll be working with in BIDS when you’re developing your cubes. It’s a sophisticated pivot table–style interface. The more familiar you become with it, the more productive you’ll be. Just click Browse on the shortcut menu after you’ve selected any cube in the Object Explorer in SSMS to get started. Doing this presents you with the starter view. This view includes the use of hint text (such as Drop Totals Of Detail Field Fields Here) in the center work area that helps you understand how best to use this browser.

On the left side of the browser, you’re presented with another object browser. This is where you select the items (or aspects) of the cube you want to view. You can select measures, dimension attributes, levels, or hierarchies. Note that you can select a particular measure as a filter from the drop-down list box at the top of this object browser. Not only will this filter the measures selected, it will also filter the associated dimensions so that you’re selecting from an appropriate subset as you build your view.

Measures can be viewed in the Totals work area. Dimension attributes, levels, or hierarchies can be viewed on the Rows, Columns, or Filters (also referred to as slicers) axis. These axes are labeled with the hint text Drop xxx Fields Here. We’ll look at Filters or Slicers axes in more detail later in this chapter.

At the top of the browser, you can select a perspective. A perspective is a defined view of an OLAP cube. You can also select a language. Directly below that is the Filter area, where you can create a filter expression (which is actually an MDX expression) by dragging and dropping a dimension level or hierarchy into that area and then completing the rest of the information—that is, configuring the Hierarchy, Operator, and Filter Expression options. We’ll be demonstrating this shortly. To get started, drag one or more measures and a couple of dimensions to the Rows and Columns axes. We’ll do this and show you our results in Figure 6-10.

Figure 6-10. Building an OLAP cube view in SSMS

To set up our first view, we filtered our list by the Internet Sales measure group in the object browser. Next we selected Internet Sales Amount and Internet Order Quantity as our measures and dragged them to that area of the workspace. We then selected the Product Categories hierarchy of the Product dimension and dragged it to the Rows axis. We also selected the Sales Territory hierarchy from the Sales Territory dimension and dragged it to the Columns axis.

We drilled down to show detail for the Accessories product category and Gloves subcategory under the Clothing product category on the Rows axis. And finally, we filtered the Sales Territory Group information to hide the Pacific region. The small blue triangle next to the Group label indicates that a filter has been applied to this data. If you want to remove any item from the work area, just click it and drag it back to the left side (list view). Your cursor will change to an X, and the item will be removed from the view.

It’s much more difficult to write the steps as we just did than to actually do them! And that is the point. OLAP cubes, when correctly designed, are quick, easy, and intuitive to query. What you’re actually doing when you’re visually manipulating the pivot table surface is generating MDX queries. The beauty of this interface is that end users can do this as well. Gone are the days that new query requests of report systems require developers to rewrite (and tune) database queries.

Let’s add more sophistication to our view. To do this, we’ll use the filter and slicer capabilities of the cube browser. We’ll also look at the pivot capability and use the built-in common queries. To access the latter, you can simply right-click on a measure in the measures area of the designer surface and select from a shortcut menu, which presents you with common queries, such as Show Top 10 Values and other options as well. Figure 6-11 shows our results.

Figure 6-11. Results of building an OLAP cube view in SSMS

Here are the steps we took to get there.

First we dragged the Promotions hierarchy from the Promotion dimension to the slicer (Filter Fields) area. We then set a filter by clearing the check boxes next to the Reseller promotion dimension members. This resulted in showing data associated only with the remaining members. Note that the label indicates this as well by displaying the text “Excluding: Reseller.”

We then dragged the Ship Date.Calendar Year hierarchy from the Ship Date dimension; we set the Operator area to Equal, and in the Filter Expression area we chose the years 2003 and 2004 from the available options. Another area to explore is the nested toolbar inside of the Browser subtab. Using buttons on this tab toolbar, you can connect as a different user and sort, filter, and further manipulate the data shown in the working pivot table view. Note that there is an option to show only the top or bottom values (1, 2, 5, 10, or 25 members or a percentage). Finally, if drillthrough is enabled for this cube, you can drill through using this browser by right-clicking on a data cell and selecting that option. Drillthrough allows you to see additional columns of information that are associated with the particular fact item (or measure) that you’ve selected. You should spend some time experimenting with all the toolbar buttons so that you’re thoroughly familiar with the different built-in query options. Be aware that each time you select an option, you’re generating an MDX query to the underlying OLAP cube.

Note also that when you select cells in the grid, additional information is shown in a tooltip. You can continue to manipulate this view for any sort of testing purposes. Possible actions also include pivoting information from the rows to the column’s axis, from the slicer to the filter, and so on. Conceptually, you can think of this manipulation as somewhat similar to working with a Rubik’s cube. Of course, OLAP cubes generally contain more than three dimensions, so this analogy is just a starting point.

Viewing OLAP Cube Properties and Metadata

If you next want to view the administrative properties associated with the particular OLAP cube that you’re working with (as you did for dimensions), you simply right-click that cube in the SSMS Object Browser and then click Properties. Similar to what you saw when you performed this type of action on an OLAP dimension, you’ll then see a dialog box similar to the one shown in Figure 6-12 that allows you to view some properties. The only properties you can change in this view are those specifically associated with cube processing. As mentioned previously, we’ll look at cube processing options in more detail in Chapter 9.

Figure 6-12. OLAP cube properties in SSMS

By now, you can probably guess how you’d generate an XMLA metadata script for an OLAP cube in SSMS. Just right-click the cube in the object browser and click Script Cube As on the shortcut menu, choose Create To, and select New Query Editor Window. Note also that you can generate XMLA scripts from inside any object property window. You do this by clicking the Script button shown at the top of Figure 6-12.

Now that we’ve looked at both OLAP dimensions and cubes in SSMS, it’s time to look at a different type of object—SSAS data mining structures. Although conceptually different, data mining (DM) objects are accessed using methods identical to those we’ve already seen—that is, browse, properties, and script.

How Do I View DM Structures?

As we begin our tour of SSAS data mining structures, we need to remember a couple of concepts that were introduced earlier in this book. Data mining structures are containers for one or more data mining models. Each data mining model uses a particular data mining algorithm. Each data mining algorithm has one or more data mining algorithm viewers associated with it. Also, each data mining model can be viewed using a viewer as well via a lift chart. New to SQL Server 2008 is the ability to perform cross validation. Because many of these viewing options require more explanation about data mining structures, at this point we’re going to stick to the rhythm we’ve established in this chapter—that is, we’ll look at a simple view, followed by the object properties, and then the XMLA. Because the viewers are more complex for data mining objects than for OLAP objects, we’ll spend a bit more time exploring.

We’ll start by browsing the Customer Mining data mining structure. Figure 6-13 shows the result. What you’re looking at is a rendering of the Customer Clusters data mining model, which is part of the listed structure. You need to select the Cluster Profiles tab to see the same view. Note that you can make many adjustments to this browser, such as legend, number of histogram bars, and so on. At this point, some of the viewers won’t make much sense to you unless you have a background using data mining. Some viewers are more intuitive than others. We’ll focus on showing those in this section.

Figure 6-13. Data mining structure viewer in SSMS

It’s also important for you to remember that although these viewers are quite sophisticated, SSMS is not an end-user client tool. We find ourselves using the viewers in SSMS to demonstrate proof-of-concept ideas in data mining to business decision makers (BDMs), however. If these viewers look familiar to you, you’ve retained some important information that we presented in Chapter 2. These viewers are nearly identical to the ones that are intended for end users as part of the SQL Server 2008 Data Mining Add-ins for Office 2007. When you install the free add-ins, these data mining viewers become available as part of the Data Mining tab on the Excel 2007 Ribbon. Another consideration for you is this—similar to the OLAP cube pivot table viewer control in SSMS that we just finished looking at, these data mining controls are also part of BIDS.

In our next view, shown in Figure 6-14, we’ve selected the second mining model, Subcategory Associations, associated with the selected mining structure. Because this second model has been built using a different mining algorithm, after we make this selection the Viewer dropdown list automatically updates to list the associated viewers available for that particular algorithm. We then chose the Dependency Network tab from the three available views and did a bit of tuning of the view, using the embedded toolbar to produce the view shown (for example, sized it to fit, zoomed it, and so on).

Figure 6-14. Data mining structure viewer in SSMS showing the Dependency Network view for the Microsoft Association algorithm

An interesting tool that is part of this viewer is the slider control on the left side. This control allows you to dynamically adjust the strength of association shown in the view. We’ve found that this particular viewer is quite intuitive, and it has helped us to explain the power of data mining algorithms to many nontechnical users.

As you did with the OLAP pivot table viewer, you should experiment with the included data mining structure viewers. If you feel a bit frustrated because some visualizations are not yet meaningful to you, we ask that you have patience. We devote Chapter 12, to a detailed explanation of the included data mining algorithms. In that chapter, we’ll provide a more detailed explanation of most included DM views.

TIP

You can change any of the default color schemes for the data mining viewers in SSMS by adjusting the colors via Tools, Options, Designers, Analysis Services Designers, Data Mining Viewers.

Because the processes for viewing the data mining object administrative properties and for generating an XMLA script of the object’s metadata are identical to those used for OLAP objects, we won’t spend any more time reviewing them here.

How Do You Query SSAS Objects?

As with relational data, you have the ability to write and execute queries against multidimensional data in SSMS. This is, however, where the similarity ends. The reason is that when you work in an RDBMS, you need to write any query to the database using SQL. Even if you generate queries using tools, you’ll usually choose to perform manual tuning of those queries. Tuning steps can include rewriting the SQL, altering the indexing on the involved tables, or both.

SSAS objects can and sometimes are queried manually. However, the extent to which you’ll choose to write manual queries will be considerably less than the extent to which you’ll query relational sources. What are the reasons for this? There are several:

• MDX and DMX language expertise is rare among the developer community. With less experienced developers, the time to write and optimize queries manually can be prohibitive.

• OLAP cube data is often delivered to end users via pivot table–type interfaces (that is, Excel, or some manual client that uses a pivot table control). These interfaces include the ability to generate MDX queries by dragging and dropping members of the cube on the designer surface—in other words, by visual query generation.

• SSMS and BIDS have many interfaces that also support the idea of visual query generation for both MDX and DMX. This feature is quite important to developer productivity.

What we’re saying here is that although you can create manual queries, and SSMS is the place to do this, you’ll need to do this significantly less frequently while working with SSAS objects (compared to what you have been used to with RDBMS systems). It’s very important for you to understand and embrace this difference. Visual development does not mean lack of sophistication or power in the world of SSAS.

As you move toward understanding MDX and DMX, we suggest that you first monitor the queries that SSMS generates via the graphical user interface. SQL Server Profiler is an excellent tool to use when doing this.

What Is SQL Server Profiler?

SQL Server Profiler is an activity capture tool for the database engine and SSAS that ships with SQL Server 2008. SQL Server Profiler broadly serves two purposes. The first is to monitor activity for auditing or security purposes. To that end, SQL Server Profiler can be easily configured to capture login attempts, access specific objects, and so on. The other main use of the tool is to monitor activity for performance analysis. SQL Server Profiler is a powerful tool—when used properly, it’s one of the keys to understanding SSAS activity. We caution you, however, that SQL Server Profiler can cause significant overhead on production servers. When you’re using it, you should run it on a development server or capture only essential information.

SQL Server Profiler captures are called traces. Appropriately capturing only events (and associated data) that you’re interested in takes a bit of practice. There are many items you can capture! The great news is that after you’ve determined the important events for your particular business scenario, you can save your defined capture for reuse as a trace template.

If you’re familiar with SQL Server Profiler from using it to monitor RDBMS data, you’ll note that when you set the connection to SSAS for a new trace, SQL Server Profiler presents you with a set of events that is specific to SSAS to select from. See the SQL Server Books Online topics “Introduction to Monitoring Analysis Services with SQL Server Profiler” and “Analysis Services Event Classes” for more detailed information. Figure 6-15 shows some of the events that you can choose to capture for SSAS objects. Note that in this view, we’ve selected Show All Events in the dialog box. This switch is off by default.

Figure 6-15. SQL Server Profiler allows you to capture SSAS-specific events for OLAP cubes and data mining structures.

After you’ve selected which events (and what associated data) you want to capture, you can run your trace live, or you can save the results either to a file or to a relational table for you to rerun and analyze later. The latter option is helpful if you want to capture the event on a production server and then replace the trace on a development server for analysis and testing of queries.

At this point, we’re really just going to use SQL Server Profiler to view MDX queries that are generated when you manipulate the dimension and cube browsers in SSMS. The reason we’re doing this is to introduce you to the MDX query language. You can also use SQL Server Profiler to capture generated DMX queries for data mining structures that you manipulate using the included browsers in SSMS.

To see how query capture works, just start a trace in SQL Server Profiler, using all of the default capture settings, by clicking Run on the bottom right of the Trace Properties dialog box. With the trace running, switch to SSMS, right-click on the Adventure Works sample cube in the object browsers, click Browse, and then drag a measure to the pivot table design area.

We dragged the Internet Sales Amount measure for our demo. After you’ve done that, switch back to SQL Server Profiler and then click on the pause trace button on the toolbar. Scroll through the trace to the end, where you should see a line with the EventClass showing Query End and EventSubclass showing 0 - MDXQuery. Then click that line in the trace. Your results should look similar to Figure 6-16.

Figure 6-16. SQL Server Profiler allows you to view MDX query text details.

Note that you can see the MDX query that was generated by your drag action on the pivot table design interface in SSMS. This query probably doesn’t seem very daunting to you, particularly if you’ve worked with Transact-SQL before. Don’t be fooled, however; this is just the tip of the iceberg.

Now let’s get a bit more complicated. Click the Play button in SQL Server Profiler to start the trace again. After that, return to the SSMS OLAP cube pivot table browse area and then drag and drop some dimension information (hierarchies or members) to the rows, columns, slicer, and filter areas. After you have completed this, return to SQL Server Profiler and again pause your trace and then examine the MDX query that has been generated. Your results might look similar to what we show in Figure 6-17. You can see if you scroll through the trace that each action you performed by dragging and dropping generated at least one MDX query.

Figure 6-17. Detail of a complex MDX query

We find SQL Server Profiler to be an invaluable tool in helping us to understand exactly what type of MDX query is being generated by the various tools (whether developer, administrator, or end user) that we use. Also, SQL Server Profiler does support tracing data mining activity. To test this, you can use the SSMS Object Browser to browse any data mining model while a SQL Server Profiler trace is active. In the case of data mining, however, you’re not presented with the DMX query syntax. Rather, what you see in SQL Server Profiler is the text of the call to a data mining stored procedure. So the results in SQL Server Profiler look something like this:

CALL System.Microsoft.AnalysisServices.System.DataMining.AssociationRules. GetStatistics(‘Subcategory Associations’)

These results are also strangely categorized as 0 - MDXQuery type queries in the EventSub-class column of the trace. You can also capture data mining queries using SQL Server Profiler. These queries are represented by the EventSubclass type 1 – DMXQuery in SQL Server Profiler.

We’ll return to SQL Server Profiler later in this book, when we discuss auditing and compliance. Also, we’ll take another look at this tool in Chapter 10 and Chapter 11, which we devote to sharing more information about manual query and expression writing using the MDX language. Speaking of queries, before we leave our tour of SSMS, we’ll review the methods you can use to generate and execute manual queries in this environment.

Using SSAS Query Templates

Another powerful capability included in SSMS is that of being able to write and execute queries to SSAS objects. These queries can be written in three languages: MDX, DMX, and XMLA. At this point, we’re not yet ready to do a deep dive into the syntax of any of these three languages; that will come later in this book. Rather, here we’d like to understand the query execution process. To that end, we’ll work with the included query templates for these three languages. To do this, we need to choose Template Explorer from the View menu, and then click the Analysis Services (cube) icon to show the three folders with templated MDX, DMX, and XMLA queries. The Template Explorer is shown in Figure 6-18.

Figure 6-18. SSMS includes MDX, DMX, and XMLA query templates

You can see that the queries are further categorized into functionality type in child folders under the various languages—such as Model Content and Model Management under DMX. You can also create your own folders and templates in the Template Explorer by right-clicking and then clicking New. After you do this, you’re actually saving the information to this location on disk: C:\Users\Administrator\AppData\Roaming\Microsoft\Microsoft SQL Server\100\ Tools\Shell\Templates\AnalysisServices.

Using MDX Templates

Now that you’ve opened the templates, you’ll see that for MDX there are two types of queries: expressions and queries. Expressions use the syntax With Member and create a calculated member as part of a sample query. You can think of a calculated member as somewhat analogous to a calculated cell or set of cells in an Excel workbook, with the difference being that calculated members are created in n-dimensional OLAP space. We’ll talk in greater depth about when, why, and how you choose to use calculated members in Chapter 9.

Queries retrieve some subset of an OLAP cube as an ADO.MD CellSet result, and they do not contain calculated members. To execute a basic MDX query, simply double-click the Basic Query template in the Template Explorer and then connect to SSAS. You can optionally write queries in a disconnected state and then, when ready, connect and execute the query. This option is available to reduce resource consumption on production servers.

You need to fill the query parameters with actual cube values before you execute the query. Notice that the query window opens yet another metadata explorer in addition to the default Object Explorer. You’ll probably want to close Object Explorer when executing SSAS queries in SSMS. Figure 6-19 shows the initial cluttered, cramped screen that results if you leave all the windows open. It also shows the MDX parser error that results if you execute a query with errors. (See the bottom window, in the center of the screen, with text underlined with a squiggly line.)

Figure 6-19. The SSMS SSAS query screen can be quite cluttered by default.

Now we’ll make this a bit more usable by hiding the Object Explorer and Template Explorer views. A subtle point to note is that the SSAS query metadata browser includes two filters: a Cube filter and, below it, a Measure Group filter. The reason for this is that SSAS OLAP cubes can contain hundreds or even thousands of measure groups.

Figure 6-20 shows a cleaned-up interface. We’ve left the Cube filter set at the default, Adventure Works, but we’ve set the Measure Group filter to Internet Sales. This reduces the number of items in the viewer, as it shows only items that have a relationship to measures associated with the selected measure group. Also note that in addition to a list of metadata, this browser includes a second nested tab called Functions. As you’d expect, this tab contains an MDX function language reference list.

Figure 6-20. The SSMS SSAS query screen with fewer items in the viewer

You might be wondering why you’re being presented with yet another metadata interface, particularly because you’re inside of a query-writing tool. Aren’t you supposed to be writing the code manually here? Nope, not yet. Here’s the reason why—MDX object naming is not as straightforward as it looks. For example, depending on uniqueness of member names in a dimension, you sometimes need to list the ordinal position of a member name; at other times, you need to actually list the name. Sound complex? It is. Dragging and dropping metadata onto the query surface can make you more productive if you’re working with manual queries.

To run the basic query, you need to replace the items shown in the sample query between angle brackets—that is, <some value>—with actual cube metadata. Another way to understand this is to select Specify Values For Template Parameters on the Query menu. You can either type the information into the Template Parameters dialog box that appears, or you can click on any of the metadata from the tree view in the left pane and then drag it and drop it onto the designer surface template areas.

We’ll use the latter approach to build our first query. We’ll start by dragging the cube name to the From clause. Next we’ll drag the Customers.Customer Geography hierarchy from the Customer dimension to the On Columns clause. We’ll finish by dragging the Date.Calendar Year member from the Date hierarchy and Calendar hierarchy to the On Rows clause. We’ll ignore the Where clause for now. As with Transact-SQL queries, if you want to execute only a portion of a query, just select the portion of interest and press F5. The results are shown in Figure 6-21.

Figure 6-21. SSMS SSAS query using simple query syntax

Do the results seem curious to you? Are you wondering which measure is being shown? Are you wondering why only the top-level member of each of the selected hierarchies is shown on columns and rows? As we’ve said, MDX is a deceptively simple language. If you’ve worked with Transact-SQL, which bears some structural relationship but is not very closely related at all, you’ll find yourself confounded by MDX. We do plan to provide you with a thorough grounding in MDX. However, we won’t be doing so until much later in this book—we’ll use Chapter 10 and Chapter 11 to unravel the mysteries of this multidimensional query language.

At this point in our journey, it’s our goal to give you an understanding of how to view and run prewritten MDX queries. Remember that you can also re-execute any queries that you’ve captured via SQL Server Profiler traces in the SSMS SSAS query environment as well.

Because we know that you’re probably interested in just a bit more about MDX, we’ll add a couple of items to our basic query. Notably, we’ll include the MDX Members function so that we can display more than the default member of a particular hierarchy on an axis. We’ll also implement the Where clause so that you can see the result of filtering. The results are shown in Figure 6-22.

Figure 6-22. MDX query showing filtering via the Where clause

We changed the dimension member information on Columns to a specific level (Country), and then we filtered in the Where clause to the United States only. The second part of the Where clause is an example of the cryptic nature of MDX. The segment [Product].[Product Categories].[Category].&[1] refers to the category named Bikes. We used the drag (metadata) and drop method to determine when to use names and when to use ordinals in the query. This is a time-saving technique you’ll want to use as well.

Using DMX Templates

Next we’ll move to the world of DM query syntax. Again, we’ll start by taking a look at the included templates in the Template Explorer. They fall into four categories: Model Content, Model Management, Prediction Queries, and Structure Content.

When you double-click on a DMX query template, you’ll see that the information in the Metadata browser reflects a particular mining model. You can select different mining model metadata in the pick list at the top left of the browser. Also, the functions shown now include those specific to data mining. The Function browser includes folders for each data mining algorithm, with associated functions in the appropriate folder. Because understanding how to query data mining models requires a more complete understanding of the included algorithms, we’ll simply focus on the mechanics of DMX query execution in SSMS at this point.

To do this, we’ll double-click the Model Attributes sample DMX query in the Model Content folder that you access under DMX in the Template Explorer. Then we’ll work with the templated query in the workspace. As with templated MDX queries, the DMX templates indicate parameters with the <value to replace> syntax. You can also click the Query menu and select Specify Values For Template Parameters as you can with MDX templates. We’ll just drag the [Customer Clusters] mining model to the template replacement area. Note that you must include both the square brackets and the single quotes, as shown in Figure 6-23, for the query to execute successfully.

Figure 6-23. A DMX query showing mining model attributes

If you click on the Messages tab in the results area (at the bottom of the screen), you’ll see that some DMX queries return an object of type Microsoft.AnalysisServices.AdomdClient. AdomdDataReader. Other DMX query types return scalar values—that is, DMX prediction queries.

For more information, see the SQL Server Books Online topic “Data Mining Extensions (DMX) Reference.”

Using XMLA Templates

As with the previous two types of templates, SSMS is designed to be an XMLA query viewing and execution environment. The SSMS Template Explorer also includes a couple of types of XMLA sample queries. These are Management, Schema Rowsets, and Server Status. The XMLA language is an XML dialect, so structurally it looks like XML rather than a data structure query language, such as MDX or DMX (which look rather Transact-SQL-like at first glance). One important difference between MDX and XMLA is that XMLA is case-sensitive and space-sensitive, following the rules of XML in general.

Another important difference is that the Metadata and Function browsers are not available when you perform an XMLA query. Also, the results returned are in an XML format. In Figure 6-24, we show the results of executing the default Connections template. This shows detailed information about who is currently connected to your SSAS instance.

Figure 6-24. SSAS XMLA connections query in SSMS

Be reminded that metadata for all SSAS objects—that is, OLAP dimensions, cubes, data mining models, and so on—can easily be generated in SSMS by simply right-clicking the object in the Object Browser and then clicking Script As. This is a great way to begin to understand the capabilities of XMLA. In production environments, you’ll choose to automate many administrative tasks using XMLA scripting.

The templates in SSMS represent a very small subset of the XMLA commands that are available in SSAS. For a more complete reference, see the SQL Server Books Online topic “Using XMLA for Analysis in Analysis Services (XMLA).” Another technical note: certain commands used in XMLA are associated with a superset of commands in the Analysis Services Scripting Language (ASSL). The MSDN documentation points out that ASSL commands include both data definition language (DDL) commands, which define and describe instances of SSAS and the particular SSAS database, and also XMLA action commands such as Create, which are then sent to the particular object named by the ASSL. ASSL information is also referred to as binding information in SQL Server Books Online.

Closing Thoughts on SSMS

Although our primary audience is developers, as discussed, we’ve found that many SSAS developers are also tasked with performing SSAS administrative tasks. For this reason, we spent an entire chapter exploring the SSMS SSAS interface. Also, we find that using SSMS to explore built objects is a gentle way to introduce OLAP and DM concepts to many interested people. We’ve used SSMS to demonstrate these concepts to audiences ranging from .NET developers to business analysts. Finally, we’d like to note that we’re continually amazed at the richness of the interface. Even after having spent many years with SSAS, we still frequently find little time-savers in SSMS.