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

When creating models and queries in SAS Financial Management release 4.4, consider the following guidelines:

Practical Limits of Model Size:

• A cycle or model should not exceed 12 dimensions.
• A dimension hierarchy should not exceed 5,000 members.
• Internal Organization (INTORG) hierarchies should not exceed 1,000 members.
• The number of Reporting Entities should not exceed 100.
• TIME hierarchies should not be more granular than Month (no Weeks or Days) and should not exceed a span of more than 8 years.

Practical limits of data storage:

• A model should not exceed 5 million facts.
• An implementation should not exceed a combined anticipated queried model size of 8 million facts.

Practical limits of formulas:

• A model should not contain more that 25 Modeling formulas if those formulas have limited scoping applied.
• A model should not contain more 50 Driver formulas.
• The size of the cube defined by Modeling formulas should not exceed approximately 2 billion crossings.
• Avoid the use of IF()/NESTIF() functions to test for other inputs.

Please be aware that query size and model size are not the same thing.
A query is a subset of what is contained in the model, and the query does need to be considerably smaller than the model size limits listed above.

A size limit of 2.147 billion crossings applies for the query.
For example, if you create a SAS Financial Management report to show revenue by product by location by month, actual vs. budget for current year compared to prior year (which is fewer dimensions than exist in the overall model, and should be considerably fewer members from each dimension), that total query size needs to contain fewer than 2.147 billion crossings.

Suppose the above query displays the following members:

Account (revenue) - 1 member
Product (cars, trucks, motorcycles) - 3 members
Location (N. America, S. America, Europe, Asia, Africa) - 5 members
Time (current year, last year) - 2 members
Analysis (actual, budget) - 2 members

If you multiply the number of members specified above, this query APPEARS to have 60 crossings (1*3*5*2*2).

But, you need to keep in mind the expanded size of the query, because there may be children under some of these members (assuming you have chosen to display "member and it's children" or "member and it's descendants").

Account (revenue) - 1 member
Product (assume there are 15 models of cars, 6 models of trucks and 8 motorcycles) - 29 members
Location (assume there are 2 members in N. America, 5 in S. America, 3 in Africa, 7 in Europe & 2 in Asia ) - 19 members
Time (current year, last year, + 12 months in each year, + 4 quarters each year) - 34 members
Analysis (actual, budget) - 2 members

So, the query size might actually have more than 37,000 crossings (1*29*19*34*2).

Also note that if you haven't done any subsetting in your dimensions when you create the report, you will be creating a MUCH larger query. The query will actually contain all members from each of the dimensions displayed above.

If the model consists of dimensions with the following members, and if you do not subset when creating the query:

Account - 2000 members
Product (they also make planes, yard equipment, etc.) - 200 members
Location (the entire company truly does global business) - 100 members
Time (5 yrs of data, incl. quarters & months) - 85 members
Analysis (also contains rolling 12 month forecast, 5 year plan, and 9 + 3 view) - 5 members

You have very quickly gotten to a query of 17 billion crossings (2000*200*100*85*5), which is far too large to be processed by SAS Financial Management release 4.4.

Scoping enables a formula to be Calculated only across specified crossings, as opposed to being calculated for all possible crossings in the model/query. Scoping can significantly reduce query size and greatly improve query performance.
For more information on formula scoping, please refer to SAS Note 21090 .

Finally, consider the practical limits on model size, as specified above. Even if you have a model of 8 dimensions, with 50 members each (which is not particularly large - except, of course, for the ANALYSIS dimension), the result is a model of 39 trillion crossings.

So, you can specify a large model in SAS Financial Management release 4.4, but be careful when creating a query to ensure that the number of crossings in the query does not exceed approximately 2 billion.

Operating System and Release Information