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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 16, 2002

MDX Essentials: Structure of the MDX Data Model - Page 5

By William Pearson

Now let's expand the query beyond its current selection. Suppose the information consumer asks that we add a couple of features to the query result. He asks that we again provide total headcount for the years 1997 and 1998 for the entire company, but this time he wants us to break out the current pay types to show composition of the workers in our totals. Again, we are to provide the information in a two-dimensional grid, with the years as column headings. He states that he wants to see the pay types in the rows.

  1. Select File -> New from the top menu.
  2. Type the following query into the Query pane:
--MDX02-2: Tutorial Query No. 2 SELECT {([Time].[1997]), ([Time].[1998])}ON COLUMNS, {[Pay Type].[Pay Type].Members} ON ROWS FROM HR WHERE ([Measures].[Count])

The diagram below labels the various parts of the query:

Illustration 5: Labeled Parts of a Basic MDX Query

  1. Click Query on the top menu, and then select Run.

We see the results below, which appear in the Query pane as soon as Analysis Services fills the cells that it determines to be specified by the query. As is apparent, the two pay types within the organization are hourly and salaried.

Illustration 6: The Initial Query Results

The query delivers the results that were requested by the information consumer. A quick review indicates that the values associated with the two pay types total to the values we received in the first query (MDX02-01) above. (We will discuss the .members components in the next lesson).

  1. Save the query as MDX02-2.
  2. Exit the Sample Application.

As we move into the stages of query building, from basic to advanced, we will constantly reinforce the concept that an MDX query result consists of another cube. All queries return cells, and the end consumer may be interested in either the data in the respective cells, the members that are associated with the data, properties of those members, or a combination of these. We will look at examples of each of these from various perspectives as we progress through the series.

Page 6: Next in Our Series

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