ASO Essbase Cube Performance - Tips and Tricks

Hello Friends,

In Analytics world, the most important criteria to make your footprint on the world scale is to have the ability to store and hold humungous volume of data and to have speed of thought response time.

I would talk about Performance part of the ASO cube in this article today (both from books and from experience)

First some tips directly from book: Although there are very limited tuning which you can do on ASO cube based on its architecture, still there are some good ways to better the performance of the cube:

Compression: Default : Account Dimension. Can be changed to any other Dimension having Dynamic Hierarchy

A good candidate for a compression dimension is one that optimizes data compression while maintaining retrieval performance

Dimensions with a large number of stored level 0 members do not perform well if tagged Compression as it would require more time to dynamically aggregate upper level members reducing the performance of the retrieval. 

You can check below details from Administration Services Console for an ASO application:

Average bundle fill: The average bundle fill is the average number of values stored in the groups. It can range from 1 to 16, best being 16. Choosing a compression dimension that has a higher average bundle fill means that the database compresses better.

Average Value length: The average value length is the average storage size, in bytes, required for the stored values in the cells. It can vary between 2 bytes and 8 bytes with 2 bytes being the best.Rounding the data values to no more that two digits after the decimal point can reduce the average value length, improving compression

Expected Level 0 size:This field indicates the estimated size of the compressed database. A smaller expected level 0 size indicates that choosing this dimension is expected to enable better compression

Compacting Outline: When you delete members from an aggregate storage outline, the corresponding records of members in the outline file (.otl file) are marked as deleted but remain in the file. The outline file continues to grow as members are deleted and added. You can minimize the outline file size by compacting the file to remove the records of deleted members. Compacting the outline file causes Essbase to restructure the outline and can take place only when no other users or processes are actively using the database

Creating Aggregate View: This helps in improving the retrieval time of ASO Essbase queries. This requires to run aggregation process (single step for combination of aggregation selection and aggregation build) to pre-aggregate database based on:

   -  Most queried user selection (use query enable feature)

   - Query Hint

Aggregate view with following options:

1) Allow Essbase to automatically generate aggregate view based on automatic data analysis and outline hint

  - Best for smaller cubes having about 1 million cells

  - For larger cubes the Sample Cell values can be adjusted using Essbase.cfg parameter : ASOSAMPLESIZEPERCENT

  - Increasing this parameter has its own cons as the amount of data which needs to be analysed would be too high with may reduce the time of aggregation 

2) Based on User Query by enabling query tracking 

3) Manually creating Aggregate view - This required understanding of data and type of queries which users would be running more frequently

Now time for some real life experiences

Trickle Feed: ASO cubes has the ability to load multiple data files in parallel using Trickle Feed feature. This feature allows allocation on buffer ids to each data file and then create slice by using the existing buffer ids and committing the slice by merging it into the cube datafiles.

Virtual Cube:If you are dealing with large volume of data, we can design the cube by grouping the cubes in number data categories and create respective ASO cubes and can build a transparent partitioned virtual cube on top of multiple data categories based ASO cubes. This helps in improving the retrieval time drastically.  

Transaction based outline: Create transaction based outline and incremental data load for ASO reporting cube

Hope this helps many of the developers in building great performing ASO applications

Creating Partition for Essbase applications

Hello,

I am sure you all have created partitions for Essbase applications to cater to specific business requirement using EAS. However, last time when I was trying to create a partition, I was continuously getting an error message while validating the partition :

"Validation was not successful. Please see the errors".

However it does not show any errors in the "Messages" panel. Also I could not find any errors in application / essbase server log (If anyone know where the error messages are stored for partitions, please let me know).

Although I was doing everything right while creating the partitioning - Areas section, mapping section, cell count... everything was correct, still I was getting above error.

While doing research on this error, I found that, the Essbase server which was configured with EAS was showing "EssbaseCluster-1" - This is the default connection which gets created while configuring the Essbase Server with EAS.

Just for my curiosity, I added another connection for the same Essbase server with the host name ("Server Name:Port"). When I did this and tried creating the same partition, it went through.

Again, this is from my experience. Hence please do your own investigation/ research before you recommend this.

Hope this helps we all.

Encrypt Essbase UserID and Password in MaxL script

Hi, You all would be using Encryption - Decryption of MaxL script containing userID and password for Essbase server. But I just thought of posting it in my blog so that if some of you want a quick reference without going to Technical Reference guide.

Follow the below easy steps to encrypt and decrypt the MaxL script:

Step 1: Create a simple MaxL script (Loginscript.mxl) to login into Essbase server using MaxL commands login on ; logout; 


Step 2: Run essmsh -gk command to generate Encryption and Decryption code 


         D:\oracle\Middleware\EPMSystem11R1\common\EssbaseRTC\11.1.2.0\bin>essmsh -gk 
         Essbase MaxL Shell - Release 11.1.2 (ESB11.1.2.1.0B347) Copyright (c) 2000, 2011, Oracle
         and/or its affiliates. All rights reserved. 
     
        Public Key for Encryption: 13005,692804953 
        Private Key for Decryption: 565324885,692804953 


       MaxL Shell completed 


Step 3: Encrypt Loginscript.mxl by issuing the below command along with Encryption Code generated above. This would create an encrypted MaxL script with extension Loginscript.mxls


      D:\oracle\Middleware\EPMSystem11R1\common\EssbaseRTC\11.1.2.0\bin>essmsh -Em D:\ 
      Scripts\Loginscript.mxl 13005,692804953 


         Essbase MaxL Shell - Release 11.1.2 (ESB11.1.2.1.0B347) Copyright (c) 2000, 2011, Oracle 
         and/or its affiliates. All rights reserved. 


Step 4: Open the file Loginscript.mxls to check whether the userID and Password got encrypted or not.  

       login $key 225460064724436062164017310 $key 137249360634406684332940616180146296;  
       logout; 


Step 5: If you want to execute encrypted file, run the below command with decryption code 
        
      D:\oracle\Middleware\EPMSystem11R1\common\EssbaseRTC\11.1.2.0\bin>essmsh -D D:\S   
      cripts\Loginscript.msh.txts 565324885,692804953 
    
        Essbase MaxL Shell - Release 11.1.2 (ESB11.1.2.1.0B347) Copyright (c) 2000, 2011, Oracle 
        and/or its affiliates. All rights reserved. 


        MAXL> login $key 225460064724436062164017310 $key 37249360634406684332940616180  
       146296; 
              
               OK/INFO - 1051034 - Logging in user [admin@Native Directory]. 
               OK/INFO - 1241001 - Logged in to Essbase. 


        MAXL> logout; 


               User admin is logged out 
               MaxL Shell completed 

Hope these simple steps would help you all while encryting and executing an encrypted MaxL script 

Keep Sharing

MaxL API Instance Not Active EAS to Essbase connectivity issue

Hi Guys,

Today we faced a very peculiar issue with EAS. Once we restart EAS services on Windows box, the EAS services use to start but when we try to open any Essbase Analytic Server, it use to throw below error message : MaxL API instance not Active. Sometime it use to hang EAS console completely.

We investigated the issue and found that the Environment Variable (ESSLANG and ESSBASEPATH) set on the EAS server was not correct. Value for ESSLANG should be .Latin1@Default and ESSBASEPATH value must point to Essbase Client. This make sense as while connecting Essbase server, EAS looks for Essbase Client to get it connected and hence if the values are not proper, if would not connect with Essbase.

Hope this help when you face a similar problem

Keep Sharing

Peroid-To-Date ( or DTS functionality) in ASO

Again I am going to give you all a link with provides you ways to implement one of the functionality which is not an Out of Box functionality of ASO cubes:

Period-To-Date functionality: This is a common requirement for Financial data to have YTD, QTD, MTD values displayed in their financial reports. BSO cubes provide an in-built DTS funtionality which caters to this requirement. However, ASO cube , being the most efficient for Financial Reporting, does not provide this functionality. However the same can be achieved in ASO cube using MDX function - PeriodsToDate.

Here is a link which shows ways to implement the same in ASO cube:
http://garycris.blogspot.com/2008/07/time-functionality.html


Enjoy ASO cube

Currency Conversion in ASO cube

Hi All...

Long time no post :-)... was busy with personal commitments

So I am back and this time am going to give you a link which is a work around to what is considered to be a limitation with ASO cube

Currency Conversion is not supported in ASO cube as we cannot write Calculation rules / scripts in ASO cube. However we can write MDX formulas and with some tweaks in calculation order, we can achieve what is considered to be a limitation.

I found an interesting link on Oracle Forum with Step by Step ways to implement currency conversion in ASO cube and thought of sharing the same with you all


https://community.oracle.com/message/3334237#3334237

Copy Paste upper link in any browser and you could find the process of using ASO cube for currency conversion.

So enjoy sharing... :-)

CLEARDATA ambiguity

This is a very strange observation which I discovered in one of the blogs on CLEARDATA command which challanged my knowledge on BSO CLEARDATA Command. I personally tested it and found that the blogger is correct and so thought of sharing it with you if you all have not visited that blog

CLEARDATA when dealing with purely sparse dimensions…removes blocks

For more details explanation... please refer the blog
http://camerons-blog-for-essbase-hackers.blogspot.com/2011/01/stupid-programming-tricks-6_03.html

Happy posting

Email Alerts Using Essbase Triggers

While working for a client there was a requirement for altering the users/administrators during data load failures in Essbase.

This requirement was met using Essbase Triggers which has a capability of sending emails during data load, calculation and Lock and Send using Spreadsheet add-in. We used this feature of Essbase which came very handy during monitoring of data loads in Batch mode (during nightly refresh)

So, keep exploring Essbase :-)

CDF Utility : JExtendedExportTo

While working/reading through number of blogs, I found an interesting article on CDF Essbase calc script utility function 'JExtendedExportTo' which can be used to export specific (tailored) data from Essbase using Calc Script. I liked the article too much and thought of sharing in my blog.

Here is the details of the function:

Exports (extended) the data to a text file or relational database (using either JDBC or JDBC-ODBC bridge).
* @param targetType The type of export (file, dsn or jdbc).
* @param targetName The name of the destination (see use). This file name must be used to close the file after the calculation completes.
* @param delimiter The character to use to delimit the fields (see use). Accepts "tab" for tab delimited.
* @param textQualifier text qualifier. Accepts "quotes" for " qualifier.
* @param tableName The name of the table (see use).
* @param strArgs An array of strings (member names). Use "" if you are not exporting members names.
* @param dblArgs An array of values (data). Use 0 if you are not exporting Data values.
* @param decimalPoints An array of int values that sets decimal points per column. Use -1 to return the stored number.
* @param replaceMiss an array of String values that replaces #Missing per column.
* #Missing can be replaced with any text or a number such as "0".
* @cdf.maxl CREATE OR REPLACE FUNCTION '@JExtendedExportTo'
* AS 'com.oracle.essbase.cdf.ExportTo.ExportToTarget(String,String,String,String,String,String[],double[],int[],String[])'
* SPEC '@JExtendedExportTo(targetType,targetName,delimiter,textQualifier,tableName,strArgs,dblArgs,intDecPts,replaceMi)'
* COMMENT 'Exports the data to a text file or relational database (using either JDBC or JDBC-ODBC bridge).';
* @cdf.use
* Export to a text file:

* arg 1: specify "file" to export to a text file

* arg 2: file name. This file name must be used to close the file after the calculation completes

* arg 3: delimiter. Accepts "tab" for tab delimited.

* arg 4: text qualifier. Accepts "quotes" for " qualifier.

* arg 5: leave blank when exporting to text files

* arg 6: an array of member names (Use "" if you are not exporting members names)

* arg 7: an array of data (Use 0 if you are not exporting Data values)

* arg 8: an array of int values that sets decimal points per column (use -1 to return the stored number)

* arg 9: an array of String values that replaces #Missing per column.
* #Missing can be replaced with any text or a number such as "0".

*
*
@JExtendedExportTo("file","%HYPERION_HOME%\flatdec.txt","tab","quotes","",
*
@List(@NAME(@currmbr(Market)),@NAME(@currmbr(Product)),"[None]"),
*
@LIST(jan:dec),@LIST(-1),
*
@list("NONE","0","0","#Mi","0","0","0","#Mi","0","0","0","#Mi") ) ;

However this utility is not an in-bult Hyperion Essbase feature. You need to download and register the utility on your essbase server. Please find the details in EssbaseLabs.blogspot.com URL : http://essbaselabs.blogspot.com/2008/10/cdf-jexport-and-dynamic-members.html

Hope you will find more useful functions in this utility

Smart View V11.1.1.2: Tip #1

Its been long that I posted in this blog as I am busy with my current assignment. However while exploring Smart View features, I found quite interisting feature which I would like to discuss here.

Smart View has a feature to save POV member selection. Users can save POV member selection of a particular essbase cube and use the same selection again while connecting to the cube.

All user need to do is to add Cube selection as Favourite (Add to Favourites) and then the connection becomes Active in POV Manager... selection which allows user to save a POV members for future use

Hope this helps in making users feel more confortable in SV reporting

CALC DIM Vs AGG

If you have being using Calc Scripts in your Essbase database, I am sure you may have got confused when to use CALC DIM and when to use AGG for aggregation.

Here is the difference

CALC DIM (Product);
This line calculates the Product dimension doing both outline
aggregation (such as rolling all the Colas up into the parent
value) and member formulas, if they exist for members in that
dimension. If we want to calculate multiple dimensions using
this command (say, Market and Product), just separate them
with commas:
CALC DIM (Market, Product);
Remember how we said that “CALC DIM” not only does
aggregation but also member formulas? Well, how many member
formulas are there in the Market and Product dimensions?
That’s right: none, so “CALC DIM” is wasting time looking for
formulas that you know aren’t there. For sparse dimensions that
don’t have formulas, there’s a command that only does aggregation
and as such, is usually faster:
AGG (Market, Product);

Hope this would have made you some what comfortable about CALC DIM and AGG

So go on scripting

Excel Add-in Reporting : Tip#1

You may have got requirements from you client that once they retrieve data at the Top level of different dimension, they just need to see the data at the Bottom Level for the same dimension at the same time. However, if you are using Hyperion Reports/Hyperion Financial Reporting for the reporting, you may need to do lots of customization to achieve the above requirement and at the same time, if your dimensions are too deep, there can be performance issues too.

So, you can suggest an Excel Addin report wherein you can set the Excel Addin "Options" to Zoom to Bottom Level.



This will very quick and your client would love to work on excel rather than on Hyperion Reports PDFs

Essbase Tricks & Tips - #1

As Essbase is so vast that we do forget some of the basics of Essbase. So I thought to start the "Tricks & Tips" series on my blog.

Disclaimer:
Please be informed that the information posted here is solely based on my knowledge and experience. Please check the content before using it - OraHyperion

Tip #1: Sometimes we face an error of Essbase server ( using command prompt or Windows Services) not getting startup.
The reason may be that the server could have got crashed while performing some calculation/restructure on the outline. Do not need to panic if this happens. Essbase engine always create a Security Backup file which contains the Users/Groups/Application/Filter information of the server in \bin folder of Essbase server. It regularly updated the .BAK file with the contents of .SEC file in the Essbase Server BIN folder.

Just delete the .SEC file and rename .BAK file to .SEC file and restart Essbase server. This re-establishes the Essbase server to the point when the Essbase server was working perfectly.

MS Excel Macro to Essbase's Rescue

I never knew that MS Excel's "Macros" can be so beneficial as far as Essbase is concerned. Macro is such a powerful scripting utility in Excel that it can be used in multiple facets.

As most of the Essbase design is done keeping the Finance in mind and all of us know that Finance guys are generally good in Excel.

MS Excel can be used to create files in different format which can be a source for multiple other systems.

Here is a sample Macro for generating a Text file which prompts for file name and stores the data from Excel in a text file.

Sub Macro1()

Dim sName As String, sString As String
Dim rng As Range, cell As Range
sSting = """" ' for inserting " in the output file
sName = ActiveSheet.Name
sName = Application.GetSaveAsFilename( _
InitialFileName:=sName & ".txt", _
FileFilter:="Text Files (*.txt),*.txt")
If sName = "" Then Exit Sub
Open sName For Output As #1
Set rng = Range(Range("A1"), _
Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If cell.Text = "Yes" Then
Print #1, cell.Text , cell.Offset(0, 1).Text ' Output 1st column & 2nd column
Print #1, sSting & cell.Text & sSting ' Output 1st column under "
End if
Next
Close #1
End Sub

Things to note: Column Values (offset) starts from 0