Improving The User Experience with Global Rates

, , ,

 

Almost every planning or forecasting application will have some type of allocation based on a driver or rate that is loaded at a global level.  Sometimes these rates are a textbook example of moving data from one department to another based on a driver, and sometimes they are far more complicated. Many times, whether it is an allocation, or a calculation, rates are entered (or loaded) at a higher level than the data it is being applied to.

A very simple example of this would be a tax rate.  In most situations, the tax rate is loaded globally and applied to all the departments and business units (as well as level 0 members of the other dimensions).  It may be loaded to “No Department”, “No Business Unit”, and a generic member in the other custom dimensions that exist.

If a user needs the tax rate, in the example above, they have to pull “No Department” and “No Business Unit.”  Typically, users don’t want to take different members in the dimension to get a rate that corresponds to the data (Total Department for taxes, and No Department for the rate).  They want to see the tax rate at Total Department, Total Business Unit, and everywhere in-between.

There are a number of ways to improve the experience for the user.  An effective solution is to have two members for each rate.  One is stored and one is dynamic.  There is no adverse effect on the number of blocks, or the block size.  The input members can be grouped in a hierarchy that is rarely accessed, and the dynamic member can be housed in a statistics hierarchy.

Using tax rate in the example above, create a “Tax Rate Input” member.  Add this to a hierarchy called “Rate Input Members”.  Any time data is loaded for the tax rate; it is loaded to Tax Rate Input, No Department, No Business Unit, etc.  Under the statistics/memo hierarchy, create a dynamic member called “Tax Rate”.  “Tax Rate” would be the member referenced in reports.  The formula for this includes a cross-dimensional reference to the “Tax Rate Input” member, and would look something like this.

“No Department”->”No Business Unit”->”Tax Rate Input”;

When a user retrieves “Tax Rate”, it always returns the rate that is loaded to “No Department,” “No Business Unit,” and “Tax Rate Input,” no matter what department or business unit the report is set to.  The effort involved in creating reports in Financial Reporting or Smart View now becomes easier!

There is an added bonus for the system administrators.  Any calculation that uses the rate (you know, the ones with multi-line cross-dimensional references to the rates) is a whole lot easier to write, and a whole lot easier to read because the cross-dimensional references no longer exist.

Before you move the application to production, make sure to set the input rates consolidation method to “Never.”  Don’t expect this change to make great improvements in performance, but it will cause the aggregations to ignore these members when consolidating the hierarchies.  A more important benefit is that users won’t be confused if they ever do look at the input rates at a rolled up level.  The ONLY time they would see the rate would be at level 0, and would be an accurate reflection of the rate.

Note:  It is recommended to create member names without spaces.  The examples above ignored this rule in an effort to create an article that is more readable.

 
/0 Comments/by

Navigating Misaligned Menus in Workspace with IE8

, , ,

 

If you have recently upgraded your web browser to IE8 and attempted to use Hyperion Workspace, you’ve likely encountered difficulty in navigating the interface. When a user clicks on the wheel in the upper left hand corner, selects “Applications,” they can’t see the menu as it apearrs to be condensed. 

 

This issue can be quickly resolved by making a browser modification to the IE security. It is best to run this settings change past your IT department to ensure it will not open holes in your company’s security profile.

 

To correct this issue, 

 

1.       Open IE8

 

2.       select “Tools”->”Internet Options”

 

3.       Select the “Security” tab and “Custom Level”.

 

4.       You will have 4 zones where security changes can be performed (Internet, Local Internet, Trusted sites, Restricted sites). The change can be made to each of these 4 zones if necessary, but it’s possible only one zone needs modified. Test the combination that works for your environment and fits your companies IT/security profile.

 

5.       Select the “Internet” zone and scroll down the menu of options to find “Allow script-initiated windows without size or position constraints”. This will likely be set to “Disable”… select “Enable” and click “OK”. (If prompted, accept the change just made to the zone security).

 

6.       Select “OK” from the security tab to finalize the modification.

 

7.       Test the modification to verify the change worked as intended. As noted above, you might need to enable this setting on multiple zones in order for the setting to take effect.

 
/0 Comments/by

Unique Essbase Customizations Using Java – Custom Logging

, , , ,

Introduction

Many companies have in depth working knowledge of Hyperion Essbase and are looking to enhance their enterprise reporting capabilities to the next level. Companies typically have specific processes and calculations that set them apart in their industry. However, they are often limited to basic reporting capabilities provided by the standard functions in Essbase. Additionally, complex operations can quickly become arduous using Calculation Scripts and Business Rules. This post will demonstrate the how to easily build Custom Java Routines to extend Essbase and dramatically reduce development time.

Complete details will be provided on how to implement a simple customized logging function for use in Calculation Scripts and Business Rules. Essbase’s streamlined, parallel nature makes it difficult for application developers to trace line by line. By using Java to implement a custom logging routine, one may use personalized log entries within their Essbase scripts. Consequently, developers can add tracing to their scripts and quickly determine how Essbase is approaching each calculation. Accordingly, application developers are able to see exactly how the script is being executed – providing quick debugging and faster development time. One powerful feature is to help determine block creation  within FIX statements.

The first step to integrating a custom Java routine into Essbase is to write some simple Java code. It is very easy – the code does not have to include any special APIs for Essbase.  During development, a few issues were encountered where Essbase was a bit picky about how the code is written.  Here are a few tips to help in getting started. These tips were gathered while doing real development, and it is best to follow at first, though you may revisit the items and find out what will work for you.

  • Do not include the code in a package such as “com.company.product_name” – remove the “package” declarative at the top of the code
  • Do not use the keyword “this” to refer to variables
  • Do not overload methods
  • Set all methods and variables to static

With these provisions in mind, the following code can be written to implement a custom logging routine.

CustomLoggerV2.java

import java.io.FileWriter;
import java.io.Writer;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.Calendar;
import java.util.ArrayList;

public class CustomLoggerV2
{

    private static String logFile;
   
    public static int logFilterLevel;
   
    public static void setLogFilterLevel(int logFilterLevel2)
    {
        logFilterLevel = logFilterLevel2;
    }
   
    public static void setLogFilename(String logFilename)
    {
        logFilterLevel = 0;
        logFile = logFilename ;
    }

    public static synchronized void customLog (int logLevel, String message)
    {
        log(logLevel, message);
    }

    private static synchronized void log (int logLevel, String message)
    {
       
       
        // do not log
        if (logLevel < logFilterLevel)
            {
                return ;
            }
       
        try {
       
            Calendar c = Calendar.getInstance();
           
            FileWriter fw = new FileWriter(logFile, true);
               
            fw.write(c.getTime()   ": "   message   "\n");
            fw.close();
             
        } catch (Exception e)
            {
                System.out.println("Error, cannot open , "   logFile);
                e.printStackTrace();
            }
        }

 
}

The code implements three public methods:

  • setLogFilterLevel(int logFilterLevel) – sets the minimum message level to log (think about ERROR=100, WARN=90, INFO=70, DEBUG=0) – so you can easily change the verbosity of the output.
  • setLogFilename(String filename) – The full path to the log file you wish to use
  • customLog(logLevel, String message) – The log message, with its indicated priority

The next step is to package up the code above. It is important to use the same version of Java which is running your Essbase instance. To find the version, look for the JRE being used within the environment, for instance, Hyperion\common\JRE\Sun\1.5.0\bin. To obtain the specific revision, open a cmd prompt, cd to the bin directory, and run “java –version”.

E:\Hyperion\common\JRE\Sun\1.5.0\bin>java -versi
java version "1.5.0_11"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)

Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode

To compile the code a JDK is required, which will contain the javac command. Hyperion only packages the JRE, meaning you will have to download the correct JDK in order to compile the code. You can find older versions of Java JDK from Oracle(Sun)’s web site. Once you have obtained the correct version of the JDK, compile and package up the code:

javac CustomLoggerV2.java

jar -cf CustomLoggerV2.jar CustomLoggerV2.class

Next, copy the CustomLoggerV2.jar file into the Essbase file structure:

Copy CustomLoggerV2.jar into the E:\Hyperion\products\Essbase\EssbaseServer\java\udf folder. If the udf folder does not already exist, create it.

Now it is time to start including the Java class within Essbase. Essbase runs within its own JVM and therefore has its own Java security. In the example above, we are writing to a local log file, which will violate the default security policy setup in the udf.policy file. The file is usually found in Hyperion\products\Essbase\EssbaseServer\java . The simplest way to get around the security concerns for development purposes is to remove the comment from the last line in the file, which effectively includes the directive “permission java.security.AllPermission”

…

permission java.util.PropertyPermission “java.vm.version”, “read”;

permission java.util.PropertyPermission “java.vm.vendor”, “read”;

permission java.util.PropertyPermission “java.vm.name”, “read”;

// Uncomment the following line if you want to remove all restrictions

permission java.security.AllPermission;

};

Now that the Essbase security and jar file have been put in, a restart of the Essbase process is required to register the changes. Please restart Essbase now.

The final step is to run some maxl statements to register the public java methods with Essbase.

CustomLoggerV2.mxl

create or replace function '@JCustomLoggerV2_setLogFilename'

as 'CustomLoggerV2.setLogFilename(String)'

spec '@JCustomLoggerV2.setLogFilename(absolute file name)'

comment 'Nicholas King'

with property runtime;


create or replace function '@JCustomLoggerV2_customLog'

as 'CustomLoggerV2.customLog(int, String)'

spec '@JCustomLoggerV2.customLog(log level, log message)'

comment 'Nicholas King'

with property runtime;


create or replace function '@JCustomLoggerV2_setLogFilterLevel'

as 'CustomLoggerV2.setLogFilterLevel(int)'

spec '@JCustomLoggerV2.setLogFilterLevel(filter level)'

comment 'Nicholas King'

with property runtime;


One final thing… In order to run a custom java function, the value of the result has to be stored in an Essbase member. This is true even if there is not any use for the return value, such as this case where there is no value returned from the Java methods. To get around this, create a new Essbase member called “No Measure” somewhere within your Essbase outline. This will act as a dummy member intended only to direct the return value, if any, of the Java methods. An example is shown below.


Sample Calc Script or Business Rule to Invoke the Logger


//ESS_LOCALE English_UnitedStates.Latin1@Binary

/* SETUP The Logger */

/* Fix on something so it runs only once */

FIX (Actual, Texas, "100-10")

"No Measure" = @JCustomLoggerV2_setLogFilename("E:\CustomEssbaseLog.log");

"No Measure" = @JCustomLoggerV2_setLogFilterLevel(50);

ENDFIX;

/* In your script, do some actual logging */

FIX (Actual, Texas, "100-10")

/* Won’t be displayed */

"No Measure" = @JCustomLoggerV2_customLog(0, "This is a debug message");

/* Will be displayed */

"No Measure" = @JCustomLoggerV2_customLog(50, "This is a normal message");

"No Measure" = @JCustomLoggerV2_customLog(100, "This is an important message");

ENDFIX;


The result of running the script is the log entries will be added to the log file E:\CustomEssbaseLog.log,


Mon Feb 21 01:30:25 EST 2011: This is a normal message

Mon Feb 21 01:30:25 EST 2011: This is an important message


Troubleshooting Tips


A very common error you may receive is,


Error: 1200324 Error compiling formula for [No Measure] (line 8): operator expected after [@JCustomLogger_customLog]


This error is a generic error that indicates something in your custom function is not registered properly.  Unfortunately, there is not a lot of detailed log information at this point to help discover the problem. If you receive this message a few things might help:


    

  • Retrace your steps – carefully review all instructions above
    • 

  • Check that the correct version of Java was used to compile the class file and package the jar
    • 

  • Check the jar is in the correct “udf” folder in Essbase
    • 

  • Check the syntax of the MAXL to register the functions is correct
    • 

  • Simplify your script as much as possible to reduce the possibility of syntax errors
    • 



Conclusion


This example shows how to create a custom Java based logger integrated into Essbase. The possibilities are endless – anything that can be done in Java can be added to Essbase. You can create development aids, or even read/modify the values within the cube. For instance, this model has successfully been used to perform complex financial calculations within Hyperion Planning Forms using Business Rules.  It could also be used for integrating Web Services with your cube by reading or writing cube data and interacting with an enterprise Web Service.


 
/0 Comments/by 
SmartView Performance Gains Using Compression
, , , , , , 
If you have users that rely on SmartView to pull data from your Essbase and/or Planning application, many of them may have large spreadsheets.  One way to improve the perception of the performance of Essbase is the method in which SmartView (client side) communicates with the server.


APS, Planning, and HFM have the ability to take advantage of compression during the communication process.  When large queries, retrieving and submitting data, are initiated, the performance can be significant.


The default compression settings for APS and Planning are not turned on.  The good news is that turning this on is relatively simple.


Find the essbase.properties file on the APS server and change it to false.  The path to this file is different in versions 9 and 11.  In 11, the path is \Products\Essbase\aps\bin.


smartview.webservice.gzip.compression.disable=false


Open the Hyperion Planning application in question and change the SMARTVIEW_COMPRESSION_THRESHOLD in the System Properties (Administration/Manage Properties – System Properties tab) to a value no less than 1.  This threshold is the minimum size of the query in which compression will be used.  So, a value of 1000 would mean compression would be used for anything greater than 1,000 bytes.


For smaller queries, compression may not be necessary.  It may even decrease performance because of the overhead to compress and uncompress the data.  Every environment is different so there is no “right” answer as to what this value should be.


If you have used compression, please share your experiences.


 
/0 Comments/by 
Recovering An Essbase Application From a Corrupt Data File
, , , , 
It is possible for a database in Essbase to become corrupt.  This can be caused by server hangs, software glitches, and a variety of other reasons.  Although infrequent, if a database cannot be loaded for any reason, and it needs to be restored, the following actions can be a quick resolution.  Keep in mind that this will remove the data and it will need to be imported from a backup export.


Before performing this, verify that the database is not attempting to recover.  To determine if this is occuring, open the application log file.  If it states that it is recovering free space, be patient as it may correct itself.


File Structure


Essbase has a simple file structure that it follows.  It can vary with each application depending on the options used.  The area to focus on for this process is below.  The application and database that is being restored would take the place of appname and dbname.


Hyperion\Products\Essbase\EssbaseServer\App\AppName\DbName


Restoring To A Usable State


In this directory, files with the following extensions will need to be removed.  This will delete all of the data  and temporary settings that are causing the application to function improperly.  It will NOT delete the database outline, calc scripts, load rules, or business rules.


    

  • .ind (index files)
    • 

  • .pag (data files)
    • 

  • .esm (Essbase kernel file that manages pointers to data blocks, and contains control information that is used for database recovery)
    • 

  • .tct (Essbase database transaction control file that manages all commits of data and follows and maintains all transactions)
    • 



After these files are removed, verify that the application and database is functioning.  This can be done in Essbase Administration Services by starting the application.  If the application doesn’t start, more research will have to be performed. If the application loads, import the most recent data backup and run an aggregation.


There are a number of other possible file types in this directory.  Below is some information that may be helpful.


Audit Logs


    

  • .alg:  Spreadsheet audit historical information
    • 

  • .atx:  Spreadsheet audit transaction
    • 



Temporary Files


    

  • .ddm:  Temporary partitioning file
    • 

  • .ddn:  Temporary partitioning file
    • 

  • .esn:  Temporary Essbase kernel file
    • 

  • .esr:  Temporary database root file
    • 

  • .inn:  Temporary Essbase index file
    • 

  • .otm:  Temporary Essbase outline file
    • 

  • .otn:  Temporary Essbase outline file
    • 

  • .oto:  Temporary Essbase outline file
    • 

  • .pan:  Temporary Essbase database data (page) file
    • 

  • .tcu:  Temporary database transaction control file
    • 



Objects


    

  • .csc:  Essbase calculation script
    • 

  • .mxl:  MaxL script file (saved in Administration Services)
    • 

  • .otl:  Essbase outline file
    • 

  • .rep:  Essbase report script
    • 

  • .rul:  Essbase rules file
    • 

  • .scr:  Essbase ESSCMD script
    • 



Other


    

  • .apb:  Backup of application file
    • 

  • .app:  Application file, defining the name and location of the application and other application settings
    • 

  • .arc:  Archive file
    • 

  • .chg:  Outline synchronization change file
    • 

  • .db:  Database file, defining the name, location, and other database settings
    • 

  • .dbb:  Backup of database file
    • 

  • .ddb:  Partitioning definition file
    • 

  • .log:  Server or application log
    • 

  • .lro:  LRO file that is linked to a data cell
    • 

  • .lst:  Cascade table of contents or list of files to back up
    • 

  • .ocl:  Database change log
    • 

  • .ocn:  Incremental restructuring file
    • 

  • .oco:  Incremental restructuring file
    • 

  • .olb:  Backup of outline change log
    • 

  • .olg:  Outline change log
    • 

  • .sel:  Saved member select file
    • 

  • .trg:  Trigger definition file.XML (Extensible Markup Language) format
    • 

  • .txt:  Text file, such as a data file to load or a text document to link as a LRO used for database recovery
    • 

  • .xcp:  Exception error log
    • 

  • .xls:  Microsoft Excel file
    • 



 
/0 Comments/by 
Business Rules That Take More Than Five Minutes to Execute Are Re-launched Repeatedly
, , , , 
There is, what appears to be, a bug in Hyperion Planning that causes business rules that take longer than 5 minutes to re-launch.  The following, published by Oracle, explains the root issue of this problem.  It is not a bug, but a setting in the host web server that causes the request post multiple times.  This explaination from Oracle clearly states that this is ONLY an issue when accessing Hyperion Planning through Hyperion Workspace.  I have seen the same response while accessing Hyperion Planning directly.  Regardless of your entry point, it is a good proctice account for either entry method and should be applied.


This applies to Hyperion Planning, Version: 9.3.1.0.00 to 11.1.1.3.00 and is applicable to all operating systems.


Symptoms


When accessing Planning, Business Rules that normally take more than 5 minutes to complete

run for an unlimited period of time.  By viewing the running Essbase sessions in the EAS console, you can see that the Business Rules “Calculate” sessions are being re-launched every 5 minutes, so that a new instance of the Rule is launched before the first can complete.


This issue only affects Business Rules that normally take more than 5 minutes to complete.


This issue does not affect Business Rules launched directly from Planning (accessing Planning directly on its own URL, bypassing the Workspace).  This issue does not affect Business Rules launched from the EAS console.  This issue only affects systems using Weblogic as a web application server.


Cause


This issue is caused by a default timeout setting of 5 minutes (300 seconds) in the Weblogic HTTP Server Plugin.  This plugin is a set of configuration files in which Weblogic defines how it will interact with the HTTP Server through which Workspace is accessed.  More information on Weblogic Plugins is available here:  http://download.oracle.com/docs/cd/E13222_01/wls/docs92/pdf/plugins.pdf


Solution


Hyperion System 9 and Oracle EPM 11.1.1.x support the use of either Microsoft Internet Information Services (IIS) or Apache as an HTTP server. The steps to increase the timeout depend on which you are using.  The new timeout value should be set to a value larger than the time the longest-running Business Rule takes to execute. The examples below use a setting of 30 minutes (1800 seconds).


Apache HTTP Server


Step 1


Edit %HYPERION_HOME%\common\httpServers\Apache\2.0.52\conf\HYSL-WebLogic.conf


Step 2


Add (or edit, if already present) the following parameters to the two sections for Planning, and also to the two sections for Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.Each section begins with an XML tag.


WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800
  <LocationMatch /HyperionPlanning>
<LocationMatch /HyperionPlanning/*>


Add the new “WLIOTimeoutSecs 1800” and “HungServerRecoverSecs 1800” properties as new lines within the tags.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs 1800” in addition to the “WLIOTimeoutSecs 1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).


PathTrim /
KeepAliveEnabled ON
KeepAliveSecs 20
WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800


Internet Information Services (IIS)


Step 1


There are several copies of the iisproxy.ini file. Oracle recommends you modify the files for Planning, Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.


Paths (note that “hr” below stands for Financial Reporting):


%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\hr

%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\HyperionPlanning

%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\workspace


Step 2


For each copy of iisproxy.ini, add the following lines at the end of each file.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs=1800” in addition to the “WLIOTimeoutSecs=1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).


WLIOTimeoutSecs=1800

HungServerRecoverSecs=1800


Step 3


Restart IIS from the IIS Manager and restart the Workspace web application service


Oracle HTTP Server is used


Step 1


Modify the file mod_wl_ohs.conf file under the directory, $EPM_ORACLE_INSTANCE\httpConfig\ohs\config\OHS\ohs_component with the following content:


<LocationMatch ^/HyperionPlanning/>
SetHandler weblogic-handler
WeblogicCluster PlaningServer:8300
WLIOTimeoutSecs -1
WLSocketTimeoutSecs 600
</LocationMatch>


Step 2


Restart the Oracle HTTP server and the Workspace web application services after the modifications are complete.


 
/0 Comments/by 
Why NOT Using the Currency Option with Hyperion Planning has Benefits
, , , 
Hyperion Planning applications often require multiple currencies.  Hyperion Planning includes a currency option that easily allows multiple currencies to be managed.  Allowing Planning to manage this introduces a couple of limitations and inherent costs. These can be avoided if currency is managed manually.


When the Hyperion Planning currency option is enabled, an additional 2 dimensions are required.  This raises the required dimensions from 6 to 8.  Most planning applications have a need for at least 2 to 3 custom dimensions.  Even smaller applications suffer greatly when adding the additional 2 dimensions.  So, by using the currency option, the ability to use custom dimensions is limited.  By adding a few accounts to hold the currency conversion and adding one dimension that has members for all the currencies, multi-currency applications can be handled with only one additional dimension.  If the currency option is not used, the currency calculations may be written more efficiently than the default calculations introduced with the currency option.


Another drawback with the currency option is that is only allows data input to the base currency.  The majority of the applications I have built that require multiple currencies require the input at more than base currency.  Assume a retail company has stores in a number of countries with different currencies.  Salaries may be budgeted in the local currencies, but the cost of the bags used by customers to carry merchandise out of the store is budgeted in USD.  The costs are distributed in USD based on units, and converted to the local currencies.


Lastly, using the currency option, because of the number of dense dimensions, limits the number of time periods.  Executing calculations is limited to using 64k of memory.  Applications that use something other than month (like week, or day) can regularly hit this limit.


 
/0 Comments/by 
Dynamically Referencing Members in Business Rules
, , , 
Often times with a Hyperion Essbase or Planning application, an allocation of data will be required.  Many times, the allocation is simply moving data from one member to another.  When the number of members involved is large, developing the script can be time consuming.  When the members frequently change, the maintenance of the calculation can be a nuisance.


When the members involved in the allocation are similar on both sides (the from and the to), the following method can be employed to speed the development and limit, or eliminate, any maintenance required.


Requirement


The application has 50 members in which the data needs to be moved.  The data originates from an account coming from the general ledger.  The data needs to be moved to a new member that doesn’t exist in the chart of accounts.  The new member will exist in a different part of the hierarchy.


Solution


The first step is to create a corresponding member for each of the 50 accounts that need allocated.  These accounts will be identical to the original 50, except they will be prefixed with a “D” identifying them as a dummy, or made up, account.  Each of these new accounts will have a UDA of “allocation.”  The prefix of the member and the UDA are not critical.  They will likely be something more meaningful to the requirements.






GL Acct
  Dummy Account




500345
  D500345




500578
  D500578




607878
  D607878






Once the hierarchy is ready to handle the allocation, the following function can be used.  In layman’s terms, this only executes on the new members added (identified by the unique UDA) and makes them equal to the corresponding member without the added prefix.  We will assume that this is being executed on a scenario that equals “Actuals.”


FIX(@UDA(“allocation”))

/* Make the new member equal to the old member */

“Actuals” = @MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1));

/* Clear the old member */

@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1)) = #Missing;

ENDFIX


Let’s assume that the UDA is NOT added to the new, or dummy, member.  If the UDA is on the originating member, the calculation would look like this.


FIX(@UDA(“allocation”))

/* Make the new member equal to the old member */

@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;

/* Clear the old member */

Actuals = #Missing;

ENDFIX


Now we can break down these functions. Remember, the calculations loop through all members in all dimensions.  In this example, setting the result equal to “Actuals” is simply making the account that the calculation is looking at, at that particular point in the loop, equal to whatever is on the other side of the equation.


@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1))

There are four functions used in this string.


    

  • @MEMBER will convert a string to a member name
    • 

  • @SUBSTRING requires 2 parameters (3 optional).  The first is the larger string from which you want to take a smaller string.  The second is where to start, with 0 being the first character.  The third is how many characters to include.  If this is left blank, it will take all the characters to the right of the second parameter.
    • 

  • @NAME will convert a member to a string.
    • 

  • @CURRMBR gets the current member of a specified dimension.
    • 



Putting this all together, this calculation (from inside out) is getting the current member of the Accounts dimension (“d345678”).  It converts that member to a string.  It takes all the characters to the right of the first character (“345678”).  Then it converts the string back to a member.  At this point, we can set that member equal to something.


@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))

The functions here are the same as above, except we are not removing the “d.”  We are adding it.


    

  • @CONCATENATE accepts two parameters and will combine those two in to one string
    • 



Putting this all together, this calculation (from inside out) is concatenating two strings, a “D” and the current member of the Accounts dimension (“d345678”).  It then converts the string to a member. At this point, we can set that member equal to something.


Benefits


By using these functions, the calculations can be much smaller, quicker to develop, and completely maintained by the outline.  This effectively gives the user community ownership on the maintenance.


 
/7 Comments/by 
Why is the maximum number of returnable rows in SmartView only 5,000?
, , , , 
Regardless of whether the perception of using SmartView for large queries is good or bad, the reality is that finance and accounting users require the ability to pull large volumes of information out of Essbase.  The only limit that I am aware of in the days of the Excel Add-In was the maximum number of rows Excel would allow (assuming the Essbase application cache settings were high enough to support it).  With SmartView, there is a limit.  The limit is controllable very easily, however.  The error that users may question an administrator follows.


“Cannot perform cube view operation. OLAP error (1020011): Maximum number of rows [5000] exceeded.”


To increase the maximum number of rows a user can retrieve, or submit, edit the service.olap.dataQuery.grid.maxRows property in the essbase.properties file.  The default is 5000. While editing this property, it may be benefitial to evaluate the size if the columns (.olap.dataQuery.grid.maxColumns), which is set to 255 by default.


Once this is updated, restart the Hyperion services.


The location of the essbase.properties file is dependent on the version of Essbase installed.  Start by going to the server with APS installed.


Location for version 9.3

%HYPERION_HOME%\AnalyticProviderServices\bin directory


Location for version 11

%HYPERION_HOME%\products\Essbase\aps\bin\


 


 
/0 Comments/by 
Managing Virtual Machines
, , , , , , , , 
Many developers that work with Hyperion products, as well as many any other software product, use virtual machines.  Virtual machines are an easy way to create multiple environments for testing and developing multiple product versions


The 3 main applications to create and use virtual machines are



I have used all 3.  Opinions can be found that promote all 3.  Many IT professionals prefer VMWare.  I have found it to be a little cumbersome to use, and find sharing virtual machines to be a frustrating experience.  I am not a stereotypical IT professional, but rather a business person with an aptitide for technology.  That said, I prefer VirtualBox.  For me, VirtualBox is easier to install and manage the virtual machines.  It is easy to move virtual hard drives to another computer, simple to duplicate a virtual hard drive and allows users to take snapshots, which allows, for lack of a better explanation, a huge undo if required.


Through my VirtualBox travels, I have found the following knowledge to be very valuable.  Here are some HOW TOs that might be useful if you decide to use Sun’s VirtualBox.


How to reduce the size of a virtual machine

The use of virtual machines (just like any system) cause fragmentation and the size of the virtual hard drive to grow, sometimes substantially.  Managing the size of the virtual machine is relatively easy, and is not time consuming.  It involves 3 actions (defrag, delete free space, and compact the virtual hard drive).  Here is one way to accomplish reducing the size of your virtual machine / virtual hard drive.


    

  1. Open the virtual machine that needs compressed
    2. 

  2. Download sDelete, and extract the sDelete.exe to c:\
    3. 

  3. NOT REQUIRED:  Download and install Smart Defrag – this is a free disk defrag tool that I have found to be more effective than the one that comes with the Windows OS.
    4. 

  4. Use the disk defrag tool that comes with Windows or the one above, and defragment the hard drive.
    5. 

  5. Go to Start/Run, and enter “c:\sdelete.exe -c”
    6. 

  6. Shut Down the OS on the virtual machine
    7. 

  7. On the host computer, open a command window (Start/Run, and enter cmd)
    8. 

  8. If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
    9. 

  9. Enter “VBoxManage modifyvdi HardDrivePathAndName compact” where HardDrivePathAndName is the full path to the hard drive the virtual machine is using.
    10. 



If the virtual machine/hard drive has free space, this process will find it and reduce the overall size of your virtual machine/hard drive.


How to duplicating, or clone, a hard drive

Often times there is a need to replicate a virtual machine on the same host environment.  Copying the file doesn’t do the trick, as every virtual machine’s hard disk must have a unique key.  VirtualBox comes with a tool to duplicate the hard drive and assign it a new key.  To accompolish, follow the following steps.


    

  1. Reduce the hard drive size (see previous topic)
    2. 

  2. On the host computer, open a command window (Start/Run, and enter cmd)
    3. 

  3. If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
    4. 

  4. Enter “VBoxManage clonevdi Source Destination” where Source is the full path to the hard drive the virtual machine to duplicate and Destination is the location of the new virtual machine hard drive.
    5. 

  5. Open VirtualBox and create a new virtual machine that points to the newly created hard drive in the previous step.
    6. 



 


 
/0 Comments/by