Usage Note 21090: SAS® Financial Management formula scoping guidelines
 |  |  |  |
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)There are two issues with this approach. The first problem is that this formula will be executed for all members of the Analysis dimension even though the intent is to execute only for the BUDGET Analysis member. As previously mentioned, this can dramatically increase the number of times the formula must be calculated. The second issue is that due to the way the IF function works, the value of Sales Account for all other Analysis members will be zero, which may not be optimal if Sales is utilized for fact input in other Analysis dimensions. Therefore, not only does restricting the scope of formula execution improve the formula performance, it is also particularly important when using a common chart of accounts for both Actual and Planning purposes. A much more efficient way to improve performance is to restrict the scope of the formula such that it executes only for the crossings where it should produce a value.
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
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Financial Management | AIX | 4.4 | 9.1 TS1M3 | ||
| 64-bit Enabled AIX | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows 2000 Advanced Server | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows 2000 Datacenter Server | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows 2000 Professional | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows 2000 Server | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows Server 2003 Datacenter Edition | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows Server 2003 Enterprise Edition | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows Server 2003 Standard Edition | 4.4 | 9.1 TS1M3 | ||||
| Microsoft Windows XP Professional | 4.4 | 9.1 TS1M3 | ||||
| Type: | Usage Note |
| Priority: | medium |
| Date Modified: | 2010-06-17 14:01:27 |
| Date Created: | 2007-10-05 09:57:12 |