A Calculated Column by Any Other Name

This week I will look a little more into how to manipulate the data going into the cube by showing you how to create a Named Calculation that you can then surface in the pivot table.  While it is called a Named Calculation in SSAS, it is essentially the same thing as a Calculated Column.  So let’s begin.

Named Calculations are defined in the Data Source View.  In this case, I’m opening my Contoso Retail data source view that I’ve used for the last two weeks and want to focus on the FactSales table.

Notice in this table that there are quite a few fields related to costs, prices, quantities, etc. for each sale.  One field that is missing is the Total Profit field. If we want to perform analysis on profit as a function of the sales channel, we need to create a new measure for Total Profit.  Of course we could have done this back in the original database.  However, suppose that for some reason, this is not allowed.  Therefore, we need to calculate this field in the SSAS cube project instead.  Using the existing fields from the FactSales table, we know that we could calculated the Total Profit using an expression such as:

(UnitPrice – UnitCost – DiscountAmount) * (SalesQuantity – ReturnQuantity)

Therefore, we can open the Data Source View and right click the FactSales header in the diagram and select the command: New Named Calculation.

This command opens the dialog: Create Named Calculation as shown in the next figure.  In this dialog, you must enter a column name for the new column.  Obviously, it must be a unique name for the table.  You must also specify the expression.  The expression language is determined by the native expression language of the database.  Since my data is in SQL Server, that means T-SQL.  You can also provide a Description.  However, that field is optional.

If you click OK in this dialog and then redeploy the cube, you should on successful deployment be able to open the cube and add Total Profit to the Totals or Detail Fields area of the pivot table.

In the figure below, I also added as a row dimension the Channel Key which I mapped to the Channel Name field to provide a user friendly name.  However, you may remember that we mentioned this problem previously.  The raw formatting of the summed data makes the values hard to read and interpret.

You could specify a format like we did last week by opening the cube and using the Perspectives or Translations tab to change the properties.  Another way to achieve the same result is through the Cube Structure tab (the first one on the left).  If you click on the fact table where you want to format the measure values and then click on the Show Measures Grid icon in the toolbar ( ), you can display the fields in a grid as shown in the following figure.

To change the formatting of a field, select the field by clicking on it as shown in Total Profit above.  (You can also select multiple fields by holding the Ctrl key while clicking on other fields that you want to format the same way.)  Then right click on any of the selected field rows to display the menu of options.  Select Properties from this menu.

In the Properties panel, as shown below, find the property FormatString.  Click anywhere in the right column for this property to display the dropdown arrow.  Click the dropdown arrow to select one of the predefined formats.  (You can also define your own formatting.)  In this example, I selected the predefined format Currency which displays a leading dollar sign and two decimal places with commas separating the thousands.

When you are done, right click on the project in the Solution Explorer to display the project menu and select Deploy to redeploy the cube with your changes.

After the cube deploys, click on the Browser tab of the cube to display the pivot table.  If you have a measure field already selected in the pivot table, the change in formatting will not affect that field until you remove the field and then add it back again to the pivot table.  If all the stars align, you should see a pivot table like the following one with values formatted as you expected.

That’s all for this week since I’m at the Orlando SharePoint Saturday today.  Next time, I will look at how to created named queries to define custom tables for your cube.

C’ya then.