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 7, 2006

Mastering Enterprise BI: Introduction to Perspectives - Page 7

By William Pearson

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.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date