Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 14, 2006

Other MDX Entities: Perspectives - Page 3

By William Pearson

Syntax

Syntactically, anytime we write an MDX query targeting a Perspective, we need only name the Perspective within the FROM clause, instead of a Cube name – within the context of the query, Perspectives behave in a manner very similar to cubes.  The general syntax, then, is shown in the following string:

FROM [Perspective_Name]

Let's take a look at an illustration.   The following snippet employs one of the Perspectives we saw in the sample UDM above (called Finance), whose purpose is to provide a visually restricted focus to the financial reporting measures and the related dimensions within the Adventure Works cube.


SELECT
   
   {[Measures].[Amount]} ON AXIS(0),
   
   {[Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
   
FROM
    [Finance]
WHERE
    ([Date].[Fiscal].[Fiscal Year].[FY 2004])

This query, executed against the Finance Perspective, would produce a results dataset similar to that depicted in Illustration 9.


Illustration 9:  Results DataSet of a Basic Query Targeting a Perspective

In the example dataset, we see that the members of Account Level 02 of the Account dimension appear in the order in the row axis, with the corresponding Amounts appearing in the column.  In the present example, although we have used a Perspective as the target within the FROM clause of the query, we would have obtained identical results had we simple targeted the Adventure Works cube itself.

It is important to keep in mind that, while Perspectives behave as cubes within the context of being the made the target of a query, important differences issue from the fact that they are not physical cubes, but serve merely as "visual focuses."   As a result of their nature, we are not prevented, within a given MDX query, from selecting a dimension or dimensions for retrieval that are not included within a targeted Perspective, provided that it / they are accessible through the underlying cube.  The treatment of measures, however, is more along the lines of what the otherwise unenlightened might expect:  a measure that is not included within the Perspective's design will not be retrieved when specified in a query.  We can circumvent this behavior easily enough, via the creation and use of a calculation to bring in the "excluded" measure, as we shall see within our practice session, but any attempt to directly retrieve a measure that is excluded from the Perspective will result in the return of an empty results dataset.

Practice

Preparation:  Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use a Perspective within a couple of queries that illustrate the points we have discussed.  We will do so in simple scenarios wherein we place the concepts within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent is to demonstrate the handling and operation of Perspectives in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 10.


Illustration 10:  Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 11.


Illustration 11:  Exposing the Analysis Services Databases in the Object Browser ...

NOTE:  The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 11 above.  For purposes of this practice session, the Adventure Works DW database must be present.  If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 12.


Illustration 12:  Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens.  We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 13.


Illustration 13:  The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 14.


Illustration 14:  Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 15.


Illustration 15:  Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among my other series' at Database Journal, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions.  Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient.  We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct.  For more information on the use of the Query Editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series' explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM