- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 416字
- 2021-08-13 17:55:23
Time for action – creating a Named Calculation
In this exercise, we will create a new calculated column in the DSV for the full name because the customer dimension only contains the first name and last name separately. You will see how we can use T-SQL commands to create a Named Calculation. Perform the following steps to create a Named Calculation.
- Go to the Adventure Works DW2012 DSV designer.
- In the Customer dimension, you can see that there are three name columns as First name, Middle name, and Last name, but there is no full name. We want to have the full name shown as the main customer attribute of the customer dimension in the cube. So, we will create a column in DSV for that.
- Right-click on DimCustomer and select New Named Calculation.
- Set the column name as Full Name, and write the following code in the expression area:
FirstName+' '+ISNULL(MiddleName+' ','')+LastName
- Click on OK and you will see the new column added to the table. Right-click on DimCustomer and select Explore Data. Some sample data rows from the Customer table will be shown in another window and you can also see the Full Name column populated at the end of the column's list as well.
- Go to the Customer Dimension Designer, select the Customer Key attribute, and change the NameColumn to Full Name from DimCustomer. Also, rename the
CustomerkeytoCustomer. - Browse the Customer dimension (after deploying and processing), and you will see that the full name of the customer appears as Customer Values in the browser.
What just happened?
Named Calculations are very useful when you want to add calculated columns to the underlying database, but for some reason, you cannot do that in the database. For example, your user account only has read-only permissions on the database. In such cases, you can benefit by creating Named Calculations.
As you've seen in this example, Named Calculations are created based on the expression area. The language of the expression is based on the underlying database. This means that if the underlying database is an Oracle database, you can use functions such as rownum or decode, which work in an Oracle environment. If the underlying database is SQL Server, you can use functions and T-SQL codes that are acceptable in that SQL Server version.
You also saw how to check the result of the execution of Named Calculations with the Explore Data option. You can also change the sampling settings for the explored data.