Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I - Page 6

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.

The Create Named Calculation dialog appears.

ANSYS045_Gender Description

ANSYS045_Gender Description

CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END

The Create Named Calculation dialog appears, with our input, as shown in Illustration 26.

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.

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.

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.

We are returned to the Data Source View Designer.