Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

In SAS Financial Management software, efficient formulas are the result of two separate actions: writing and scoping. Writing efficient formulas identifies the explicit members of the formula, while scoping controls the implicit members of the formula.

The multidimensional nature of SAS Financial Management is much more complex than a typical spreadsheet. As the number and size of the dimensions and their members increases, the number of formulas that must be executed often grows exponentially. The total number of crossings in a cube is determined by multiplying the number of members of all of the dimensions together.

For more information on SAS Financial Management model and query size guidelines, please refer to SAS Note 21089 .

To improve performance with SAS Financial Management tables and form sets, reduce the number of times a formula executes. One means of accomplishing this is by scoping via the Formula Editor in SAS Financial Management Studio. Due to the large number of crossings in a typical model and the sparse population of facts for those crossings, it is possible to use scoping to drastically reduce the number of times a formula must be executed. Calculating a formula is necessary only for the crossings which have facts, therefore an understanding of the data and of the formula's intent will determine where it is beneficial to scope a formula.

Consider the following example where formula scoping is beneficial.

An account member is created to calculate Sales. Sales is defined as Price x Units. However, this calculation should only be performed for the BUDGET Analysis member, and not for any of the other members in the Analysis dimension. The value of the Sales Account member for any other members of the Analysis dimension should be available for fact input via data integration and/or via data-entry forms.

The formula, without restricting the scope, would have to be written as:

IF(CURRENT("ANALYSIS")="BUDGET",["ACCOUNT"="Units"] * ["ACCOUNT"="Price"], 0)

The ability to restrict a formula's scope of execution has many benefits. The primary benefits include:

• Faster execution of formulas. By using knowledge of the data and the intent of the formula, you can restrict the formula's execution to only the relevant crossings.
• You can choose to calculate a member for some crossings but not others. This is very useful when using the same chart of accounts for both Actual and Planning purposes.
• Less complicated formulas. By moving the checks for particular crossings out of the formula and into the calculated member's property sheet, the formula becomes easier to understand and maintain.

In the Sales = Price x Units example assume the model has 4 Analysis members - Actual, Budget, Forecast, and Prior Year. But, this formula will only be calculated for the BUDGET Analysis member.

Starting with the Formula Scope Members window for the Analysis dimension Type, the formula is scoped to the BUDGET Analysis member. Facts for other Analysis members, such as ACTUAL, are loaded from the General Ledger so the formula is not applicable for those members. Scoping the formula to one of the four analysis members in this model reduces the number of times the formula needs to be executed by 75%. The formula will read from and write to (or display the result at) the BUDGET member of Analysis. The value for Sales (for example) on the ACTUAL Analysis member is not calculated by the formula since it is outside the scope of the formula. The value displayed is based upon facts loaded into SAS Financial Management.

As more formulas are scoped, the fewer times formulas will have to be executed. Using knowledge of the formula's intent and of the data in the system can lead to dramatic performance gains.

Operating System and Release Information