New Named Calculation in SSAS DSV

Friends,

Named calculations are Virtual columns that can be added on tables in Data Source View(DSV). This named calculation acts as a Column in your table and this column won’t be reflected in your database. If you wish to extend the relational schema of a table or a view without modifying the underlying data source table or view then New Named Calculation is the correct option. One more and main advantage of “Named Calculation” is that these are calculated at the time of PROCESSING but not at QUERY Time and hence the performance will be good. The Named Calculation consists of the following things.

1) Name – Name of the Virtual Column

2) Description – Description about the Virtual Column(Optional).

3) Expression – Valid SQL Expression.

You can use one or more columns to define the expression for Named Calculation. For example to get full name as a new virtual column we have to use the below expression.

Title+’ ‘+FirstName+’ ‘+MiddleName+’ ‘+LastName

The above expression is using four columns to define Full Name Virtual column. PFB the steps to be followed to create the named calculation.

1. Right-click the table and select Create a named calculation.
2. Enter the calculation name as “Full Name” and enter a description.
3. Enter the following expression:

Title+’ ‘+FirstName+’ ‘+MiddleName+’ ‘+LastName

4. Once the OK button is clicked it will show if there are any errors in syntax. If there are no errors then you can see new Virtual column named “Full Name” with an icon “CALCULATOR” next to it as shown in below screenshot.

5. If you wish to test whether you are getting data as expected or not then you can you “EXPLORE DATA” option as shown in the below screenshot.

This is how you can use calculations. If you use the same expression which is given above to concatenate columns then you may not get desired result if we have NULLs in data. For example if Last Name is NULL for a column the <Some Value> + NULL is treated as NULL by SQL and hence we have to handle NULLs in expressions when we are working with String data. PFB the updated query which handle NULLs.

isnull(Title,”)+’ ‘+isnull(FirstName,”)+’ ‘+isnull(MiddleName,”)+’ ‘+isnull(LastName,”)

In the above expression I am checking whether the value is NULL or not and if it is NULL then I am replacing it with ‘BLANK Value.

Note – It is not mandatory that you have to use at least one column from table to create an Expression. The following are also Valid Expressions.

1) getdate()

2) 1 or any integer

3) ‘Dumy’ or any string

In short any valid SQL expression can be given as Named Calculation Expression.

That’s it and Happy Coding !

Regards,

Roopesh Babu V