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
About the Series ...
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
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:
Server 2005 Database Engine
Server 2005 Analysis Services
Server 2005 Integration Services
Intelligence Development Studio
Server 2005 sample databases
The Analysis Services
Tutorial sample project and other samples that are available with the installation
of the above.
successfully replicate the steps of the article, you may also need to have:
within one of the following:
Read permissions within any SQL
Server 2005 sample databases we access within our practice session, as
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.
more information about the Mastering Enterprise BI articles, see
the section entitled "About the
Enterprise BI Articles" in my article Relative
Time Periods in an Analysis Services Cube, Part I.
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
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
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
Browse the Cube,
focusing on the new aging dimension structures.
Introducing Named Calculations (Continued...)
Overview and Discussion
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
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
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).
Restart the SQL Server Business Intelligence
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
--> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 1.
Selecting a Project ...
Project dialog appears.
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
I to participate
in this practice session.
Select the ANSYS045 Named Calculations.sln
file within the appropriate folder, as depicted in Illustration 2.
Illustration 2: Selecting
the Part I Solution File ...
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.)
environment appears as depicted in Illustration 3.
Illustration 3: We
Return to the Design Environment
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.
the Cubes folder within the Solution Explorer.
Cube from the context menu that appears, as shown in Illustration 4.
The Welcome to the
Cube Wizard dialog appears, as depicted in Illustration 5.
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.
default settings are shown in Illustration 6.
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.
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 Select Data Source
View dialog appears, as depicted in Illustration 7.
Illustration 7: Select
AdventureWorks DW as the Data Source View
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 ...
Fact and Dimension Tables dialog next appears. Lets select a Time
dimension table, and narrow the table selection somewhat, for the purposes
of our rudimentary cube.
Select the DimTime
table in the drop-down Time dimension table selector.
Uncheck the following checkboxes:
Fact Column: DimReseller (leave checked within Dimension
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
Time Periods dialog appears.
unspecified columns blank, select the Time Table Columns values shown in
Table 1 for each of the listed Time Property Names.
Time Property Name
1: Selections within Respective Time Table Columns in Select Time Periods
The Select Time Periods dialog appears, with our selections, as
shown in Illustration
Illustration 10: The
Select Time Periods Dialog, with Our Selections
Measures dialog appears next.
Uncheck the following checkboxes:
The Select Measures dialog appears, as depicted in Illustration 11.
Illustration 11: The
Select Measures Dialog, with Adjusted Selections
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
move to the Review New Dimensions page, which appears as depicted in Illustration
Illustration 13: The Review
New Dimensions Page
the Wizard page appears next.
default Cube name to the following:
ANSYS045 - Named Calculations
the Wizard page appears as shown in Illustration 14.
Illustration 14: The Completing
the Wizard Page
Wizard concludes, and the Cube Designer opens, as depicted in Illustration
Illustration 15: The
Cube Designer Opens ...
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, lets 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
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.
the Dimensions folder within Solution Explorer.
Dimension from the context menu that appears, as shown in Illustration 16.
Illustration 16: Select
New Dimension from the Context Menus
to the Dimension Wizard page appears, as depicted in Illustration 17.
Illustration 17: The
Welcome to the Dimension Wizard Dialog
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.
De-select the Auto
Build Method page appears,
as shown in Illustration 18.
Illustration 18: The
Select Build Method Page with Our Selections
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
on the Select the Dimension Type page that appears next, as shown in
Selecting the Standard Dimension Type ...
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
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.
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.
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.
22. Replace the Name on the Completing
the Wizard page, which appears next, with the following:
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
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
25. Select Rename from the
context menu that appears, as depicted in Illustration 27.
Renaming the Attribute ...
26. Replace the existing name with the
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
28. Select Rename, as before.
29. Replace the existing name with the
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
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
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.
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
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 ...
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 ...
return to the Hierarchies tab, from which Due Date 1 has
42. Right-click the new Order Date
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
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
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.
the "Aging Buckets" within the Dimension Designer to Ascertain that
the Structure Appears as Intended
get to the Dimension Designer quickly from the Hierarchy pane by
taking the following steps.
Expand the new
Aged Period hierarchy in the Hierarchies pane, by clicking the "+"
sign to its immediate left.
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 ...
arrive within the Dimension Designer for the new dimension, from which
we can easily browse the Hierarchy structure to see that it will meet
present location within the Dimension Designer for Aged Period.dim,
click the Browser tab.
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
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)
"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.
the Project, Process the Cube and Review the Aging Capabilities within the Cube
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.
--> 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 ...
the cube within the Solution Explorer tree.
... from the context menu that appears, as depicted in Illustration 46.
Illustration 46: Select
Cube dialog appears. We will leave settings at default, including the Full
Click Run ...
in the bottom right corner of the dialog (as shown in Illustration 47)
to begin cube processing.
Illustration 47: Click
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
to dismiss the viewer.
to dismiss the Process
get to the Browser, at this stage, and see the effects of our handiwork
within the cube, by taking the following steps.
Click the ANSYS045
- Named Calculations.cube tab within the design environment, once again.
Click the Browser
tab within the Cube Designer, as shown in Illustration 49.
---> 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"
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
16. Drag Sales Amount to the
area marked Drop Totals or Detail Fields Here within the pivot table
17. The Browser pivot table
area appears as depicted in
Illustration 52: The
Browser Pivot Table with Additions ...
We can see
that the Sales Amounts totals appear within their respective "aging
18. Expand the < 30 "aging
bucket," by clicking the "+" sign to its immediate left.
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)
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
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.
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
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.