FRAGMENTATION

Fragmentation is unused disk space. Fragmentation is likely to occur with the following:

1. Read/write databases that users are constantly updating with data.
2. Databases that execute calculations around the clock.
3. Databases that frequently update and recalculate dense members.
4. Data loads that are poorly designed.
5. Databases that contain a significant number of Dynamic Calc and Store members.
6. Databases that use an isolation level of uncommitted access with commit block set to zero.

How can we measure fragmentation?

We can measure fragmentation using the Average Clustering Ratio or Average Fragmentation Quotient.

Average Clustering Ratio

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

Average Fragmentation Quotient

Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:

• The reported value of the Fragmentation Quotient is more accurate when there are no other write transactions running on the database.
• For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks and all of it might not get used right away.

How we can prevent and remove fragmentation?

We can prevent and remove fragmentation:

1. To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.
2. To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.
3. To remove fragmentation, force a dense restructure of the database.

With Warm Regards

SST.!

ESSBASE OPTIMIZATION

Essbase Optimization

One of the reasons of Essbase optimization can be somewhat tricky, to put it mildly, is because Essbase cube performance is almost directly linked to the design of the cube, i.e. the dimensions, hierarchies and members in each dimension, stored vs. dynamic members, etc. This is unlike other (relational) systems where there are general technical items to check for optimization, which has nothing to do with the data in the system (except maybe for the amount of data).

In Essbase the data dictates the cube structure, and the way the cube storage works in blocks and indexes, and therefore every cube is different and has different performance implications. Also Essbase is unique in that just increasing hardware specifications (specifically memory and CPU power), while causing some improvements as a matter of course, will not cause as dramatic performance improvement as changing the cube design to be optimal for the data set used.

Please note this is applicable to block storage cubes.

The Essbase optimization main items checklist :

Block size

Large block size means bigger chunks of data to be pulled into memory with each read, but might also mean more of the data you need is in memory IF your operations are done mainly in-block. Generally we prefer smaller block sizes, but there is not a specific guide. In the Essbase Admin Guide they say blocks should be between 1-100Kb in size, but now a days with more memory on servers it can be larger. Blocks is all dependent on the actual data density in the cube. Do not be afraid to experiment with dense and sparse settings to get to the optimal block size, we have done numerous cubes with just one dimension as dense (typically a large account dimension), and cubes where neither the account nor time dimension is dense.

Block density

This gives an indication of the average percentage of each block which contains data. In general data is sparse, therefore a value over 1% is actually quite good. If your block density is over 5%, then your dense/sparse setting is generally spot-on. A large block with high density is OK, but large blocks with very low density (< 1%) not.

Cache settings

Never ever leave a cube with the default cache settings. Often a client complains about Essbase performance, and sure enough when I look at the cache settings it is the default settings. This is never enough (except for a very basic cube). Rule of thumb here is to see if you can get the entire index file into the cache, and make the data cache 3 times index cache, or at least some significant size. Also check that the cube statistics to see the hit ratio on index and data cache, this gives an indication what % of time the data being searched is found in memory. For index cache this should be as close to 1 as possible, for data cache as high as possible.

Outline dimension order

Remember the hourglass principle. This means order the dimensions in your outline as follows – first put the largest (in number of members) dense dimension, then the next largest dense dimension, and continue until the smallest dense dimension. Now put the smallest sparse dimension, then next smallest, and continue until the largest sparse dimension. Because of the way the Essbase calculator works, this arrangement optimizes number of passes through a cube. A variation of this which also seems to work well is the hourglass on a stick, where you put all non-aggregating sparse dimensions (i.e. years, verison, scenario) beneath the largest sparse dimension.

Commit Block settings

This controls how often blocks in memory are written to disk while busy loading or calculating a cube. You want to minimize disk writes, as this takes up a lot of processing time, so set this to be quite high. The default setting is 3000 blocks, if your block size is relatively small (< 10KB) make this much higher, 20000 to 50000. This setting alone can cause dramatic performance improvements specifically on Calc All operations and cube loads.

Use of FIX..ENDFIX in calc scripts or in BR’s

One of the most misunderstood and common mistakes in calc scripts is the usage of FIX..ENDFIX. Always FIX first on sparse dimensions only, and then within this FIX again on dense members, or use dense members in IF statements within the FIX statements. The reason for this is that if you FIX only on sparse members first, it filters on just specific blocks, which is faster than trying to fix within blocks (i.e. dense members).

Optimizing data loads

The best technique to make large data loads faster is to have the optimal order of dimensions in the source file, and to sort this optimally. Do this, order the fields in your source file (or SQL statement) by having as your first field your largest sparse dimension, your next field your next largest sparse dimension, and so on. So if you are using the hourglass dimension order, you data file should have dimensions listed from the bottom dimension upwards. Your dense dimensions should always be last, and if you have multiple data columns these should be dense dimension members. Then you should sort the data file in the same order, i.e. by largest sparse dimension, next largest sparse dimension, etc. This will cause blocks to be created and filled with data in sequence, making the data load faster and the cube less fragmented.

These are just the initial general optimization points which can cause huge performance improvements without too much effort,  generally these ones should handle 70% of our optimization issues.

With Warm Regards
SST.!

Business Rule Migration from One Server to Another using Export Import in EAS.

Business rule  Migration from one Server to Another using Export Import in EAS

When we export the Business Rules (BR's), EAS exports the BR's in a xml file format, this xml files contains all the BR's, Security (if you are not exporting for Calc Manager), locations etc. Now if we are trying to migrate the BR's from say Production to Development environment, we have to update the location of the BR's in the xml file because when we export our xml file, it have the location of BR's of Production Server and here we are trying to migrate the rules to Development, so we need to update the xml and modify the location of these BR's.

Here are the steps to export and import rules from one server to another:

1. Log into EAS console of Source Environment using an admin id.
2. Right Click on Business Rules Node.
3. Click on Export.
4. In the right hand pane you will get all the BR's listed. Click on Select All.
5. Uncheck For Calc Manager.
6. Click on Dependents.
7. Export the rules.
8. This will ask to save the BR's in a XML format, save the xml file.
9. Save As the xml file with a different name.
10.Edit the xml file and replace all the Old locations of BR's with the new one, the location is case sensitive and there will be two location we need to replace small case with the small one and Upper case with the Upper one, 

Ex: A part of exported .xml

Source .xml:

<Location>
<property class="int" method="setLocID" value="41"/>
<property class="int" method="setLocationID" value="41"/>
<property class="java.lang.String" method="setLocation" value="ProdClusterMXYZ"/>
<property class="java.lang.String" method="setUpperLocation" value="PRODCLUSTERMXYZ"/>
<property class="int" method="setCluster" value="-1"/>
</Location>

Say if our Destination Location is: "Planning/WDEPMR0543/PLAN/XYZ"

In this case in your source file replace All:

ProdClusterMXYZ with Planning/WDEPMR0543/PLAN/XYZ

PRODCLUSTERMXYZ with PLANNING/WDEPMR0543/PLAN/XYZ

12. Save the file.
13. Login to the destination EAS console.
14. Right click on Business Rule node.
15. Import the BR's using the modified .xml file.

Hope this Helps.

Greetings

SST!

How to avoid implicit share in config file.

When does Implicit sharing happen in Essbase? And what are the ways we can define them in Essbase config file?

Cause & Solution

1) A parent has only one child. In this situation, the parent and the child contain the same data.
Essbase ignores the consolidation property on the child and stores the data only once — thus the parent has an implied shared relationship with the child.

2) A parent has only one child that consolidates to the parent. If the parent has four children, but three are marked as no consolidation, the parent and child that consolidates contain the same data. Essbase ignores the consolidation property on the child and stores the data only once — thus the parent has an implied shared relationship with the child.

3) The following commands can be inserted in the CFG file to disable Implied Sharing:
IMPLIED_SHARE FALSE/TRUE (This shall Turn OFF/ON implied sharing for All Application on that Essbase Server)
IMPLIED_SHARE [app_name] FALSE/TRUE (This shall Turn OFF/ON implied sharing for Specified Application on that Essbase Server)
Tagging the Outline Members “Never Share” Turns OFF implied Sharing for those Members and not tagging Turns ON implied sharing.

Order of Prevalence
Member level Implied Sharing Setting – Prevails over Application, Server Setting
Application level Implied Sharing Setting – Prevails over Server Setting
Server level Implied Sharing Setting – Least

4) ImpliedShare.txt exists in E:\Oracle\Middleware\user_projects\epmsystem1\ EssbaseServer\essbaseserver1\bin

Hope this Helps.

Greetings

SST!

Application Performance optimization

Application Performance optimization

Application Performance optimization can be done by below techniques:

Designing of the Outline using Hour Glass Model

Defragmentation

Restructuring

Compression techniques

Cache Settings

Intelligent calculation

Uncommitted Access

Data Load Optimization

Designing of the Outline using Hour Glass Model:

Outline should be designed in such a way that dimensions are placed in the following order – first put the largest (in number of members) dense dimension, then the next largest dense dimension, and continue until the smallest dense dimension. Now put the smallest sparse dimension, then next smallest, and continue until the largest sparse dimension followed by the attribute dimension.

Hour glass model improves 10% of calculation performance of cube.

Defragmentation:

Fragmentation is caused due to the following:

Frequent Data load

Frequent Retrieval

Frequent Calculation

We can check whether the cube is fragmented or not by seeing it Average Clustering Ratio in the properties. The Optimum clustering value is 1, if the average clustering ratio is less than 1, then the cube is fragmented which degrades the performance of the cube.

There are 3 ways of doing defragmentation:

Export Data of the application in text files, then clear data and reload it without using rule files.

Using Maxl Command. Maxl > Alter Database Appname.DBname Force restructure

Add and Delete One dummy member in the dense dimension.

Restructuring:

There are 3 types of restructure:

Outline Restructure

Sparse Restructure

Dense Restructure / Full Restructure

Outline Restructure: When we rename any member or add alias to any member then outline restructure would happen.

.OTL file is converted to .OTN which in turn converts in to .OTL again.

.OTN file is a temp file deleted by default after restructure.

Dense Restructure: If a member of dense dimension is moved, deleted or added, Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data clocks. Empty blocks are not removed. Essbase marks all restructure block as dirty, so after a dense restructure you must recalculate the database. Dense restructuring is most time consuming, can take a long time to complete for large database.

Sparse Restructure: If a member of sparse dimension is moved, deleted or added, Essbase restructure the index and creates new index files. Restructuring the index is relatively fast. Time required depends on index size.

Compression technique:

When Essbase stores blocks to disk, it can compress the data blocks using one of the following compression methods, this is based on the type of data that is being loaded into the Essbase database.

No Compression:  It is what it says, no compression is occurring on the database.
zLib Compression:  This is a good choice if your database has very sparse data.
Bitmap compression:  This is the default compression type and is good for non-repeating data.
RLE (Run Length Encoding) compression:  This type of compression is best used for data with many zeroes or repeating values.
Index value Pair: Essbase applies this compression if the block density is less than 3%.Index Value Pair addresses compression on databases with larger block sizes, where the blocks are highly sparse.

In most of all cases Bitmap is always the best choice to give your database the best combination of great performance and small data files.  On the other hand much depends on the configuration of the data that is being placed into the database.  The best way to determine the best method of compression is to attempt each type and evaluate the results.

Caches: There are 5 types of caches

Index Cache: It is a buffer in memory that holds index files (.ind). Index cache should be set equal to the size of index file.

Note- Restart the database in order to make the new cache setting come onto effect.

Data Cache: It is a buffer in memory that holds uncompressed data blocks. Data cache should be 12.5% of PAG file memory, by default it is set to 3MB.

Data File Cache: It is a buffer in memory that holds compressed data blocks. Size of data file cache should be size of PAG file memory. It is set to 32MB by default. Max size for it is 2GB.

Calculator Cache: it is basically used to improve the performance of calculation. We set the calculator cache in calculation script. Set cache High|Low|off. We also set cache value for calculator cache in Essbase.cfg file. We need to restart the server to make the changes in calculator caches after setting it in the config file.

Dynamic Calculator Cache: The dynamic calculator cache is a buffer in memory that Essbase uses to store all of the blocks needed for calculation of Dynamic Calc member in a dense dimension.

Intelligent Calculation:

Whenever the block is created for the first time Essbase would treat it as Dirty block. When we run CalcAll/Calc dim, Essbase would calculate and mark all blocks as clean. Subsequently, when we change value in any blocks, it will be marked as Dirty block and when we run the script again only dirty block are calculated and this is known as Intelligent calculation.

Be default calculation is ON. To turn of the intelligent calculation use command Set Update Calc Off in scripts.

Uncommitted Access:

Under uncommitted access, Essbase locks blocks for write access until Essbase finishes updating the block. Under committed access, Essbase holds locks until a transaction completes. With uncommitted access, blocks are released more frequently than with committed access. The Essbase performance is better if we set uncommitted access. Beside parallel calculation only works with uncommitted access.

Data load Optimization: Data load optimization can be achieved by the following

Always load the data from Server than File System.

The data should be as last after the combination in the data load file.

Should use #Mi instead of zero (0). If we use zero it use 8 bytes if memory for each cell.

Restrict max decimal points to ‘3’ like 1.234

Data should be loaded in the form of Inverted Hour Glass Model.

Always pre-Aggregate data before loading data in to database.

These are just the initial general optimization points which can cause huge performance improvements without too much effort, generally these ones should handle 70% of our optimization issues.

 Hope this Helps.

Greetings

SST!

Data Block & Index System

Data Block (Building Block of Essbase) & Index System

Data values are defined as the intersection of one member from one dimension with one member from another dimension. A data value is stored in one cell in the database.  In order to refer to that specific data value in a multidimensional database you need to specify its members from each dimension.

  

Essbase stores and accesses data through the use of data blocks and the index system. A data block is created for each unique combination of sparse dimension members.  Essentially, the data block represents all the dense dimension members for its combination of sparse dimension members.

Every time a data block is created, Essbase then creates an index entry.  The index is comprised of combinations of sparse dimension members.  There is one entry for each combination of sparse dimension members where a data value exists. The data block is a fixed format data structure the existence of which is driven by data-relevant sparse member combinations in the index. By data-relevant we mean that only where business data actually exists across sparse member combinations will a data block be generated.

In the example below, Entity, Scenario, and Year are sparse dimensions, while Accounts and Periods are dense dimensions.  When Essbase searches for a data value, it is using the index to locate the block containing that data value.  Once the data block is located, it targets the exact cell containing the data value.  Essbase is able to handle sparse data so well because the index provides a pointer to the correct data block.  Once the data block is located, Essbase can quickly retrieve the data value.  In the example below, let’s say you are searching for Budgeted Expense for NY in February 2013.   The index provides a pointer using the sparse dimensions to locate the data block containing the data for NY, Budget, 2013. The data value in question is then contained at the intersection of Expense and Feb.

Introduction about Essbase

Essbase stands for Extended SpreadSheet dataBASE.

Essbase is a multi-threaded OLAP database software that takes advantage of symmetric multiprocessing hardware platforms - is based on Web-deployable, thin-client architecture. The server acts as a shared resource, handling all data storage, caching, calculations, and data security.

Essbase Architecture

Essbase product incorporate powerful architecture feature to handle a wide range of analytic application across the large multiuser environments.

Essbase product family feature a middle tier architecture to handle a wide range of analytic application across large multiple-user environments.

The database tier consists of Essbase Server (where Essbase database are stored) and any relational databases that you are using to support Essbase environment.

The client tier includes locally installed client application, such as EAS & Smart View

The middle tier includes application services that facilitates the communication and data transfer between the database tier and client tier.

Essbase Storage Models

Essbase supports two storage types: Block Storage (BSO) and Aggregate Storage (ASO). Essbase can support many different business model by using these two models.

Below screen shot provide you a partial list of business analyses that you can model in Essbase, with suggestion for the storage type that best meets the challenge of each business model.

Block Storage

Block Storage databases are optimized for data set that are partially dense. Data is stored in dense data blocks which are indexed along sparse dimension for retrieval.

This storage paradigm enables you to perform:

• Top down budgeting and planning
• In addition to sophisticated pre-aggregation calculations

Aggregation Storage

Aggregate storage database are optimized for sparse data set that primarily require simple aggregation. Any non-aggregation calculations are performed dynamically when requested in reports. Incremental loading and fast aggregation can provide near real-time analysis of transactional data.

Aggregate storage databases enable dramatic improvements in both:

• Database aggregation time
• Dimensional scalability.