Tag Archive for: Essbase

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

What’s New in Hyperion Shared Services 11.1.2?

,

What’s New in Hyperion 11.1.2?

Shared Services

 

As you’ve no doubt noticed by now, this has turned into a series of posts involving new features in the 11.1.2 release of the Hyperion products. This post will cover some of the significant changes to Shared Services, including improvements to Security Administration, Lifecycle Management, and Taskflows.

Security Administration

It’s been well-documented at this point that there have been multiple issues with the OpenLDAP approach to the Native Directory. In 11.1.2, the OpenLDAP has been replaced with a relational database as the storage point for native accounts and provisioning. This has already proven beneficial, as it allows for the next improvement below.
There is no longer a need for Essbase synchronization for users, as it is now done automatically. This is a welcome change from most, as it was always very easy to forget to refresh security. However, group synchronization must still be done manually.
The supported SSL configurations have also seen significant improvements. These include:
  1. SSL Offloading
  2. 2-way SSL deployment
  3. SSL termination at the web server
Oracle Single Sign-On (OSSO) is also supported in this release. The Oracle Internet Directory (OID) is used to provide SSO access to web applications.

Lifecycle Management (LCM)

Like the rest of Shared Services, LCM has adopted Oracle Diagnostics Logging (ODL) as the standard logging mechanism.
Perhaps the biggest improvement to LCM is that it now supports the extraction of data. Essbase data now appears as a selectable artifact when performing an export, and can be updated with the outline. On this note, I should probably point out that for cross-product migrations, LCM determines the correct order based on dependencies.
Some other modifications to LCM include:
  1. Additional information in migration status reports, including source and destination details.
  2. Users must be provisioned with the Shared Services Administrator role to work with the Deployment Metadata tool.
  3. The Calc Manager is supported, and has its own node under Foundation. As a result, business rules can now be migrated to classic HFM and Planning applications.

Shared Services Taskflow

This release has seen the addition of two new roles in Shared Services
  1. Manage Taskflows – This role allows users to create and edit a taskflow
  2. Run Taskflows – This role permits users to view and run a taskflow, but they cannot create or edit taskflows

 

Follow the link below to view the complete document of changes
 
 
/0 Comments/by

What’s New in Hyperion EPMA 11.1.2?

, , ,

What’s New in Hyperion 11.1.2?

EPMA

The release of version 11.1.2 has brought a plethora of improvements to the entire Hyperion suite of products, and EPMA is no different. This post will cover some of the significant changes that were included.

Improved Support for Essbase

This release has provided several updates that increase the functionality of EPMA as it relates to Essbase. Some of the more important ones include:
  1. Utilizing the Reorder Children dialog box, a new sort order can now be created to reorder members in the hierarchy.
  2. Performance settings for dimensions can now be modified in EPMA
  3. Dynamic Time Series (DTS) is now supported on the period dimension (BSO cubes)
  4. The ability to add Typed Measures and members with a Date Format has also been included.
    1. Varying Attributes are still not supported in this release

Application Troubleshooting Support

As we all know, EPMA can occasionally become out of sync with the dimension library or one of the products to which we are trying to push metadata. A new application diagnostic feature has been added in this release to help users fix this issue. This diagnostic tool determines inconsistencies between the source and target. Once the inconsistencies have been discovered, they can either be corrected manually or dealt with automatically.

Financial Management Copy Application Utility

HFM supports the ability to copy an EPMA app using the Copy Application Utility. This can be done two different ways:
  1. Select the Financial Management app. It will then be copied as a Classic application. Once this has been done, the EPMA upgrade feature can be uses
  2. Alternatively, the LCM tool can be used to migrate the application. Once this is done, the Copy Application Utility can be utilized to move the data.

Batch Client

 This release includes a couple of adjustments to the batch client that improve the automation process.
  1. Login through a proxy is now supported
  2. Single Sign On (SSO) login is also supported
Follow the link below to view the complete document of changes

Oracle EPMA Documentation

 
/0 Comments/by

Create Excel Groupings (Outline Levels) to show Essbase Hierarchies

,

Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don’t have access to Essbase administration services or EPMA.  So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies.

Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone.

The following script can be added to any workbook to automate this effort.

Sub CreateOutline()
    Dim cell As Range
    Dim iCount As Integer
    For Each cell In Selection
        'Check the number of spaces in front of the member name 
        'and divide by 5 (one level)
        iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
        'Only execute if the row is indented
        If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
    Next cell
    MsgBox "Completed"
End Sub

Setup

First, this sub routine has to be added to a workbook.  Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module.  The editor is in different places in different version.  In Excel 2007 and 2010, the Developer ribbon is not visible by default.  To make it visible, go to the navigator wheel and click Excel Options.  There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.

How To Use

First, open the member selection option in the Essbase add-in or smart view and select the parent.  Add all its descendants.  Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.

Retrieve, or refresh, the data, and make sure the indent is set so the children are indented.  Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet.  Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script.  When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.

Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.

Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic.  This Google search will get you started if you choose to go down that path.

So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.

 
/0 Comments/by

What’s New in Hyperion Essbase 11.1.2?

, , , ,

Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don’t have access to Essbase administration services or EPMA.  So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies.

Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone.

The following script can be added to any workbook to automate this effort.

Sub CreateOutline()
    Dim cell As Range
    Dim iCount As Integer
    For Each cell In Selection
        'Check the number of spaces in front of the member name 
        'and divide by 5 (one level)
        iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
        'Only execute if the row is indented
        If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
    Next cell
    MsgBox "Completed"
End Sub

Setup

First, this sub routine has to be added to a workbook.  Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module.  The editor is in different places in different version.  In Excel 2007 and 2010, the Developer ribbon is not visible by default.  To make it visible, go to the navigator wheel and click Excel Options.  There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.

How To Use

First, open the member selection option in the Essbase add-in or smart view and select the parent.  Add all its descendants.  Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.

Retrieve, or refresh, the data, and make sure the indent is set so the children are indented.  Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet.  Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script.  When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.

Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.

Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic.  This Google search will get you started if you choose to go down that path.

So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.

 
/0 Comments/by

Empower Users To Improve Calculation Times

, ,

As an Essbase user, you have more power to improve performance than you think.  How many times do you lock and send data through Excel, SmartView, or web forms, that include zeros?  How many times do you allocate data to a finite level out of convenience?  Understanding what this does to Essbase is critical to understanding how a user can negatively impact performance without adding any value to the analysis or the results the database produces.

I analyzed a planning database used in one of the largest financial institutions in the world.  Over 60% of the values entered were zero.  Another 20% of the values were less than 1 dollar.  By eliminating the zeros, the total calculation time of the planning application was under 20 minutes.  With the zeros, it was nearly 2 hours.

There are two reasons for this.  First, there is a different between empty and zero.  Empty consumes no space to store whereas a zero consumes the same space as 1 billion.  Think of this as a grocery bag.  If you fill a grocery bag with nothing, it takes up no space.  If you fill it with empty cans (a zero), it consumes the same amount of space as if those cans were full (1 billion).

The example below is very common.  Assume that a forecast needs to be done for the last 3 months of the year.  Frequently, a spreadsheet would hold zeros for the first 9 months.  18 cells have zero and 6 cells have a positive value.  That means that 75% of your data could be eliminated by not loading zeros.

The same load with #Missing is more effective.

I highly recommend reading the article explaining dense and sparse to understand what a block is and what it represents before you continue this article.

There is also another very significant factor in loading zeros.  Loading a zero that creates a block just to hold a value of zero can explode the size of the database, as well as the time it takes to consolidate and execute business rules.  The more blocks that have to be loaded and consolidated, the longer it takes to finish.  If each block was a spreadsheet and you had to do this manually, you would have to open each spreadsheet and enter the number into a calculator to consolidate.  If 75% of the blocks you opened were zero, it wouldn’t change your total, but it would drastically increase the time it takes because you still have to open each spreadsheet.   If an Essbase database has 1,000 blocks, and 75% of them only hold zeros, it will likely take 2 or 3 times longer to calc the zeros because it still has to open the block and add the zero.  Remember, a zero acts no differently than a value of 100.

As an example to the above, the following example would create a block for South and West, inflating the database size.

 

Users can significantly reduce this unnecessary explosion in size by loading a blank as apposed to a zero.  If zeros are already in the database, leaving the cell blank will NOT overwrite the zeros.  If zeros are loaded inadvertently, a #Missing has to be used to remove them.

For all you users loading data, it can be a hassle removing the zeros.  Being responsible can significantly improve your experience with Essbase.  To make it easier, take a look at the function in the In2Hyperion Excel Ribbon that replaces all zeros with #Missing.

 
/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

Essbase Database Restructures

, , ,

Changes to an Essbase outline cause changes to the Essbase index and data files, regardless of the method (Essbase Administration Services, Hyperion Planning database refreshes, or from a script).

Changes that require restructuring the database are time-consuming (unless data is discarded before restructuring).  Understanding the types of restructures and what causes them can help database owners more effectively manage the impacts to users.

TYPES OF RESTRUCTURES

Essbase initiates an implicit restructure after an outline is changed, whether done with the outline editor, through an automated build, or some other fashion like a Hyperion Planning database refresh.  The type of restructure that is performed depends on the type of changes made to the outline.

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

SPARSE RESTRUCTURE:  If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.

Sparse restructures are typically fast, but depend on the size of the index file(s).  Sparse restructures are faster than dense restructures.

OUTLINE ONLY:  If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.

Outline restructures are very quick and typically take seconds.

Explicit restructures occur when a user requests a restructure to occur.  This can be done in Essbase Administration Services or via Maxl (and EssCmd for those of you who still use it) and forces a full restructure (see dense restructure above).  It is worth noting that this also removes empty blocks.

CALCULATING IMPLICATIONS AFTER RESTRUCTURES

When a restructure occurs, every block that is impacted is tagged as dirty.  If Intelligent Calculations are used in the environment, they don’t provide any value when a dense restructure occurs as all blocks will be calculated.  When member names or formulas are changed, the block is not tagged as dirty.

WHAT DICTATES THE RESTRUCTURE TYPE

The following outline changes will force a dense restructure, which is the most time- consuming restructure.

DENSE AND SPARSE

  • Defining a regular dense dimension member as dynamic calc
  • Defining a sparse dimension regular member as dynamic calc or dynamic calc and store
  • Defining a dense dimension dynamic calc member as regular member
  • Adding, deleting, or moving dense dimension dynamic calc and store members
  • Changing dense-sparse properties [Calc Required]
  • Changing a label only property [Calc Required]
  • Changing a shared member property [Calc Required]
  • Changing the order of dimensions [Calc Required]

DENSE (DATA FILES)

  • Deleting members from a dense dimension  [Calc Required]
  • Adding members to a dense dimension
  • Defining a dense dynamic calc member as dynamic calc and store member

SPARSE (INDEX)

  • Adding members to a sparse dimension
  • Moving members (excluding shared members) in a sparse dimension
  • Defining a dense dynamic calc member as dynamic calc and store
  • Adding, deleting, or moving a sparse dimension dynamic calc member
  • Adding, deleting, or moving a sparse dimension dynamic calc and store member
  • Adding, deleting, or moving a dense dimension dynamic calc member
  • Changing the order of two sparse dimensions

NO RESTRUCTURE OCCURS

  • Deleting members of a sparse dimension [Calc Required]
  • Deleting members of an attribute dimension
  • Deleting shared members from a sparse or dense dimension [Calc Required]
  • Adding members to an attribute dimension
  • Adding shared members to a sparse or dense dimension
  • Moving a member in an attribute dimension
  • Renaming a member
  • Changing a member formula [Calc Required]
  • Defining a sparse dynamic calc member as dynamic calc and store member
  • Defining a dense or sparse dynamic calc and store member as dynamic calc
  • Defining a regular dense dimension member as dynamic calc and store
  • Defining a sparse dimension dynamic calc and store member or dynamic calc member as regular member
  • Defining a dense dimension dynamic calc and store member as regular member
  • Changing properties other than dense-sparse, label, or shared [Calc Required]
  • Changing the order of an attribute dimension
  • Creating, deleting, clearing, renaming, or coping an alias table
  • Importing an alias table
  • Setting a member alias
  • Changing the case-sensitive setting
  • Naming a level or generation
  • Creating, changing, or deleting a UDA

WHAT DOES THIS MEAN

Understanding this can help users and administrators manage applications to better meet the needs of all those involved.  When designing an application, knowledge of this topic can be instrumental in the success of the application.  Here are some things to keep in mind.

  • When updating an outline or refreshing a planning application, it may be faster to export level 0 (or input level) data, clear the data, perform the update, and reload/aggregate the export when  changes cause a dense restructure.
  • For dimensions that are updated frequently, it may be beneficial to define those dimensions as sparse.  Changes to sparse dimensions typically require only restructures to the index file(s), which are much faster.
  • If frequent changes are required, enabling incremental restructuring may make sense.  Using this defers dense restructures.  The Essbase restructure happens on a block by block basis, and occurs the first time the data block is used.  The cost is that calculations will cause restructures for all the blocks included and the calculation performance will degrade.
  • Setting the isolation level to committed access may increase memory and time requirements for database restructure.  Consider setting the isolation level to uncommitted access before a database restructure.
  • If multiple people have access to change the outline, outline logging may be useful.  This can be turned on by adding OUTLINECHANGELOG = TRUE in the essbase.cfg.
  • Monitoring progress of a restructure is possible when access to the server is granted.  Both sparse and dense restructures create temporary files that mirror the index and data files.  Data exists in the .pag files while indexes are stored in .ind files.  As the restructure occurs, there are equivalent files for each (pan for data files and inn for index files).  In total, the restructure should decrease the size of the ind and pag files, but the pan and inn files can be used for a general idea of the percent of completion.

 

 
/0 Comments/by

Essbase Add-in Ribbon, Version 2 Is Here

Thanks for all the great feedback on our Essbase Add-in Ribbon!  I have seen praise and thanks on the Oracle forums, network54, and a number of other popular hotspots.  I am constantly getting emails of gratitude.  Hundreds, if not thousands, of Hyperion customers are using the ribbon.  With the accolades, I am also getting some great suggestions for additional functionality.  In the spirit of giving back to the Hyperion community, I have every intention of implementing these requests.

What is new for the second release of the ribbon?  For those of you who used the Essbase Powerbar, you are aware of the option to save commonly used server connections.  I am happy to announce that it is now part of the In2Hyperion Essbase Add-in Ribbon feature set!

We moved the connection button that existed on the right, to the first button on the ribbon and renamed it Quick Connect.  From this menu button, users can select connect, add quick connection, or remove quick connection.  As connections are added, they will appear automatically in the Quick Connect menu.

The benefit of this option is that a user can select a “quick connection,” which remembers the server, application, database, username, and password.  Connecting to an Essbase application requires fewer clicks and less typing.  After quick connections are added, a file in My Documents named In2Hyperion.txt will exist.  This is where the connection information is stored.  The password is encrypted to ensure your information is not made available to other parties.

Download version 2.  To stay informed about future releases by signing up for our newsletter.  If you have any feedback, send us an email through the contact page.  Thanks again for all your support!

 
/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