Oracle EPM 11.1.2.4 Release

Finally it was available for download the EPM 11.1.2.4.
In OTN and not in eDelivery (Yet).

Follow the link

http://www.oracle.com/technetwork/middleware/epm/downloads/index.html

Advertisements
Posted in Uncategorized | Leave a comment

How to decipher Custom Dimensions in the DCE and Account table of an 11.1.2.2+ HFM App

Hi.

With the help of the posts from Roman Genis (HFM Custom_Map) and Henri Vilminko (Custom Dimensions in 11.1.2.2), I list here two script to decipher Custom Dimensions of DCE table and Account tables in HFM applications later than 11.1.2.2.

Thanks to Roman who wrote the first script.

*Just remember to rename the “APPNAME_” to your application name.

For DCE table:

select c1.label as C1,
c2.label as C2,
c3.label as C3,
c4.label as C4,
d. *
from
(
select bitand (t.lcustom1, to_number (‘00000000ffffffff’, ‘XXXXXXXXXXXXXXXX’)) as C1id,
bitand (t.lcustom1, to_number (‘ffffffff00000000’, ‘XXXXXXXXXXXXXXXX’)) / to_number (‘000000100000000’, ‘XXXXXXXXXXXXXXXX’) as C2id,
bitand (t.lcustom2, to_number (‘00000000ffffffff’, ‘XXXXXXXXXXXXXXXX’)) as C3id,
bitand (t.lcustom2, to_number (‘ffffffff00000000’, ‘XXXXXXXXXXXXXXXX’)) / to_number (‘000000100000000’, ‘XXXXXXXXXXXXXXXX’) as C4id,
t. *
from APPNAME_DCE_1_2014 t
) D,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom1’) c1,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom2’) c2,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom3’) c3,
(Select ci. * From APPNAME_custom_item ci, APPNAME_custom_header ch
where ci.ldimid = ch.ldimid and ch.treelabel = ‘Custom4’) c4
where
d.C1id = c1.itemid and
d.C2id = c2.itemid and
d.C3id = c3.itemid and
d.C4id = c4.itemid

 

Account and Account_item tables:

select AccC1.account, AccC1.Custom1, AccC2.Custom2, AccC3.Custom3, AccC4.Custom4 from
(select Acc.label as account, CstI.label as Custom1
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst1, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst1.itemid and AccCst1.nvalue <> -1 and AccCst1.nvalue = CstI.itemid
and AccCst1.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom1’) AccC1,
(select Acc.label as account, CstI.label as Custom2
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst2, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst2.itemid and AccCst2.nvalue <> -1 and AccCst2.nvalue = CstI.itemid
and AccCst2.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom2’ ) AccC2,
(select Acc.label as account, CstI.label as Custom3
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst3, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst3.itemid and AccCst3.nvalue <> -1 and AccCst3.nvalue = CstI.itemid
and AccCst3.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom3’ ) AccC3,
(select Acc.label as account, CstI.label as Custom4
from APPNAME_account_item Acc, APPNAME_account_custattr AccCst4, APPNAME_custom_item CstI, APPNAME_custom_header CstH
where Acc.itemid = AccCst4.itemid and AccCst4.nvalue <> -1 and AccCst4.nvalue = CstI.itemid
and AccCst4.customdimnum = CstH.ldimid and CstI.ldimid = CstH.ldimid and CstH.treelabel = ‘Custom4’ ) AccC4
where AccC1.account = AccC2.account and AccC1.account = AccC3.account and AccC1.account = AccC4.account

 

Regards,

Igor Simoes.

 

Posted in Uncategorized | Leave a comment

Drillthrough HFM -> FDM: Error retrieving mapped target year recordset

Hi,

If you are getting the error “Error retrieving mapped target year recordset” while trying to drillthrough frim HFM back to FDM, check in the FDM application schema if the two tables below are populated:

– tpovperiodadaptor

– tpovcategoryadaptor

If not, probably it was due to a migration or a upload not well finished of the control tables. In this case, you will need to fill it by yourself.

Please check which is the name of your adapter and update it and after run the script below:

insert into tpovperiodadaptor (periodkey, intsystemkey, periodtargetm,periodtargetq,periodtargety,periodtargetd,yeartarget)
select periodkey, /*Insert the name of your adapter here, like: */’FM11X-G6-C’, periodtargetm,periodtargetq,periodtargety,periodtargetd,yeartarget from tpovperiod

The same strategy works for the tpovcategoryadaptor, and you will just need to update the field names of the tables.

Regards,

Igor Simoes.

 

Posted in Uncategorized | Leave a comment

Essbase Patch – 11.1.2.3.500 – Good News

Hybrid Aggregation Mode in Block Storage Databases

A new Essbase configuration setting, ASODYNAMICAGGINBSO, controls whether block storage databases use hybrid aggregation mode. Hybrid aggregation for block storage databases means that wherever possible, block storage data calculation executes with efficiency similar to that of aggregate storage databases.

The following are some scenarios where hybrid aggregation is highly likely to improve calculation performance:

  •          A block storage database has stored members that are not level 0, and are calculated according to hierarchy (rather than by calculation scripts).
  •          A Dynamic Calc member has more than 100 children.
  •          You are using a transparent partition between an empty aggregate storage target and a block storage source. If the formulas on the aggregate storage target are simple and translatable to block storage formula language, you can achieve fast results on block storage using hybrid aggregation.
  •          You are using a transparent partition between two block storage databases, and calculation performance is a concern.

 

Outline Structure for Hybrid Aggregation

To use hybrid aggregation most effectively:

  •          If there are non-level-0 stored members that are batch calculated based solely on their hierarchy, it’s recommended that you convert them to Dynamic Calc members.
  •          If the conversion to Dynamic Calc members affects solve order for dependent formulas, you may also need to adjust the outline’s order of dimensions, and/or their dense or sparse configurations, to align the solve order with the previous batch calculation order.

Faster Queries for MDX Aggregate and Sum Functions

In aggregate storage databases, performance is improved for MDX queries containing the Aggregate or Sum functions. Essbase performs dependency analysis and uses a formula cache to execute these requests dynamically.

Using FIXPARALLEL Parallel Calculation

Overview of FIXPARALLEL

Although parallel calculation can be performed using the CALCPARALLEL configuration setting, in certain cases it might be beneficial to use the FIXPARALLEL command block method.

In a FIXPARALLEL command block, you input some commands to be executed, along with a number of threads (numThreads) and a member list (mbrList) specifying the database regions (slices) to be calculated. Essbase creates a list of tasks from the combinations in the member list, and divides the tasks across the threads.

The FIXPARALLEL method can be advantageous in the following cases:

  •          If you need to use temporary variables during parallel calculation
  •          If you need to use the DATACOPY, DATAEXPORT, or CLEARBLOCK commands
  •          In conjunction with the @XREF or @XWRITE functions (You can now also use these with CALCPARALLEL; see SET FORCEPARALLELCALC)
  •          If you need to export regions of the database in parallel. See the Example in this topic.
  •          In cases where CALCPARALLEL is not meeting performance requirements, and your outline generates many empty tasks, or contains many task groupings with fewer tasks than threads made available to the calculation. See also “Task Selection Comparison of FIXPARALLEL and CALCPARALLEL.”

When considering converting FIX statements to FIXPARALLEL within a calculation script, follow these guidelines:

  •          Focus on FIX statements that do not meet your performance needs using CALCPARALLEL.
  •          Focus on FIX statements that require a substantial amount of work. Parallelizing a FIX statement requires some overhead, so trying to parallelize calculation passes with light workloads may not be beneficial. Heavier workloads, such as AGG and CALC DIM, are good candidates for FIXPARALLEL.
  •          First, try parallelism with a single large sparse dimension, or by restricting mbrList to one or more hierarchies with a limited stored member count. You may continue adding dimensions to the member list to see if the calculation time continues to improve.

Note that when “parallel” calculation of tasks occurs, it means that the tasks are divided and executed concurrently in any order. In other words, there is no guarantee that any task will be executed before any other tasks. This is why the regions you specify must not have any data or calculation dependencies. For example, assume there are two parallel threads, and there is a division of work into tasks A, B, C, and D.

The possible sequence of calculation might be:

Thread #1 executes A and then C.

Thread #2 executes B and then D.

Or,

Thread #1 executes A.

Thread #2 executes B, then C, then D.

Or,

Thread #1 executes C and then A.

Thread #2 executes D and then B.

 

For the optimized performance of these functions on aggregate storage databases, include in your query the following elements:

  •          Any of the following functions, used within the named set and/or as an argument to this function: Intersect, CurrentMember, Distinct, CrossJoin, PeriodsToDate. The use of any other functions (such as Members) disables the optimization.
  •          The second parameter, accounts_member, must be included for optimal performance.

 

Posted in Uncategorized | Tagged , , | Leave a comment

Error java.lang.OutOfMemoryError: getNewTla using Oracle EPM products

APPLIES TO:
Oracle EPM 11.1.2.x

GOAL
Fix the problem:
Error java.lang.OutOfMemoryError: getNewTla

FIX
Go to

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Hyperion Solutions\<Instance>\<Service>
  2. Add the new string value with the name JVMOption<xx> (add one more value)
  3. Edit the value of JVMOption with value:
    -XXtlasize:min=8k,preferred=128k
  4. Increase the number in JVMOptionCount
  5. Restart the service.

Posted in Oracle EPM | Tagged , | Leave a comment

How To Restore a Folder Which Has Been Deleted from EPM Workspace.

APPLIES TO:

Hyperion BI+ – Version 9.0.0.0.00 to 11.1.2.2.000 [Release 9.0 to 11.1]

GOAL

Restore a file or folder which has been deleted from Enterprise Performance Management

FIX

All items published/created in Workspace (folders, reports, other files) have their information spread in two locations:

The actual item (for instance, the report itself) is being stored in the %HYPERION_HOME%\BIPlus\data\RM1_folder
The meta-data (its actual name, its last modification date, other properties, …) is stored in the BI repository.
Therefore if you wish to restore a folder or file which has been deleted from Workspace, it will be necessary to restore the environment using backups of both the server’s file system AND the BI repository taken before the folder has been deleted.

This implies restoring:
The server’s file system
The BI repository

Please note that any configuration detail inherent to the BI platform is stored in both the server’s file system AND the BI repository.

This implies that when restoring the environment, it is necessary to restore both the server’s file system and the BI repository using backups which have been taken AT THE SAME TIME from the same backup.

Not doing so (i.e. having the BI repository in out-of-synchronization with the server’s file system) will result in inconsistent behaviors in Workspace.

Posted in Oracle Hyperion Financial Reporting, Uncategorized | Tagged , , | Leave a comment

Essbase Server does not start

Applies to:
Hyperion Essbase – Version: 11.1.2.0.00 or later

Symptoms
When try start Essbase running “opmnctl startall” receive the message

Starting opnm and all managed proccess...
opmnctl startall: opmn failed to start.

In opmn.log have

[opmn][TRACE:1][][OPMN]Failed to open wallet (file:E:\Oracle\Middleware\user_projects\epmsystem\config\OPMN\opmn\wallet) [default password] (28759)

Solution
Copy the file cwallet.sso from E:\Oracle\Middleware\user_projects\epmsystem\EssbaseServer\essbaseserver1\bin\wallet to E:\Oracle\Middleware\user_projects\epmsystem\config\OPMN\opmn\wallet

Posted in Oracle Essbase | Tagged , , , | Leave a comment