Altering Large Numbers Of Cells In Excel A hundred Times Quicker

, ,

Many processes need to write large volumes of data in Excel.  The typical method is to loop through each cell and perform the action.

   Dim CellsDown As Long
   CellsAcross As Long

   Dim CurrRow As Long
   CurrCol As Long
   Dim CurrVal As Long

   '  This can be replaced with the selected range and is just used to illustrate this example.
   CellsDown = 1000
   CellsAcross = 36

   '   Loop through cells and insert values
   CurrVal = 1
   Application.ScreenUpdating = False
   For CurrRow = 1 To CellsDown
       For CurrCol = 1 To CellsAcross
           Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
           CurrVal = CurrVal   1
       Next CurrCol
   Next CurrRow

Rather than writing the values out cell by cell, it is quicker to store the value in an array and write the array to a range of cells at one time.

   Dim CellsDown As Long
   CellsAcross As Long

   Dim CurrRow As Long
   CurrCol As Long
   Dim CurrVal As Long

   Dim TempArray() As Double

   '  This can be replaced with the selected range and is just used to illustrate this example.
   CellsDown = 1000
   CellsAcross = 36

   '   Update the array
   ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
   Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))

   '   Fill the temporary array
   CurrVal = 0
   Application.ScreenUpdating = False
   For i = 1 To CellsDown
       For j = 1 To CellsAcross
           TempArray(i, j) = CurrVal
           CurrVal = CurrVal   1
       Next j
   Next i

   '   Transfer temporary array to worksheet
   TheRange.Value = TempArray

This same method can be used when altering data.  By changing the following line

            TempArray(i, j) = CurrVal

To this

            TempArray(i, j) = TheRange(i, j) * 3

By using TheRange(i, j), the existing value can be altered

 

The process of writing values cell by cell took 3.16 seconds.  Using the array method, it took .08 seconds, nearly 40 times faster

 
/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

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

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

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

Removing Data From Excel Templates

, ,

Working with finance and accounting professional the majority of my career, I see a lot of spreadsheet “templates” that are reused for multiple budget passes or monthly forecasting processes (any repatative process).  When the workbooks have a number of worksheets, and they are large, it can be extremely tedious to clear out the old data and get back to a fresh, empty shell.  The script below can be executed on any worksheet to clear out all the numeric values and cell comments.  It ignores cells with dates, formulas, or text values.

    'loop through each cell in the range of cells used in the worksheet
    For Each c In ActiveSheet.UsedRange.Cells
        'If the cell value is null, don't do anything
        If Not IsNull(c.Value) Then
            'Do not execute on formulas or non numeric values
            If Not c.HasFormula And IsNumeric(c.Value) Then
                'If a cell comment exists and it is not equal
                'to "KEEP", set the value of the cell to null
                If Not (c.Comment Is Nothing) Then
                    If c.Comment.Text <> "KEEP" Then c.Value = Null
                ElseIf c.Comment Is Nothing Then
                    c.Value = Null
                End If
            End If
        End If
        'Execute on all cells in the range where the cell has a comment
        If Not (c.Comment Is Nothing) Then
            'If the comment is equal to "KEEP", don't delete the comment
            If c.Comment.Text <> "KEEP" Then c.Comment.Delete
        End If
    Next c
    MsgBox "Complete"

Breaking It Down

The outside loop will loop through each cell using the ActiveSheet.UsedRange.Cells.  This function will get the range of cells on the worksheet that has been used.  UsedRange will take the equivalent range of using CTRL-HOME to get the upper left cell and CTRL-END to get the bottom right of the range.

For Each c In ActiveSheet.UsedRange.Cells
…
Next c

Each cell will be checked to verify that the value is not blank, is not a formula, and is numeric (not text).  If this criteria is true, the value will be set to nothing.

If Not IsNull(c.Value) And Not c.HasFormula And IsNumeric(c.Value) Then
c.Value = Null
End If

If the cell has a cell comment, it will be removed as well.

If Not (c.Comment Is Nothing) Then
c.Comment.Delete
End If

In the full example, some additional lines are added to ignore clearing any cell with a cell comment of “KEEP”.

How To Use

To use this script, it must be added to a module.  The easiest way to do this is to create a macro and associate a CTRL-? key to it.

In Excel 2007, select the Developer ribbon and click the Record Macro button.  Immediately click the Stop Recording button.  This will create a function in a new module for you.  If the Developer tab is not visible, click the Office Button and click the Excel Options button.  On the Popular tab, select Show Developer Ribbon.

In Excel 2003, select the Tools / Macro / Record New Macro menu.  Immediately click the Stop Recording button.

After opening Visual Basic in Excel, expand the spreadsheet in the Project window.  Expand the Modules tree and open the module.  Inside the module will be a procedure that is empty.  Paste the script inside the procedure.  This can now be accessed by the CTRL-? that was assigned.

I will be posting more scripts like this.  If you find this helpful, add your email to our mailing list near the top of the right sidebar.  You will get an email any time we add a new article!

 
/0 Comments/by

Hyperion Essbase Audit Logs Turning Off Without Notification

, ,

Audit logs, or SSAUDIT, are a crucial component of backing up Hyperion Essbase applications in many environments.  It is the equivalent of a transaction log in a relational database.  To use this effectively, the audit log has to consistently log database changes.

If the audit feature in Hyperion Essbase is used, the following information is absolutely critical to know to effectively manage this feature.  If the application is on a shared environment where multiple groups/people are administering the applications, it is critical that everybody understands this, and plays nicely together!

The audit logs are turned off without any notification when the following actions occur on an Essbase server.  To turn the audit feature back on, the Essbase application in question has to be stopped and started.  It is not required to cycle the Essbase service.

  • Any operation that causes a database restructure.
  • The creation of a new application
  • The creation a new database
  • Copying a database
  • Renaming a database

After any of these operations occurs on the server, stop and start all applications that use the audit feature.

 

 
/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

Why Does Workspace Log You Out?

, ,

Many clients have issues with Workspace logging users out when attempting to open/export certain types of documents. For example, a user logged into Hyperion Workspace attempts to open a Supporting Detail document, only to be prompted with a logout warning message before the document opens. The same can happen when a user attempts to export an FR report file (.DES file) from Workspace. After being logged out of Workspace, the user is able to log back into the application and open/export the necessary document. This issue can easily fixed by adjusting a few browser settings as shown below.

NOTE: Microsoft documents typically cause this logout issue (Excel, Word, PowerPoint).

1. Open your web browser (This blog entry will assume IE).

2. Select “Tools” -> “Internet Options…” from the menu bar.

3. Select the “Internet” option inside the “Security” tab. Select “Custom Level…”.

4. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

 

5. Now select the “Local Internet” option inside the “Security” tab. Select “Custom Level…”.

 

6. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

 

7. Finally, select the “Trusted sites” option inside the “Security” tab. Select “Custom Level…”.

 

8. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

9. Now that the settings have been set properly, Click “OK” on the “Internet Options” dialog box.

10. Close and re-open your web browser for the settings to take effect. Test these settings by logging into Workspace and opening a Supporting Detail document.

 
/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