Add Named Calculations within the Data Source View Designer
The capability to add Named
Calculations within the Data Source View is yet another "abstract
layer" convenience offered in Analysis Services 2005. A Named
Calculation allows us to insert a SQL expression that is then sent to the
provider, allowing us to "extend" the existing data source for added
flexibility. Whereas we were limited to decidedly meager avenues for the insertion
of expressions within the Analysis Manager of the previous version (see
my articles Derived
Measures vs. Calculated Measures and Mastering
Enterprise BI: Create Aging "Buckets" in a Cube, within
this series,
as a couple of examples), the new option to add Named Calculations
within the Data Source View Designer provides a far more robust,
articulate means of achieving what amounts to extending our relational tables
or views.
There are several ways to
add / modify Named Calculations from within the Data Source View Designer,
as we shall see, including:
-
Clicking New
Named Calculation in the Toolbar pane;
-
Right-clicking
a table in the Tables pane of the Data Source View Designer and
selecting New Named Calculation ... from the context menu that appears;
-
Right-clicking
a table in the Diagram pane of the Data Source View Designer and
selecting New Named Calculation ... from the context menu that appears;
-
Right-clicking
a defined Named Calculation in the Diagram pane of Data Source
View Designer, and selecting Edit Named Calculation from the context
menu that appears.
We can reference tables
outside the schema defined within our Data Source View, as well. This,
combined with the capability to use elaborate SQL expressions, places
unprecedented power at this abstract layer, where we can place "calculated
columns" to enhance the underlying tables and views.
A Simple Named
Calculation: Create a Member Description with CASE
Let's
create a simple Named Calculation to provide a member name / description
where, at present, only a single character is available from within the
relational source, the DimEmployee table.
1.
Click the DimEmployee
table to select it, within the Adventure Works DW.dsv [Design] tab.
2.
Click the New
Named Calculation button in the Designer Toolbar pane, as depicted
in Illustration 25.
Illustration 25: Click
the New Named Calculation Button in the Toolbar Pane ....
The Create
Named Calculation dialog appears.
3.
Type the
following into the Column Name box, atop the dialog:
ANSYS045_Gender Description
4.
Input the
following into the Description box:
ANSYS045_Gender Description
5.
Type the
following into the Expression box, atop the dialog:
CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END
The Create Named
Calculation dialog appears, with our input, as shown in Illustration 26.
Illustration 26: The
Create Named Calculation Dialog with Our Input
6.
Click OK
to create the new Named Calculation.
The dialog closes, and we
can see the Named Calculation within the DimEmployee table, where
it appears at the end of the listed columns, as depicted in Illustration 27.
Illustration 27: The New
Named Calculation in the DimEmployee Table
Let's
take a look at the new Named Calculation at the data level – a great way
to ascertain the effectiveness of our SQL expression. (The Create Named
Calculation dialog will arrest an attempt to create a Named Calculation with
a dialog / expression that is incomplete or if the Expression box
contains syntactical errors, but it can hardly be expected to check our logic,
should we happen to "correctly specify" something we do not intend).
The Data Source View provides a way to browse a subset of the data from
the perspective of the abstract layer, with the Explore Data option.
7.
Right-click
the DimEmployee table within the Data Source View Designer.
8.
Select Explore
Data from the context menu that appears, as shown in Illustration 28.
Illustration 28: Click
Select Explore Data from the Context Menu ....
The Load
Sample Data message box briefly appears, and then a new tab appears within BI
Development Studio. Within this tab, named Explore DimEmployee Table,
we can browse table data – including our Named Calculation, as we shall
see.
9.
On the Explore DimEmployee Table tab, scroll to the rightmost
column, ANSYS045_Gender Description.
ANSYS045_Gender
Description appears,
similar to what is partially depicted in Illustration 29.
Illustration 29: The New
Named Calculation Appears within the Explore Tab
We can
easily see that the Named Calculation is performing its intended
objectives by contrasting its member fields to the associated fields in the Gender
column to its left. This Named Calculation might be useful as one
approach to provide a description versus the single-letter held in the original
table, for, as an example, a member name definition within a cube.
10.
Right-click
the Explore DimEmployee Table tab.
11.
Select Close
from the context menu that appears, as shown in Illustration 30.
Illustration 30: Closing
the Explore DimEmployee Table Tab ...
We are
returned to the Data Source View Designer.
» 