Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension

Monday Apr 11th 2005 by William Pearson
Share:

Present a crosstab display where both axes contain the same dimension. MSAS Architect Bill Pearson leads a hands-on introduction to using this approach to meet a relatively common presentation need.

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 adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack current updates are assumed for MSSQL Server, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

Introduction

In this article, we will examine a subject that is near and dear to broadly focused report authors - the use of cube structure to create desired cosmetic effects - in the Cube Browser, and more importantly, in a reporting environment. I constantly get e-mails, and see questions in forums and elsewhere, asking how to achieve effects that are not apparently "available" in "intuitive" cube structures, such as any of those that we see in the sample cubes. One of the apparent "shortfalls" that frustrate users is their inability to display the same dimension on both the "x-" and "y-" axes for presentation purposes.

In this article, we will examine an approach to meeting the relatively common requirement to present a crosstab display where both axes of the display contain the same dimension, as well as examining other considerations that might be relevant in such a scenario. We will:

  • Present an illustrative, hypothetical business need for a crosstab display whose axes contain the same dimension;
  • Create a copy of the Warehouse sample cube for use in our practice session;
  • Prepare the cube copy further by processing;
  • Add a member property to support a new virtual dimension;
  • Create a virtual dimension, based upon the member property, to support the presentation requirement we have been given;
  • Add a calculated member to present a percent contribution to total value;
  • Examine the results of our handiwork in the Analysis Services Cube Browser;
  • Examine the results of our handiwork, from a reporting perspective, in the MDX Sample Application.

A CrossTab View with the Same Dimension in Both Axes

Overview and Discussion

In many cube design scenarios in which we find ourselves, especially working with clients and customers with specific presentation effects in mind, we have numerous options for "where" to implement the mechanisms behind the final display. As I explain to clients in my daily work with MSSQL Server, Analysis Services, and Reporting Services, one of the most powerful things about an integrated Microsoft BI solution is the fact that it often presents three discrete "layers" at which we can build the structure that underlies the end results.

This is why organizations that undertake such a solution need to seek, as architects and implementers, an individual or team that is versed in all three layers of the solution. Although a "Reporting Services guru" can accomplish many of the enterprises needs from within the Reporting Services application, there are typically structural opportunities at the cube, or even RDBMS, level that might mean far more optimal reports, both from a processing standpoint and from a user-friendliness perspective.

An example might be the structure that underlies the parameterization of a Reporting Services report based upon an Analysis Services OLAP cube. While we might build the picklists and other components of parameterization solely within the reports, we might just as easily (and in some cases much more optimally) create the picklists within the underlying cubes, and simply reference them in any report based upon the same cube.

NOTE: For an approach to creating cube-based structures to support parameterization in reports, see my article Create a Cube-Based Hierarchical Picklist in our Database Journal sister series, MDX in Analysis Services.

For that matter, and along the same lines, depending upon an "expert" in any of the three components who is not fluent in all three might be a recipe for mediocrity at best, and failure to meet objectives, at worst, in the pursuit of an integrated Microsoft BI solution. When assembling a team to plan and execute larger implementations of this sort, a project lead or other manager who is versed in all three layers can certainly go a long way toward ensuring the optimal allocation and coordination of design effort among a team of more specialized practitioners. One of the clear benefits is avoidance of an architecture where the "heavy lifting" demanded by the enterprise information consumers is supported in such a way that bottlenecks are inevitable - and, perhaps worse, irreversible.

In this article, we will present a reporting requirement that I come across fairly frequently, the delivery of a crosstab capability where the same dimension needs to appear in both the "x-" and "y-" axes of the ultimate presentation. As many of us learn in our early exposure to MDX, attempts to simply cast the same dimension in the ON ROWS and ON COLUMNS specifications of our MDX query, as is done in the following query:


SELECT 
    { [Store].[Store Country].Members} ON COLUMNS,
    {[Store].[Store City].Members} ON ROWS
FROM  
   [WAREHOUSE]
WHERE
   ([Time].[1998], [Measures].[Warehouse Profit])

results in our receiving an error, upon attempts at execution, similar to that shown in Illustration 1.


Illustration 1: Results of "Straightforward" Attempt at Same Dim in Both Axes ...

We will begin with a scenario that illustrates a requirement for such a dual-axis presentation, using a hypothetical business need to add practical value. Let's say that a member of the Finance department at a client, the FoodMart organization, has approached us with a request it wishes to meet, from its Warehouse cube users. We are told that the information consumers have requested a report that presents Warehouse Profit by U. S. Warehouse City, as well as the percent contribution to total Warehouse Profit, made by each Warehouse City to the whole for the FoodMart organization, for operating year 1998. While this appears to be a simple query - indeed, it has been attempted by an internal "guru" that focuses a great deal of personal effort on learning Analysis Services - the presentation that the consumers have requested make the task a bit more considerable.

The consumers state that they want the presentation to appear with Warehouse Country (U.S.A. only, in the current request) to appear in the "x-" axis and Warehouse City, represented by a "City, State" combination, to appear across the "y-" axis. At our request, to confirm our understanding, they present a draft of the desired display that appears as depicted in Table 1.

USA

Warehouse Profit

% Profit Contribution

Total

$ xxx,xxx

xxx.xx%

Bellingham, WA

x,xxx

xxx.xx%

Beverly Hills, CA

x,xxx

xxx.xx%

Bremerton, WA

x,xxx

xxx.xx%

Los Angeles, CA

x,xxx

xxx.xx%

....

....

...


Table 1: Draft of the Desired Report Layout

After confirming our understanding of the business requirements, we agree to build in support for the requirement within the Warehouse cube. Working with a copy of the sample cube, we will make the additions and adjustments to make the report possible. We will then test the results within the Analysis Services cube browser, and then by running a query from an external application, the MDX Sample Application, to simulate the effect we would obtain from within an external reporting application.

I will pursue the use of the structure that we create from a Reporting Services perspective from an article within my Reporting Services series at Database Journal. I invite you to join us in that article to obtain an appreciation for the ease with which we can produce a report similar to the above, thanks to the structure that we will have housed in our cube to support the desired features.

Considerations and Comments

For purposes of this exercise, we will be working with the Warehouse cube, within the FoodMart 2000 MSAS database; these working samples accompany a typical installation of Analysis Services. If the samples are not installed in, or have been removed from, your environment, they can be obtained from the installation CD, from the Analysis Services section of the Microsoft website, and perhaps elsewhere.

To prepare, we will create a copy of the original Warehouse cube, to preserve the original in its current state, as well as to allow access to the original cube in the meantime from enterprise users.

Hands-On Procedure

Preparation

Create a Copy Cube

Let's get started by creating a copy of the Warehouse sample cube, which, along with the FoodMart database that contains it, accompanies an Analysis Services installation. This will allow us to keep the original sample cube intact for other uses.

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as shown in Illustration 2.


Illustration 2: Example Databases Displayed within Analysis Manager

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The sample cubes appear, as shown in Illustration 3.


Illustration 3: The Sample Cubes in the FoodMart 2000 Database

NOTE: Your databases / cube tree will differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

6.  Right-click on the Warehouse sample cube.

Again, we are making a copy of the Warehouse cube, because our lesson will involve making changes to the cube we use within the practice example. As we have noted, working with the copy will allow us to maintain our existing sample cube in its current condition, and available to other users.

7.  Select Copy from the context menu that appears.

8.  Right-click on the Cubes folder.

9.  Select Paste from the context menu that appears.

The Duplicate Name dialog appears.

As noted in previous articles, we cannot have two cubes of the same name in a given MSAS database.

10.  Type the following into the Name box of the Duplicate Name dialog:

Crosstab Reporting

The Duplicate Name dialog appears, with our modification, as depicted in Illustration 4.


Illustration 4: The Duplicate Name Dialog, with New Name

TIP: As I have mentioned elsewhere in this and other series, the foregoing is also an excellent way of renaming a cube (a "rename" capability is not available here, as it is in many Windows applications). Simply create a duplicate, give it the name to which you wish to rename the old cube, and then delete the old cube, as appropriate. This also works for Analysis Services databases, dimensions and other objects.

11.  Click OK to apply the name change.

The new cube, Crosstab Reporting, appears in the cube tree, among those already in place, as shown in Illustration 5.


Illustration 5: The New Cube Appears in the Cube Tree

We now have a copy of the Warehouse cube, within which we can perform the steps of our practice exercise. Let's process the new cube to "register" it with Analysis Services, and to ensure that we are all in a "processed" state.



Process the Copy Cube



1.  Right-click the new Crosstab Reporting cube.



2.  Select Process... from the context menu that appears, as depicted in Illustration 6.




Illustration 6: Select Process... from the Context Menu

The Process a Cube dialog appears, as depicted in Illustration 7, with the processing method defaulted to Full Process (as this is the first time the cube has been processed).


Illustration 7: Full Process Selected in the Process a Cube Dialog

3.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 8.


Illustration 8: Indication of Successful Processing Appears (Compact View)

4.  Click Close to dismiss the viewer.

We will next alter the structure of the Crosstab Reporting cube, as we take the necessary steps to provide support for the desired presentation of data that has been requested by our client.

Create a Member Property to Support a Virtual Dimension

The key to accommodating the need to present a dimension in two axes will be the creation of a virtual dimension, which will itself be based upon a member property that we establish. Because the member property we need will belong to the Store dimension, we will begin our alternations within the Shared Dimensions folder, where we will take the following actions to accomplish our objectives:

1.  Expand the Shared Dimensions folder within the FoodMart 2000 Analysis Services database.

2.  Right-click the Store dimension within the Shared Dimensions folder.

3.  Select Edit from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Select Edit from the Context Menu ...

The Dimension Editor opens.

4.  Expand the lowest level in the hierarchy, Store Name.

5.  Expand the Member Properties folder, as shown in Illustration 10.


Illustration 10: Expanded Member Properties Folder ...

We will be adding a new member property to those that already exist for the Store dimension.

6.  Right-click the expanded Member Properties folder.

7.  Select New Member Property from the context menu that appears, as depicted in Illustration 11.


Illustration 11: Select New Member Property ...

The Insert Member Property dialog appears.

8.  Select store_city as the source column for the new member property, as shown in Illustration 12.


Illustration 12: Select Store_City as the Source Column

We will adjust the appearance of the Store City information next, to meet the business requirement for a "City, State" presentation.

9.  Click OK to add the new member property.

The dialog closes, and the Store City member property appears in the Member Properties folder, underneath the existing member properties.

10.  With the new Store City member property selected, replace the following entry in the Source Column box of the Properties pane - Basic tab:

"store"."store_city"

with the following entry:

"store"."store_city"+', '+"store"."store_state"

The Source Column box of the Properties pane - Basic tab appears as depicted in Illustration 13.


Illustration 13: The Edited Source Column Entry ...

11.  Select File -> Exit from the Dimension Editor main menu.

A Dimension Editor message box appears, asking if we want to save changes, as shown in Illustration 14.


Illustration 14: Click Yes to Save Modifications to the Store Dimension

12.  Click Yes to save changes to the Store dimension and to close the Dimension Editor.

We return to Analysis Manager, where we are now ready to create a Virtual Dimension to support dual axis reporting with the same (Store) dimension.



Create a Virtual Dimension to Enable Dual Axis Reporting



Let's enter the new Crosstab Reporting cube and set about the creation of a virtual dimension.

1.  Right-click the Crosstab Reporting cube in the cube tree.

2.  Select Edit from the context menu that appears, as depicted in Illustration 15.




Illustration 15: Select Edit from the Context Menu ...

The Cube Editor opens.

3.  Right-click the Dimensions folder in the Tree pane.

4.  Select New Dimension from the context menu that appears, as shown in Illustration 16.


Illustration 16: Select New Dimension from the Context Menu ...

The Welcome page for the Dimension Wizard appears, as depicted in Illustration 17.




Illustration 17: Dimension Wizard Welcome Page

5.  Click Next.

The Choose how you want to create the dimension dialog appears.

6.  Using the radio button to its immediate left, select Virtual Dimension, as shown in Illustration 18.


Illustration 18: Select Virtual Dimension ...

7.  Click Next.

The Select the dimension with the member properties dialog appears. We are given a choice of "eligible" dimensions - that is, dimensions that the Wizard determines to possess member properties, upon which a virtual dimension is based.

8.  Click Store in the Available Dimensions list on the left side of the dialog.

The Member Properties list on the right side of the dialog becomes populated with the "eligible" member properties for the Store dimension (including the new Store City member property we added in the previous section of our exercise), as depicted in Illustration 19.

Click for larger image

Illustration 19: Eligible Member Properties ...

9.  Click Next.

10.  Select Store Name.Store City in the Available member properties list, in the left half of the Select the levels for the virtual dimension dialog that appears next.

11.  Click the ">" button to add the Store Name.Store City member property to the Selected virtual levels list on the right side of the dialog.

The Selected virtual levels list on the right side of the dialog becomes populated with the Store City member property we added earlier, as shown in Illustration 20.


Illustration 19: Adding Store City as a Virtual Dimension Level

12.  Click Next.

We will skip setting advanced options in the Select advanced options dialog that appears next, as depicted in Illustration 21.


Illustration 21: Select Advanced Options Dialog

13.  Click Next.

The Finish the Dimension Wizard dialog appears next.

14.  Type the following into the Dimension name box:

Store Location

The Finish the Dimension Wizard dialog appears as shown in Illustration 22.


Illustration 22: Our Title in the Finish the Dimension Wizard Dialog

15.  Click Finish.

The Dimension Wizard closes, and we return to the Cube Editor, where we see our new virtual dimension, Store Location, among the other dimensions in the tree, as depicted in Illustration 23.


Illustration 23: The Store Location Virtual Dimension Joins the Other Dimensions ...

(We can also see it in the Shared Dimensions folder for the Analysis Services database, as we accepted default to share the dimension in the Finish dialog above).

Create the % Profit Contribution Calculated Member

Now all that remains structurally is to add the % Profit Contribution to have all the components in place to meet the information consumers' stated business requirements.

1.  Right-click the Calculated Members folder.

2.  Select New Calculated Member from the context menu that appears, as shown in Illustration 24.

Click for larger image

Illustration 24: Select New Calculated Member ...

The Calculated Member Builder opens.

3.  Ensure that Measures is selected in the Parent Dimension selector atop the Builder.

4.  Type the following into the Member Name box within the Calculated Member Builder:

% Profit Contribution

5.  Type the following into the Value Expression box within the Calculated Member Builder:


IIF(IsEmpty(([Measures].CurrentMember, 
   Ancestor([Store Location].CurrentMember,1))), 1,
      [Measures].[Warehouse Profit]/([Measures].[Warehouse Profit],      
          Ancestor([Store Location].CurrentMember,1)))

The above MDX provides for a Percentage Profit Contribution calculation, while also making the result a "1" (or "100 percent") when the CurrentMember of the Store dimension has no parent.

NOTE: For more information regarding the CurrentMember function, see my Database Journal article MDX Essentials: MDX Member Functions: "Relative" Member Functions.

The Calculated Member Builder appears as depicted in Illustration 25.


Illustration 25: The Calculated Member Builder, with Our Input

6.  Click OK to close the Calculated Member Builder, and to accept our input.

We see the new Calculated Member appear in the tree pane, within the Calculated Member folder, as shown in Illustration 26.


Illustration 26: The % Profit Margin Calculated Member Appears

7.  Click the % Profit Contribution calculated member in the tree, to select it, if necessary.

8.  Click the Properties bar at the bottom of the pane, to open the Properties pane, if necessary. (If "Properties" appears on the bar with an upward-pointing arrow to its right, clicking will open the pane / point the arrow downward).

9.  Click the Advanced tab in the Properties pane.

10.  In the selector for the Format String property box, select Percent, as depicted in Illustration 27.


Illustration 27: Setting Percent Format for the % Profit Contribution Calculated Member

Let's process the cube again, to update it for all our work.

11.  Select Tools -> Process Cube from the Cube Editor main menu, as shown in Illustration 28.


Illustration 28: Reprocess the Cube ...

12.  Click Yes when prompted to save the cube, as depicted in Illustration 29.


Illustration 29: Save the Cube before Processing

13.  Click No when asked if you wish to design aggregations, in the next Analysis Manager dialog, shown in Illustration 30.


Illustration 30: Decline Aggregation Design ...

The Process a Cube dialog appears, as before.

14.  Click the radio button to the left of Full Process, to select full processing.

15.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as we saw earlier.

16.  Click Close to dismiss the viewer.

At this stage, all components are in place to deliver the data presentation requested by the information consumers during the business requirements gathering phase.

Verify Results - from the Cube Browser

We will conclude our practice session by examining the results of our handiwork from two perspectives: from the Analysis Manager, via the Cube Browser, and from a "reporting perspective," using the MDX Sample Application.

1.  From our present position within the Cube Editor, click the Data tab to open the browser and see cube data.

2.  Drag the Store Location dimension down, and drop it atop the MeasuresLevel heading (column axis), as depicted in Illustration 31.

Click for larger image

Illustration 31: Swapping the Store Location and Measures on the Data Tab "X" Axis

3.  Drag the Store dimension down, and drop it atop the Product Family heading (row axis), as shown in Illustration 32.

Click for larger image

Illustration 32: Swapping the Store and Product Dimensions on the Data Tab "Y" Axis

We can see the Store dimension in both the "X-" and "Y-" axes on the Data Tab.

4.  Select % Profit Contribution in the Measures dimension filter, atop the Cube Browser, as depicted in Illustration 33.


Illustration 33: Filtering for the New Calculated Member % Profit Contribution

5.  Expand the USA Store Country in the row axis by clicking the "+" sign to the left of USA.

6.  Expand the Store State level for Washington (WA).

7.  Scroll over to the right in the Cube Browser, until Seattle, WA appears under the Store City heading (and thus Seattle, WA is the left-most column in the data area), as shown in Illustration 34.


Illustration 34: Examining % Profit Contribution (Compressed, Partial View)

We can see that the components appear to be in place and working from the Cube Browser. We will next look at the data from a "reporting perspective," and ascertain that our structural enhancements to the cube allow us to precisely meet the presentation requirements of the information consumers.

8.  Select File -> Exit (main menu) to leave the Cube Editor.

9.  From the Analysis Manager main menu, select File -> Exit.

Verify Results - from a Reporting Perspective

Let's use the MDX Sample Application to practice setting up a report to display the data as requested by consumers. This is a great way to get a feel for the adequacy of the components we have designed for this purpose, as the same MDX we construct to return the data requested by the information consumers within the Sample Application will likely be used as the source query within the reporting application. (We will use the above structural preparation for a report that we will construct using MSSQL Server Reporting Services as the "front end" in an article we publish later, in the MSSQL Server Reporting Services series here at Database Journal.)

We recall that the requirement was that we display U.S.A. on the "x-" axis and Warehouse City, represented by a "City, State" combination, to appear across the "y-" axis. The consumers also only wish to present the Warehouse Profit and % Profit Contribution along the "x-" axis, which, as we will see, is easily accommodated with the other requirements. Let's initialize the MDX Sample Application, and take the following steps:

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog, depicted in Illustration 35.


Illustration 35: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

The MDX Sample Application window appears.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Crosstab Reporting cube in the Cube drop-down list box.

5.  Click File -> New to open a blank Query pane.

The MDX Sample Application window should resemble that shown in Illustration 36, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).


Illustration 36: The MDX Sample Application Window (Compressed View)

We will begin creating our query with a focus on returning results in the same general formation as the Data View we left in the Cube Editor. We will then move from the core query to a more precise presentation of the data as it was requested by the information consumers (and for which they provided a "draft" that was reproduced in Table 1 above).

We will initially retrieve our two axes, containing members of the same dimension (Store) with the primary Warehouse Profit measure, by taking the following steps.

1.  Create the following new query:


-- ANSYS34-1 Core Query - Same Dim in Both Axes of Crosstab
SELECT 
    { [Store].[All Stores].[USA]} ON COLUMNS,
    {[Store Location].Members} ON ROWS
FROM  
   [CROSSTAB REPORTING]
WHERE
   ([Time].[1998], [Measures].[Warehouse Profit])

2.  Execute the query using the Run Query button.

The results dataset appears as depicted in Illustration 37.


Illustration 37: The Results Dataset - Core Query Results - Same Dim in Both Axes

3.  Save the query as ANSYS34-1, in a convenient location, leaving it open for the next steps.

In the results dataset we note that all store locations appear, and that there are several "blank" cells. This is largely a result of our having asked for "USA only" in our column specification. Because the column is retrieving USA only, values at the intersections of USA and non-domestic locations are null.

We will further polish the results set by ridding it of the nulls, with the NON EMPTY() keyword, in the next steps, both for cosmetic effects (making the dataset more compact), and for eliminating potential confusion among the information consumers. We will do so within a modification to our last query.

4.  Change the comment line in the query to read as follows:

-- ANSYS34-2 Core Query - Eliminating "Empties"

5.  Save the query as MDX34-2, to protect MDX34-1.

6.  On line 4 of the query, enclose {[Store Location].Members} in parentheses ("( )").

7.  Place the cursor before ({[Store Location].Members}) ON ROWS.

8.  Type the following keyword phrase in front of the new left parenthesis:

   
NON EMPTY

So that the entire line appears as follows:

   
NON EMPTY({[Store Location].Members}) ON ROWS

The query, with our modifications to the ON ROWS specification, resembles that shown in Illustration 38.


Illustration 38: Query with Modifications in the Query Pane (Compressed View)

9.  Execute the query, using the Run Query button, once again.

The query executes, once again, and the results dataset appears as depicted in Illustration 39.


Illustration 39: Results Dataset - Empties (and Non-Domestic Location Members) Removed

10.  Resave the query as MDX34-2.

Because we will now rearrange a couple of things to bring the query output into alignment with the presentation requested, we will create a new query at this stage. The core axes portion remains the same, but we will be using a CROSSJOIN() function to combine our measures with the existing dimension in the ON COLUMNS specification. Moreover, we will be adding the % Profit Contribution calculated member / measure within those measures.

11.  Click File -> New to open a blank Query pane.

We will initially retrieve our two axes, containing members of the same dimension (Store), with the primary Warehouse Profit measure and the % Profit Contribution calculated measure, by taking the following steps.

12.  Create the following new query:


-- ANSYS34-3 Final Query Supporting Presentation Requirements
SELECT 
  CROSSJOIN({[Store].[Store Country].[USA]},{[Measures].[Warehouse Profit], 
      [Measures].[% Profit Contribution]}) ON COLUMNS,
  NON EMPTY({[Store Location].Members}) ON ROWS
FROM  
   [CROSSTAB REPORTING]
WHERE
   ([Time].[1998])

13.  Execute the query using the Run Query button.

The results dataset appears as depicted in Illustration 40.


Illustration 40: The Results Dataset - Supporting Presentation Requirements

14.  Save the query as ANSYS34-3.

We have now provided for all aspects of the requested data presentation. Our structural changes, coupled with the appropriate query within the respective reporting application, will allow for the generation of a report that resembles the draft we received from the information consumers. We can also support complementary charts and other report objects that might rely upon the values we have retrieved.

Conclusion

In this article, we examined an approach to meeting a relatively common requirement: to present a crosstab display with both axes of the display containing the same dimension. After discussing the general considerations, we presented an illustrative, hypothetical business need for a crosstab display whose axes contain the same dimension. We then prepared for our hands-on practice with meeting that need by creating a copy of the Warehouse sample cube, and then prepared the clone cube further by processing.

We began our structural alterations to the cube by adding a member property, which we then used to support a virtual dimension, upon which the ultimate approach to supporting the presentation requirement rested. Next, we added a calculated member to present a percent contribution to total value, an additional requirement of the information consumers. Finally, we examined the results of our handiwork from the perspective of the Cube Browser, within Analysis Manager, and from a "reporting context," which we undertook from the MDX Sample Application.

» 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