dcsimg

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

March 8, 2004

The Count Function

According to the Analysis Services Books Online, the Count function "returns the number of items in a collection, depending on the collection." The term "collection" here is generic, as the "collections" to which the Books Online refers can consist of the dimensions in a cube or a tuple, the cells in a set, or the levels in a dimension or a hierarchy. Count handles these "collections" in a couple of different syntactical ways. The scope of this article will be to focus more on the set-specific use of Count(), and we will refer to the function with the appended parenthesis ( "()" ) in that context.

We will examine the syntax for the Count() function, and then take a look at the options it affords with regard to the exclusion of empty cells from the counts it performs. Next, we will undertake practice examples built around hypothetical business needs that illustrate logical uses for the function. We will extend the examples to include exclusion of empty cells in a set we construct, to afford us a scenario for examining the options to include or exclude these cells in the overall count.

Discussion

The Count() function counts the cells defined by the set that is specified in the function. The syntax with which we employ the function affords us the option of specifying whether or not we wish to count empty cells within the range of the specified set. If we do not wish to include empties, we simply add the appropriate keyword to restrict the returned count to populated cells only.

Let's look at some syntax illustrations to further clarify the operation of Count().

Syntax

Syntactically, the set we use to specify the range of cells to count is placed within the parentheses to the right of Count, and separated by a comma from the optional flag we have described. The optional EXCLUDEEMPTY flag overrides the default behavior of the function (which is INCLUDEEMPTY) with regard to empty cells, and forces the function to ignore any unpopulated cells within the set specified.

The syntax is shown in the following string:

Count(Set, [, INCLUDEEMPTY | EXCLUDEEMPTY])

The following example expression illustrates a use of the Count() function. Let's say that FoodMart information consumers, whose data is housed within the Warehouse cube, wish to see the number of Warehouses supporting each of our Product Departments in a given year. The basic Count() function involved, disregarding for the time being the empty or non-empty consideration, would be as follows:

Count ({[Warehouse].[City].MEMBERS })  

This is simply expressing that we wish to count the "number of Warehouse City members" for a given purpose. Looking at our cube structure within the metadata pane of the MDX Sample Application, as shown in Illustration 1, we can verify that there are twenty-three Warehouse City locations by performing a manual count.


Illustration 1: We Verify Twenty - Three Warehouse Cities in the Warehouse Cube

If we were to create an MDX query, within which we construct a calculated member to perform the count (see Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives, among kindred articles, in the Database Journal MDX in Analysis Services series for a hands-on introduction to calculated members), and to obtain the information requested, we might begin with the following:

WITH
    Member [Warehouse].[No. Locations]
AS
    'COUNT ( {[Warehouse].[City].MEMBERS })'
SELECT
    { [Warehouse]. [No. Locations] } ON COLUMNS,

    { [Product].[Product Department].Members} ON ROWS
FROM
    [Warehouse]
WHERE 
     [Time].[1998]

This simple query, for a count of the Warehouse Cities by Product Department, would return a dataset similar to that depicted in Table 1.

Product Department

No. Locations

Alcoholic Beverages

23

Beverages

23

Dairy

23

Baked Goods

23

Baking Goods

23

Breakfast Foods

23

Canned Foods

23

Canned Products

23

Dairy

23

Deli

23

Eggs

23

Frozen Foods

23

Meat

23

Produce

23

Seafood

23

Snack Foods

23

Snacks

23

Starchy Foods

23

Carousel

23

Checkout

23

Health and Hygiene

23

Household

23

Periodicals

23


Table 1: Results of a Simple Count of Warehouse Cities by Product Department

If we were to scrutinize the data, or perhaps to query the cube for the same information in a different manner, we would soon realize that the above results reflect every possible combination of Product Department and Warehouse City. This is because we have left the Count() function at default; not stipulating that we wished to leave empty cells (representing Product Department / Warehouse City combinations that witnessed no action), through the use of the EXCLUDEEMPTY keyword in our Count() function means that INCLUDEEMPTY flag is assumed by default.

To prove this, we merely add the EXCLUDEEMPTY keyword, flagging the function to ignore empty cells in the count, as follows:

   COUNT ( {[Warehouse].[City].MEMBERS}, EXCLUDEEMPTY)

Upon execution with the change above, the query would return a dataset similar to that depicted in Table 2.

Product Department

No. Locations

Alcoholic Beverages

20

Beverages

23

Dairy

21

Baked Goods

23

Baking Goods

23

Breakfast Foods

22

Canned Foods

23

Canned Products

18

Dairy

23

Deli

23

Eggs

22

Frozen Foods

23

Meat

17

Produce

23

Seafood

16

Snack Foods

23

Snacks

22

Starchy Foods

20

Carousel

16

Checkout

19

Health and Hygiene

23

Household

23

Periodicals

18


Table 2: Results of a Count of Warehouse Cities by Product Department, Empty Cells Not Counted

We will practice the use of the Count() function in the section that follows. Moreover, we will explore the use of the EXCLUDEEMPTY flag we have discussed, to activate the concept of its use in eliminating empty cells from consideration by the function. Finally, we will go a step further, and confirm our results by looking at the data from another perspective.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers