ESSBASE: Use Dense Formulas Instead of Sparse for Performance Considerations

When in developing calculation scripts (or business rules), sometimes we are tempted to put together formulas without closely exam the formulas we are assembling are sparse or dense formulas. Since each time when Essbase tries to calculate sparse formulas, it has to search the blocks in the storage, those blocks which contain the sparse members, and brings them into memory. This takes time!

Let's look at an example.

Different technologies are installed into airplanes to enable the aviation connectivity. As time goes by, old technologies are uninstalled (ex. ATG), retrofit to newer technology (ex. ATG to ATG4, to KU, or ATG4 to KU), or transferred from on airline to another.  In all those scenarios, we need to calculate AOL (Airplane On Line), CLOL (Content Loader On Line).

Developers put together the following business rule -

/* Current Year Calc*/
Fix(&CurrYear, "Actual", "Jan":&CurrMonth, {rtp_Airline}, {rtp_AircraftType}, {rtp_Region}, {rtp_Route},
{rtp_TransactionType}, {rtp_Division}, {rtp_Project})

FIX("AOL")
"Final"
(
IF(@ISMBR("Jan"))
"ATG" = @Prior("ATG"->"Dec",1,@RELATIVE("Years",0)) + "ATG"->"Install" - "ATG"->"Uninstall" - "ATG"->"Retrofit to ATG4" - "ATG"->"Retrofit to KU" - "ATG"->"Retrofit to KA"+ "ATG"->"Transfer-In" - "ATG"->"Transfer-Out";
"ATG4" = @Prior("ATG4"->"Dec",1,@RELATIVE("Years",0)) + "ATG4"->"Install" - "ATG4"->"Uninstall" - "ATG4"->"Retrofit to KU" - "ATG4"->"Retrofit to KA" + "ATG"->"Retrofit to ATG4" + "ATG4"->"Transfer-In" - "ATG4"->"Transfer-Out";
"KU" = @Prior("KU"->"Dec",1,@RELATIVE("Years",0)) + "KU"->"Install" - "KU"->"Uninstall" + "ATG"->"Retrofit to KU" + "ATG4"->"Retrofit to KU" + "KU"->"Transfer-In" - "KU"->"Transfer-Out";
"KA" = @Prior("KA"->"Dec",1,@RELATIVE("Years",0)) + "KA"->"Install" - "KA"->"Uninstall" + "ATG"->"Retrofit to KA" + "ATG4"->"Retrofit to KA" + "KA"->"Transfer-In" - "KA"->"Transfer-Out";
"KU+ATG4" = @Prior("KU+ATG4"->"Dec",1,@RELATIVE("Years",0)) + "KU+ATG4"->"Install" - "KU+ATG4"->"Uninstall" + "KU+ATG4"->"Transfer-In" - "KU+ATG4"->"Transfer-Out";
"HTS" = @Prior("HTS"->"Dec",1,@RELATIVE("Years",0)) + "HTS"->"Install" - "HTS"->"Uninstall" + "HTS"->"Transfer-In" - "HTS"->"Transfer-Out";

ELSE
"ATG" = @Prior("ATG") + "ATG"->"Install" - "ATG"->"Uninstall" - "ATG"->"Retrofit to ATG4" - "ATG"->"Retrofit to KU" - "ATG"->"Retrofit to KA"+ "ATG"->"Transfer-In" - "ATG"->"Transfer-Out";
"ATG4" = @Prior("ATG4") + "ATG4"->"Install" - "ATG4"->"Uninstall" - "ATG4"->"Retrofit to KU" - "ATG4"->"Retrofit to KA" + "ATG"->"Retrofit to ATG4" + "ATG4"->"Transfer-In" - "ATG4"->"Transfer-Out";
"KU" = @Prior("KU") + "KU"->"Install" - "KU"->"Uninstall" + "ATG"->"Retrofit to KU" + "ATG4"->"Retrofit to KU"  + "KU"->"Transfer-In" - "KU"->"Transfer-Out" ;
"KA" = @Prior("KA") + "KA"->"Install" - "KA"->"Uninstall" + "ATG"->"Retrofit to KA" + "ATG4"->"Retrofit to KA" + "KA"->"Transfer-In" - "KA"->"Transfer-Out";
"KU+ATG4" = @Prior("KU+ATG4") + "KU+ATG4"->"Install" - "KU+ATG4"->"Uninstall"+ "KU+ATG4"->"Transfer-In" - "KU+ATG4"->"Transfer-Out";
"HTS" = @Prior("HTS") + "HTS"->"Install" - "HTS"->"Uninstall" + "HTS"->"Transfer-In" - "HTS"->"Transfer-Out";

ENDIF
)

ENDFIX

FIX("CLOL")
"Final"
(
IF(@ISMBR("Jan"))
"CL" = @Prior("CL"->"Dec",1,@RELATIVE("Years",0))+ "CL"->"Install" - "CL"->"Uninstall" - "CL"->"Retrofit to ATG4" - "CL"->"Retrofit to KU" - "CL"->"Retrofit to KA"+ "CL"->"Transfer-In" - "CL"->"Transfer-Out";
"Auto CL" = @Prior("Auto CL"->"Dec",1,@RELATIVE("Years",0))+ "Auto CL"->"Install" - "Auto CL"->"Uninstall" - "Auto CL"->"Retrofit to ATG4" - "Auto CL"->"Retrofit to KU" - "Auto CL"->"Retrofit to KA"+ "Auto CL"->"Transfer-In" - "Auto CL"->"Transfer-Out";

ELSE
"CL" = @Prior("CL") + "CL"->"Install" - "CL"->"Uninstall" - "CL"->"Retrofit to ATG4" - "CL"->"Retrofit to KU" - "CL"->"Retrofit to KA"+ "CL"->"Transfer-In" - "CL"->"Transfer-Out";
"Auto CL" = @Prior("Auto CL") + "Auto CL"->"Install" - "Auto CL"->"Uninstall" - "Auto CL"->"Retrofit to ATG4" - "Auto CL"->"Retrofit to KU" - "Auto CL"->"Retrofit to KA"+ "Auto CL"->"Transfer-In" - "Auto CL"->"Transfer-Out";

ENDIF
)

ENDFIX

ENDFIX

After this is deployed to production, users reported the performance is deadly slow, even though it is only running against one member from each dimensions from user prompt. To improve the usability, we tried to move the business rules into calculation scripts, and calculated for all members. But it takes over 10 hours, still not finished.

Scrutinized the way the formulas were written, we realized that the sparse members were used on the left side of the formulas! So we rewrote the scripts to put the dense members on the left side of the formulas-  

FIX(&CurrPlanYr1 : &CurrPlanYr10, "Jan":"Dec", "AOL", "CLOL")
CLEARDATA &CurPlanVersion;
ENDFIX

FIX(&CurrPlanYr1:&CurrPlanYr10)

FIX("ATG")
"AOL"
(IF(@ISMBR("Jan"))
"AOL" = @PRIOR("AOL"->"Dec",1,@RELATIVE("Years",0)) + "ATG"->"Install" - "ATG"->"Uninstall" - "ATG"->"Retrofit to ATG4" - "ATG"->"Retrofit to KU" - "ATG"->"Retrofit to KA" + "ATG"->"Transfer-In" - "ATG"->"Transfer-Out";
ELSE
"AOL" = @PRIOR("AOL") + "ATG"->"Install" - "ATG"->"Uninstall" - "ATG"->"Retrofit to ATG4" - "ATG"->"Retrofit to KU" - "ATG"->"Retrofit to KA" + "ATG"->"Transfer-In" - "ATG"->"Transfer-Out";
ENDIF)
ENDFIX

FIX("ATG4") /* ATG*/
"AOL"
(IF(@ISMBR("Jan"))
"AOL" = @PRIOR("AOL"->"Dec",1,@RELATIVE("Years",0)) + "ATG4"->"Install" - "ATG4"->"Uninstall" + "ATG"->"Retrofit to ATG4"- "ATG4"->"Retrofit to KU" - "ATG4"->"Retrofit to KA" + "ATG4"->"Transfer-In" - "ATG4"->"Transfer-Out";
ELSE
"AOL" = @PRIOR("AOL") + "ATG4"->"Install" - "ATG4"->"Uninstall" + "ATG"->"Retrofit to ATG4"- "ATG4"->"Retrofit to KU" - "ATG4"->"Retrofit to KA" + "ATG4"->"Transfer-In" - "ATG4"->"Transfer-Out";
ENDIF)
ENDFIX

FIX("KU") /*KU satellite*/
"AOL"
(IF(@ISMBR("Jan"))
"AOL" = @PRIOR("AOL"->"Dec",1,@RELATIVE("Years",0)) + "KU"->"Install" - "KU"->"Uninstall" + "ATG"->"Retrofit to KU" + "ATG4"->"Retrofit to KU" + "KU"->"Transfer-In" - "KU"->"Transfer-Out";
ELSE
"AOL" = @PRIOR("AOL") + "KU"->"Install" - "KU"->"Uninstall" + "ATG"->"Retrofit to KU" + "ATG4"->"Retrofit to KU" + "KU"->"Transfer-In" - "KU"->"Transfer-Out";
ENDIF)
ENDFIX

FIX("KA") /*KA satellite*/
"AOL"
(IF(@ISMBR("Jan"))
"AOL" = @PRIOR("AOL"->"Dec",1,@RELATIVE("Years",0)) + "KA"->"Install" - "KA"->"Uninstall" + "ATG"->"Retrofit to KA" + "ATG4"->"Retrofit to KA"+ "KA"->"Transfer-In" - "KA"->"Transfer-Out";
ELSE
"AOL" = @PRIOR("AOL") + "KA"->"Install" - "KA"->"Uninstall" + "ATG"->"Retrofit to KA" + "ATG4"->"Retrofit to KA"+ "KA"->"Transfer-In" - "KA"->"Transfer-Out";
ENDIF)
ENDFIX

FIX("KU+ATG4") /*KU satellite and ATG4*/
"AOL"
(IF(@ISMBR("Jan"))
"AOL" = @PRIOR("AOL"->"Dec",1,@RELATIVE("Years",0)) + "KU+ATG4"->"Install" - "KU+ATG4"->"Uninstall"+ "KU+ATG4"->"Transfer-In" - "KU+ATG4"->"Transfer-Out";
ELSE
"AOL" = @PRIOR("AOL") + "KU+ATG4"->"Install" - "KU+ATG4"->"Uninstall"+ "KU+ATG4"->"Transfer-In" - "KU+ATG4"->"Transfer-Out";
ENDIF)
ENDFIX

Now this script takes a minute or two to calculate for ALL members from each dimension. A greate improvement in performance.

Comments

Post a Comment

Popular posts from this blog

ESSBASE: When to Use FIX vs. IF

Hyperion developers love FIX and IF, both are powerful tools and interchangeable in usage in certain circumncetances. There are also differences between the two. Let's take a look. FIX...ENFIX Usages and Rules Every FIX must be ended with ENDFIX. But it is optional to end the statement by ";" FIX Mbr can be any comma-delimited list of members, or member set functions FIX Mbr argument can be a combination of members from multiple dimensions           FIX(@CHILDREN(Qtr1),"Actual")                 Calc Dim(Accounts);           ENDFIX FIX Mbr list from the same dimension means "OR", cross dimensions means "AND" FIX cannot be used inside of formulas in outline, they can ONLY be used in calc scripts Cannot consolidate a dimension that is FIXed on. Following will generate an error when run          FIX(@CHILDREN(Qtr1),"Actual")                 Calc Dim(Accounts, Scenario);         ENDFIX It c
Read more

Essbase Currently Not Accepting Connections

Image
Application is currently not accepting connection. Essbase Error(1054009) There are occasions when users receive the above error message when they try to connect to Planning Essbase cluster via SmartView. This is normally caused by one of the following activities:   a) Database is being refreshed (Classic) or deployed (EPMA). Solutions: Wait till refresh to be completed. b) Allow connects under security is unchecked.  Solutions: In EAS, from application, select edit property, and under General tab. c) Connection is disabled using MaxL . Solutions:   Code examples: alter application $4 disable connects; alter application $4 enable connects ;   d) Connection is disabled using ESSCMD. Solutions: Code examples: DISABLELOGIN; ENABLELOGIN; When all of these are checked, and users are still not able to connect to the application, then the ultimate solution is to restart all Hyperion services.
Read more

HFM: Performance Tuning on Application

Image
There are three Planning Applications (seven Plan Types) are already there in Production, all perform well. After HFM was migrated from SIT to the Production environment recently, we immediately noticed the performance of HFM is not well. Normally it takes 15 seconds to open a HFM application in SIT, in production it takes over a minute to open (if after the services are recycled, it takes more than four minutes to open a HFM application). However, the consolidation time is sort of normal. (two to five minutes to consolidate a month, which contains about 400K of records). Following is the list of tuning activities, with regards to HFM tuning - 1) From DB side      a) Ask DBA to confirm that Auto Create Statistics is enabled for the HFM schema.      b) Have DBA check how frequently the SQL Server is doing automatic statistics maintenance.      c) Ask the DBA if the indexes may need to be defraged. DBA reported back -      a) Auto Create Statistics are enabled for all the
Read more