The
report authors / developers have a new appreciation for the fact that, given
the current core query, the capability to perform ad hoc hierarchy generation, based
upon the individual selected at runtime, becomes a matter of parameterizing a
key component of the rows-axis specification of the query, the unique name for
the employee. Because we have demonstrated to the developers, that
parameterization of this sort becomes easily attainable within Reporting
Services, assuming that sufficiently sophisticated queries are put in place
to support it (within either the Reporting Services or Analysis
Services layers), the core query is deemed valuable to the team.
While
we will not get into the parameterization aspects of query design to make this
happen, we will concentrate upon one undesirable characteristic of the query as
it is currently written; the developers tell us that all seems to be
functioning properly, except for duplication within the results dataset that
the query produces. We will use this scenario as a vehicle to once again
illustrate the usefulness of the Distinct() function. Moreover, showing
the unintended duplication in the initial scenario, and then applying Distinct()
to the query to eliminate the duplicates, will provide a hands-on, "before
and after" look at how Distinct() behaves in another example.
To
outline the requirement further, the developers present a draft of the intended
objective within an MS Excel spreadsheet (with an additional column showing Employee
level, for illustration purposes only). The draft is intended to serve as our
"confirmation of understanding draft" of the desired dataset, and
appears in MS Excel as depicted in Illustration 13.
Illustration 13: "Confirmation Draft" of
Intended Hierarchical Results Dataset
Having obtained consensus on the proposed target dataset, we
are ready to set about constructing the query.
14.
Select File
--> New from the main menu.
15.
Select Query
with Current Connection from the cascading menu that appears next, as shown
in Illustration 14.
Illustration 14: Create a New Query with the Current
Connection ...
A new
tab, with a connection to the Adventure Works cube (we can see it listed
in the selector of the Metadata pane, once again) appears in the Query
pane.
16.
Type (or cut
and paste) the following query into the Query pane:
-- MDX044-002-1 Duplication Appears within the Use of Combined Functions
SELECT
{[Measures].[Reseller Order Count]} ON AXIS(0),
HIERARCHIZE(
{DESCENDANTS([Employee].[Employees].[Stephen Y. Jiang]),
ASCENDANTS([Employee].[Employees].[Stephen Y. Jiang])})
DIMENSION PROPERTIES [Employee Department].[Department Name]
ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Date].[Calendar Year].[CY 2004])
The Query pane appears,
with our input, as depicted in Illustration 15.
Illustration 15: Our Initial Query in the Query Pane ...
17.
Execute the
query by clicking the Execute button in the toolbar.
The Results pane is, once again, populated by
Analysis Services. This time, the dataset shown in Illustration
16 appears.
Illustration 16: Results Dataset –Duplicate in Evidence
(Circled)
In the returned dataset, we see that, except for the
duplicate seen in the above illustration, the query appears to deliver the
intended results.
18.
Select File
-> Save MDXQuery2.mdx As ..., name the file MDX044-002-1.mdx,
and place it in the same location used to store the earlier queries.
19.
Leave the
query open for the next step.
As we
can see, the initial query presents a scenario that will serve as a basis from
which to employ Distinct() to eliminate duplication. The Distinct()
function can be easily added to align the dataset completely with the "intended
results" draft that the developers have provided, as we shall see next.
20.
Replace the
comment line in query MDX044-002-1 with the following:
-- MDX044-001-2 Using DISTINCT() to Eliminate Duplication
21.
Select File
-> Save MDX044-002-1.mdx As..., name
the file MDX044-002-2.mdx, and save it with the other queries we have
constructed.
22.
Click to the
immediate right of HIERARCHIZE( - on the fourth row from the top in the
existing query - to place the cursor there.
23.
Press the Enter
key on the PC twice, to create a space between the row and the row
underneath it.
24.
Type the
following syntax into the new row:
DISTINCT(
25.
On what is now
the seventh row, place the cursor to the immediate right of the following:
ASCENDANTS([Employee].[Employees].[Stephen Y. Jiang])})
26.
Add another
right parenthesis ( ")" ) to the right of the right-most
parenthesis.
The
effect, once again, is to enclose the set involved within the Distinct() function.
Once we have accomplished this simple modification, the Query pane
appears as depicted in Illustration 17.
Illustration 17: "Adjusted" Query in the Query
Pane (Modifications Circled)
27.
Execute the
query by clicking the Execute button in the toolbar, as before.
The Results pane is populated by Analysis
Services, and the dataset shown in Illustration 18 appears.
Illustration 18: Results Dataset – Distinct() in Action
Once again, we witness that Distinct() has eliminated
the duplicate tuple, leaving only a single occurrence of manager Stephen Y.
Jiang, for whom two occurrences appeared in the earlier results dataset.
Further, while it might be impossible to ascertain precisely which of the
duplicate rows remains, the fact is that the function has removed "all
except the first instance" of the once duplicated tuple, as we discussed
earlier, and then saw in our first practice example.
28.
Select File ->
Save MDX044-002-2.mdx to ensure that that the file is saved.
29.
Select File ->
Exit to leave the SQL Server Management Studio, when ready.
The client
representatives inform us that their immediate goals have been met, and that
the examples we have shared have illustrated the principles of operation behind
Distinct().
Summary...
In this lesson, we continued our examination of MDX functions
to concentrate upon the rudimentary, but useful, Distinct() function. We
discussed the purpose of Distinct(), to return a set without duplicates
from a set we specify within the function, as well as the manner in which the
function manages to return a distinct set.
After introducing the Distinct()
function, we examined the syntax with which we employ it. We next undertook
illustrative examples whereby we put Distinct() to work, to gain some
hands-on practice in its use. Throughout our practice session, we briefly
discussed the results datasets we obtained from each of the queries we
constructed or modified.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 
Comment and Contribute
