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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 7, 2006

Mastering Enterprise BI: Introduction to Perspectives

By William Pearson

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine

  • Microsoft SQL Server 2005 Analysis Services

  • Microsoft SQL Server 2005 Integration Services

  • Business Intelligence Development Studio

  • Microsoft SQL Server 2005 sample databases

  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:

    • the Administrators local group on the Analysis Services computer

    • the Server role in the instance of Analysis Services

  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, as appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for many years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated business intelligence solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos (and other) enterprise business intelligence to the Microsoft solution. In addition to converting formerly dominant business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel – or outstrip - those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the integrated Microsoft solution will commoditize business intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.


Analysis Services 2005 extends the concept of a cube, and the more "geometrical" basis upon which it once rested, with the concept of the Universal Dimension Model ("UDM"). A UDM provides a bridge / abstract layer between users and one or more physical data sources, and combines OLAP and relational realms. Queries are, in turn, executed against the UDM through various client applications, providing the primary advantage of insulating the users from the multitudes of structural details that might exist within heterogeneous backend data sources. In addition to supplying a more intuitive data model with which to work, and enhanced performance for summary type queries, the UDM can also provide myriad additional benefits.

While we will delve into many facets of the UDM in other articles, the salient characteristic that brings it into our current field of consideration is its potential size and complexity. The UDM allows for significant enrichment of the more basic user models of before, permitting business rules to be captured within it to support richer analysis, among much other sophistication. Real-world models of enormous scope might realistically be defined, containing potentially scores of measures and dimensions, with each dimension including myriad attributes, as an illustration. A single UDM can represent the contents of a complete data warehouse, with multiple Measure Groups in a cube representing multiple fact tables, and multiple dimensions based on multiple dimension tables.

Such models can be very complex and powerful, but daunting to users who may only need to interact with a small part of a cube in order to satisfy their business intelligence and reporting requirements. For this reason, Analysis Services 2005 introduces "views" of the model, called Perspectives. Perspectives allow for the presentation of relevant subsets of the model to given groups of users, narrowing the model's focus to the measures, dimensions, attributes and so forth needed to support the groups in the accomplishment of their specific missions.

In Analysis Services 2005, we can use Perspectives to define these subsets of the model to provide focused, business-specific or application-specific viewpoints. The Perspective controls the visibility of objects, among which the following can be displayed or hidden:

  • Dimensions
  • Attributes
  • Hierarchies
  • Measure Groups
  • Measures
  • Key Performance Indicators (KPIs)
  • Calculations (Calculated Members, Named Sets, and Script Commands)
  • Actions

Let's imagine a simple illustration within the Adventure Works cube, which makes its home in the Adventure Works DW sample Analysis Services 2005 database. The sample ships with twenty-one cube dimensions and eleven Measure Groups, representing sales, sales forecasting and financial data. While a client application might directly access the whole cube, such an all-encompassing viewpoint might overwhelm a consumer whose business needs are limited, say, to basic sales forecasting information. Instead of subjecting the consumer to such an overload scenario, not to mention exposing data, perhaps, for which the consumer has no "need to know," we can implement a Sales Targets Perspective to narrow this consumer's view to the objects relevant to doing his job, providing support for forecasting sales.

It is important to remember, as we create and assign Perspectives within our local environments, that their purpose is to afford easier navigation, querying and other interaction with the cube. Perspectives do not physically restrict access to cube objects, nor do they prevent direct referencing or retrieval of the objects through MDX, XML, or DMX statements. Because these viewpoints comprise read-only views of the cubes with which they are associated, users cannot change (rename, etc.), or modify the behavior or features of, cube objects through the use of Perspectives.

Perspectives represent subsets of our cubes, as we have stated, consisting of "views" of dimensions and measures that are relevant to the audiences for which they are created. In this article, we will examine Perspectives, and get hands-on exposure to the process of adding them to a basic cube we construct within the new Business Intelligence Development Studio. We will overview the creation of Perspectives, and discuss ways in which they can enable us to offer flexibility to the end users of our cubes and solutions / applications. As a part of our examination of the steps, we will:

  • Prepare Analysis Services, and our environment, by creating an Analysis Services Project to house our development steps, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session;
  • Create a Data Source containing the information Analysis Services needs to connect to a database;
  • Create a Data Source View containing schema information;
  • Build a cube based upon our Data Source and Data Source View, containing data from our sample relational tables;
  • Add examples of Measure Groups as part of cube design;
  • Create a couple of example Perspectives for a hypothetical pair of intended audiences;
  • Deploy our Analysis Services Solution;
  • Browse the Cube, focusing on the new Perspectives and associated details.

Working with Perspectives

Overview and Discussion

We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in the past, the pre-assembled Analysis Services Project that makes its home within the Studio assists us in organizing and managing the numerous objects that we will need to support our efforts to create and deploy our Analysis Services database.

We will leverage the Cube Wizard in this article to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. The Cube Wizard not only helps us simplify the design and creation of our cubes, as it did within Analysis Services 2000: the Analysis Services 2005 Cube Wizard is more powerful, leveraging IntelliCube technology to examine and classify many of the attributes of our data. Analysis Services can determine, for example, prospective fact tables, dimensions, hierarchies, levels and other structural members of our cubes from a given database schema at which it is pointed. Regardless of whether we make a habit of using the wizard in our cube development efforts, it certainly provides a way to rapidly generate a cube, if only to eliminate part of the repetitive work involved to create a "starting point" model, which we can then "prune and groom" to more precisely meet the business requirements of our employers and customers.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

MS SQL Archives

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