Using the Calculation Wizard

You can use the Calculation Wizard to create new calculation columns that compare two existing columns, and also to create metrics in bulk.

It has a built-in mechanism to handle divide-by-zero and null cases, as well as other difficult situations. The Calculation Wizard provides an automated way to calculate the sales by quarter, the percentage of revenue, minimum and maximum values, and so on.

In the Calculation Wizard, the New Calculation page select from the following options:

  • Change (CurrentX - ComparisonX). Subtract the value of the comparison column from the source column.

    For example, for the Change calculation, you can choose whether to return NULL or some other value when the comparison column is NULL.

  • Percent Change (100.0 * (CurrentX - ComparisonX) / ComparisonX). Subtract the value of the comparison column from the source column and express as a percentage.

  • Index (1.0 * CurrentX / ComparisonX). Divide the source column by the comparison column.

  • Percent (100.0 * (CurrentX / ComparisonX)). Divide the source column by the comparison column and express as a percentage.

The image shows the Calculation Wizard, the New Calculation page.

Description of GUID-857CF51D-5404-4D65-BA75-913AE4AF5AFF-default.gif follows
Description of the illustration GUID-857CF51D-5404-4D65-BA75-913AE4AF5AFF-default.gif
  1. In the Business Model and Mapping layer, right-click any logical fact or dimension column of data type numeric measure column, and then select Calculation Wizard.
  2. Click Next
  3. In the Select Columns, choose the columns that you want to compare with the source column.

    If the source column is mapped to multiple logical tables, a list of tables appears in the upper left pane. Select a table, then select a column or columns from the upper right pane to add comparison columns to the Selected Columns list.

  4. ClickRemove to take items out of the selected list.
  5. Click Next.
  6. In the New Calculations screen, choose the calculations to perform for specific columns.
  7. Click Next when you have finished creating calculations.
  8. When the repository is open in online mode, the Checkout screen appears. In online mode, objects need to be checked out before you can make changes to them. Click Next to check out the necessary objects.
  9. The Finish screen displays a summary of the calculations that will be created. If you want to make changes, click Back, or select a particular step from the navigation panel.
  10. Click Finish. The new calculation columns are created.