Mastering Enterprise BI: Introduction to Perspectives

Monday Aug 7th 2006 by William Pearson
Share:

Support information consumers with easier navigation and more focused analysis choices. In this article, BI Architect Bill Pearson leads hands-on exposure to Perspectives, another new Analysis Services 2005 feature.

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.

Introduction

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.

Hands-On Procedure

We will get started by creating a new project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

Preparation

Create a New Analysis Services Project

We begin our preparation within Business Intelligence Development Studio, where we will create a new Microsoft SQL Server 2005 Analysis Services (SSAS) Project. As we have noted in other articles, those of us who have worked within earlier versions of Reporting Services, or within the Visual Studio development environment under other circumstances, will recognize the general look and feel of the Studio. Under this new style of development for Analysis Services, to which much of the documentation refers as "project mode," we create an Analysis Services Project, which houses a set of Analysis Services objects. The object set typically includes Data Sources, Data Source Views, Dimensions, Cubes, and other components.

As we have noted in other articles, using an Analysis Services Project template affords us many benefits, such as helping us to organize and manage the items that are required to build, debug, and deploy an Analysis Services database. The development of an Analysis Services cube, a preparatory step to support our primary objectives within this session, as well as one of the most common tasks in Analysis Services, can be accelerated though the use of the Analysis Services 2005 Cube Wizard, which both guides and simplifies the process (as is the way of wizards).

In an effort to remain focused on the essentials of working with Perspectives, we will not go into the myriad processes and structures that surround and underlie the UDM / cube in general. As we have noted previously, however, it is useful to make a mental note that all the objects that we create and store within an Analysis Services project are defined as files (using an XML representation) that eventually are deployed to an Analysis Services database. We will see how this transpires when we deploy the project within which we work in our practice session.

1.  Click the Start button.

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

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File -> New on the Visual Studio main menu.

6.  Select Project from the cascading menu, as depicted in Illustration 3.


Illustration 3: Beginning a New Project ...

The New Project dialog appears.

7.  Select Business Intelligence Projects in the Project types pane of the dialog.

8.  Select Analysis Services Project in the Templates pane, as shown in Illustration 4.


Illustration 4: Select Analysis Services Project

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as upon whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.

9.  Change the project Name (currently displaying a default) to the following:

ANSYS050 Perspectives

The Solution Name changes to match the project Name by default.

10.  Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).

The relevant section of the New Project dialog appears similar to that depicted in Illustration 5.


Illustration 5: The New Project Dialog (Relevant Section), with our Input

11.  Click OK to accept our input and to create the new Analysis Services Project.

The New Project dialog closes. Visual Studio creates the project, which appears underneath solution ANSYS050 Perspectives, within the Solution Explorer as shown in Illustration 6.


Illustration 6: The New Analysis Services Project Appears

Having created a new Analysis Services Project, we are ready to define a Data Source and Data Source View.

Define a Data Source

Defining a Data Source, as we have discovered in other articles of our series, is typically the first step we take with a new Analysis Services Project. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual sources of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2005, the AdventureWorksDW database.

Let's create a Data Source within our new ANSYS050 Perspectives project by taking the following steps:

1.  Right-click Data Sources folder within the Solution Explorer.

2.  Select New Data Source ... from the context menu that appears, as depicted in Illustration 7.


Illustration 7: Initial Step in Creating a New Data Source

The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page, as shown in Illustration 8.


Illustration 8: Welcome to the Data Source Wizard ...

3.  Click Next.

The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. (It also allows us to base a Data Source upon a connection that has been previously created.)

4.  Click New on the Select how to define the connection page.

The Connection Manager dialog appears.

5.  Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.

6.  Type / select the appropriate server name / server name with instance into the Server name box.

We can substitute "localhost" or "localhost\<instance name>" here, if it is appropriate to our environment. (My server name with instance is MOTHER1\MSSQL2K5, as may be seen in several of the illustrations in this and other articles.)

7.  Select / complete the appropriate authentication choice for your environment in the section labeled Log on to the server. (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article.)

8.  Select AdventureWorksDW in the Select or enter a database name selector box, within the Connect to a database section of the dialog.

The Connection Manager dialog appears similar to that depicted in Illustration 9.


Illustration 9: Connection Manager Dialog in My Lab Environment

9.  Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.

We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears as shown in Illustration 10.


Illustration 10: Confirmation Message Indicating Successful Connection Test

10.  Click OK to dismiss the message box and return to the Connection Manager dialog.

11.  Click OK on the Connection Manager dialog to accept our settings and create the data connection.

We return to the Select how to define the connection page of the Data Source Wizard, which appears similar to that depicted in Illustration 11.


Illustration 11: Select How to Define the Connection Page with New Data Connection

12.  Ensuring that the new Data Connection is selected, click Next.

We arrive at the Impersonation Information page, where we define connection credentials for the data source.

13.  Click the radio button to the immediate left of Use the service account (or supply other appropriate local credentials), as shown in Illustration 12.


Illustration 12: Select the Use the Service Account Option

14.  Click Next.

The Completing the Wizard page appears, as depicted in Illustration 13.


Illustration 13: Completing the Wizard ...

We will leave the assigned Data Source name in place for purposes of our practice session.

15.  Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer as shown in Illustration 14.


Illustration 14: The New Data Source Appears in the Solution Explorer

Having established the basic prerequisites, we are ready, at this stage, to create a Data Source View.

Define a Data Source View

Having defined the Data Source within our Analysis Services Project, our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in most Analysis Services Projects. The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.

NOTE: For more information on Data Source Views, see my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views at Database Journal.

To define a Data Source View, we will take the following steps:

1.  Right-click Data Source Views folder within the Solution Explorer.

2.  Select New Data Source View ...from the context menu that appears, as depicted in Illustration 15.


Illustration 15: Initial Step in Creating a New Data Source View

The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard page, as shown in Illustration 16.


Illustration 16: The Initial Page of the Data Source View Wizard

3.  Click Next.

We arrive at the Select a Data Source page, where we see our Adventure Works DW Data Source in the Relational data sources list box on the left side of the page, as depicted in Illustration 17.


Illustration 17: Adventure Works DW Appears in the Relational Data Sources List

Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise.

4.  Click Next.

Analysis Services scans the Adventure Works DW schema, and then we arrive at the Select Tables and Views page. Here we see the various tables of the Adventure Works DW data source appear in the Available objects list box on the left of the page. We will be selecting two fact tables, to allow us to house both sales and "budget" data within our cube.

5.  Click FactInternetSales in the Available objects list to select it.

6.  Click the button marked ">" to move FactInternetSales to the Included objects list on the right half of the page.

7.  Click FactSalesQuota in the Available objects list to select it.

8.  Click the button marked ">" to move FactSalesQuota to the Included objects list on the right half of the page.

The added tables appear within the Included objects list of Select Tables and Views page as shown in Illustration 18.


Illustration 18: Table Selections for Inclusion in the Data Source View

9.  Click the newly added FactInternetSales table in the Included objects list to select it.

10.  Click the Add Related Tables button, located underneath the Included objects list.

11.  Click the newly added FactSalesQuota table in the Included objects list to select it.

12.  Click the Add Related Tables button, located underneath the Included objects list.

Several related tables appear within the Included objects list, as depicted in Illustration 19.


Illustration 19: Our Tables Selection for the Data Source View

13.  Click Next.

The Completing the Wizard page appears, as shown in Illustration 20.


Illustration 20: The Final Page of the Data Source View Wizard

As indicated in the Completing the Wizard page, our selection includes the following tables:

  • FactInternetSales
  • FactSalesQuota
  • DimProduct
  • DimCurrency
  • DimTime
  • FactInternetSalesReason
  • DimSalesTerritory
  • DimPromotion
  • DimCustomer
  • DimEmployee

14.  Click Finish to create the new Data Source View, and to dismiss the Data Source View Wizard.

Our new Data Source View, Adventure Works DW, appears in the Data Source Views folder within the Solution Explorer window, as depicted in Illustration 21.


Illustration 21: The New Data Source View in the Solution Explorer

The Data Source View is also presented within the Data Source View Designer, which opens simultaneously with the Data Source View. The contents of the Designer appear within Business Intelligence Development Studio, as shown in Illustration 22.


Illustration 22: The New Data Source View in the Data Source View Designer

One of several designers within the Studio, the Data Source View Designer for our new Data Source View contains numerous elements that aid us in our organization and design efforts. We can modify the composition of the tables that comprise our view, as well as performing other activities, here.

We will next create a cube based upon the Data Source and Data Source View we have assembled. As a part of this process, we will also set up, and at least partially align, Measure Groups.

Create a Cube with Basic Objects

Create a Cube

We will create a basic cube, with measures we will add as a part of cube creation, within which we will create the Perspectives which form the focus of our practice session. We will concentrate upon other areas within the overall design and creation process in other articles of the series – our primary objective here is to gain some familiarity with Perspectives – not to concern ourselves with the myriad other options and topics contained within general cube design and development.

1.  Right-click the Cubes folder within the Solution Explorer.

2.  Select New Cube ... from the context menu that appears, as depicted in Illustration 23.


Illustration 23: Select New Cube to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as shown in Illustration 24.


Illustration 24: The Welcome to the Cube Wizard Dialog Appears ...

3.  Click Next.

4.  Ensure that Build the cube using a data source checkbox is selected on the Select Build Method dialog that appears next, along with the Auto build checkbox, and with Create attributes and hierarchies selected in the drop-down selector underneath the Auto build setting.

These default settings are depicted in Illustration 25.


Illustration 25: Ensuring the Default Settings on the Select Build Method Dialog

5.  Click Next.

6.  Ascertain that the Data Source View we have created, Adventure Works DW, is selected within the Available data source views list on the left side of the Select Data Source View dialog, which appears next. (The tables contained within the Data Source View are displayed on the right side of the dialog).

The Select Data Source View dialog appears, as shown in Illustration 26.


Illustration 26: Select AdventureWorks DW as the Data Source View

7.  Click Next.

The Cube Wizard begins scanning the relational schema, identifying fact and dimensional tables, as indicated on the Detecting Fact and Dimension Tables page that appears next. We quickly receive a message indicating that the Wizard has completed its analysis of the relationships between tables in the Data Source View, and that it proposes its suggestions on the following page, as depicted in Illustration 27.


Illustration 27: The Detecting Fact and Dimension Tables Page Indicates Completion ...

8.  Click Next.

The Identify Fact and Dimension Tables dialog next appears. Let's select a Time dimension table, and narrow the table selection somewhat, for the purposes of our rudimentary cube.

9.  Select the DimTime table in the drop-down Time dimension table selector.

10.  Uncheck the following checkbox:

Dimension Column: DimCurrency

NOTE: We can also make table selections graphically via the Diagram tab of the Identify Fact and Dimension Tables dialog.

The Identify Fact and Dimension Tables dialog appears, as shown in Illustration 28.


Illustration 28: The Identify Fact and Dimension Tables Dialog, with Our Modifications

11.  Click Next.

The Select Time Periods dialog appears.

12.  Leaving any unspecified columns blank, select the Time Table Columns values shown in Table 1 for each of the listed Time Property Names.

Time Property Name

Time Table Columns

Year

CalendarYear

Quarter

CalendarQuarter

Month

EnglishMonthName

Date

FullDateAlternateKey


Table 1: Selections within Respective Time Table Columns in Select Time Periods Dialog

The Select Time Periods dialog appears, with our selections, as depicted in Illustration 29.


Illustration 29: The Select Time Periods Dialog, with Our Selections

13.  Click Next.

The Select Measures dialog appears next.

14.  Uncheck the following checkboxes:

  • Revision Number
  • Discount Amount
  • Product Standard Cost
  • Tax Amt
  • Freight

The Select Measures dialog appears, as partially shown in Illustration 30.


Illustration 30: The Select Measures Dialog (Partial View), with Adjusted Selections

15.  Click Next.

The Detecting Hierarchies page appears next, as hierarchy detection begins. We receive rapid indication that dimension tables have been scanned and hierarchies detected, as depicted in Illustration 31.



Illustration 31: The Detecting Hierarchies Page, with Indication of Relationships Detected

16.  Click Next.

We move to the Review New Dimensions page, which appears as shown in Illustration 32.


Illustration 32: The Review New Dimensions Page

17.  Click Next.

The Completing the Wizard page appears next.

18.  Change the default Cube name to the following:

ANSYS050 – Perspective 

The Completing the Wizard page appears as depicted in Illustration 33.


Illustration 33: The Completing the Wizard Page

19.  Click Finish.

The Wizard concludes, and the Cube Designer opens, as shown in Illustration 34.


Illustration 34: The Cube Designer Opens ...

Perform Basic Modifications to Measure Groups

Because our cube incorporates three fact tables, FactInternetSales, FactSalesQuota, and FactInternetSalesReason, which we included within the underlying Data Source View, the Cube Wizard has generated a default Measure Group for each originating fact table. (The Measure Groups are named after the tables they represent, by default.) We can verify this fact by examining the tree within the Measures pane that appears in the upper left corner of the Cube Structure tab, which itself appeared by default within the Designer when the Cube Wizard concluded its work.

20.  In the Measures pane, expand the three Measure Groups, Fact Internet Sales, Fact Sales Quota and Fact Internet Sales Reason.

The expanded Measure Groups, with member measures exposed, appear as depicted in Illustration 35.


Illustration 35: The Measures within their Respective Groups ...

While we can alter the Measure Groups to suit the nature of the measures, and would likely normally do so to manage their respective granularities, and so forth, we will leave these and other cube objects largely as they have been created by default, to allow us to focus upon the central subject matter of this session. We will make just a few alterations to flesh out the "views" that are relevant to the Perspectives which we will define within the Procedure section that follows.

NOTE: For more information on the creation and management of Measure Groups, see Mastering Enterprise BI: Working with Measure Groups, a member of my Introduction to MSSQL Server Analysis Services series at Database Journal.

Perform Basic Modifications to Measure Groups

We will make our minimal modifications to the Measures Groups within the Cube Designer, before making alterations to suit the nature of the measures. The basis of our modifications, typically enough, is that the granularities of the Measure Groups are different. As an illustration, Sales Amount, together with the other measures from the Fact Internet Sales table, is related to the actual date (the "TimeKey" field in the Time dimension) of the transaction, while the Sales Amount Quota measure, from the Fact Sales Quota table, is a "budget" value, relating to the year level of the Time dimension. To separate conceptual "single apples from baskets of apples," we need to ensure that our Measure Groups are dimensionally aligned to reflect their true nature.

1.  Click the Dimension Usage tab atop the Cube Designer, as shown in Illustration 36.


Illustration 36: Click the Dimension Usage Tab ...

The Dimension Usage page appears.

2.  Click the box currently containing "TimeKey," at the intersection of the Fact Sales Quota (Measure Group) column and the Dim Time (Dimension) row.

3.  Click the ellipses ("...") button that appears to the immediate right of the selected box, as depicted in Illustration 37.


Illustration 37: Accessing a Dimension Usage Setting ...

The Define Relationship page appears.

4.  In the selector box labeled Select relationship type, ensure that Regular remains selected.

5.  In the selector box labeled Granularity attribute, select CalendarYear.

6.  Click the selector box that appears at the intersection of the row labeled CalendarYear (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationships page.

7.  Select CalendarYear in the selector, as shown in Illustration 38.


Illustration 38: Select CalendarYear in the Relationships Table ...

The Define Relationship page appears, with our new settings, as depicted in Illustration 39.


Illustration 39: The Define Relationship Page with Our Settings

8.  Click OK to accept settings and close the Define Relationship page.

9.  Within the Dimension Usage settings, click the ellipses ("...") button to the immediate right of the box (currently unoccupied) at the intersection of the Fact Internet Sales (Measure Group) column and the Dim Time (Dimension) row, as shown in Illustration 40.


Illustration 40: Defining Relationship for Fact Internet Sales – Dim Time

The Define Relationship page appears.

10.  In the selector box labeled Select relationship type, select Regular.

The page expands to display previously hidden fields, which support definition of the Regular dimension type.

11.  In the selector box labeled Granularity attribute, select TimeKey.

12.  Click the selector box that appears at the intersection of the row labeled TimeKey (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationship page.

13.  Select ShipDateKey in the selector, as depicted in Illustration 41.


Illustration 41: The Define Relationship Page with Our Input

(We have selected ShipDateKey for purposes of our practice exercise, as if it were organizational accounting policy to record sales as of Ship Date. This is only to make the exercise more convenient.)

14.  Click OK to accept settings and close the Define Relationship page.

The Dimension Usage settings appear, with all modifications to this point, as shown in Illustration 42.


Illustration 42: Dimension Usage Settings, with Modifications ...

We can obviously perform myriad enhancements and extensions to, and operations surrounding, our cube from within the multi-tabbed Cube Designer component of the Business Intelligence Development Studio. We examine the many options in other articles of my Introduction to MSSQL Server Analysis Services series, where we define business needs and then meet them with the appropriate functionality(ies). At this point, we have sufficiently aligned measures with dimensions to complete our preparation of an environment within which we can get some hands-on exposure to the definition of Perspectives within our cube.

Procedure

Define Perspectives within the Cube

Let's create a couple of Perspectives to get some experience with the process. For purposes of our practice session, we will say that we have been asked by a the Adventure Works organization to create a simplified "view," for each of two groups of internal consumers of data, that is contained in the ANSYS050_Perspective cube we have created. First, we are told, we need a subset of cube data that presents data related to forecasting (mostly information surrounding sales quotas) for the Planning department consumers. Next, we need actual sales and related data for a group of consumers within the Operations department.

As we noted earlier, we can select our choice of cube objects (including dimensions, attributes, hierarchies, measure groups, measures, calculations, KPIs and actions) for inclusion within each Perspective. Let's create a couple of Perspectives within our cube to meet the expressed client needs by taking the following steps:

1.  Click the Perspectives tab atop the Cube Designer, as depicted in Illustration 43.


Illustration 43: Click the Perspectives Tab ...

The Perspectives page appears. Here, we see a list of the Cube Objects making up the Perspective, grouped by object type, and subgrouped by parent structures, as appropriate.

2.  Right-click the "blank" space to the right of the objects list.

3.  Select New Perspective from the context menu that appears, as shown in Illustration 44.


Illustration 44: Creating a New Perspective

Upon initiating Perspective creation, we see a new column, Perspective Name appear, underneath which the default selection of "all available objects" accompanies the start of new Perspective creation. For this reason, every cube is born with at least one "Perspective" – with the name of the cube itself. Creating a Perspective is largely a process of starting with all possible cube objects, and eliminating those that we do not need, to form the subset presented within the new Perspective, as we shall see.

We will first create a Perspective to house objects of interest to the Planning component of the client information consumers.

4.  Click the box labeled Perspective Name in the new column.

5.  Replace the existing Perspective Name ("Perspective") with the following:

ANSYS050 - Planning

The Perspective Name input box appears, with our replacement, as depicted in Illustration 45.


Illustration 45: Adding the Perspective Name

6.  In the objects list, expand Measure Groups (if not already expanded) by clicking the "+" sign to the left of the label, as shown in Illustration 46.


Illustration 46: Expanding Measure Groups ...

The individual Measure Groups are displayed. Assuming that the Measure Groups already correspond with the consumer groups for which we are creating Perspectives, the creation of Perspectives, based upon associated Measure Groups, can be convenient, indeed. While this is the case in our present scenario, we might also select individual measures for exclusion (as we shall see shortly), by simply expanding a level further into the appropriate Measure Groups.

7.  Deselect the following Measure Groups, by "unchecking" them.

  • Fact Internet Sales
  • Fact Internet Sales Reason

This leaves the Sales Quota Measure Group, which is relevant to the Planning department consumers, in place, by itself, within the Planning Perspective. Next, we will remove dimensions that are not useful to the Planning consumers within the context of the stated business requirements.

8.  Expand Dimensions by clicking the "+" sign to the left of the label.

9.  Deselect the following entries underneath the Dimensions group:

  • Fact Internet Sales
  • Dim Customer
  • Dim Product
  • Dim Promotion
  • Fact Internet Sales – Ship Date
  • Fact Internet Sales – Due Date
  • Fact Internet Sales – Order Date

(This means that the only remaining, selected Dimensions are Dim Employee, Dim Time, and Dim Sales Territory).

The object list appears, with our modifications, as depicted in Illustration 47.


Illustration 47: Measure Group and Dimensions Selected for the Planning Perspective

In addition to the Planning Perspective, we will next create a Perspective to meet the needs of the Operations consumers.

10.  Right-click the "blank" space to the right of the objects list, once again.

11.  Select New Perspective from the context menu that appears, as we did earlier.

Another new column, again with a default name of "Perspective," appears. We also note, as before, that the default selection of "all available objects" is apparent in the new Perspective column. We have only to rename the Perspective and to remove cube objects that are not relevant to the Operations consumers, to create a custom "view" of the cube for those users.

12.  Click the box labeled Perspective Name in the new column, as we did for our first Perspective.

13.  Replace the existing Perspective Name ("Perspective") with the following:

ANSYS050 - Operations

14.  In the objects list once again, expand Measure Groups by clicking the "+" sign to the left of the label, if necessary.

The individual Measure Groups are again displayed. While the Measure Group Fact Internet Sales correspond largely with the needs of the Operations consumer group, we will eliminate one measure within the Measure Group that the consumers have indicated to lay beyond their immediate interests, Unit Price Discount Percent.

15.  Expand the Fact Internet Sales Measure Group by clicking the "+" sign to the left of its label.

16.  Deselect the Unit Price Discount Percent measure.

17.  Deselect the following Measure Groups:

  • Fact Sales Quota
  • Fact Internet Sales Reason

This leaves the Fact Internet Sales Measure Group, which is relevant to the Operations department consumers, in place within the new Operations Perspective. Next, we will remove dimensions that are not useful to the Operations consumers within the context of business needs they have communicated.

18.  Expand Dimensions, if collapsed, by clicking the "+" sign to the left of the label.

19.  Deselect the Dim Employee entry underneath the Dimensions group within the new Operations Perspective column.

The object list appears (settings highlighted for the relevant settings), with our modifications, as shown in Illustration 48.


Illustration 48: Settings for the New Operations Perspective (Circled)

We have completed the creation of the two Perspectives that the client representatives have requested. Having taken the steps required to meet the business requirements, we are ready to browse the cube and verify the effectiveness of our new Perspectives. Before we can do this, we must deploy our Analysis Services solution. We will do so in the section that follows.

Verification

Deploy the Analysis Services Solution

Deploying the solution will dispatch the code we have created using the Cube Wizard and Designer interfaces. After the XML reaches the targeted Analysis Server (the destination server must be targeted, before beginning deployment, within the project's Deployment Configuration Properties page), the Analysis Services database is created and processed. Once this has been accomplished, we can fully leverage the Browser that the Cube Designer makes available to us.

1.  Right-click the ANSYS050 Perspectives solution, atop the tree in Solution Explorer.

2.  Select Deploy Solution from the context menu that appears, as depicted in Illustration 49.


Illustration 49: Deploying the Solution ...

Processing begins, as we can see in the Deployment Progress pane. Once deployment has completed successfully, we see a message to this effect in the Status area, toward the bottom of the pane, as depicted in Illustration 50.


Illustration 50: Indication of Successful Deployment Appears ...

We are left with a processed Analysis Services database and cube. We are ready for the final part of our article, where we will verify the effectiveness of our handiwork via the Cube Browser.

Browse the Cube

We can get to the Cube Browser, at this stage, and see our Perspectives at work within the cube, by taking the following steps.

1.  Click the Browser tab within the Cube Designer, as shown in Illustration 51.


Illustration 51: Click the Browser Tab ...

2.  Once the Browser loads, click the Reconnect button, as depicted in Illustration 52.


Illustration 52: Click the Reconnect Button ...

As we proceed, keep in mind that the power of Perspectives lies in "what they make available" to the consumer. We'll do a quick examination that will reinforce this concept. The Browser is an extremely convenient place to accomplish this. However, more in-depth details about use of the Browser, a formidable tool within itself, are developed within other articles of this series, whose objectives (typically within "verification" procedures) are to examine or analyze values and other details within the cube.

3.  Select the ANSYS050 – Planning Perspective within the Perspective list box, as shown in Illustration 53.


Illustration 53: Selecting a Perspective in the Browser

We notice that the cube tree (inside the Metadata pane, on the left side of the Browser tab) immediately shifts to display the objects included within the new Perspective. We also note that the Perspective itself appears atop the tree, in the position typically occupied by the cube. This perhaps illustrates well the nature of the Perspective within a browsing – and even a querying – context.

NOTE: We examine the employment of Perspectives within MDX queries in Other MDX Entities: Perspectives, a member of my MDX Essentials series at Database Journal.

It is useful to realize that, although we can dictate dimension membership within our Perspective design, we also can leverage the dimensional structure that we have already built into our Measure Group relationships. As we can see in the present case, only the dimensions associated with the Measure Groups we selected for inclusion within the Perspective appear within the Metadata pane. In addition to the obviously "thinned down" dimensions that we see within the cube tree, let's take a look at the Measure Groups that are made available within the ANSYS050 – Planning Perspective.

4.  Within the cube tree appearing inside the Metadata pane, expand Measures.

5.  Expand the Fact Sales Quota folder as depicted in Illustration 54.


Illustration 54: Examining Selected Measure Group in the Measure Folder

We see that the only Measure Group (with member measures) that appears within the Measures folder of the Perspective is Fact Sales Quota, exactly as we might have expected from our Perspective design in the previous section.

6.  Select the ANSYS050 – Operations Perspective within the Perspective list box.

7.  Within the cube tree appearing inside the Metadata pane, expand the Measures folder, as before.

8.  Expand the Fact Internet Sales folder, as shown in Illustration 55.


Illustration 55: Examining the Measure Group within Another Perspective

We see that the only Measure Group that appears within the Measures folder of the Perspective is Fact Internet Sales, again as we might have expected from our Perspective design in the previous section. We also note, within the Fact Internet Sales folder, the absence of the single measure, Unit Price Discount Percent, which we eliminated from inclusion within the ANSYS050 – Operations Perspective in our earlier design steps. Moreover, the dimensions that appear within the Perspective reflect the selection we made earlier.

9.  Further inspect the structure of the Perspectives, performing browses as desired.

10.  Select File -> Save All from the main menu, to save our work through this point, as depicted in Illustration 56.


Illustration 56: Saving All Work from Our Session

11.  Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.

Conclusion

In this article, we explored Perspectives, another exciting new feature that debuts in Analysis Services 2005. After introducing the concept of Perspectives, we overviewed their creation, and discussed ways in which they can offer flexibility in supporting our cube and solution / application end users. In preparation for our examination of the steps involved in extending our cubes to leverage Perspectives, we prepared 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. We next performed the steps that are common to the design and creation of any cube within Analysis Services 2005, including the creation of a Data Source, containing the information Analysis Services needs to connect to a database, and a Data Source View containing schema information.

We then created a basic cube, referencing our Data Source and Data Source View, which contained data from our sample relational tables. Next, we got some hands-on exposure to creating Perspectives, via the Perspectives tab of the Cube Designer. Finally, after deploying our Analysis Services solution, we browsed the cube, focusing on the new Perspectives and the cube objects that each made available.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved