Working with Formulas

Creating a Formula

How to Create a Formula

You can create a formula in two ways:

by invoking the New Formula wizard
by copying an existing formula to a folder, renaming it, and modifying it

You can create a formula and save it in any of the Formulas folders to which you have Write privileges. For example, you can create a formula in the Formulas folder of Shared Data or in your private folder within My Folder.

If you want to use a formula in a wizard, you can specify the location of that formula. If the formula is not found in your specified location, then the wizard will search for the formula in this location:

Shared
                        Data/SAS IT Resource Management/IT Formulas

. If the formula is still not found, the wizard will search for it in this location: Products/SAS IT Resource Management/IT Formula.

Create a Formula by Using the New Formula Wizard

To create a formula using the New Formula wizard, perform the following steps:

From the SAS Data Integration menu bar, select FileNewIT Resource ManagementFormula to open the New Formula wizard.
On the General Information page of the New Formula wizard, specify the name, description, and location of the new formula.
- The Name field can contain a maximum of 60 characters. A value is required in this field and it must be unique within its folder.
  
  Note: The wizard automatically generates a name for this new formula. You can either delete the system-generated name and enter a more meaningful name for this formula or you can retain the system-generated name.
- The Description text box can contain no more than 200 character. A value is optional in this field.
- The Location field is automatically generated and, by default, specifies /Shared Data/SAS IT Resource Management/IT Formulas. Click Browse to navigate to another folder to use as the location for your new formula.
  
  Tip
  User-defined formulas are more easily accessed if they are all stored in a location that is identified as a “Formulas” folder.
On the Expression page of the New Formula wizard, enter the expression that specifies the combination of functions and mathematical operations that are used to derive a value. A value is required in this field.
You can enter the expression for a formula in any one of these three ways:
- Type the expression in the text area of the Expression field.
- Copy and paste the expression text from the Expression tab of an existing formula to the Expression text area of the new formula.
- Copy and paste the computation from the Expression tab of the computed column into the Expression text area of the new formula.
In addition to the conventional rValue expression (where the expression consists of code that is appropriate only for the right-hand side of an assignment statement), SAS IT Resource Management also supports more complex expressions. For example, your expression can use SAS code that might include loops, IF statements, and so on. As shown in the following display, this code must be written in valid SAS DATA step syntax.

Expression Page of the New Formula Wizard
The final page of the New Formula wizard is a summary page that displays the following information about the formula that you specified. Click Finish to store the formula that you created in the folder that you specified in the Location field.

Create a Formula by Copying an Existing Formula

To create a formula by copying an existing formula, perform the following steps.

In the Folders tree of SAS IT Resource Management, navigate to an IT Formulas folder (or to a folder that contains formulas) to locate the formula that you want to use as a template for the new formula.

Tip
User-defined formulas are more easily accessed if they are all stored in a location that is identified as a “Formulas” folder.
Right-click the formula that you want to copy. From the list that is displayed, select Copy. Then navigate to the folder where you want to store your new formula and click Paste.

If you are copying and pasting the formula to the same folder, then the name of the new formula is retained unless a formula of the same name already exists at this location. In that situation, Copy of is appended to the original name of the formula. This ensures that the formula name is unique within its folder.

Note: You can select the Copy to Folder option, which opens the Select a Location dialog box in which you can navigate to the folder where you want to store the copied formula. You can also select the Move to Folder option, which opens the Select a Location dialog box in which you can navigate to the folder where you want to store the formula.
On the new copy of the formula, modify the fields as required. For information about changing the fields of a formula, see Modify a Formula.

Delete a Formula

You can delete formulas only from the folders for which you have Write privileges.

To delete a user-defined formula, perform the following steps:

In the Folders tree of SAS data Integration Studio, navigate to an IT Formulas folder or a folder where the formula that you want to delete is located.
Right-click the formula. From the list that is displayed, select Delete.
In the confirmation dialog box, click Yes to delete the formula. Otherwise, click No.

Deleting a formula does not change the expression that used the formula, and the columns that are computed using that expression are not affected. The columns will continue to be created according to the specified expression. However, when a formula that is used in an expression no longer exists, then modifications to that expression can no longer be performed simply by changing the formula. The modifications must be changed in every column where the expression is used.

Modify a Formula

You can change any formula to which you have Write privileges. To do so, perform the following steps:

Double-click the formula to open the Properties dialog box, where you can change the name, description, location, and expression of the formula.
- Select the General tab to change the name, description, or location of the formula.
  
  To change the name or description of the formula, type your modifications in the corresponding fields.
  
  To change the location of the formula, click Browse to open the Select a Location dialog box where you can navigate to another folder.
Select the Expression tab to change the combination of functions and mathematical operations that are used to derive a value.

If you modify the expression text for a formula, then the system checks to see whether the original formula is used by any computed column of any table. If the formula is being used, an Update Formula dialog box opens that lists the computed columns that will be affected by the change. You can select Yes to change the formula in the computed columns that use it or No. If you click Yes, then the modified expression for the formula is saved and applied in all instances where it is used.

Note: Computed columns in staged tables or aggregation tables will be calculated using the latest version of the formula.
Select the Notes tab to enter additional information about the formula.

CAUTION:

The Advanced tab contains information about the metadata for this formula. The Authorization tab contains information about the groups, users, and permissions that are in effect for this formula. Do not change the information that is displayed on these tabs.
Click OK to make your changes take effect.

Consequences of Changing a Formula

Formulas can be shared across multiple computed columns and across multiple IT data marts. For example, the SHIFT computed columns for a particular IT data mart can all be based on the same SHIFT formula. If you want to change the site-specific definition of SHIFT, you can make a change to the SHIFT formula and click OK. If this expression is used in any other formula, the Update Formula dialog box appears and displays the computed columns where this formula is used. Click Yes to cause all computed columns that use the formula to inherit the change. Otherwise, click No.

Computed columns in staged or aggregation tables are calculated using the latest version of the formula. In other words, if you change the expression for a computed column in a staged table or an aggregation table and redeploy the job, then the next time the job is run, the values for that column in that staged table or aggregation table will be computed according to the new expression. This computation occurs whether the expression is from a formula or only for that computed column.

CAUTION:

If the expression of a computed column is changed and if that column was already used as input to a subsequent transformation that in turn created a target table, then the values in that target table are not automatically recreated.

To make the old and new values of the computed columns consistent with each other, redeploy and rerun all the previous ETL jobs that contain the new formula, according to the following guidelines.

Case 1: The change to the expression of a formula should be reflected in all the tables, including the aggregation tables. This situation might occur if an erroneous expression was entered and needs to be corrected. Previously aggregated data is affected and must be changed.

Solution: Purge the aggregation tables. Then redeploy and rerun all the staging jobs and all the aggregation jobs that contain the data that uses the altered expression to create a new column. Thus, you recreate the aggregation tables.
Case 2: The change to the expression of a formula should be reflected only from this time forward. This situation might occur if there has been a change to the billing rate. Previous aggregated data is not affected.

Solution: Redeploy the staging job that uses the altered expression. You do not have to redeploy and rerun the aggregation jobs whose source tables use the altered expression to create a new column.

For example, consider the following situation:

There is a computed column RATE in a staged table, where RATE=BYTES/SECOND.
There is an aggregation with statistics based on that RATE column (for example, the mean RATE).
Data for the RATE and the mean RATE columns has already been aggregated.

If you subsequently change the expression for RATE to RATE=BYTES/MINUTE, and redeploy the job, in the next ETL, the values of the staged table's RATE column will be computed according to BYTES/MINUTE. However, the values in the aggregation table that already exist (that is, they were already aggregated) will still reflect the previous BYTES/SECOND calculation. According to the guidelines in Case 1, if you want the statistics that are generated to reflect the rate change in the aggregated data, you should rerun all the associated staging and the aggregation jobs.

Rename a Formula

Renaming a formula does not affect the tables that use it. To rename a formula, perform the following steps:

In the Folders tree of SAS data Integration Studio, navigate to the IT Formulas folder where the formula that you want to rename is located.
Right-click the formula. From the list that is displayed, select Rename. The name of the formula is highlighted.
Enter the new name of the formula. The name can contain no more than 60 characters. The name must be unique within its folder.
Click Enter.