As we might have expected, this simple approach hardly
achieves our ultimate objectives: it returns the top three Warehouses from
the perspective of Warehouse Profit for 1998 - but the top three
from the entire FoodMart organization. We will next advance beyond our
previous basic queries by employing another MDX function, Descendants(),
to supply the <<Set>> argument for the TopCount() function, but
have a bit more to do to reach our goal of presenting the top performer Warehouses
by U.S. Warehouse State.
Next, we need to add logic to generate the same "top
three" data, but for an ancestor of Warehouse (a level in
the Warehouse hierarchy), the State it inhabits. To do so, we
will introduce more MDX functions, as we see in the next step.
9.
Within the
query we have saved as MDX032-1-1, replace the top comment line of the
query with the following:
-- MDX032-1-2, TopCount(), combined with Ancestor() and .Name
-- in a calculated member, to provide Warehouse State with each of "Top 3
-- Most Profitable Warehouses in FoodMart Organization in 1998"
10.
Save the query
as MDX032-1-2, to keep MDX032-1-1 intact as a working sample.
11.
Add the
following lines to the query, between the top comment line we just replaced,
and the SELECT keyword that begins the query:
WITH
MEMBER
[Measures].[State]
AS
'ANCESTOR([Warehouse].CURRENTMEMBER,
[Warehouse].[State Province]).NAME'
This
will create the calculated member State, which will allow us to present
the States alongside their respective member Warehouses.
NOTE: For an
introduction to the Ancestor() function, see my article MDX Member Functions: The "Family" Functions, a member of the MDX Essentials series at Database
Journal.
12.
Modify the ON
COLUMNS line of the query, which already contains the Warehouse Profit
measure, to contain the new State calculated member we defined above, as
follows:
{[Measures].[State], [Measures].[Warehouse Profit]} ON COLUMNS,
13.
Leave the
remainder of the query in its original state.
The
Query pane
appears as depicted in Illustration 2, with our modifications marked.
Illustration 2: The Query with Added Calculated Member
14.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset shown in Illustration 3 appears.
Illustration 3: Results Dataset - With Our Modifications
We note that the State to which each of the "top
three" warehouses (still the top three for the entire FoodMart
organization) belongs appears in the results. Our calculated member has taken
us a step closer to realization of the information consumers' requirements.
15.
Re-save the file as MDX032-1-2.
16.
Leave the
query open for the next step.
At this point, all that remains is to generate the "top
three" data for each of the Warehouse States, limiting the States
to the three American States within which FoodMart operates Warehouses.
The word "generate" is particularly apt here: we will use the Generate()
function in our efforts to return the "top three" from each of the
three U.S. States.
17.
Within the
query we have saved as MDX032-1-2, replace the top comment line of the
query with the following:
-- MDX032-1-3, Addition of Generate() to retrieve
-- "3 Most Profitable Warehouses in Each U. S. State in 1998"
18.
Save the query
as MDX032-1-3, to keep MDX032-1-2 intact as a working sample.
19.
Substitute the
following for the entire existing ON ROWS line within the SELECT statement
of the query:
{GENERATE([Warehouse].[USA].CHILDREN,
TOPCOUNT(
DESCENDANTS(
[Warehouse].CURRENTMEMBER, [Warehouse].[Warehouse Name]
),3,[Measures].[Warehouse Profit] ) )} ON ROWS
(In
actuality, we are only adding the Generate() statement to the "front
end" of the ON ROWS specification, as it already existed from
earlier steps, with a closing right parenthesis - ")" - just before
the ON ROWS keyword).
The Generate()
statement builds a set based upon the top performer Warehouses,
as retrieved by the TopCount() function (with Warehouse Profit as
the <<Numeric Expression>> argument, and the Descendants()
function, once again, specifying the <<Set>> argument as the individual Warehouses),
limited to U.S. States (the "children" of the USA level of the Warehouse
dimensional hierarchy.
NOTE: For information
surrounding the Generate() function, see my MDX Essentials article
Set and String Functions: The GENERATE() Function, (where we perform
a combination of Generate() and TopCount(), as well). For an
introduction to the .Children function, see MDX Member Functions: The "Family" Functions, within
the same series.
20.
Leave the
remainder of the query in its original state.
The
Query pane
appears as depicted in Illustration 4, with our addition marked.
Illustration 4: The Query with Substituted ON ROWS Line
21.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset shown in Illustration 5 appears.
Illustration 5: Results Dataset - After Final Modifications
Finally, we see the
presentation as requested by the information consumers: 1998 Warehouse
Profit for the three Warehouses with the highest Warehouse Profit
for each of the U. S. States within which FoodMart conducts Warehouse
operations. (We note that the State of Oregon shows only two Warehouses,
but can easily verify that this is because only two Warehouse locations
exist within the State, by expanding the Warehouse dimension at
the OR level in the tree pane below the Cube selector in the MDX
Sample Application, as depicted
in Illustration 6.
Illustration 6: Verifying that Only Two Warehouses Exist in
Oregon ...
22.
Re-save the file as MDX032-1-3.
» 