Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I - Page 6
March 13, 2006
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:
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.
The Create Named Calculation dialog appears.
3. Type the following into the Column Name box, atop the dialog:
4. Input the following into the Description box:
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.
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.
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.
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.
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.
We are returned to the Data Source View Designer.