SQL Server 2005 : Advanced OLAP - Key Performance Indicators (part 2) - KPI Queries in Management Studio

10/25/2012 1:17:13 AM

KPI Queries in Management Studio

At this point, you should have a sense of the effectiveness of KPIs and the relative ease with which you can create them. The graphics might seem simplistic at first, but they make the data in your cube much more digestible and easier to understand.

To convey KPI data to your end users, though, you must use something other than the KPI browser, which is simply a technologist’s tool. For example, access to KPIs from the cube browser might be more helpful. (The cube browser might seem to also be a technologist’s tool, but it is actually the Excel PivotTable component, which is available to users through the 2003 versions of Office Web Components and Microsoft Excel itself.)

Unfortunately, KPIs are not available in the cube browser, so you must use a different end-user technology. To this end, we’ll now explore how to write simple MDX queries that return KPI data.

Management Studio Does BI

So far, we’ve used Management Studio mostly as a tool to manage relational databases. Management Studio is the successor to Enterprise Manager and Query Analyzer from SQL Server 2000, but it is also the successor to Analysis Manager and the MDX Sample Application, the Analysis Services 2000 management tools.

Using Management Studio, you can create and edit MDX queries for OLAP cubes, DMX queries for data mining models , and XML for Analysis (XMLA) scripts that you can use to create, modify, and drop virtually any Analysis Manager object, as well as to execute embedded MDX and DMX queries. Management Studio also supports the creation and maintenance of Analysis Services Scripts projects, which can contain a combination of MDX, DMX, and XMLA queries/scripts. You can combine Analysis Services Scripts projects and SQL Server Scripts projects into a single solution to manage the entire universe of objects in your data warehouse systems.

To see how all of this works, open Management Studio, and make sure the Registered Servers and Object Explorer windows are visible. If they’re not, simply click the Registered Servers and Object Explorer buttons (fifth and third from the right, respectively) on the Standard toolbar or like-named options from the View branch of Management Studio’s main menu. Now click the Analysis Services button on the Registered Servers window’s toolbar (second from the left, with a cube icon) and then double-click the tree view node representing your server. (If it’s not there, add it.) A node representing your Analysis Services server, as well as Databases and Assemblies child nodes, should now be displayed in the Object Explorer window. Expand the Databases node, and all databases on your Analysis Services server should be displayed, as shown in Figure 3.

Figure 3. SQL Server Management Studio, with our Analysis Services server displayed in the Registered Servers and Object Explorer windows

The MDX Query Window

An MDX query window appears. On the left, the window has a tools pane, consisting of a drop-down list of cubes in the database; a Metadata tab that lists the measures, dimensions, and KPIs in the selected cube; and a Functions tab (equivalent to the one you’ve seen on the Calculations and KPIs tabs of the cube designer in Visual Studio). On the right side of the MDX query window is an MDX editor that supports color coding, squiggle syntax error prompting, and interspersing of comments and code. Member listing and word completion are available through the Edit/IntelliSense option on the main menu or via keyboard shortcuts.


Clicking the New Query button is only one of several ways to open an MDX query window in Management Studio. For example, you can click the Analysis Services MDX Query button (third from the left) on the Standard toolbar or choose the File/New/Analysis Services MDX Query option from the main menu.

Either action opens the Connect To Analysis Services dialog box, where you specify your Analysis Services server and database names. To specify the database name, click the Options button, click the Connection Properties tab that becomes visible, and then select or type the database name in the Connect To Database drop-down list. Then click Connect.

You can avoid the entire Connect To Analysis Services dialog box step by using the New Query toolbar button when a specific Analysis Services database is selected in the Object Explorer window because the connection and database are implied. Another shortcut is to select the New Query/MDX option from the database node’s shortcut menu in the Object Explorer window.

You can change the connection parameters for an open MDX query window by choosing the Connection/Change Connection... option from its shortcut menu, the Query/Connection/ Change Connection... option on the main menu, or by clicking the Change Connection button (third from the left) on the SQL Analysis Services Editors toolbar. The server and database you are currently connected to are displayed in the query window’s status bar and in the Properties window when the query window is active.

If you expand the KPI’s node on the Metadata tab, you should see a single node for our new KPI; if you expand that node, you should see one child node each for the KPI’s value, goal, status, and trend, as shown in Figure 4.

Figure 4. The Management Studio MDX query window with our sample cube loaded and its KPI displayed on the Metadata tab

You can drag and drop any of these child nodes into the query editor to generate expressions that can be used in an MDX SELECT statement. Using a combination of manual typing and drag-and-drop from the Metadata tab, enter the MDX code in Listing 3 into the editor pane of the MDX query window. (The query is in the file KPIQuery.mdx in the Management Studio solution included with the sample code.)

Listing 3. An MDX query suitable for execution from Management Studio
 KPIValue("Average Total Sales KPI"),
 KPIGoal("Average Total Sales KPI"),
 KPIStatus("Average Total Sales KPI"),
 KPITrend("Average Total Sales KPI")
FROM [Sales]
 [Order Date].[Year -  Quarter -  Month -  Date].[Year].&[1997-01-01T00:00:00],


The query selects the Average Total Sales KPI value, goal, status, and trend for data in 1997 from sales to Brazil and Canada (the same slicer data retrieved by our most recent KPI browser view query). You can run a syntax check on the query by choosing the Query/Parse main menu option or by clicking the Parse button (seventh from the right, with the check mark icon) on the SQL Analysis Services Editors toolbar. A Results pane appears with a Messages tab, on which the following output should be displayed:

Parsing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet
Parsing complete

Assuming you have passed your syntax check, go ahead and execute the query. You will find an Execute option on the query editor’s shortcut menu, the Query branch of the menu, or the SQL Server Analysis Services Editors toolbar. You can also press the F5 key or Ctrl+E on your keyboard to execute the MDX query.


As with the Management Studio Database Engine Query (SQL query) window, the MDX query window executes as a query whatever text is selected, or it executes the entire editor’s contents if no text is selected within it. The Parse option behaves this way as well. You should therefore make sure that no text (or all text) is selected in the MDX query editor when you parse or execute your query.

After you execute your query, the result is displayed on a new Results tab in the Results pane of the MDX query editor and should look similar to Figure 5.

Figure 5. The MDX Query window with KPI-revealing MDX query text and results shown

Compare the output to the KPI browser view of the cube designer back in our Analysis Services project in Visual Studio, and you will see that they are essentially the same. One obvious exception is that the status value is displayed numerically rather than graphically. In your own application, you can write code to execute this MDX query and render the KPI status and trend data graphically through conditional interpretation of the KPI values.

More Info

You’ll notice that this query returns a non-NULL value for trend (specifically, a value of –1). If you run the same query for calendar 1996, you will see that the average total sales for that period was $1987.43. Therefore, the KPI value for calendar 1997 ($1420.01) is in fact a decrease over the prior period, and –1 is the correct trend result.

If you run the query for calendar 1998, you will see that the KPI value rises to $1787.37 and the KPI trend formula correctly returns a value of 1. The trend returned for calendar 1996 is NULL, which is correct because we have no data in the cube for what would be the previous period (calendar 1995).

The cube designer’s KPI browser filters data in such a way that prior period data is not available, resulting in a NULL return value (and no graphic) for all filter conditions. MDX queries do not (and your own applications will not) exhibit this same defect.

Other BI Tricks in Management Studio

We still need to discuss adding actions, perspectives, translations, and roles to our cube, and how to manage their storage. Before we do so, however, let’s examine what other OLAP-related tasks we can accomplish in Management Studio.

You should see child nodes appear for the database’s data sources, data source views, cubes, dimensions, mining structures, roles, and assemblies. These are, of course, the same high-level nodes that appear in the Solution Explorer window for an Analysis Services project in Visual Studio. Although Management Studio does not offer full design control over these elements, you can enumerate each of them and view their properties in multi-page property sheets by right-clicking any of them and selecting the Properties shortcut menu option.

By using the objects’ shortcut menus, you can browse dimensions and cubes in Management Studio just as if you were in Visual Studio. You can also process objects and add and configure partitions. We will cover partitions shortly, but we mention these options now so you know they are available in both Management Studio and the Analysis Services designers in Visual Studio.

XMLA Scripts in Management Studio and Visual Studio Analysis Services Projects

You can also generate XMLA scripts for objects by using their shortcut menus. By generating scripts for individual objects, or entire Analysis Services databases, you can easily migrate your databases to other servers. You can then transmit the scripts to other developers and/or keep them in source control by organizing the scripts within Management Studio Analysis Services Scripts projects.

More Info

Management Studio offers source control integration through the Source Control\Plug-in Selection tree view item in its Options dialog box. (Choose Tools/Options from the main menu.)

To get a feel for what XMLA scripts look like, drill down to the Products dimension in the Object Explorer window, right-click on it, and choose Script Dimension As/CREATE To/New Query Editor Window from the shortcut menu. The XMLA script for the dimension appears in a new XMLA query window. Notice that each facet of the dimension is meticulously described within the various XML elements in the body of the document.

Now return to our Visual Studio Analysis Services project and drill down to and select the Products.dim dimension node in the Solution Explorer window. Right-click this node and select the View Code option from its shortcut menu or choose View/Code from the main menu. You will see that nearly the same XML content is displayed. This similarity reveals that the Visual Studio Analysis Services project designers are merely GUI front ends to the XMLA scripts that are necessary to create the objects on an Analysis Services server. When you deploy your project from Visual Studio, you are essentially executing the scripts generated by the designers. XMLA scripts can also be executed in Management Studio; to do so you simply use one of the Execute menu or toolbar options from within the XMLA query editor window.

  •  Oracle Database 11g : Database Fundamentals - Work with Object and System Privileges, Introduce Yourself to the Grid
  •  Oracle Database 11g : Database Fundamentals - Become Familiar with Other Important Items in Oracle Database 11g
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 2) - Creating a Record Editor
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 1)
  •  Microsoft ASP.NET 4 : Single-Value Data Binding
  •  SQL Server 2005 : Dynamic T-SQL - Dynamic SQL Security Considerations
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 4) - sp_executesql: A Better EXECUTE
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 3) - SQL Injection
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 2) - Going Dynamic: Using EXECUTE
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 1) - Optional Parameters via Static T-SQL
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone