Procedure:
Demonstrate that Perspectives Do Not Prevent Selection of Dimensions that Lie
Outside the Perspective Design
We
continue our exploration of querying Perspectives by informing the
Operations representatives with which we are working of a significant
difference in behavior that occurs with regard to dimensions. Unlike the
results we noted when specifying excluded measures (that is, direct selection
of a measure excluded from the Perspective returns an empty results
dataset), we are not similarly restricted by the Perspective in querying
dimensions.
1.
Replace the comment
line in query MDX046-001 with the following:
--MDX046-002 MDX Query Targeting a Perspective of
-- the Adventure Works Cubes; Specify Excluded Dimension
2.
Select File -à Save MDX046-001.mdx As ...,
name the file MDX046-002.mdx, and place it in the same location as its
predecessor, to protect the former query.
3.
Replace the fifth row
(the row axis definition of the query), currently:
{[Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
with the following:
[Product].[Product Categories].[All Products] ON AXIS(1)
A
quick review of the Perspective tab in the Cube Designer makes it
clear that the Product dimension is excluded from the Finance
Perspective. The
result of our change, of course, is to intentionally select
a dimension that is not included within the Perspective. Once we have
accomplished this simple modification, the Query pane appears as
depicted in Illustration 21.
Illustration 21:
"Adjusted" Query in the Query Pane (Modifications Circled)
4.
Execute the query by
clicking the Execute button in the toolbar, as before.
The Results
pane is populated by Analysis Services, and the dataset partially
shown in Illustration 22 appears.
Illustration 22:
Results Dataset (Partial View) – Excluded Dimension Specified
The fact that
the Product dimension, which we know to be excluded from the Finance
Perspective, is returned per our row axis specification in the query above
makes it clear that we are able to query "around" the Perspective
to retrieve dimensions within the underlying cube. This
perhaps serves as an emphasis that, while Perspectives afford useful
"focuses" that are great ways to "narrow" visual displays
within the Cube Browser and perhaps some client applications, the use of
Perspectives to support any form of security / other access control is
not viable, as various cube objects can be queried directly from the cube with
which the Perspective is associated.
5.
Select File -à Save MDX046-002.mdx to ensure that that the file is
saved.
The
Operations authors express satisfaction with the results, and confirm their
understanding, so far, of the points we have illustrated surrounding the use of
Perspectives in MDX queries. They then pose an additional consideration,
which we will take up in the final procedural section that follows.
Procedure:
Demonstrate a Means of Selecting Measures Excluded from a Perspective
which is the Target of an MDX Query
Our
client colleagues next pose a question: "Since we can pull even excluded dimensions
into the query to return the associated data, regardless of the object
membership within the Perspective we name in the FROM clause, how
might we 'go around' the Perspective to retrieve measures that are not
included in the Perspective design?" We answer that, to
achieve this, we must add a calculation to embody the measure under
consideration.
Let's say that, as an example, the authors wish to see the Sales
Quota measure, a member of the Sales Orders Measures Group - which
we know to be excluded from the Finance Perspective - within a query
similar to the one we crafted in our first example.
We
could add the calculation via the Cube Designer, but for purposes
of our exercise, we will do so using the WITH clause within our MDX
query. We will begin with a new query.
1.
Select File --à New from the main menu.
2.
Select Query with
Current Connection from the cascading menu that appears next, as depicted
in Illustration 23.
Illustration 23:
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.
--MDX046-003 MDX Query Targeting a Perspective of
-- the Adventure Works Cubes; Specify Excluded Measure
WITH
MEMBER
[Measures].[Sales Order Count]
AS
[Measures].[Order Count]
SELECT
{[Measures].[Sales Order Count]} ON AXIS(0),
{ [Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
FROM
[Finance]
WHERE
([Date].[Fiscal].[Fiscal Year].[FY 2003])
The Query pane appears, with our input, as shown in
Illustration 24.
Illustration
24: Our Initial Query in the Query Pane ...
4.
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 depicted in Illustration 25 appears.
Illustration 25:
Results Dataset –Duplicate in Evidence (Circled)
In the
returned dataset, we see that the excluded measure, Sales Quota, is
returned via the calculation. (For the measure to be more
meaningful, of course, we might perform some realignment via the Dimension
Usage tab of the Cube Designer, as well as perhaps elsewhere).
5.
Select File -à Save MDXQuery2.mdx As ...,
name the file MDX046-003.mdx, and place it in the same location used to
store the earlier queries.
We have
demonstrated the means of retrieving an excluded member when targeting the Perspective
from which it is excluded in our MDX query.
6.
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 Perspectives within MDX queries.
7.
Select File -à Exit, when ready, to leave the Business
Intelligence Development Studio.
Summary ...
In this
article, we extended our examination of MDX to concentrate upon the use of Perspectives,
as well as considerations that arise when we leverage them within our
respective business environments. As a part of introducing the concepts
behind Perspectives, we prepared for our practice session by examining
the structure of existing Perspectives within the Adventure Works DW
Analysis Services Database.
After
introducing our subject matter, we examined the syntax with which we employ Perspectives
within MDX queries. We next undertook illustrative examples whereby we
put a Perspective to work, to gain some hands-on practice with the
concepts we had explored. 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.
» 