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 5

By William Pearson

Ascertaining Accuracy and Completeness

To get a feel for the correctness of the information we are about to report to the information consumers in Finance, let's draft a query that asks for the data from a different viewpoint. We will request the information indirectly, without using the Count() function at all, but simply asking for Units Ordered information for each Store Type, by Country.

We will use a Crossjoin() function to accomplish this alternative perspective. CrossJoin() allows us to combine two or more dimensions on a single row, column, etc., and can be a powerful tool to use in MDX queries. We will devote an article to CrossJoin() in our next lesson, but, for now, we will use it as a means of checking our results from the prior sections. Consider this a preview of CrossJoin() as well, and as an opportunity to give it some thought before we focus on its use in the next and later articles.

1.  Select File --> New to create a new query in the Sample Application.

2.  Type the following into the Query pane:

-- MDX17-4:  Proof of  Count() Results
    {[Measures].[Units Ordered] } ON COLUMNS,
    {[Store].[Store Country].Members},
    {[Store Type].[Store Type].Members}
        ) ON ROWS

3.  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 8 appears.

Illustration 8: Result Dataset - Using CrossJoin() to Generate Another Perspective

While we have asked for the Units Ordered quantities for each Store Type, by Country (excluding empty combinations with another keyword for that purpose, NON EMPTY, that we will encounter again in the future), we have also achieved another objective: we can see that our counts were accurate in reviewing the physical breakdown of the Store Types that had activity for each country. This provides proof positive that our counts in earlier sections were correct, and, in our business case, that the numbers we intend to provide to Finance are accurate. (With only a little additional work, we will even be ready to respond to the virtually certain request for "more information" once we provide the statistics requested!)

Summary ...

In this lesson, we stepped out of the purely set-related functions that we have examined in recent articles to focus on the numerical Count() function, as it is applied to sets. We introduced the Count() function with a discussion of its straightforward purpose, to return the number of cells in a specified set, and then exposed options within the syntax for overriding the default behavior of the function, to exclude empty cells within the range of the specified set, and therefore within the returned result of a query using the function.

Along with an introduction to the Count() function, our lesson included an examination of the syntaxes surrounding the function and illustrative examples where we used the function, both with and without the EXCLUDEEMPTY keyword, to meet a hypothetical business need. We performed a query using the CrossJoin() function to act as a "proofing" procedure, to ensure the accuracy and completeness of the results we obtained within our practice with the Count() function, as well as to preview CrossJoin() for our next article. Finally, throughout the steps of our examples, we discussed the results we obtained using MDX.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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