programming4us
programming4us
DATABASE

SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 1) - Management Studio as an MDX Client

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/30/2012 6:03:16 PM
We have covered in great detail how to develop powerful front ends, both for Windows and the Web, using OWC. OWC front ends are a serious option for OLAP development, given the ubiquity of and business users’ familiarity with Office. Moreover, using OWC controls enables you to create compelling front ends with minimal programming.

Although the OWC option lets you avoid the need for much programming, it also precludes the granular control that a more programmatic approach would offer. OWC controls also require that Office be installed on the user’s machine if you want to offer the controls’ full feature set and avoid tricky deployment issues. Furthermore, OWC controls all but require direct intranet access to an Analysis Services server from the user’s machine. Even when run from a Web page, the controls run and connect to cubes locally rather than on the server.

If you want to write true Web applications, where the user needs only a browser and nothing else to run queries against your cube, you should avoid using OWC controls. If you want fine-grained control over the query—moving beyond mere selection of measures and dimensions—you’ll once again want to steer clear of OWC. And if you’re a .NET developer and want to use your ADO.NET and/or data binding skills in the context of an OLAP application, you’ll absolutely want to steer clear of OWC.

MDX and Analysis Services APIs

In short, if you want to build highly customized OLAP applications, you’ll almost definitely want to learn some basic multidimensional expression language (MDX) and understand how to execute MDX queries using ADO MD.NET (the Multidimensional ADO.NET data provider). We will discuss both of these technologies shortly and demonstrate how to apply them to Windows Forms applications.

As we cover MDX and ADO MD.NET, we will explain how to use SQL Server Management Studio as an MDX client. We will also investigate how to execute XML for Analysis (XMLA) queries from Management Studio. XMLA can be used as both a DML (data manipulation language) and DDL (data definition language) facility from Management Studio. ADO MD.NET acts as a wrapper around the DML side of XMLA. Another managed object model, Analysis Management Objects (AMO), acts as a wrapper against its DDL side. We will examine programmatic use of XMLA in your code, and how to expose “raw” XMLA responses from ADO MD.NET.

To complete our coverage of OLAP development, we will show you how to take advantage of the CLR server-side programming features in Analysis Services. As we do this, you’ll see how your client-side OLAP development skills can be migrated to the server to develop sophisticated logic that extends the innate capabilities of MDX from the client.

Moving to MDX

Whether you’re programming on the client or the server, using ADO MD.NET, or just working with SQL Server Management Studio, you’ll need at least a working knowledge of MDX. MDX is an extremely rich language. Luckily, using MDX for simple queries is itself simple and will be somewhat familiar to those with a grounding in SQL. A practical prototype of an MDX query is as follows:

SELECT [column members] ON COLUMNS,
      [row members] ON ROWS
FROM [cubename]
WHERE [measure]

This “schematic” code glosses over the details of specifying row and column members and oversimplifies the real purpose of the WHERE clause, but we will remedy these shortcomings soon. 

SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS,
      Time.Year.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Total Sales]

Before we go any further in our discussion of this query or of MDX itself, let’s discuss SQL Server Management Studio’s use as a top-notch tool for running, designing, and learning how to write MDX queries.

Management Studio as an MDX Client

Management Studio is a replacement not only for SQL Enterprise Manager and SQL Query Analyzer but also for the SQL Server 2000 Analysis Services tools: Analysis Manager and the MDX Sample Application. The MDX Sample Application, shown in Figure 1, allows for execution of ad hoc MDX queries and saving and retrieving them to and from text files, and it includes a simple UI that allows for drag-and-drop visual composition of these queries. It’s a useful tool, although it is completely separate from the other SQL Server 2000 tools, and it is provided, as its name implies, as a sample application (including its Visual Basic 6.0 source code) more than as a tool per se.

Figure 1. The MDX Sample Application (supplied with SQL Server 2000 Analysis Services)

Management Studio includes the functionality of the MDX Sample Application by providing MDX Query windows that work almost identically to its SQL Query windows. MDX query functionality is thus integrated with the rest of the Management Studio feature set, and Management Studio also provides some extra features, including IntelliSense. Analysis Services script projects, which can include MDX queries, can be created within Management Studio, allowing you to create solutions consisting of both SQL and Analysis Services projects with scripts for related tables and cubes.

Caution

Although both the MDX Sample Application and Management Studio use the .mdx extension for their respective MDX query files, the two file formats are not compatible. The MDX Sample Application stores multiple queries per file and separates them using a quote delimiter scheme. Management Studio stores MDX query text without any additional formatting, much as it does with SQL text in .sql files.



Open the solution now, and double-click on the SimpleQuery.mdx query file to open the query in Management Studio. Click the Execute button on the SQL Server Analysis Services Editors toolbar (or use the keyboard, main menu, or shortcut menu equivalent) to see the results of the MDX query displayed in a grid on the Results tab, as shown in Figure 2.
Figure 2. The SQL Server Management Studio MDX Query window, with the Results tab visible

Rich MDX Support

Besides being a convenient text editor for writing and testing your queries, Management Studio includes many features that make the creation of MDX queries easy for people who are new to MDX.

Making the Connection

First you should understand the various ways to open a new query. These include the File/New/Analysis Services MDX Query main menu option, and the toolbar button of the same name (third from the left) on the Standard toolbar. Either option opens a new MDX Query window but first presents the Connect To Analysis Services dialog box, which requires you to connect or cancel before it places you in the MDX editor. Much like its Connect To Database Engine “cousin,” this dialog box requires you to provide a server/instance name and allows you to enter optional connection properties by clicking the Options button and supplying information (such as the database to connect to) on the Connection Properties tab that appears.

To avoid the Connect To Analysis Services dialog box and go directly into an MDX Query window, select an Analysis Services server in the Registered Servers or Object Explorer windows, and then click the New Query button (first on the left) on the Standard toolbar. To view Analysis Services servers in the Registered Servers window, you must click the Analysis Services button (second from the left, with the cube icon) on the window’s toolbar.

Other ways of “speed dialing” into the MDX Query window include double-clicking an Analysis Services connection in the Solution Explorer window; right-clicking a connection there and choosing New Query; right-clicking an Analysis Services server node in the Registered Servers window and choosing Connect/New Query from the shortcut menu; and right-clicking an Analysis Services server or database node in the Object Explorer window and choosing New Query/MDX. When you use the last of these techniques with a database node, your MDX Query window will select that database by default.

Once an MDX Query window is open, you can change the connection information by using the Change Connection button (third from the left) on the SQL Server Analysis Services Editors toolbar or choosing Query/Connection/Change Connection... from the main menu. Either approach opens the Connect To Analysis Services dialog box. If you did not connect successfully to begin with, you can accomplish the same thing by clicking the Connect button (first from the left) on the SQL Server Analysis Services Editors toolbar or choosing Query/ Connection/Connect... from the main menu. The Query/Connection main menu options are also available from the Connection option on the MDX Query window’s shortcut menu.

Once connected, you can use a different database on the same server by selecting that database from the databases drop-down list (just to the right of the Change Connection button) on the SQL Server Analysis Services Editors toolbar. You can intentionally return your MDX Query window to a disconnected state by clicking the Disconnect button (second from the left) on the SQL Server Analysis Services Editors toolbar or by choosing Query/Connection/ Disconnect from the main menu (or Connection/Disconnect from the MDX Query window’s shortcut menu).

After you open the MDX Query window and connect to the appropriate server and database, the Tools pane (the section to the left of the MDX edit region) will become enabled, its drop-down list of available cubes/perspectives will populate, and metadata for the selected cube/perspective will be displayed in the Metadata tab’s tree view.

Building a Query

One excellent way to learn MDX is through the use of the MDX templates supplied with Management Studio. To see these, you must show Template Explorer if it’s not shown already. Use the Template Explorer button (second from the right) on the Standard toolbar, choose View/ Template Explorer from the main menu, or press Ctrl+Alt+T. Next, click the Analysis Services button (second from the left or right) on the Template Explorer window’s toolbar to display Analysis Services templates. Drill down on the MDX node and then on its child Expressions and Queries nodes to show all available MDX templates (Figure 3).

Figure 3. The SQL Server Management Studio Template Explorer, with Analysis Services MDX templates displayed


In all, SQL Server Management Studio includes 10 MDX templates to help you learn the language. Because we’re writing simple queries, we’ll start with the Basic Query template. Drag and drop the Basic Query node (under the Queries parent node) into the open MDX window, and then press the left arrow or Home key on your keyboard to deselect the MDX template text. Notice that the skeletal MDX provided is similar to the “schematic” MDX query we discussed earlier:

-- =============================================
-- Basic MDX Query
-- =============================================

Select <row_axis, mdx_set,> on Columns,
      <column_axis, mdx_set,> on Rows

From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>

Important

Management Studio’s Basic Query MDX template erroneously places a row_axis place holder in the on Columns clause and a column_axis placeholder in the on Rows clause. It should, of course, use the reverse arrangement. Nevertheless, if you follow our directions carefully, the query you will generate will be correct.


With this skeletal code in place, our next task is to fill out all the parameterized clauses that appear as angle-bracketed text. Choose Query/Specify Values For Template Parameters... from the main menu or click the Specify Values For Template Parameters button (fifth from the right) on the SQL Server Analysis Services Editors toolbar to open the Specify Values For Template Parameters dialog box (Figure 4).

Figure 4. The Specify Values For Template Parameters dialog box in SQL Server Management Studio


This dialog box offers a single UI for specifying all query specifics, but it is designed for programmers who are already sufficiently experienced with MDX syntax such that typing the clauses without assistance is feasible and preferable. We are still getting acquainted with MDX, so we’ll supply the parameterized clauses through different means. For now, click Cancel to dismiss the dialog box.

We will supply the four requested clauses (ON ROWS, ON COLUMNS, FROM, and WHERE) one at a time. Let’s start with the row_axis: Select the entire <row_axis, mdx_set,> angle-bracketed clause and press the Delete key. Next, in the metadata tree view, drill down on the Geography dimension node and then drag its Country attribute child node into the MDX edit region, dropping it just to the left of the “on Columns” portion of the command (leaving a space to the right of the drop position). Press the right arrow key to deselect the dropped text. Next, type a period. Your query text should now appear as follows:

Select [Geography].[Country]. on Columns,
      <column_axis, mdx_set,> on Rows

From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>

With the cursor just to the right of the period you typed, choose IntelliSense, List Members from the Edit menu or press Ctrl+J. An IntelliSense popup list should appear; type members, and the keyword MEMBERS should become selected. Press Enter or Tab to insert the entire word into your code.

Repeat this process for the column_axis clause, but this time use the Beverages member of the Category - Product hierarchy of the Products dimension as the base of the expression (that is, drill down to the Products\Category - Product\Members\All\Beverages node and drag it into the MDX editor). Next, after typing a period as you did for the row_axis clause, click on the Functions tab in the Tools pane, drill down to the Navigation\CHILDREN node, and drag it to the right of the period in the editor. After you drop the text, delete the «Member». text (including the period) immediately to the left of the CHILDREN keyword.

Remove the bracketed from_clause text from the query, and then click the Metadata tab and drag the Sales (root) node to the cursor position. Finish by removing the where_clause parameter and dragging the Measures\Main\Sum\Total Sales node to take its place. Your query should appear as follows. (If not, you can find it in the file Children.mdx.)

Select [Geography].[Country].MEMBERS on Columns,
      [Products].[Category - Product].[Category Name].&[Beverages].CHILDREN on Rows

From [Sales]
Where [Measures].[Total Sales]

					  

Execute the query. Figure 5 shows a partial view of the results you should receive.

Figure 5. Our fully configured MDX query, with results displayed

Beyond Syntax

Although we have written only two queries so far, we have covered quite a lot. You’ve learned the basic MDX syntax and the use of the MDX Query window. You’ve learned how to use the Registered Servers and Object Explorer windows with Analysis Services servers, including several shortcuts for opening MDX Query windows against specific connections and databases. In the MDX Query window, you learned how to use Template Explorer, MDX IntelliSense, and the Metadata and Functions tabs in the Tools pane to design your MDX queries step by step. However, although we’ve exposed you to the mechanics of MDX and the MDX features in Management Studio, we haven’t really discussed the language itself in much depth. Let’s remedy that now by focusing on some important MDX concepts.

Let’s start with our first query. Notice that both the ON COLUMNS and ON ROWS clauses use a dot-separated syntax to specify the dimension and attribute. The name of our measure, Total Sales, is enclosed within square brackets; this is required syntax for any name that contains one or more embedded spaces. Names without embedded spaces can also be enclosed in square brackets, although we did not do so in this query. As you saw when you used the MDXgenerating tools in SQL Server Management Studio, however, use of squared brackets for non-spaced names is encouraged.

Immediately following the attribute names in the ON COLUMNS and ON ROWS clauses, we used the MDX function keyword MEMBERS to request that each individual member of the attribute be included as a row or column. Axis specifiers (that is, the expressions preceding the ON ROWS or ON COLUMNS keywords) must always indicate a set—that is, a collection of items belonging to one or a combination of attributes or hierarchy levels. These items must either be listed explicitly or specified through the use of an MDX function, such as MEMBERS.

The proper MDX name for items in a set is tuple. A tuple, whose more formal syntax we will describe shortly, specifies a member in every dimension in the cube. In practice, however, a tuple need not explicitly specify members for more than one or perhaps a few dimensions. That’s because each tuple implicitly references the default member of each dimension when a specific member is not given, and in most cases the default member is the [All] member. So even if we identify a tuple by, say, a shipper name and nothing else, that tuple will refer to the subset of the cube for that shipper in the Shippers dimension and the [All] member (that is, the entirety) of all other dimensions.

We must tell you now that this rather theoretical definition of a tuple makes sense to very few people at first. At this point, you need simply to acknowledge it and file it away for later, when you’ve had more OLAP experience and it will make more sense. In practice, you can think of sets as consisting of simply specified members, rather than cryptically defined tuples. To illustrate the set concept more directly, here is a variation on our first query in which we use an explicit set specifier for the COLUMNS axis:

SELECT {[Speedy Express], [Federal Shipping]} ON COLUMNS,
      Time.Year.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Total Sales]

This query (contained in the file SelectedShippers.mdx) allows us to request a limited, specific set of Shippers attribute members rather than having to show them all. Each shipper is technically a tuple, but you can feel free to think of each one as a member for now.

As you can see, explicitly specifying even members can be a bit tedious and the syntax requirements are quite stringent. That’s why functions such as MEMBERS can be extremely useful—they allow you to specify large sets with relatively little typing and few curly braces. But OLAP developers do not live by the MEMBERS function alone! MDX contains a dizzying array of other set-generating functions.

For example, you can use the MDX keyword CHILDREN to list all of the child members of a specified node in a hierarchy. To understand how this works, refer back to our second query (in Children.mdx). The ROWS axis shows products in the Beverages category (the children of the Beverages member of the Category Name level of the Category – Product hierarchy of the Products dimension).

Grokking the WHERE Clause

Now let’s discuss the WHERE clause of this query in more detail. We have been using the WHERE clause thus far as a mechanism to specify the measure we want to see in our result set. This is a valid and typical use of the WHERE clause in an MDX query, but it obfuscates the real purpose of the clause: to specify for the query a filter condition for each and every dimension in the cube.

On initial inspection, this seems counterintuitive on a number of fronts. For example, our WHERE clause specified our desired measure but it did not seem to specify any type of filter or filter condition, nor anything relating to dimensions. But it turns out that, technically speaking, our WHERE clause does indeed supply a dimension filter. Understanding why this is the case requires an appreciation of how MDX views measures and how it uses the default members of a dimension.

Simply put, MDX considers your cube to consist of dimensions and data only. Rather than viewing measures as distinct entities within your cube, it considers each measure to be a member of a flat hierarchy of a single dimension called Measures. For this reason, the expression [Measures].[Total Sales] in the WHERE clause is a filter condition: In effect, you are telling the MDX engine to limit the query to analysis of the [Total Sales] member of the Measures “dimension.” For all other dimensions in the cube, no filter condition is specified, so the MDX query returns the entire contents of all the actual dimensions.

If you want to place a filter that seems more “real” in your query, you can do so by adding to the WHERE clause. For example, to limit our first query to sales of products in the Condiments category, you would modify the previous query to the following (which can be found in the script file TrueFilter.mdx):

SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS,
      Time.Year.MEMBERS ON ROWS
FROM Sales
WHERE (Measures.[Total Sales],
   Products.[Category Name].Condiments)

Notice that the filtering condition on the Products dimension is concatenated to the existing condition on the Measures “dimension” with a comma delimiter, and that the entire WHERE clause expression is enclosed in parentheses.

The parentheses are required for any tuple that is specified through two or more explicitly named members. Because in this case we are specifying a tuple with two members (Measures.[Total Sales] and Products.[Category Name].Condiments), we must supply them in a comma-delimited list and enclose the list in parentheses. The tuple in our WHERE clause is the subset of the cube consisting of [Total Sales] data for products within the Condiments product category and the entirety of all other dimensions in the cube. The rest of our query then breaks down this subset of the cube by shipper name and calendar year.

More Info

It is important to remember that the WHERE expression in the previous query (which contained the simple expression Measures.[Total Sales]) was also a tuple in its own right. Even though it appeared to be a simple member specifier, its implicit reference to the [All] member of each dimension in the cube qualified it to be every bit as much a tuple expression as the more complex one used just now.


The Power of Axes

Let’s take a step back and discuss the concept of axes. The queries we’ve looked at thus far have all returned results over two axes, namely ROWS and COLUMNS. Such two-dimensional result sets are convenient and often sufficient, but MDX can handle many more than two axes. Although the first five axes have names, you can also refer to them by number.

For example, consider the following query (which can be found in the file SingleAxis.mdx) that returns data along a single, numbered axis:

SELECT Geography.Country.MEMBERS ON 0
FROM Sales
WHERE Measures.[Total Sales]

Here we are specifying only a columns axis, using its axis number. Axis 0 is the COLUMNS axis, and axis 1 is the ROWS axis. Note that no gaps are allowed in the axis sequence, so a single-axis query must use the COLUMNS axis and not the ROWS axis. Axes can, however, be listed out of sequence, as in the following query (which can be found in the file AxesOutOfSequence.mdx) that returns data on two axes but specifies the ROWS axis before the COLUMNS axis and does not use a WHERE clause:

SELECT Products.[Category - Product].[Category Name].Beverages.CHILDREN ON 1,
      Measures.MEMBERS ON 0
FROM Sales

					  

This query asks for the members of the Measures “dimension” to be listed along the COLUMNS axis, thus allowing us not to use a WHERE clause that would normally specify our selected measure.

What about queries with more than two axes? MDX permits queries to return a theoretically boundless number of axes (though available resources on the server and client hardware will impose some limit). You can name three axes past the COLUMNS and ROWS axes. The axes’ names after ROWS are, in axis order, PAGES, SECTIONS, and CHAPTERS. These correspond numerically to axes 2, 3, and 4 (with COLUMNS and ROWS being 0 and 1, respectively). The names of these higher axes convey what it means to bring back data on more than two axes: Although it is difficult to imagine seeing data presented on more than two axes at once, it is relatively easy to imagine a series of two-axis result sets spread over several pages, sections, and chapters of a book.

Another way to think through axis management is to imagine an Excel workbook. In Excel, data is always shown one spreadsheet at a time, and each sheet has both columns and rows. But one workbook can contain multiple tabs (pages), several workbooks can be contained in a folder (section), and a collection of folders can be contained in a parent folder (chapter). You can easily imagine more “axes” by understanding that multiple drives can be managed by a single server, multiple servers can belong to a single farm, and so on. You can thus imagine writing queries with more axes than the five named ones.

With this understanding of the use of multiple axes, the following query (which can be found in the file ThreeAxes.mdx) should now seem quite sensible:

SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS,
      Geography.Country.MEMBERS ON ROWS,
      Time.Year.MEMBERS ON PAGES
FROM Sales
WHERE (Measures.[Total Sales],
   Products.[Category Name].Condiments)

When executed, the query returns total sales for all products in the Condiments category, broken down into shippers and country on the ROWS and COLUMNS axes, with the data for each distinct calendar year on a separate page.

However, if you execute this query in SQL Server Management Studio, the Results tab will be empty and the following text will be returned on the Messages tab of the results pane:

Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet
Formatting.
Results cannot be displayed for cellsets with more than two axes.
Execution complete

The second line of this message indicates that the query is valid and executed without error. However, the fourth line indicates that queries returning data over more than two axes cannot be displayed in the SQL Server Management Studio MDX Query window.


CROSSJOIN and DRILLDOWNMEMBER

How can we deal with this dilemma? Shortly, we’ll explore how to execute MDX queries in .NET code and discuss two ways of handling queries that return more than two axes. For now, however, you can rewrite your query as follows:

SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS,
      CROSSJOIN([Time].[Year].MEMBERS,
          Geography.Country.MEMBERS) ON ROWS
FROM Sales
WHERE (Measures.[Total Sales],
      Products.[Category Name].Condiments)

The query (which can be found in the file CrossJoin.mdx) uses the MDX CROSSJOIN function to “stack” or “flatten” the members of Time.Year and Geography.Country together on the ROWS axis. This has the effect of creating a result set where each row “header” has two members within it: a country and a year.

Note

In our crossjoin query, data is returned by year and then country. If the order of the sets in the CROSSJOIN function had been reversed, the data would have been returned by country and then year.


If you review the results data, you will see that the second row in the 1996 section of our result set (and the 25th row overall), which contains total sales data for all condiments sold in Argentina in 1996, contains nothing but NULLs. This indicates that no condiment products were sold in Argentina in 1996; likewise, the last column (for the Unknown product member) also consists of empty cells. MDX makes it easy to suppress these empty axis members with the use of the NONEMPTYCROSSJOIN and NONEMPTY functions. To see this in action, modify the query as follows. (This version can be found in the file NonEmpty.mdx.)

SELECT NON EMPTY(Shippers.[Shipper Name].MEMBERS) ON COLUMNS,
      NONEMPTYCROSSJOIN(Time.Year.MEMBERS, Geography.Country.MEMBERS) ON ROWS
FROM Sales
WHERE (Measures.[Total Sales],
      Products.[Category Name].Condiments)

					  

When you execute the query, the columns and rows that were completely populated with NULLs will be gone and you will find the results much easier to read.

We can’t hope to cover the comprehensive set of hierarchy-oriented functions in MDX, but we can give you a taste by showing another query (which can be found in the file DrillDownMember.mdx). This query returns the same data as the previous one but also returns the state/ province-level data for Canada and the United States:

SELECT NONEMPTY(Shippers.[Shipper Name].MEMBERS) ON COLUMNS,
      NONEMPTYCROSSJOIN(Time.Year.MEMBERS,
      DRILLDOWNMEMBER(Geography.[Country - State-Province - City -
      Postal Code].Country.MEMBERS, {CANADA, USA})) ON ROWS
FROM Sales
WHERE (Measures.[Total Sales],
      Products.[Category Name].Condiments)

Note the use of the DRILLDOWNMEMBER function to obtain the [Total Sales] data for the states and provinces in Canada and the USA in addition to the country data. The DRILLDOWNMEMBER function accepts two sets and returns the union of the first with the children of any of its members contained in the second.

KPIs, Calculated Members, and Named Sets

We’re almost done with the theoretical side of MDX, and you now have a good sense of its power (although we still have plenty to explain about how to execute MDX queries programmatically). But all the queries we’ve examined so far leverage only the simple components of our cube—specifically, dimensions and measures.

First let’s start with KPIs. Recall that a KPI, when applied to a slice of our cube, generates a value, a goal, a status, and a trend (the latter two usually returning a discrete value between –1 and +1, inclusive). Recall also that MDX functions exist for returning all four of these values. 

SELECT {KPIValue("Average Total Sales KPI"),
      KPIGoal("Average Total Sales KPI"),
      KPIStatus("Average Total Sales KPI"),
      KPITrend("Average Total Sales KPI")} ON COLUMNS,
      [OrderDate].[Year -  Quarter -  Month -  Date].Year.MEMBERS ON ROWS
FROM Sales
WHERE Geography.Country.UK

MDX cannot tell us the status and trend graphics we configured the KPI to use, but we can retrieve that information from the ADO MD.NET Kpi object.

What about calculated members and named sets? The beauty of calculated members is that they can be referenced from an MDX query as if they were physically part of the cube, rather than calculated. You can use named sets as substitutes for explicitly stated sets, such as the one we examined earlier in the SelectedShippers.mdx query. All of this is much easier to understand when viewed in a query such as the following one (which can be found in NamedSetCalcMember.mdx):

SELECT Time.[Year -  Quarter -  Month -  OrderDate].Year.MEMBERS ON COLUMNS,
      [Test Markets] ON ROWS
FROM Sales
WHERE Measures.[Adjusted Sales]

					  

This query returns values for the [Adjusted Sales] calculated measure, broken down by calendar year on the COLUMNS axis and by the three cities in our [Test Markets] named set on the ROWS axis.

The next query (which can be found in AdjustedGrowthByState.mdx), demonstrates how to query a calculated measure whose formula is based on a time series comparison. In this case, we are querying our [Adjusted Sales Growth] calculated measure, which measures year-on-year sales growth as a percentage. This calculated measure requires that the Order Date dimension, on which its formula is based, be used in one of the axis specifiers, which it is.

SELECT [Order Date].[Year].MEMBERS ON COLUMNS,
[Geography].[Country - State-Province - City Postal Code].[Country].USA.CHILDREN ON ROWS
FROM Sales
WHERE Measures.[Adjusted Sales Growth]

					  
This query shows the growth numbers, for all years for which the cube holds data, on the COLUMNS axis, broken down by individual states in the United States on the ROWS axis.

At this point, we’ve studied enough MDX and it’s time to start learning how to put it to use. Keep in mind that we’ve covered how to take advantage of a number of advanced cube features, including KPIs and calculations (named sets and calculated members). We haven’t covered drillthrough-based or URL-based actions, even though these are all based on stored MDX expressions. We will cover these shortly; having some basic API knowledge under your belt first will make this easier. So let’s set aside writing queries for now and learn how to put them to work in our applications.

Other  
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 2) - Using FILESTREAM Storage for Data Columns
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 1) - Enabling FILESTREAM Storage
  •  SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages
  •  SQL Server 2005 : Using Excel (part 1) - Working Within Excel
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 1) - Creating a Hierarchy, Populating the Hierarchy, Querying the Hierarchy
  •  Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers
  •  Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 2) - Custom Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

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

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