Let’s
begin with an “intuitive” approach – as a means of crafting a core query, as
well as generating a result that will form a basis for contrast between
a listing of “all Sales employees with an associated Reseller Sales
Amount value” (including the sales managers I mentioned earlier) and our
ultimate focus of sibling members of the Sales organization with an
associated Reseller Sales Amount value.”
6.
Type (or cut
and paste) the following query into the Query pane:
-- MDX051-002-1 Initial Attempt at a Sales Employee Listing
SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),
NONEMPTY( {[Employee].[Employee Department].MEMBERS})ON AXIS(1)
FROM
[Adventure Works]
WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
The Query pane appears,
with our input, as shown in Illustration 7.
Illustration 7: Our Initial Query in the Query Pane ...
7.
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 partially depicted in Illustration
8 appears.
Illustration 8: Results Dataset – Unfiltered Employee
Members
In the returned dataset, we see the unfiltered list of Employees
with an associated Reseller Sales Amount value. As we have discussed,
these members happen to be sales personnel, but the presented list contains
non-sibling Employees. We can verify
this by inspecting the dimensional structure in the Analysis Services Cube Browser,
a view of which appears in Illustration 9.
Illustration 9: The Employee Dimension Hierarchy –
Relevant Members
We can see that, while fourteen employees exist at the
bottom level (Level 5), several more members exist when we count higher
levels (including the “All” level) within the dimension. Our ultimate
objective is to deliver the sibling Sales members – in this case, the
fourteen individuals appearing within Level 5. We ask for the name of
one of the salespersons within the level, so as to be able to specify it as the
secondary member expression within the IsSibling() function (the
one against which the Current Members will be compared to determine
“siblinghood.”)
8.
Select File
-> Save MDXQuery2.mdx As ..., name the file MDX051-002-1,
and place it in a meaningful location.
Our
next step will be to filter the non-sibling members from the Employees
listed in the returned dataset. We will do this within the query first, before
finalizing the solution by placing the working logic into a Named Set we
create for that purpose in the last step. We are given the name Jae B. Pak,
which we are able to locate within the hierarchy we examined earlier in a view
from the Cube Browser. We can thus construct the qualified name of our
“basis” member, at the appropriate point within the modifications we will next
perform upon our initial query.
9.
Replace the
top line of the query (commented out) with the following:
-- MDX051-002-2 Adding the Filter() / IsSibling() Combination
10.
Select File
-> Save MDX051-002-1.mdx As ..., name the file MDX051-002-2,
and place it in a meaningful location.
11.
Place the
cursor to the immediate right of the left curly brace - “ { “ –
following the NONEMPTY keyword (currently on the fourth line of the
query).
12.
Press the ENTER
key four times to “push down” the rest of the line, and to add space
between the remaining “NONEMPTY(“ and the rest of the line.
13.
Between what
is now the fourth (containing “NONEMPTY(“ ) line and the fifth
(containing “{[Employee].[Employee
Department].MEMBERS})ON AXIS(1)”) line of the query, type in the following
syntax:
FILTER(
14.
Place the
cursor to the immediate right of the MEMBERS keyword (currently on the
sixth line of the query), between “MEMBERS” and the right curly brace -
“ } “ - that is at its right.
15.
Insert a comma
( “,” ) to the immediate right of the MEMBERS keyword.
16.
Press the ENTER
key four times, once again to “push down” the rest of the line, and to add
space between the remaining “... MEMBERS,” and the rest of the line.
17.
Between what
is now the sixth (containing “[Employee].[Employee Department].MEMBERS,”) line and the seventh
(containing “})ON AXIS(1)”) line of the query, type in the following
syntax:
ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
18.
Press the ENTER
key two times, once again, to add space between the new line and the line
immediately underneath it.
19.
Between what
is now the seventh (containing “ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,” line and the eighth (containing “})ON AXIS(1)”)
line of the query, type in the following syntax:
[Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))
The complete query is as
follows, if cutting and pasting is the preference:
-- MDX051-002-2 Adding the Filter() / IsSibling() Combination
SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),
NONEMPTY( {
FILTER(
[Employee].[Employee Department].MEMBERS,
ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
[Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))
})ON AXIS(1)
FROM
[Adventure Works]
WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
The Query pane appears,
with our input, as depicted in Illustration 10.
Illustration 10: Our Modified Query in the Query Pane ...
20.
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
11 appears.
Illustration 11: Results Dataset – Sibling Employee
Members
In the returned dataset, we see the now-filtered list of Employees.
We can see that the Employees that appear in the returned dataset represent
members that are siblings of member Jae B. Pak (and include Jae
B. Pak), as shown in Illustration 8 above, with an associated Reseller
Sales Amount value.
21.
Select File
-> Save MDX051-002-2 to save the file.
» 
Comment and Contribute
