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 Mar 8, 2004

MDX Essentials: Basic Numeric Functions: The Count() Function - Page 3

By William Pearson


The Basics

To reinforce our understanding of the basics we have covered so far, we will use the Count() function in a manner that illustrates its operation through a multi-step practice example: We will first construct a simple select query that performs a basic count.

We will use the MDX Sample Application again, as our tool to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

Let's assume for our practice example that we have received a call from corporate Finance needing some statistics to insert into a footnote of the 1998 Financial Statements. They wish to know how many of each Store Type the organization operates in each Country. "Operation" in this context is taken to mean that placement of orders from organizational warehouses.

To rephrase, our objective will be to present a count of Store Types by Country, for Year 1998, based upon data in the FoodMart Warehouse cube.

We will begin by composing a simple query to return the count without regard to empty cells within the specified set.

5.  Type the following query into the Query pane:

-- MDX17-1:  Counting that includes Empty Cells
    Member [Store Type].[Types Count]
    'Count( {[Store Type].[Store Type].MEMBERS })'
    {  [Store Type].[Types Count] } ON COLUMNS,
    { [Store].[Store Country].MEMBERS } ON ROWS
([Time].[1998], [Measures].[Units Ordered])

The purpose of this query is simply to generate a basic count of all cells that lie within the range of the specified set. Keep in mind that, though no flag appears in the Count() function, the default condition, INCLUDEEMPTY, is in force.

6.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.

Illustration 2: Result Dataset - Simple Count with Default INCLUDEEMPTY

While we see the count of Store Types in each of our three Countries of operation to be six, a quick glance at the Store Type dimension in the metadata pane of the Sample Application indicates that the FoodMart organization coincidentally has exactly six Store Types, as depicted in Illustration 3.

Illustration 3: The Store Type Dimension in the Metadata Pane

One of those store types is Headquarters, a type that exists for classification purposes only, as an entity that experiences no ordering activity. For this and other reasons, we can see that the counts we deliver to Finance need to be adjusted for empty cells / invalid combinations.

7.  Select File --> Save As, name the file MDX17-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

We will next adjust the Count() function in our query to enforce removal of empty cells from the count.

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