17 Using Expression Builder and Other Utilities

This chapter describes Expression Builder and provides instructions for creating constraints, aggregations, and other definitions within a repository. It also describes the various utilities and wizards contained in the Administration Tool.

This chapter contains the following topics:

Using Expression Builder
Using Administration Tool Utilities
Using the Calculation Wizard

Using Expression Builder

You can use the Expression Builder dialogs in the Administration Tool to create constraints, aggregations, and other definitions within a repository. Expression Builder provides automatic color highlighting and other formatting enhancements to make expressions easier to build and to read.

The expressions you create with Expression Builder are similar to expressions created with SQL. Except where noted, you can use all expressions constructed with Expression Builder in SQL queries against the Oracle BI Server.

For information about using SQL with Expression Builder, and for information about the SQL functions supported by the Oracle BI Server, see Appendix C, "Logical SQL Reference."

This section contains the following topics:

About the Expression Builder Dialogs
About the Expression Builder Toolbar
About the Categories in the Category Pane
Setting Up an Expression

About the Expression Builder Dialogs

You can access Expression Builder from the following dialogs:

Logical Table Source—Content tab
Logical Table Source—Column Mapping tab
Logical Column—General tab
Logical Column—Aggregation tab
Logical Foreign Key
Physical Foreign Key
Session Variable
Repository Variable

Figure 17-1 shows Expression Builder.

Figure 17-1 Example Expression Builder Dialog

This image is described in the surrounding text.

Description of "Figure 17-1 Example Expression Builder Dialog"

The dialog contains the following sections:

The edit pane on the right hand side of the dialog lets you edit the current expression.
The toolbar at the bottom of the dialog contains commonly used expression operators.
In the left section of the dialog:
- The top pane is the Category pane. It displays categories that are appropriate for the dialog from which you accessed Expression Builder.
- The middle pane displays a list of available items for the category you selected in the Category pane.
  
  You can use the Find field below the middle pane to display specific values in the middle pane.
- The lower pane is the Building Blocks pane. It displays the individual building blocks for the item you selected in the middle pane.
  
  You can use the Find field below the lower pane to display specific values in the lower pane.

When creating expressions in Expression Builder, you select a category from the Category pane and values are displayed in the lower panes depending on the value selected in the Category pane. When you type a value into a Find field, it filters out the non-matching strings and displays matching strings only. After typing search criteria in a Find field, you can move up and down the list using the scroll bar, and use the tab key to move between the Find fields. To return to the full list of results, delete the string from the Find field.

Note that you can only enter text in the Find field that matches the text of one of the available strings. For example, if the available string options begin with A11, A12, and A13, the text you enter in the Find field must begin with A.

When you locate the building block you want to insert into the expression, select it and do one of the following:

Click the arrow button
Double click the item
Press Enter on your keyboard

The building block you selected appears in the expression in the edit pane.

When you first open Expression Builder, the items are not sorted. When selected, the Sort Panes option sorts all items in the panes. As soon as you select this option, the panes are automatically redrawn without changing the contents of the panes or your filtering criteria.

About the Expression Builder Toolbar

The toolbar is located at the bottom of Expression Builder. Table 17-1 describes each button and its function in an expression.

Table 17-1 Expression Builder Toolbar

Operator	Description
+	Plus sign for addition.
-	Minus sign for subtraction.
*	Multiply sign for multiplication.
/	Divide by sign for division.
\|\|	Character string concatenation.
(	Open parenthesis.
)	Close parenthesis.
>	Greater than sign, indicating values higher than the comparison.
<	Less than sign, indicating values lower than the comparison.
=	Equal sign, indicating the same value.
<=	Less than or equal to sign, indicating values the same or lower than the comparison.
>=	Greater than or equal to sign, indicating values the same or higher than the comparison.
<>	Not equal to, indicating values higher or lower, but different.
AND	`AND` connective, indicating intersection with one or more conditions to form a compound condition.
OR	`OR` connective, indicating the union with one or more conditions to form a compound condition.
NOT	`NOT` connective, indicating a condition is not met.
,	Comma, used to separate elements in a list.

About the Categories in the Category Pane

The categories that appear in the Category pane vary, depending on the dialog from which you accessed Expression Builder. Table 17-2 describes the categories that may appear.

Table 17-2 Expression Builder Categories in the Category Pane

Category Name	Description
Aggregate Content	Contains the available aggregate functions. Aggregate sources must use one of the functions listed here to specify the level of their content.
Time Dimensions	Contains the time dimensions configured in the business model. If no time dimensions exist in a business model, or if time dimensions are not pertinent to a particular Expression Builder, the Time Dimensions category is not displayed. When you select the Time Dimensions category, each configured time dimension appears in the middle pane, and each level for the selected dimension appears in the lower pane.
Logical Tables	Contains the logical tables configured in the business model. If logical tables are not pertinent to a particular Expression Builder, the Logical Tables category is not displayed. When you select the Logical Tables category, each logical table in the business model appears in the middle pane, and each column for the selected logical table appears in the lower pane.
Value Based Dimensions	Contains the dimensions with parent-child hierarchies configured in the business model. If no dimensions with parent-child hierarchies exist in a business model, or if dimensions with parent-child hierarchies are not pertinent to a particular Expression Builder, the Value Based Dimensions category is not displayed. When you select the Value Based Dimensions category, the configured dimensions with parent-child hierarchies appear in the middle pane. No lower pane exists for this category.
Logical Levels	Contains the related logical levels. If level-based dimensions are not pertinent to a particular Expression Builder, the Logical Levels category is not displayed. When you select the Logical Levels category, you can then select the appropriate logical dimension (level-based) in the middle pane, and the level itself in the lower pane.
Physical Tables	Contains the related physical tables. If physical tables are not pertinent to a particular Expression Builder, the Physical Tables category is not displayed.
Operators	Contains the available SQL logical operators.
Expressions	Contains the available expressions.
Functions	Contains the available functions. The functions that appear depend on the object you selected.
Constants	Contains the available constants.
Types	Contains the available data types.
Repository Variables	Contains the available repository variables. If no repository variables are defined, this category does not appear.
Session Variables	Contains the available system session and non-system session variables. If no session variables are defined, this category does not appear.

Setting Up an Expression

Figure 17-2 shows the Expression Builder dialog for a derived logical column.

Figure 17-2 Expression Builder for Derived Logical Columns

This image is an example of the populated screen.

Description of "Figure 17-2 Expression Builder for Derived Logical Columns"

To set up an expression, select Functions from the Category pane, select a function type from Functions pane, then select a function from the lower pane. Double-click the function you want to use to paste it in the edit pane. Then, in the edit pane, click once between the parentheses of the function to select that area as the insertion point for adding the argument of the function.

To paste a logical column at the insertion point, select Logical Tables from the Category pane, select the table you want to use in the Logical Tables pane, and then double-click the logical column in the lower pane to paste the logical column at the insertion point as the argument of the function in the edit pane. Figure 17-3 shows where the expression appears in the edit pane.

Figure 17-3 Example Logical Column Function in the Editing Pane

Description of "Figure 17-3 Example Logical Column Function in the Editing Pane"

Navigating Within Expression Builder

Use the following procedure to navigate within Expression Builder.

To navigate within Expression Builder:

In the Category pane, select the appropriate category for the expression you want to build.

The available expression types for the selected category appear in the middle pane.
Select the appropriate item for the expression you want to build.

The available building blocks for the selected item appear in the lower pane.
Double-click a building block to display it in the edit pane.
To insert an operator into the expression, click an operator on the Expression Builder toolbar.

Building an Expression

Use this procedure to build an expression in Expression Builder.

To build an expression:

Navigate to the individual building blocks you want in the expression.

The Syntax bar at the bottom of the Expression Builder dialog shows the syntax for the expression.

For example: BETWEEN <<Upper Bound>> AND <<Lower Bound>>
Add the building blocks to the edit pane.
Edit the building blocks to reflect the expression you want.
Use the Expression Builder toolbar to insert operators into the expression.
Repeat the preceding steps until the expression is complete, and then click OK.

The Administration Tool displays a message for any syntax errors in the expression. When the expression is syntactically correct, the Administration Tool adds the expression to the dialog from which you accessed Expression Builder.

Note that if the parameter PREVENT_DIVIDE_BY_ZERO is set to YES in NQSConfig.INI, the Oracle BI Server prevents errors in divide-by-zero situations, even for Answers column calculations. The Oracle BI Server creates a divide-by-zero prevention expression using nullif() or a similar function when it writes the physical SQL. Because of this, you do not have to use CASE statements to avoid divide-by-zero errors, as long as PREVENT_DIVIDE_BY_ZERO is set to YES (the default value).

See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about configuration settings.

About the INDEXCOL Conversion Function

The INDEXCOL function enables you to build a derived logical column. Selecting INDEXCOL automatically generates the following function template:

IndexCol( <<integer literal>>, <<expr1>> [, <<expr2>>, ?-] )

Note: The argument integer literal can also be a session variable, an arithmetic expression, or a CASE WHEN statement (evaluation must be possible without reference to back-end data).

See "INDEXCOL" for more information.

Using Administration Tool Utilities

In addition to Expression Builder, the Administration Tool provides several utilities and wizards that perform functions like renaming objects, persisting aggregates, and externalizing strings.

This section contains the following topics:

Using the Replace Column or Table Wizard
Using the Oracle BI Event Tables Utility
Using the Externalize Strings Utility
Using the Rename Wizard
Using the Update Physical Layer Wizard
Generating Documentation of Repository Mappings
Generating a Metadata Dictionary
Removing Unused Physical Objects
Persisting Aggregates

Using the Replace Column or Table Wizard

The Replace Column or Table Wizard automates the process of replacing physical columns or tables in logical table sources. For example, if you have purchased Oracle BI Applications, you can update your logical table sources to map to a different database type. You can also use this utility to change logical table source mappings from a development table to a production table.

You can use the Replace Column or Table Wizard to replace a single column (within the same table), or to replace an entire table. If you replace a table, you must map all the columns in the table.

To replace a physical column in logical table sources:

In the Administration Tool, select Tools, then select Utilities.
Select Replace Column or Table in Logical Table Sources and click Execute.
In the Select Object screen, select Replace single column only.
In the left pane, select the physical column that you want to replace. You must select a valid column. For example, you cannot select a column that is used in a logical table source that has more than one table as a source.
In the right pane, select the physical column that you want to use as a replacement for the original column. Then, click Next.
The Select Sources screen shows all logical table sources that map to the physical column you selected. Select the logical table sources in which you want to change the physical column mapping. Select Show Qualified Names to see the full context for each source.

If you select an invalid logical table source, or in other words, one that cannot be used for replacement, a message appears explaining why that source cannot be used, and the check box for that source is disabled.

Note that invalid logical table sources do not appear in the list when Hide unusable logical table sources in Replace wizard has been selected in the General tab of the Options dialog. Instead, the Info button is displayed when a logical table source that maps to that column does not appear in the list. Click Info to see details on why the physical objects could not be replaced in the logical table source or sources.

The Select Sources screen only appears if there are multiple logical table sources that map to the physical column you selected.

Click Next after you have selected logical table sources.
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.
The Finish screen displays a summary of the objects that will be replaced. If you want to make changes, click Back, or select a particular step from the navigation panel.
Click Finish.

To replace a physical table in logical table sources:

In the Administration Tool, select Tools, then select Utilities.
Select Replace Column or Table in Logical Table Sources and click Execute.
In the Select Object screen, select Replace whole table.
In the left pane, select the physical table that you want to replace.
In the right pane, select the physical table that you want to use as a replacement for the original table. Then, click Next.
The Select Sources screen shows all logical table sources that map to the physical table you selected. Select the logical table sources in which you want to change the physical table mapping. Select Show Qualified Names to see the full context for each source.

If you select an invalid logical table source, or in other words, one that cannot be used for replacement, a message appears explaining why that source cannot be used, and the check box for that source is disabled.

Note that invalid logical table sources do not appear in the list when Hide unusable logical table sources in Replace wizard has been selected in the General tab of the Options dialog. Instead, the Info button is displayed when a logical table source that maps to that column does not appear in the list. Click Info to see details on why the physical objects could not be replaced in the logical table source or sources.

The Select Sources screen only appears if there are multiple logical table sources that map to the physical table you selected.

Click Next after you have selected logical table sources.
The bottom pane of the Select Columns screen shows individual column mappings between the selected physical tables. If column names in the two selected tables match, default column mappings appear in the bottom pane.

To add a column mapping to the list of mapped columns, first select a source column in the left pane. Then, select a replacement column in the right pane and click Add.

To remove a column mapping from the list of mapped columns, select a row of mapped columns from the list and click Remove.

Figure 17-4 shows the Select Columns screen.

Figure 17-4 Select Columns Screen of the Replace Column or Table Wizard

Description of "Figure 17-4 Select Columns Screen of the Replace Column or Table Wizard"
When you have finished mapping columns between the selected physical tables, click Next.
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.
The Finish screen displays a summary of the objects that will be replaced. If you want to make changes, click Back, or select a particular step from the navigation panel.
Click Finish.

Using the Oracle BI Event Tables Utility

This utility lets you identify a table as an Oracle BI event polling table. An event polling table is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

For more information about event polling tables, see "Cache Event Processing with an Event Polling Table" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To start the Oracle BI Event Tables utility:

In the Administration Tool, select Tools, then select Utilities.
Select Oracle BI Event Tables and click Execute.

Using the E xternalize Strings Utility

You can use the Externalize Strings utility to localize the names of Presentation layer subject areas, tables, hierarchies, columns, and their descriptions. You can save these text strings to an external file with ANSI, Unicode, and UTF-8 encoding options.

Before you can use the Externalize Strings utility, you must externalize strings in the Presentation layer. Note the following about externalizing strings in the Presentation layer:

You can right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and choose Externalize Display Names > Generate Custom Names or Externalize Descriptions > Generate Custom Descriptions to externalize strings.
Choosing one of these right-click externalization options automatically selects the Custom display name or Custom description options in the Properties dialog for the selected object and all of its child objects.

For example, if you right-click a subject area and choose one of the externalization options, the externalization flag is set on all presentation tables, columns, hierarchies, and levels within that subject area.
Running the Externalize Strings utility only externalizes those strings that have been selected for externalization in the Presentation layer.

For full information about using the Externalize Strings utility, see "Localizing Metadata in the Repository" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To start the Externalize Strings utility:

In the Administration Tool, select Tools, then select Utilities.
Select Externalize Strings and click Execute.

Using the Rename Wizard

You can use the Rename Wizard to rename tables and columns in the Presentation layer and Business Model and Mapping layer. It provides a convenient way to transform physical names to user-friendly names.

It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.

Be aware that when you use the Rename Wizard to rename presentation columns, the Use Logical Column Name property gets set to false.

To use the Rename Wizard:

In the Administration Tool, select Tools, then select Utilities. Then, select Rename Wizard and click Execute.

You can also access the Rename Wizard by right-clicking an object or set of objects in the Business Model and Mapping layer or Presentation layer, and then selecting Rename Wizard. The wizard starts in the Select Rules screen and only applies to the logical or presentation objects you selected.
In the Select Objects screen, select the objects you want to rename. First, select the layer that contains the objects (Presentation or Business Model and Mapping), then select an object and click Add. Click Add Hierarchy to add all objects associated with the selected object.

Click Next after you have selected the objects you want to rename.
In the Select Types screen, select the object types you want to rename, such as Subject Area, Logical Table, or Logical Column. Then, click Next.
In the Select Rules screen, select the renaming rules you want to apply and click Add. Select Change specified text to rename particular words or phrases.

The renaming rules are applied in the order in which they appear in the list. Select a rule that you have added and click Up or Down to change the order in which the rules will be applied.

For example, say you want to rename the logical columns GlobalGROUP, GlobalSales, and GlobalCustomerName to Group, Sales, and Customer Name. To achieve this, you can apply the following rules in the given order:
```
Insert space before each first uppercase letter, unless on the first position
or there is a space already
All text lowercase
First letter of each word capital
Change each occurance of "Global " to "" (not case sensitive)
```
Click Next after you have selected renaming rules.
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.
The Finish screen displays a summary of the objects that will be renamed. If you want to make changes to the list of renamed objects, click Back, or select a particular step from the navigation panel. Click Finish to rename the objects.

Figure 17-5 shows the Rename Wizard.

Figure 17-5 Rename Wizard

Description of "Figure 17-5 Rename Wizard"

Using the Update Physical Layer Wizard

You can use the Update Physical Layer Wizard to update database objects in the Physical layer of a repository, based on their current definitions in the back-end database. This wizard is not available for repositories that are open in read-only mode, because they are not available for updating.

When the wizard processes the update, the Oracle BI Server connects to each back-end database. The objects in the Physical layer are compared with those in the back-end database. Explanatory text alerts you to differences between objects as defined in the database in the Physical layer and as defined the back-end database, such as data type-length mismatches and objects that are no longer found in the back-end database. For example, if an object exists in the database in the Physical layer of the repository but not in the back-end database, the following text is displayed:

Object does not exist in the database and will be deleted

The wizard does not add columns or tables to the repository that exist in the back-end database, but not in the repository. Additionally, the wizard does not update column key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values do not match, the wizard updates the type and length of the column in the repository.

The connection pool settings for each database need to match the connection pool settings used when the objects were last imported into the Physical layer from the back-end database. See "Creating or Changing Connection Pools" for more information about connection pool settings.

To update objects in the Physical layer:

In the Administration Tool, select Tools, then select Utilities.
Select Update Physical Layer and click Execute.

The databases in the Physical layer of the repository are listed in the left pane of the wizard.
In the Select Database screen, select the databases that you want to update in the left pane, and then click Add. To remove a database from the update list in the right pane, select it and click Remove.
Click Next.
In the Select Connection Pool screen, select the connection pool for each database that you want to update and then click Next. You might need to set or confirm values for variables before continuing.
In the Update screen, review the information about each update and select the updates you want to perform. You can sort the rows (toggle between ascending and descending order) by clicking the Name column heading.
If you decide that you do not want the wizard to update a particular object in the Physical layer, click the Back button and remove the object.
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.
Click Finish.

The wizard updates the objects in the Physical layer, and then closes automatically. Objects that do not exist in the database are deleted.
From the File menu, select Save to save the updated objects in the Physical layer.

Generating Documentation of Repository Mappings

The Repository Documentation utility documents the mapping from the presentation columns to the corresponding logical and physical columns. The documentation also includes conditional expressions associated with the columns. The documentation can be saved in comma separated (CSV), XML, or tab delimited (TXT) format.

You can use the Repository Documentation utility to extract Oracle Business Intelligence metadata to a flat file so that it can be loaded into Excel and RDBMS. You can query the resulting file to answer questions such as "If I delete physical column X, what logical columns will be affected?" or "How many places in the business model refer to the physical table W_SRVREQ_F?" Then, you can establish dependency relationships among elements in the repository.

Excel only allows data sets of 1,000,000 rows. You might exceed this in a large repository. Therefore, you might want to run the Repository Documentation utility on a subset of the repository by extracting relevant business models into a new project. For more information, see Chapter 3.

The Repository Documentation utility creates a comma-separated values file or a tab-separated values file that shows the connections between the Presentation and Physical layers in the current repository. This file can be imported into a repository as a Physical layer. Note that the file does not include information about repository variables and marketing objects.

To run the Repository Documentation utility:

In the Administration Tool, select Tools, then select Utilities.
Select Repository Documentation and click Execute.
In the Save As dialog, choose the directory where you want to save the file.
Type a name for the file.
Choose a type of file and an Encoding value, and then click Save. Current encoding options are ANSI, Unicode, and UTF-8.

Generating a Metadata Dictionary

You can generate a metadata dictionary to help Oracle Business Intelligence users obtain more information about metrics or attributes for repository objects. For example, users might need to resolve issues caused by confusing metadata object names, or to obtain more details when an attribute is derived in a complicated way.

A metadata dictionary is a static set of XML documents. Each XML document describes a metadata object, such as a column, including its properties and relationships with other metadata objects. These XML documents can be viewed within the Oracle BI Presentation Services user interface, or they can be viewed directly in a browser.

Use the Administration Tool to generate a metadata dictionary for your repository. Because the dictionary does not change dynamically as repository changes are made, you must generate the dictionary periodically to update the content.

The metadata dictionary files need to be hosted on a Web server, such as Oracle HTTP Server or Apache HTTP Server. When you generate the dictionary, you can set the output location to the final location on the Web server, or to a temporary location. If you generate the dictionary in a temporary location, you must then copy the files to the location on the Web server.

Note that some large repositories can contain tens of thousands of objects. Generating a dictionary for a large repository can take a significant period of time.

To generate a metadata dictionary:

In the Administration Tool, open your repository in offline mode. You cannot generate a metadata dictionary in online mode.
Select Tools, then select Utilities.
Select Generate Metadata Dictionary and click Execute.
In the Choose Directory dialog, click Browse to locate and select the location where you want to store the dictionary. You can select a destination for your dictionary in the following ways:
- Select a local or network location. When the dictionary is generated, a subdirectory with the same name as the repository is created in that location. The dictionary directories and files are created in that subdirectory.
  
  For example, if you select J:\BI_DataDictionary and your repository name is demo1.rpd, the dictionary files, including the style sheets, will be located in J:\BI_DataDictionary\demo1.
- If you want to use an IIS virtual directory, you can create or select a virtual directory in IIS before you generate the dictionary. When you generate the dictionary, choose the physical directory associated with the IIS virtual directory.
Click OK.
If you did not save the files directly to a location on a Web server, copy the files over to your Web server and ensure they are accessible. Refer to the documentation for your Web server for detailed information.

The location where the metadata dictionary files can be viewed is dependent on the host name and port number of your Web server, along with the directory location where you store the files.
You must edit the instanceconfig.xml configuration file to enable the metadata dictionary feature in the Oracle BI Presentation Services user interface, as well as grant the appropriate privilege to your users, groups, or application roles. See "Providing Access to Metadata Dictionary Information" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about these additional configuration steps.

After you generate a metadata dictionary, style sheets and index files are created for that dictionary. The related style sheets (XSL files) are created and stored in a directory named xsl within the repository directory.

A name index and tree index are created and stored in the [drive]:\[path]\[repository name] root directory. The index files are associated with each other so that you can quickly switch views.

For additional information about viewing metadata dictionary information from the Oracle BI Presentation Services user interface, see "Viewing Metadata Information from the Subject Areas Pane" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

Removing Unused Physical Objects

Large repositories use more memory on the server and are harder to maintain. Additionally, development activities take longer on a large repository. This utility enables you to remove objects that you no longer need in your repository. You can remove databases, initialization blocks, physical catalogs, and variables.

To remove unused physical objects:

In the Administration Tool, select Tools, then select Utilities.
Select Remove Unused Physical Objects and click Execute.
In the Remove Unused Physical Objects dialog, from the Type list, select the type of object.
In the list of objects, verify that only the objects that you want to remove are selected.

Below the list of objects, the number of selected objects and the total number of objects appears.
To remove the selected objects, click Yes.

Persisting Aggregates

You can use the Aggregate Persistence Wizard to create the SQL file that will be used to create aggregate tables and map them into the metadata. See "Using the Aggregate Persistence Wizard to Generate the Aggregate Specification" for more information.

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.

To start the Calculation Wizard, right-click any logical fact or dimension column in the Business Model and Mapping layer of data type numeric, and then select the option Calculation Wizard. The wizard starts with the column on which you right-clicked as the source column, and then displays the other columns in that table for comparison.

To use the Calculation Wizard:

Right-click a measure column in the Business Model and Mapping layer (any logical fact or dimension column of data type numeric), and then select Calculation Wizard.
The first time you use the Calculation Wizard, the Introduction screen appears. Select In the future, do not show this introduction screen if you do not want this screen to display subsequently. If you choose not to display the Introduction screen, you can go to Tools > Options to cause it to appear again. See "Setting Preferences" for more information.

Click Next to display the Select Columns screen.
Select 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.

You can remove items from the Selected Columns list by selecting a column and clicking Remove.

Click Next when you have finished selecting comparison columns.
In the New Calculations screen, you can choose which calculations you want to perform for specific columns. The elements of the New Calculation screen are as follows:
- The upper left pane shows the name of the source column, followed by a list of comparison columns that you selected in the Select Columns screen. Select a particular column to create calculations for that column.
- The upper right pane shows a list of calculations you can perform. Select a calculation to view the calculation definition and the default calculation name. In the calculation definition, CurrentX refers to the value of the source column, and ComparisonX refers to the value of the comparison column you selected in the upper left pane.
  
  You can optionally change the calculation name. This name becomes the name of the resulting calculation column.
  
  The following calculations are available:
  - Change (CurrentX - ComparisonX). Subtract the value of the comparison column from the source column.
  - 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 lower left pane shows special cases that are available for the selected calculation. You can keep the default values, or specify how you want the special cases to be handled. For example, for the Change calculation, you can choose whether to return NULL or some other value when the comparison column is NULL.
  
  Select a calculation in the upper right pane to view and set special cases for that calculation.
- The lower right pane shows the resulting SQL for the selected calculation.
Figure 17-6 New Calculations Screen of Calculation Wizard

Description of "Figure 17-6 New Calculations Screen of Calculation Wizard"
Click Next when you have finished creating calculations.
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.
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.
Click Finish. The new calculation columns are created.