Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 6, 2002

MDX at First Glance: Introduction to SQL Server MDX Essentials - Page 4

By William Pearson

The following general discussion items apply to the syntax above, and to MDX queries in general:

  • The top line of the query is a comment. We will discuss comments later in the series, but suffice it to say for now that the two dashes (--) represent one of three typical ways to place a comment in MDX syntax, so that it is ignored when the MDX is parsed.

    I introduce this at the present stage because I like to "label" queries in this way as I create them, so as to make them easy to identify for reuse or review. This is particularly handy when using the Sample Application, because the application displays the initial characters of the query in the dropdown selector (labeled "Query:") to the right of the database ("DB:") selector in the toolbar. Selection of a given query from a query file is easy, therefore, given the use of intuitive names/descriptions in the top line of the syntax.

  • The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL (Structured Query Language), where it stipulates the tables used as sources for the query.

  • The query syntax also uses other keywords that are common in SQL, such as SELECT and WHERE. Even though there are apparent similarities in the two languages, there are also significant differences. A prominent difference is that the output of an MDX query, which uses a cube as a data source, is another cube, whereas the output of an SQL query (which uses a columnar table as a source) is typically columnar.

    It is important to realize that MDX's cube output allows us to place any dimension from the source cube onto any axis of the query's result cube. Many axes can exist, and it is often better to think in terms of "axes" than in "dimensions" (as is quite common among both developers and information consumers) when designing an MDX query. This is for two main reasons: The "axes" concept allows for distinction between the source dimensions and the apparent result cube dimensions, which may be very different indeed. Another reason is that a given axis can contain a number of cube dimensions in combination. Axis references are therefore more precise, and less subject to misinterpretation.

  • A query has one or more axes. The query above has two. (The first three axes that are found in MDX queries are known as rows, columns and pages.) We stipulate the axes above through our use of the "columns" and "rows" specifications. Keep in mind that columns always come before rows, and rows always precede pages, within the query.

  • Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions. The query above has one dimension each on the two query axes. The dimensions that appear are the Measures and Time dimensions.

  • We can display more than one dimension on a result axis. When we do this, an "intersection" occurs, in effect, and each cell appearing in the associated axis relates to the combination of a member from each of the indicated dimensions. When more than one dimension is mapped onto an axis, the axis is said to consist of "tuples," containing the members of each of the mapped dimensions.

  • Dimensions that are not specified within the axes of a query will have members specified by default; we can also stipulate such members in the WHERE clause, as shown in our query above.

  1. Click the Run Query button (the button sporting the green, arrowhead-shaped icon -- a tool tip will alight when the cursor is placed upon the button to positively identify it for us).

We see the results below, which appear as soon as Analysis Services fills the cells that it determines to be specified by the query.

Illustration 4: The Initial Query Results

  1. Save the query by selecting File -> Save As and call the file MDX01-1, as shown in the illustration below.

Illustration 5: Saving the MDX Query via the Save As Dialog

Note: I typically prefer to save files to a context-oriented directory/folder (for example a folder I have created for a client for whom I am writing MDX queries as a part of an engagement, or for a presentation I am assembling). This is obviously a point of personal taste; the objective is simply to keep track of where the queries are, so that we can find them in time of need. Much rewriting and confusion between altered versions can be avoided by storing the queries in a logical system of some sort to keep organized. My favorite way to do this is to create a database within which to store the query strings, together with descriptions, author and keyword information, along with date time data, "version" information, and other specifics, if applicable.

Page 5: Another MDX Query

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM