Entering TEXT data in Essbase using Text List
Hi All,
In essbase 11 onwards there is an option to have planning smartlist like functionality in Essbase cube .. This new functionality is called Text List .. Just like planning smartlist, using essbase text list you can enter Text data in essbase also, you can restrict entries as in smartlist. Following is the process for having textlists in Essbase.
1. Enable type measures for essbase database : Go to Outline Properties and enable Typed measures.
2. Creating Text List : Just like planning smartlist , create Essbase text list by going into Text List Manager Tab. In below screenshot I have created a text list for inserting ratings.
In essbase 11 onwards there is an option to have planning smartlist like functionality in Essbase cube .. This new functionality is called Text List .. Just like planning smartlist, using essbase text list you can enter Text data in essbase also, you can restrict entries as in smartlist. Following is the process for having textlists in Essbase.
1. Enable type measures for essbase database : Go to Outline Properties and enable Typed measures.
 |
| Add caption |
2. Creating Text List : Just like planning smartlist , create Essbase text list by going into Text List Manager Tab. In below screenshot I have created a text list for inserting ratings.
3. Apply data type as Text and assign TextList : In below example I have assigned Rating Textlist to Rating account.
Now your Essbase tasklist is ready for use .. You can use it just like Smartlist from smartview. Also, using excel ad-in you can simply lock n send text data into Essbase. For calculation purpose just like Smartlist you can use id value ..
Cheerz!
-Chinmay Joshi
Now your Essbase tasklist is ready for use .. You can use it just like Smartlist from smartview. Also, using excel ad-in you can simply lock n send text data into Essbase. For calculation purpose just like Smartlist you can use id value ..
Cheerz!
-Chinmay Joshi
SATURDAY, JULY 17, 2010
Block creation Issue
Hi All,
Many times while writing Essbase calc scripts you must have encountered an issue of block creation i.e. you are not able to see any results for your simple calculations !...
Essbase normally create blocks when data is loaded, during aggregation of sparse dimensions
and as a result of a DATACOPY command.
Essbase does not create blocks as a result of most dense member assignment calculations. For
example based on the Sample.Basic outline, assume that “Sales” is a dense dimension member
and actual is a sparse dimension member. The following formula will fail because it will not
create new blocks for Budget.
FIX (budget)
"Sales"="Sales"->"Actual"*1.1;
ENDFIX
So, in short if you are performing some basic arithmatic calculations, essbase won't create new block for sparse dimensions.. This is done by essbase to improve it's calculation performance but it is a headache for developer..
Ways to tackle this issue are:
1.DATACOPY into Sparse Member:
You can use the data copy command to create blocks in the same pattern as the actual
blocks. In above example you can do DATACOPY ACTUAL TO BUDGET prior to writing calc. command... It will first create a block for Budget and then will overwrite a calculated value into it.. You can also fix some combination and always enter some dummy data into it as a part of your essbase process and can use this combination for block creation.
e.g. You can always enter '1' in Actual->Test_Acc and can use it for all block creation datacopy commands..
2. CALCULATION COMMANDS TO ENABLE BLOCK CREATION
You can also use SET CREATEBLOCKONEQ and SET CREATENONMISSINGBLK commands for block creation. But these commands are bit heavy on your calculation and degrades calc script performance so for big calc scripts it is always advisable to use DATACOPY approach...
Cheerz!
--CJ
Many times while writing Essbase calc scripts you must have encountered an issue of block creation i.e. you are not able to see any results for your simple calculations !...
Essbase normally create blocks when data is loaded, during aggregation of sparse dimensions
and as a result of a DATACOPY command.
Essbase does not create blocks as a result of most dense member assignment calculations. For
example based on the Sample.Basic outline, assume that “Sales” is a dense dimension member
and actual is a sparse dimension member. The following formula will fail because it will not
create new blocks for Budget.
FIX (budget)
"Sales"="Sales"->"Actual"*1.1;
ENDFIX
So, in short if you are performing some basic arithmatic calculations, essbase won't create new block for sparse dimensions.. This is done by essbase to improve it's calculation performance but it is a headache for developer..
Ways to tackle this issue are:
1.DATACOPY into Sparse Member:
You can use the data copy command to create blocks in the same pattern as the actual
blocks. In above example you can do DATACOPY ACTUAL TO BUDGET prior to writing calc. command... It will first create a block for Budget and then will overwrite a calculated value into it.. You can also fix some combination and always enter some dummy data into it as a part of your essbase process and can use this combination for block creation.
e.g. You can always enter '1' in Actual->Test_Acc and can use it for all block creation datacopy commands..
2. CALCULATION COMMANDS TO ENABLE BLOCK CREATION
You can also use SET CREATEBLOCKONEQ and SET CREATENONMISSINGBLK commands for block creation. But these commands are bit heavy on your calculation and degrades calc script performance so for big calc scripts it is always advisable to use DATACOPY approach...
Cheerz!
--CJ
FRIDAY, JANUARY 15, 2010
Design Essbase ASO aggregation
Hi,
ASO cubes are by default dynamic in nature i.e. in ASO all aggregations happen on the fly .. So data is stored only at Lev 0 .. For small ASO cubes it gives pretty good performance in reporting, but for huge ASO cubes ( Generally which is the case) report performance is a concern ..
'Design Aggregation' feature of ASO is a good option provided by Essbase .. In aggregation data is calculated at upper level and stored, so data retrieval becomes much faster.. Following are the steps for designing aggregation for ASO cubes
1. Enable 'Query Tracking' : Query tracking will track data combinations which have more data values and which requires Agg to improve performance..
e.g. If you have 2 parents with 100 children each, then Query tracking will keep track of which all children have data for some combinations and will suggest in future required aggregate views .
2. Go to 'Design Aggregation'
ASO cubes are by default dynamic in nature i.e. in ASO all aggregations happen on the fly .. So data is stored only at Lev 0 .. For small ASO cubes it gives pretty good performance in reporting, but for huge ASO cubes ( Generally which is the case) report performance is a concern ..
'Design Aggregation' feature of ASO is a good option provided by Essbase .. In aggregation data is calculated at upper level and stored, so data retrieval becomes much faster.. Following are the steps for designing aggregation for ASO cubes
1. Enable 'Query Tracking' : Query tracking will track data combinations which have more data values and which requires Agg to improve performance..
e.g. If you have 2 parents with 100 children each, then Query tracking will keep track of which all children have data for some combinations and will suggest in future required aggregate views .
2. Go to 'Design Aggregation'
3. First select Use recommended views to materialize aggregation .. It will show all the views selected by system .. It is a good practice to include them first in Agg view definition ... Select all these views and save and materialize them .
4. Go back to 'Design Aggregation' and now select second option .
5. Select 'Select all recommended aggregate views' .. You can specify total storage space in this screen
6. Select 'Use query Tracking data during view selection' (Option present at bottom) and click on Start .. It will show all views tracked by system using Query tracking ..
7. Save and materialize views..
8. Repeat these steps once you load any new data and save new aggregate views in existing saved aggregation..
If you change any dimension name or change property of member like if stored becomes Label Only or calculated one , then entire Aggregation becomes invalid and you need to re-do this process..
-Cheerz -- :)
SUNDAY, JUNE 28, 2009
Book on Essbase Implementation
Hey all,
Check this : New book for essbase implementation:
For more info..Check this link :
A great new book by Joseph Sydney Gomez, Sarma Anantapantula on Oracle Essbase 9 Implementation .. Looks impressive.. I think it is first of it's kind implementation guide on Hyperion Essbase ... Must buy for Hyperion practitioners and people who wants to jump into Essbase Space ! :)
Cheerz!
-CJ-
Check this : New book for essbase implementation:
For more info..Check this link :
http://www.packtpub.com/oracle-essbase-9-implementation-guide
A great new book by Joseph Sydney Gomez, Sarma Anantapantula on Oracle Essbase 9 Implementation .. Looks impressive.. I think it is first of it's kind implementation guide on Hyperion Essbase ... Must buy for Hyperion practitioners and people who wants to jump into Essbase Space ! :)
Cheerz!
-CJ-
TUESDAY, JANUARY 27, 2009
Committed access in Essbase
Hi,
Committed access option provided by Hyperion essbase proves very helpful in many cases.First letz understand what is committed and uncommitted access... Committed access means while loading data into essbase , it first stores data in memory and once wait period is over it loads data into physical disk. It is synonymous to Commit access in SQL.Essbase works in similar fashion..You can set committed/uncommitted access in DB properties under transactions.
There are some real scenarios when data load if interrupted between needs to restart from beginning, in essbase we can achieve this by setting committed access and then click on "Abort on error" while data loading.
Committed access option provided by Hyperion essbase proves very helpful in many cases.First letz understand what is committed and uncommitted access... Committed access means while loading data into essbase , it first stores data in memory and once wait period is over it loads data into physical disk. It is synonymous to Commit access in SQL.Essbase works in similar fashion..You can set committed/uncommitted access in DB properties under transactions.
There are some real scenarios when data load if interrupted between needs to restart from beginning, in essbase we can achieve this by setting committed access and then click on "Abort on error" while data loading.
WEDNESDAY, JANUARY 21, 2009
Restoring Data from Backups
Restoring Data from Backups
To restore data from backup:
1.Stop OpenLDAP service or process.
2.Using a command prompt window, navigate to/server/scripts and execute the following command.
Windows:
recover.bat
UNIX:
recover.sh
In this command,
Path_backup_directory indicates the path of the directory where the backup is stored.
3.Monitor the utility to ensure that it runs successfully.
4.Start OpenLDAP.
5.Restart Shared Services.
To restore data from backup:
1.Stop OpenLDAP service or process.
2.Using a command prompt window, navigate to
Windows:
recover.bat
UNIX:
recover.sh
In this command,
Path_backup_directory indicates the path of the directory where the backup is stored.
3.Monitor the utility to ensure that it runs successfully.
4.Start OpenLDAP.
5.Restart Shared Services.
WEDNESDAY, JANUARY 14, 2009
Rules for Base and Attribute Dimensions and Members
Rules for Base and Attribute Dimensions and Members
There are several important rules regarding members of attribute dimensions and their base
dimensions.
● You can tag only sparse dimensions as attribute dimensions.
● Before you can save an outline to the server, each attribute dimension must be associated
with a standard, sparse dimension as its base dimension.
● Attribute dimensions must be the last dimensions in the outline.
● Attribute dimensions have a type setting—text, numeric, Boolean, or date. Text is the default
setting. Although assigned at the dimension level, the type applies only to the level 0 members
of the dimension.
● If you remove the attribute tag from a dimension, Essbase removes prefixes or suffixes from
its member names. Prefixes and suffixes are not visible in the outline. For more information,
● A base dimension member can have many attributes, but only one attribute from each
particular attribute dimension.
For example, product 100-10 can have size and packaging attributes, but only one size and only one type of packaging.
● You cannot associate an attribute with an implied shared member the child of which is tagged as shared.
● Essbase does not support attributes for Hybrid Analysis-enabled members.
You can use attribute values in calculations in the following comparisons:
● > (greater than)
● >= (greater than or equal to)
● < (less than)
● <= (less than or equal to)
● = = (equal to)
● <> or != (not equal to)
● IN
There are several important rules regarding members of attribute dimensions and their base
dimensions.
● You can tag only sparse dimensions as attribute dimensions.
● Before you can save an outline to the server, each attribute dimension must be associated
with a standard, sparse dimension as its base dimension.
● Attribute dimensions must be the last dimensions in the outline.
● Attribute dimensions have a type setting—text, numeric, Boolean, or date. Text is the default
setting. Although assigned at the dimension level, the type applies only to the level 0 members
of the dimension.
● If you remove the attribute tag from a dimension, Essbase removes prefixes or suffixes from
its member names. Prefixes and suffixes are not visible in the outline. For more information,
● A base dimension member can have many attributes, but only one attribute from each
particular attribute dimension.
For example, product 100-10 can have size and packaging attributes, but only one size and only one type of packaging.
● You cannot associate an attribute with an implied shared member the child of which is tagged as shared.
● Essbase does not support attributes for Hybrid Analysis-enabled members.
You can use attribute values in calculations in the following comparisons:
● > (greater than)
● >= (greater than or equal to)
● < (less than)
● <= (less than or equal to)
● = = (equal to)
● <> or != (not equal to)
● IN
SATURDAY, JANUARY 3, 2009
Fine Tuning Hyperion Essbase Cache Settings
Fine Tuning Cache Settings
After using a database at your site with typical data, user access, and standard environment (including server machines, network, etc.), check to see how Essbase performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust your cache settings.
Understanding Cache Settings
The sizes of the index cache and the data file cache (when direct I/O is used) are the most critical Essbase cache settings. In general, the larger these caches, the less swapping activity occurs; however, it does not always help performance to set cache sizes larger and larger. Read this entire section to understand cache size considerations.
Index Cache
The advantages of a large index cache start to level off after a certain point. Whenever the index cache size equals or exceeds the index size (including all index files on all volumes), performance does not improve. However, to account for future growth of the index, you can set the index cache size larger than the current index size. Because the index cache is filled with index pages, for optimum use of storage, set the size of the index cache to be a multiple of the size of the index page (8 KB). See Index Files for an example of estimating index size.
Data File Cache
If possible, set the data file cache to equal the size of the stored data, which is the combined size of all ess*.pag files. Otherwise, the data file cache should be as large as possible. If you want to account for future growth of stored data, you can set the data file cache size larger than the current size of stored data.
Note:
The data file cache is used only if you are using direct I/O.
Data Cache
The data cache should be about 0.125 times the data file cache. However, certain calculations require a larger data cache size. If many concurrent users are accessing different data blocks, this cache should be larger.
In general, if you have to choose between allocating memory to the data file cache or allocating it to the data cache, choose the data file cache if you are using direct I/O. If you are upgrading from a previous version of Essbase, see the Hyperion Essbase - System 9 Installation Guide.
Checking Cache Hit Ratios
Every cache has a “hit ratio.” The hit ratio indicates the percentage of time that a requested piece of information is available in the cache. You can check the hit ratio of the index cache, the data cache, and the data file cache to determine whether you need to increase the cache size.
* To check cache hit ratios, see “Checking Cache Hit Ratios” in Essbase Administration Services Online Help.
*
The cache hit ratio indicates the percentage of time that a requested piece of information is already in the cache. A higher hit ratio indicates that the data is in the cache more often. This improves performance because the requested data does not have to be retrieved from disk for the next process. A hit ratio of 1.0 indicates that every time data is requested, it is found in the cache. This is the maximum performance possible from a cache setting.
*
The Hit Ratio on Index Cache setting indicates the Essbase Kernel success rate in locating index information in the index cache without having to retrieve another index page from disk.
*
The Hit Ratio on Data File Cache setting indicates the Essbase Kernel success rate in locating data file pages in the data file cache without having to retrieve the data file from disk.
*
The Hit Ratio on Data Cache setting indicates the Essbase success rate in locating data blocks in the data cache without having to retrieve the block from the data file cache.
*
Check memory allocation. Add smaller amounts of memory at a time , if needed, because a smaller increment may have the same benefit as a large one. Large, incremental allocations of memory usually result in very little gain in the hit ratio.
After using a database at your site with typical data, user access, and standard environment (including server machines, network, etc.), check to see how Essbase performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust your cache settings.
Understanding Cache Settings
The sizes of the index cache and the data file cache (when direct I/O is used) are the most critical Essbase cache settings. In general, the larger these caches, the less swapping activity occurs; however, it does not always help performance to set cache sizes larger and larger. Read this entire section to understand cache size considerations.
Index Cache
The advantages of a large index cache start to level off after a certain point. Whenever the index cache size equals or exceeds the index size (including all index files on all volumes), performance does not improve. However, to account for future growth of the index, you can set the index cache size larger than the current index size. Because the index cache is filled with index pages, for optimum use of storage, set the size of the index cache to be a multiple of the size of the index page (8 KB). See Index Files for an example of estimating index size.
Data File Cache
If possible, set the data file cache to equal the size of the stored data, which is the combined size of all ess*.pag files. Otherwise, the data file cache should be as large as possible. If you want to account for future growth of stored data, you can set the data file cache size larger than the current size of stored data.
Note:
The data file cache is used only if you are using direct I/O.
Data Cache
The data cache should be about 0.125 times the data file cache. However, certain calculations require a larger data cache size. If many concurrent users are accessing different data blocks, this cache should be larger.
In general, if you have to choose between allocating memory to the data file cache or allocating it to the data cache, choose the data file cache if you are using direct I/O. If you are upgrading from a previous version of Essbase, see the Hyperion Essbase - System 9 Installation Guide.
Checking Cache Hit Ratios
Every cache has a “hit ratio.” The hit ratio indicates the percentage of time that a requested piece of information is available in the cache. You can check the hit ratio of the index cache, the data cache, and the data file cache to determine whether you need to increase the cache size.
* To check cache hit ratios, see “Checking Cache Hit Ratios” in Essbase Administration Services Online Help.
*
The cache hit ratio indicates the percentage of time that a requested piece of information is already in the cache. A higher hit ratio indicates that the data is in the cache more often. This improves performance because the requested data does not have to be retrieved from disk for the next process. A hit ratio of 1.0 indicates that every time data is requested, it is found in the cache. This is the maximum performance possible from a cache setting.
*
The Hit Ratio on Index Cache setting indicates the Essbase Kernel success rate in locating index information in the index cache without having to retrieve another index page from disk.
*
The Hit Ratio on Data File Cache setting indicates the Essbase Kernel success rate in locating data file pages in the data file cache without having to retrieve the data file from disk.
*
The Hit Ratio on Data Cache setting indicates the Essbase success rate in locating data blocks in the data cache without having to retrieve the block from the data file cache.
*
Check memory allocation. Add smaller amounts of memory at a time , if needed, because a smaller increment may have the same benefit as a large one. Large, incremental allocations of memory usually result in very little gain in the hit ratio.
Hyperion Essbase Caches
Essbase uses five memory caches to coordinate memory usage:
Index cache
The index cache is a buffer in memory that holds index pages. How many index pages are in memory at one time depends upon the amount of memory allocated to the cache.
Data file cache
The data file cache is a buffer in memory that holds compressed data files (.pag files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. The data file cache is used only when direct I/O is in effect.
Data cache
The data cache is a buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.
Calculator cache
The calculator cache is a buffer in memory that Essbase uses to create and track data blocks during calculation operations.
Dynamic calculator cache
The dynamic calculator cache is a buffer in memory that Essbase uses to store all of the blocks needed for a calculation of a Dynamic Calc member in a dense dimension (for example, for a query).
Index cache
The index cache is a buffer in memory that holds index pages. How many index pages are in memory at one time depends upon the amount of memory allocated to the cache.
Data file cache
The data file cache is a buffer in memory that holds compressed data files (.pag files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. The data file cache is used only when direct I/O is in effect.
Data cache
The data cache is a buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.
Calculator cache
The calculator cache is a buffer in memory that Essbase uses to create and track data blocks during calculation operations.
Dynamic calculator cache
The dynamic calculator cache is a buffer in memory that Essbase uses to store all of the blocks needed for a calculation of a Dynamic Calc member in a dense dimension (for example, for a query).
