Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II

Monday Apr 10th 2006 by William Pearson
Share:

Architect Bill Pearson returns in the second half of a hands-on exploration of Named Calculations. In this article, we continue with using Named Calculations to support aging buckets within an Analysis Services 2005 cube.

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 may 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, as appropriate. 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 over for fourteen 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 BI 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 BI to the Microsoft solution. In addition to converting formerly dominant enterprise 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 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 (as well as their Cognos 8 incarnations), 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 to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves – and, 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 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

In this, the second half of a two-part article, we continue the examination of Named Calculations we began in Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I. In Part I, by way of introduction, we recalled ways of "extending" the data source tables underlying our Analysis Services 2000 cubes which we had examined in past articles of this series, as an introduction to this article, where we explore yet another new feature where Analysis Services 2005 offers us more flexibility in this area. We noted that we were limited, in the previous version of Analysis Services, to using SQL expressions within the Member Key and Member Name columns (in the case of dimension structures), and in the Source column (in the case of measures) to achieving similar extensions. We referred to my article Mastering Enterprise BI: Create Aging "Buckets" in a Cube, where I proposed the use of an IIF / CASE scenario to build the necessary dimensional structure into a sample cube to support aging buckets, as an example of such an extension, and we got a glimpse of how, although the approach might work to help us deliver desired results in our business environments, the use of SQL expressions within these rather limited selectors might become cumbersome in many situations.

At this point in Part I, we noted that, among many overall improvements, and added conveniences in the design arena, Analysis Services 2005 offers us far more flexibility in this area, as well. We stated that the advent of the Data Source View represents a significant design and development enhancement within Analysis Services, pointing to my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views, where we first introduced this new abstract layer within the design environment. We noted, in review, that the Data Source View contains the logical model of the schema used by database objects, including cubes, dimensions, and so forth, and that it forms a central, unified view of the metadata within our Analysis Services Project.

We recalled that, in addition to being capable of representing one or more Data Sources (allowing us to integrate data from multiple data stores within a single cube, or even dimension), another of the many advantages offered by the Data Source View layer is its capacity to contain logical objects, such as queries, relationships, and calculated columns, that do not exist within (and, indeed, are entirely separate from) the underlying data sources. This factor, as we discovered, lies at the heart of our current focus upon Named Calculations, which become quite useful to us when we cannot create, for whatever reason, these "extending" objects within the data sources upon which we are constructing our Analysis Services Projects

In the first half of this article, we laid out our objective to examine Named Calculations, and to get hands-on exposure to the process of adding them to a basic cube we construct within the Business Intelligence Development Studio. We overviewed the creation of Named Calculations, and discussed ways in which they can offer flexibility in general cube design and development. We then prepared Analysis Services, and our environment, by creating an Analysis Services Project (which we called ANSYS045 Named Calculations), 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. Next, we created a Data Source containing the information Analysis Services needs to connect to a database, and then we created a Data Source View containing schema information. Finally, as the last step of Part I, we added examples of Named Calculations within the Data Source View.

As a part of our continuing examination of Named Calculations in this article, we will:

  • Create a Cube containing data from our sample relational tables;
  • Create a Dimension based upon two of our Named Calculations to support "aging buckets;"
  • Deploy our Analysis Services Solution;
  • Browse the Cube, focusing on the new aging dimension structures.

Introducing Named Calculations (Continued...)

Overview and Discussion

We will return to the Business Intelligence Development Studio, which, as we stated in Part I, provides the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005.

Having created an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop our current cube and its supporting objects, we have created a Data Source and Data Source View (containing our Named Calculations examples). We are ready to leverage the Cube Wizard next, to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. As we have noted elsewhere, 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 to which it is pointed. We further pointed out that, 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

As we stated prior to beginning Part I, 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. For more information on installing the samples, please see the Considerations and Comments section in Part I.

NOTE: It is necessary to have completed Part I to participate in this practice session.

Hands-On Procedure (continued ...)

We will pick up where we left off in Part I, having created three Named Calculations. Recall that the first Named Calculation we created, ANSYS045_Gender Description, was a simple example that was based upon a simple CASE statement. The second and third Named Calculations, ANSYS045_Aging_Bucket_Key and ANSYS045_Aging_Bucket_Name, were also based upon a CASE statement (albeit a slightly more complicated one). The first Named Calculation simply provided the word, "Male" or "Female," based upon the "M" or "F" provided in the source database, which might be used to support a more user-friendly Gender description within the intended cube. (We will not work further with this simple example in our current session). The purpose of the second pair of Named Calculations was to support the population of data, via its transaction dates, into "aging buckets", with one being designed to supply a numeric key to Analysis Services within the cube, and the other to supply a character-based description of the "aging bucket" (ANSYS045_Aging_Bucket_Key and ANSYS045_Aging_Bucket_Name, respectively).

Procedure (continued...)

1.  Restart the SQL Server Business Intelligence Development Studio.

We again briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

2.  Close the Start page, if desired.

3.  Select File --> Open from the main menu.

4.  Click Project / Solution ... from the cascading menu, as shown in Illustration 1.


Illustration 1: Selecting a Project ...

The Open Project dialog appears.

5.  Browse to the location where we left the ANSYS045 Named Calculations solution at the conclusion of Part I.

NOTE: It is necessary to have completed Part I to participate in this practice session.

6.  Select the ANSYS045 Named Calculations.sln file within the appropriate folder, as depicted in Illustration 2.


Illustration 2: Selecting the Part I Solution File ...

7.  Click Open.

The ANSYS045 Named Calculations solution opens within the Designer, and we again see the Data Source View that we created in the last session. (If we closed the Data Source View last session, right click it in the Solution Explorer, and select Open from the context menu that appears, to open it.)

The Designer environment appears as depicted in Illustration 3.


Illustration 3: We Return to the Design Environment

We are now ready to build a basic cube, to provide a vehicle for seeing our Named Calculations in action.

Create a Basic Cube

We will create a basic cube, to house our Named Calculations, and to see how we put them to work in the dimensional structure. We will focus on other areas within the overall design and creation process in other articles of the series – our primary objective here is to see how we use the Named Calculations we have created – not to concern ourselves with the myriad other topics contained within general cube design.

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

2.  Select New Cube from the context menu that appears, as shown in Illustration 4.

Click for larger image

Illustration 4: Select New Cube to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as depicted in Illustration 5.

Click for larger image

Illustration 5: 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 shown in Illustration 6.

Click for larger image

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

These settings will allow the Wizard to leverage the IntelliCube technology built into Analysis Services 2005, and to detect the relationships between attributes, dimensions, and facts within the data source we have defined. While we will not accept all the judgments that the wizard makes, we can still take advantage of the actions it performs, and modify or eliminate those that are inappropriate or of no use to us.

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 depicted in Illustration 7.


Illustration 7: 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 shown in Illustration 8.


Illustration 8: 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 checkboxes:

Fact Column: DimReseller (leave checked within Dimension column)

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 depicted in Illustration 9.


Illustration 9: 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 shown in Illustration 10.


Illustration 10: 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 Amount
  • Freight

The Select Measures dialog appears, as depicted in Illustration 11.


Illustration 11: The Select Measures Dialog, 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 shown in Illustration 12.


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

16.  Click Next.

We move to the Review New Dimensions page, which appears as depicted in Illustration 13.


Illustration 13: The Review New Dimensions Page

17.  Click Next.

The Completing the Wizard page appears next.

18.  Change the default Cube name to the following:

ANSYS045 - Named Calculations

The Completing the Wizard page appears as shown in Illustration 14.


Illustration 14: The Completing the Wizard Page

19.  Click Finish.

The Wizard concludes, and the Cube Designer opens, as depicted in Illustration 15.


Illustration 15: The Cube Designer Opens ...

Here, within the multi-tabbed Cube Designer component of the Business Intelligence Development Studio, we can perform myriad enhancements and extensions to, and operations surrounding, the existing cube. We will be examining these new features in prospective articles, where we define business needs and then meet them with the appropriate functionality(ies). For the purposes of this article, let’s put our new Named Calculations to work, and verify their effectiveness in our basic cube.

Add an Aged Periods Dimension

Because of the many options that await us in Analysis Services dimensional structure design, specifically with the introduction of Attributes, in addition to Hierarchies, there might be numerous ways to proceed, even at this point, in creating our "aging buckets." We will take the route we developed in Mastering Enterprise BI: Create Aging "Buckets" in a Cube, the article to which we referred earlier, where we developed a solution for an Analysis Services 2000 cube. We might gain direct reporting benefits via this approach, namely the capability to drill down aging buckets to the transactional dates that make them up, but that is not to say that we could not obtain similar capabilities using another approach. The focus here is to show that, with our newly added Named Calculations, we can flexibly support "aging buckets" within the dimensional structure of the cube, based upon calculations we have created at the abstract layer of the Data Source View, which did not exist in our underlying relational data source.

To create an Aged Periods dimension, we will construct dimension Attributes using our Named Calculations, together with a dimension table column. We will then build a simple dimension Hierarchy using the Attributes that we have created. We will begin with the creation of a standard dimension, using the Dimension Wizard, by taking the following steps.

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

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


Illustration 16: Select New Dimension from the Context Menus

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


Illustration 17: The Welcome to the Dimension Wizard Dialog

3.  Click Next.

4.  Select Build the dimension using a data source (the radio button is selected by default) on the Select Build Method page, which appears next.

This selection directs that the dimension structure will be based upon dimension tables, their columns and any relationships that exist between columns existing within an available data source view.

5.  De-select the Auto build option.

The Select Build Method page appears, as shown in Illustration 18.


Illustration 18: The Select Build Method Page with Our Selections

6.  Click Next.

The Select Data Source View page appears, defaulted to the sole Data Source View within our project, Adventure Works DW, as depicted in Illustration 19.


Illustration 19: Select Data Source View Page with Selection

7.  Click Next.

8.  Select Standard on the Select the Dimension Type page that appears next, as shown in Illustration 20.


Illustration 20: Selecting the Standard Dimension Type ...

9.  Click Next.

The Select the Main Dimension Table page appears.

10.  Using the dropdown selector, select dbo.DimTime, in the Main table section atop the page.

11.  Click the checkbox to the immediate left of TimeKey, the top item in the Key columns list, to select it as the Key Column.

12.  Using the dropdown, select FullDateAlternateKey, in the optional Column containing the member name selector at the bottom of the page.

The Select the Main Dimension Table page appears as depicted in Illustration 21.


Illustration 21: The Select the Main Dimension Table Page, with Our Selections

13.  Click Next.

We arrive at the Select Dimension Attributes page.

14.  Scroll to the bottom of the Dimension attributes list.

15.  Click the check box to the immediate left of the bottom Attribute Name, ANSYS045 Aging Bucket Name, to select it.

16.  Select ANSYS045_Aging_Bucket_Key in the selector to the immediate right of the ANSYS045 Aging Bucket Name selection, designating this as the Attribute Key Column.

17.  Leave the Attribute Name Column at its default of ANSYS045_Aging_Bucket_Name.

The Select Dimension Attributes page appears, with our selections, as shown in Illustration 22.

Click for larger image

Illustration 22: The Select Dimension Attributes Page, with Our Selections

18.  Click Next.

19.  Ensure that the default, Regular, is set within the Dimension type selector on the Specify Dimension Type page that appears next, as depicted in Illustration 23.

Click for larger image

Illustration 23: The Specify Dimension Type Page, Defaulted to Regular Dimension Type

20.  Click Next.

21.  Leaving all settings at default, click Next at the Define Parent-Child Relationship page that appears next, as shown in Illustration 24.

Click for larger image

Illustration 24: The Define Patent-Child Page, Settings at Default ...

22.  Replace the Name on the Completing the Wizard page, which appears next, with the following:

Aged Period

The Completing the Wizard page appears, with our modification, as depicted in Illustration 25.


Illustration 25: The Completing the Wizard Page, with Our New Name

23.  Click Finish.

The Dimension Wizard completes its work, and then dismisses itself. We arrive at the Dimension Structure tab for the new Aged Period dimension, within the Dimension Designer, as shown in Illustration 26.


Illustration 26: The New Aged Period Dimension – Dimension Structure Tab

24.  Right-click the new ANSYS045 Aging Bucket Name attribute, appearing just under Aged Period in the Attributes pane.

25.  Select Rename from the context menu that appears, as depicted in Illustration 27.


Illustration 27: Renaming the Attribute ...

26.  Replace the existing name with the following:

Aged Period

The new name appears in the attribute caption box as shown in Illustration 28.


Illustration 28: The New Name is Assigned ...

27.  Right-click the Dim Time attribute, appearing just under the newly renamed Aged Period attribute in the Attributes pane.

28.  Select Rename, as before.

29.  Replace the existing name with the following:

Trans Date

The Attributes pane appears, with our modifications, as depicted in Illustration 29.


Illustration 29: New Names in the Attribute Pane

Having created a new Aged Periods Dimension to serve as the structure for our new aging capabilities, we now need to create and populate a Hierarchy to house the Named Calculations we created in Part I to act as our "aging buckets." We will accomplish this in the next steps.

30.  Right-click the newly renamed Aged Period attribute in the Attributes pane.

31.  Select Start New Hierarchy, as shown in Illustration 30.


Illustration 30: Creating a New Hierarchy in the Dimension

A new Aged Period hierarchy appears in the Hierarchies and Levels pane of the Aged Period Dimension Structure tab of the Designer for the Aged Period dimension.

32.  Right-click the newly renamed Trans Date attribute in the Attributes pane.

33.  Select Create Level, as depicted in Illustration 31.


Illustration 31: Creating a New Level within the Hierarchy ...

A new Trans Date level appears underneath the Aged Period hierarchy we created above, within the Hierarchies and Levels pane of the Aged Period Dimension Structure tab, as shown in Illustration 32.


Illustration 32: The New Hierarchy and Level within the Designer ...

All that remains in our "construction phase" is to add the new Aged Periods dimension to our cube. We will do so through the following steps.

34.  Click the ANSYS045 - Named Calculations.cube tab within the design environment (to the right of the Aged Periods.dim tab), as depicted in Illustration 33.

Click for larger image

Illustration 33: Moving to the Cube Designer ...

35.  Within the Cube Designer, on the Cube Structure tab, and inside the Dimensions pane (on the left side of the Cube Structure tab, underneath the Measures tab) right click the cube (ANSYS045 - Named Calculations) atop the Hierarchy tree.

36.  Select Add Cube Dimension ... from the context menu that appears, as shown in Illustration 34.


Illustration 34: Adding a Dimension to the Cube ...

37.  Select Aged Period in the list labeled Select dimension from the Add Cube Dimension picklist that that next appears, as shown in Illustration 35.


Illustration 35: Adding the Aged Period Dimension ...

38.  Click OK.

The Add Cube Dimension dialog disappears, and we see the three dates (Due, Ship and Order Dates) reproduce themselves within the Hierarchies tab. Each sports an added numeral "1" to distinguish it from the respective original (each of which is a member of another hierarchy).

The dates appear as depicted in Illustration 36.


Illustration 36: Three New Dates Appear

Because we are only interested in the Ship Date for aging purposes, per our discussion earlier, we will discard the other two (although their classification into "aging buckets" might themselves add value for other reasons).

39.  Right-click the new Due Date 1.

40.  Select Delete from the context menu that appears, as shown in Illustration 37.


Illustration 37: Deleting an Unwanted Date

41.  Confirm our wishes to delete Due Date 1 by clicking OK on the Delete Objects confirmation dialog that appears next, as depicted in Illustration 38.


Illustration 38: Confirming the Deletion Action ...

We return to the Hierarchies tab, from which Due Date 1 has disappeared.

42.  Right-click the new Order Date 1.

43.  Select Delete from the context menu that appears, once again.

44.  Confirm our wishes to delete Order Date 1 by clicking OK on the Delete Objects confirmation dialog that appears next, as before.

We again return to the Hierarchies tab, where both unwanted dates have been removed.

45.  Right-click the remaining new Ship Date 1.

46.  Select Rename from the context menu that appears, as shown in Illustration 39.


Illustration 39: Renaming the Hierarchy that Appears in the Cube ...

47.  Rename the Hierarchy to the following:

Trans Date

The Hierarchies pane, with our latest adjustments, appears as depicted in Illustration 40.


Illustration 40: The New Hierarchy within the Hierarchies Pane

We are now ready to verify that the dimension and hierarchy that we have put into place are structured as we expect. Once we ascertain that our Ship Dates appear to have been "collected" into the appropriate "aging buckets," we will be ready to build / deploy our project, process our cube, and take a look at the effects of our work via the cube browser within the Cube Designer.

Verification

Review the "Aging Buckets" within the Dimension Designer to Ascertain that the Structure Appears as Intended

We can get to the Dimension Designer quickly from the Hierarchy pane by taking the following steps.

1.  Expand the new Aged Period hierarchy in the Hierarchies pane, by clicking the "+" sign to its immediate left.

2.  Click the Edit Aged Period link that appears immediately under the expanded Aged Period hierarchy, as shown in Illustration 41.


Illustration 41: Editing the New Hierarchy ...

We arrive within the Dimension Designer for the new dimension, from which we can easily browse the Hierarchy structure to see that it will meet our needs.

3.  From the present location within the Dimension Designer for Aged Period.dim, click the Browser tab.

4.  Expand the All level of the hierarchy to expose the "aging buckets" of the Aged Period dimension, as depicted in Illustration 42.


Illustration 42: The "Aging Buckets" Appear

5.  Expand the < 30 Aged Period "bucket" to expose the Ship Date members within it, as partially shown in Illustration 43.


Illustration 43: The Ship Date Members Appear (Partial View)

6.  Expand additional "buckets," as desired, to inspect their member Ship Dates.

We see that, from the perspective of Ship Date ranges stored within the Adventure Works cube, the "buckets" seem to be collecting correctly.

Deploy the Project, Process the Cube and Review the Aging Capabilities within the Cube Browser

Let’s deploy the project, and then process the cube. Once this has been accomplished, we can fully leverage the browser that the Cube Designer makes available to us.

1.  Select Build --> Deploy Solution from the main menu, as depicted in Illustration 44.


Illustration 44: Select Build --> Deploy 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 shown in Illustration 45.


Illustration 45: Indication of Successful Deployment Appears ...

2.  Right-click the cube within the Solution Explorer tree.

3.  Select Process ... from the context menu that appears, as depicted in Illustration 46.


Illustration 46: Select Process ...

The Process Cube dialog appears. We will leave settings at default, including the Full Processing designation.

4.  Click Run ... in the bottom right corner of the dialog (as shown in Illustration 47) to begin cube processing.


Illustration 47: Click Run ...

Processing begins immediately, and the Process Progress viewer appears, displaying the logged events. Processing completes, and the viewer presents the green Process succeeded message within the Status bar, as depicted in Illustration 48.


Illustration 48: Process Completes Successfully, as Indicated on the Process Progress Viewer

5.  Click Close to dismiss the viewer.

6.  Click Close to dismiss the Process Cube dialog.

We can get to the Browser, at this stage, and see the effects of our handiwork within the cube, by taking the following steps.

7.  Click the ANSYS045 - Named Calculations.cube tab within the design environment, once again.

8.  Click the Browser tab within the Cube Designer, as shown in Illustration 49.

Click for larger image

Illustration 49: Click the Browser Tab ...

9.  Select Cube ---> Reconnect from the main menu, as depicted in Illustration 50, to ensure that we are browsing an updated copy.


Illustration 50: Reconnect the Cube ... "To Ensure Freshness"

10.  Within the tree appearing inside the Metadata pane to the left, expand Dim Sales Territory by clicking the "+" sign to its immediate left.

11.  Drag Dim Sales Territory.Sales Territory Group to the area marked Drop Row Fields Here within the pivot table area, as shown in Illustration 51.


Illustration 51: Creating a PivotTable Browse ...

12.  Within the Metadata pane, once again, expand Aged Periods.

13.  Drag Hierarchy to the area marked Drop Column Fields Here within the pivot table area.

14.  Expand Measures.

15.  Expand the Fact Reseller Sales folder.

16.  Drag Sales Amount to the area marked Drop Totals or Detail Fields Here within the pivot table area.

17.  The Browser pivot table area appears as depicted in Illustration 52.


Illustration 52: The Browser Pivot Table with Additions ...

We can see that the Sales Amounts totals appear within their respective "aging buckets."

18.  Expand the < 30 "aging bucket," by clicking the "+" sign to its immediate left.

The < 30 Aged Period expands to reveal the totals, by Ship Date, that make it up, as partially shown in Illustration 53.


Illustration 53: The Sales Amounts Totaled by Sales Date (Partial View)

We can visualize an array of reporting capabilities at this point, once we add formatting, subtotal and other nuances. For now, we will conclude our article, having focused upon the construction of one approach to the creation of an aging capability, using Named Calculations to extend the data underlying our cube to afford us a presentation capability that we might not otherwise have had.

19.  Examine other features of the Aged Period dimension within the Cube Browser as desired.

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


Illustration 54: Saving Our Work from the Session

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

Conclusion

In this "second half" of a two-part article, we continued the examination of Named Calculations we began in Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I. We briefly summarized our introduction in Part I, where we focused initially upon the Data Source View as a significant design and development enhancement within Analysis Services, and then overviewed our stated objective to examine Named Calculations. Moreover, we reviewed our initial steps in the construction of working examples, based upon an illustrative business need to create "aging buckets" within our cube to support a reporting and analysis capability.

In this article, we returned to the Named Calculations we had created in Part I, and next created a Cube containing data from our sample relational tables. We then added a Dimension based upon two of our Named Calculations, to contain "aging buckets. We reviewed our new Aged Periods dimension to ascertain that its structure appeared as expected, and then we deployed our solution, and processed our cube. Finally, we browsed the Cube, focusing on the new aging dimension structures, to verify their operation within the design environment.

» 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