Practice
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
WITH
Member [Store Type].[Types Count]
AS
'Count( {[Store Type].[Store Type].MEMBERS })'
SELECT
{ [Store Type].[Types Count] } ON COLUMNS,
{ [Store].[Store Country].MEMBERS } ON ROWS
FROM
[Warehouse]
WHERE
([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.
» 
Comment and Contribute
