Author Archives: Padmanabh

A novice’ guide to configuring Oracle EPM on Linux

Most of the time, Hyperion consultants who are not very much familiar with Linux operating system, face problems working with the command line interface.

A utility called Xming comes handy for configuring the EPM system on Linux.

Following steps explain the steps to run EPM Configuration Utility in a console format.

Step 1:
Download and Install Xming available Here.
Step 2:
Run Xming. Make sure it is running.

 

Xming

Step 3: Activate X11 forwarding in Putty

Putty

Step 4: Connect to the server, and go to Configuration directory path
(In our case:

Terminal

Step 5: Run the Configtool utility.

Conofigtool.png

Sometime The file descriptor soft limit may be too less, and not allow opening of the utility.
Use following command to update the File Descriptor Limit
ulimit -n 12000

You should now be able to work with the Configuration utility normally like you work in console.

Advertisements

ESSBASE user defined language for Notepad++

Copy and paste the following text into UserDefineLang.xml in the Appdata/Roaming folder of your username in Windows.

 

<NotepadPlus>
<UserLang name=”ESSBASE_CALC” ext=”csc”><Settings><Global caseIgnored=”yes”/><TreatAsSymbol comment=”no” commentLine=”yes”/><Prefix words1=”no” words2=”no” words3=”no” words4=”no”/></Settings><KeywordLists><Keywords name=”Delimiters”>”00″00</Keywords><Keywords name=”Folder+”>IF FIX</Keywords><Keywords name=”Folder-“>ENDIF ENDFIX</Keywords><Keywords name=”Operators”>” & ( ) , ; [ ] { } =</Keywords><Keywords name=”Comment”>1/* 2*/ 0//</Keywords><Keywords name=”Words1″>@ABS @ACCUM @ALLANCESTORS @ALIAS @ALLOCATE @ANCEST @ANCESTORS @ANCESTVAL @ATTRIBUTE @ATTRIBUTEBVAL @ATTRIBUTESVAL @ATTRIBUTEVAL @AVG @AVGRANGE @BETWEEN @CALCMODE @CHILDREN @COMPOUND @COMPOUNDGROWTH @CONCATENATE @CORRELATION @COUNT @CURGEN @CURLEV @CURRMBR @CURRMBRRANGE @DATEDIFF @DATEPART @DATEROLL @DECLINE @DESCENDANTS @DISCOUNT @ENUMVALUE @EQUAL @EXP @EXPAND @FACTORIAL @FORMATDATE @GEN @GENMBRS @GROWTH @IALLANCESTORS @IANCESTORS @ICHILDREN @IDESCENDANTS @ILANCESTORS @ILDESCENDANTS @ILSIBLINGS @INT @INTEREST @IRDESCENDANTS @IRR @IRSIBLINGS @ISACCTYPE @ISANCEST @ISCHILD @ISDESC @ISGEN @ISIANCEST @ISIBLINGS @ISICHILD @ISIDESC @ISIPARENT @ISISIBLING @ISLEV @ISMBR @ISPARENT @ISSAMEGEN @ISSAMELEV @ISSIBLING @ISUDA @LANCESTORS @LDESCENDANTS @LEV @LEVMBRS @LIKE @LIST @LN @LOG @LOG10 @LSIBLINGS @MATCH @MAX @MAXRANGE @MAXS @MAXSRANGE @MBRCOMPARE @MBRPARENT @MDALLOCATE @MDANCESTVAL @MDPARENTVAL @MDSHIFT @MEDIAN @MEMBER @MERGE @MIN @MINRANGE @MINS @MINSRANGE @MOD @MODE @MOVAVG @MOVMAX @MOVMED @MOVMIN @MOVSUM @MOVSUMX @NAME @NEXT @NEXTS @NEXTSIBLING @NOTEQUAL @NPV @PARENT @PARENTVAL @POWER @PREVSIBLING @PRIOR @PRIORS @PTD @RANGE @RANK @RDESCENDANTS @REMAINDER @REMOVE @ROUND @RELATIVE @RSIBLINGS @SANCESTVAL @SHARE @SHIFT @SHIFTMINUS @SHIFTPLUS @SHIFTSIBLING @SIBLINGS @SLN @SPARENTVAL @SPLINE @STDEV @STDEVP @STDEVRANGE @SUBSTRING @SUM @SUMRANGE @SYD @TODATE @TODATEEX @TODAY @TREND @TRUNCATE @UDA @VAR @VARPER @VARIANCE @VARIANCEP @WITHATTR @XREF @XRANGE

</Keywords><Keywords name=”Words2″>AGGMISSG CACHE CALCPARALLEL CALCTASKDIMS CCTRACKCALC CLEARUPDATESTATUS COPYMISSINGBLOCK CREATENONMISSINGBLK CREATEBLOCKONEQ FRMLBOTTOMUP LOCKBLOCK MSG NOTICE UPDATECALC UPTOLOCAL VAR ARRAY ALL AVERAGE DIM FIRST LAST TWOPASS ON OFF HIGH DEFAULT LOW ALL

</Keywords><Keywords name=”Words3″>AGG CALC SET CCONV CLEARBLOCK CLEARCCTRACK CLEARDATA DATACOPY ELSE ELSEIF LOOP ENDLOOP EXCLUDE ENDEXCLUDE

</Keywords><Keywords name=”Words4″>+ – * / = % > >= < <= == <> != AND OR NOT -></Keywords></KeywordLists><Styles><WordsStyle name=”DEFAULT” styleID=”11″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”FOLDEROPEN” styleID=”12″ fgColor=”0000FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”FOLDERCLOSE” styleID=”13″ fgColor=”0000FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD1″ styleID=”5″ fgColor=”FF9A00″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD2″ styleID=”6″ fgColor=”FF0000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD3″ styleID=”7″ fgColor=”0000FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD4″ styleID=”8″ fgColor=”FF00FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”COMMENT” styleID=”1″ fgColor=”009A00″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”COMMENT LINE” styleID=”2″ fgColor=”009A00″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”NUMBER” styleID=”4″ fgColor=”63009C” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”OPERATOR” styleID=”10″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER1″ styleID=”14″ fgColor=”9C9AFF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER2″ styleID=”15″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”DELIMINER3″ styleID=”16″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/></Styles></UserLang><UserLang name=”ESSBASE_MAXL” ext=”mxl”><Settings><Global caseIgnored=”no”/><TreatAsSymbol comment=”no” commentLine=”no”/><Prefix words1=”no” words2=”no” words3=”no” words4=”no”/></Settings><KeywordLists><Keywords name=”Delimiters”>'”0′”0</Keywords><Keywords name=”Folder+”/><Keywords name=”Folder-“/><Keywords name=”Operators”>” . ;</Keywords><Keywords name=”Comment”>1/* 2*/ 0//</Keywords><Keywords name=”Words1″>direct force

aggregate_missing minutes seconds aggregate_storage any bitmap buffered cache_pinning write zlib unicode_mode unlimited single runtime rle read nonunicode_mode

multiplication never no_access none optional optional_group meta_read immediate committed_mode definition_only days division fixed_decimal scientific_notation</Keywords><Keywords name=”Words2″>alter application database group object partition session system tablespace trigger user create algorithm calculation function location alias macro outline level long medium short objects display disk volume row lock privilege spool variable drop execute export data lro grant import dimensions query login refresh custom definitions replicated abort active add advanced after alias_names alias_table all allow append application archive area as at attribute attribute_info attribute_spec attribute_to_base_member_association autostartup before begin blocks calc_script calc_string cascade clear columns commands comment commitblock compression connect connects copy create_application create_blocks create_user cube_size_info currency currency_conversion currency_database currency_member currency_rate data_block data_cache_size data_file data_file_cache_size database_synch database_asynch dbstats default delete designer dimension direction directory disable disabled disallow dynamic_calc enable enabled end end_transaction enforce eqd error error_file estimated event excel exceeds export_directory external from file file_size file_type filter_access gb get ha_trace held high hostname identified implicit_commit in inactive_user_days index index_cache_size index_data index_page_size information input instead invalid_block_headers invalid_login_limit io_access_mode kb kernel_io kernel_cache kill level0 license_info linked list load local lock_timeout logfile logout lotus_2 lotus_3 lotus_4 low mapped max_lro_file_size for mb member member_instance_data member_alias_namespace member_UDA member_UDA_namespace member_calculation member_comment member_fixed_length_data member_formula member_info member_name_namespace member_uda member_uda_namespace member_variable_length_data minimum mining mode model note of off on opg_cache opg_state or outline_paging_file overview partition_file partition_size password password_reset_days performance permission persistence pmml_file ports where then mail else preserve input level0 log_value pre_image_access preserve process property protocol recover remove rename repair replace report_file request request_history reset restructure result retrieve_buffer_size retrieve_sort_buffer_size reverse rows security_file rules_file scope security security_backup select selection semaphore server session_idle_limit session_idle_poll set shutdown size spec spinlock startup statistics status stop stopping supervisor suppress sync table task tb template text thread to total_size transparent trigger_spool two_pass_calc type unicode unload unlock update updated updates use using validate verification version when with wizard worksheet exit by numerical_display</Keywords><Keywords name=”Words3″>filter parent members is aggregate uda</Keywords><Keywords name=”Words4″/></KeywordLists><Styles><WordsStyle name=”DEFAULT” styleID=”11″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”FOLDEROPEN” styleID=”12″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”FOLDERCLOSE” styleID=”13″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”KEYWORD1″ styleID=”5″ fgColor=”FF0000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD2″ styleID=”6″ fgColor=”0000FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD3″ styleID=”7″ fgColor=”FF9900″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”KEYWORD4″ styleID=”8″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”COMMENT” styleID=”1″ fgColor=”009900″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”COMMENT LINE” styleID=”2″ fgColor=”009900″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”NUMBER” styleID=”4″ fgColor=”650099″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”OPERATOR” styleID=”10″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER1″ styleID=”14″ fgColor=”9999FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER2″ styleID=”15″ fgColor=”9999FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER3″ styleID=”16″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/></Styles></UserLang><UserLang name=”ESSBASE_MDX” ext=”mdx”><Settings><Global caseIgnored=”yes”/><TreatAsSymbol comment=”no” commentLine=”no”/><Prefix words1=”no” words2=”no” words3=”no” words4=”no”/></Settings><KeywordLists><Keywords name=”Delimiters”>'”0′”0</Keywords><Keywords name=”Folder+”/><Keywords name=”Folder-“/><Keywords name=”Operators”>( ) , ; [ ] { | } < ></Keywords><Keywords name=”Comment”>1/* 2*/ 0</Keywords><Keywords name=”Words1″>Dimension set member index END property from SELECT NON EMPTY COLUMNS FROM WHERE ROWS AXIS ON PAGES CHAPTERS SECTIONS PROPERTIES AFTER BEFORE ALL or AND NOT IN first AVERAGE FOR Level CREATE DELETE</Keywords><Keywords name=”Words2″>Abs Aggregate Ancestor Attribute Avg BottomCount BottomPercent BottomSum CASE Children ClosingPeriod CoalesceEmpty Concat Contains Count Cousin CrossJoin CurrentMember CurrentTuple DateDiff DatePart DateRoll DateToMember DefaultMember Descendants Distinct DrilldownByLayer DrilldownMember DrillupByLayer DrillupMember Except Exp Extract Factorial FILTER FirstChild FirstSibling FormatDate GetFirstDate GetLastDate Generate Generations Generations Head Hierarchize IIF InStr Int Intersect IS IsAccType IsAncestor IsChild IsEmpty IsGeneration IsLeaf IsLevel IsSibling IsUda IsValid Item Item Lag LastChild LastPeriods LastSibling Lead Left Len Leaves Levels Levels Ln Log Log10 Lower LTrim Max Median MemberRange Members Min Mod NextMember NonEmptyCount NTile Ordinal ParallelPeriod Parent Percentile PeriodsToDate Power PrevMember Rank Remainder Right Round RTrim Siblings Stddev Stddevp StrToMbr StrToNum Subset Substring Sum Tail Todate TodateEx Today TopCount TopPercent TopSum Truncate TupleRange Uda Union Upper WithAttr Generation xTD IS</Keywords><Keywords name=”Words3″>ANY</Keywords><Keywords name=”Words4″/></KeywordLists><Styles><WordsStyle name=”DEFAULT” styleID=”11″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”FOLDEROPEN” styleID=”12″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”FOLDERCLOSE” styleID=”13″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”KEYWORD1″ styleID=”5″ fgColor=”0000FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD2″ styleID=”6″ fgColor=”FF9900″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD3″ styleID=”7″ fgColor=”FF0000″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”KEYWORD4″ styleID=”8″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”COMMENT” styleID=”1″ fgColor=”009900″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”COMMENT LINE” styleID=”2″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”NUMBER” styleID=”4″ fgColor=”650099″ bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”OPERATOR” styleID=”10″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”DELIMINER1″ styleID=”14″ fgColor=”9999FF” bgColor=”FFFFFF” fontName=”Lucida Console” fontStyle=”0″/><WordsStyle name=”DELIMINER2″ styleID=”15″ fgColor=”9999FF” bgColor=”FFFFFF” fontName=”” fontStyle=”0″/><WordsStyle name=”DELIMINER3″ styleID=”16″ fgColor=”000000″ bgColor=”FFFFFF” fontName=”” fontStyle=”0″/></Styles></UserLang>
</NotepadPlus>

Start to Copy from <NotepadPlus> to </NotepadPlus> if you already have a language.

 

Source: http://notepad-plus.sourceforge.net/commun/userDefinedLang/Essbase_userDefineLang.xml

FDMEE Error : Other Language Support

While we setup the Planning Applications in languages other than English, The Hyperion Essbase Database is created with Unicode UTF-8 encoding.

This might require additional configuration steps for pushing data from FDM

By Default the FDM supports UTF-8 Character set.

UTFAnd if you have special characters in languages other than English, in FDM Period definitions as well as in Dimension mappings,

UTF8.PNG

You may get error while trying to load data to Planning/Essbase:
2015-10-17 08:46:08,835 FATAL [AIF]: Error in CommData.consolidateData
Traceback (most recent call last):
File “<string>”, line 5288, in consolidateData
File “<string>”, line 1116, in callOdiServlet
File “__pyclasspath__/urllib.py”, line 1250, in urlencode
UnicodeEncodeError: ‘ascii’ codec can’t encode characters in position 0-4: ordinal not in range(128)

2015-10-17 08:46:08,910 DEBUG [AIF]: Comm.finalizeProcess – START
2015-10-17 08:46:08,911 DEBUG [AIF]: CommData.updateRuleStatus – START
2015-10-17 08:46:08,911 DEBUG [AIF]:
UPDATE AIF_BALANCE_RULES
SET STATUS = CASE ‘FAILED’
WHEN ‘SUCCESS’ THEN
CASE (
SELECT COUNT(*)
FROM AIF_PROCESS_DETAILS pd
WHERE pd.PROCESS_ID = 26
AND pd.STATUS IN (‘FAILED’,’WARNING’)
)
WHEN 0 THEN ‘SUCCESS’
ELSE (
SELECT MIN(pd.STATUS)
FROM AIF_PROCESS_DETAILS pd
WHERE pd.PROCESS_ID = 26
AND pd.STATUS IN (‘FAILED’,’WARNING’)
)
END
ELSE ‘FAILED’
END
WHERE RULE_ID = 6

2015-10-17 08:46:08,913 DEBUG [AIF]: CommData.updateRuleStatus – END
2015-10-17 08:46:08,913 FATAL [AIF]: Error in COMM Consolidate Data
2015-10-17 08:46:08,914 DEBUG [AIF]: Comm.updateProcess – START
2015-10-17 08:46:08,917 DEBUG [AIF]: Comm.updateProcess – END
2015-10-17 08:46:08,918 DEBUG [AIF]: The fdmAPI connection has been closed.
2015-10-17 08:46:08,918 INFO [AIF]: FDMEE Process End, Process ID: 26

 

One way to steer around this error is to update the character encoding to UTF-16.

Worked in our case.

FDM Error : Loading ERP Data

Quick Solution::
Add Application Root Folder and Save.

Application Root

Read below for Problem Summary.

Importing ERP data into Target Hyperion Planning or Essbase Application is primary usage of FDM Application setup.

There are sometimes small elements which we might ignore, and lead to unprecedented errors. One such error occurs, while importing Data after initializing the Source system.

This error comes after ODI fetches data from source system, and puts this data in the FDM Staging Table.

The error is because of the absence of FDM Application root directory, which has inbox/outbox folders, which will be used to store the Flat file to be imported into Essbase.

Error Summary:

FDMEE Data extraction Error
The Value for Profile option ‘PROFILE_APP_ROOT_DIRECTORY’ is invalid: NULL 

‘PROFILE_APP_ROOT_DIRECTORY’ is invalid: NULL, 683 FATAL [AIF]: Error in COMM Pre Import Data

Additional ODI Logs:
[2015-10-14T19:47:38.082+04:00] [EPMServer0] [ERROR] [ODI-1134] [] [tid: 125] [userId: <anonymous>] [ecid: 0000L1b2G4EEOP15zvg8yW1M6bzc0004dm,1:24283] [APP: oraclediagent] Agent OracleDIAgent encountered an error: ODI-1217: Session COMM_LOAD_BALANCES (37501) fails with return code 7000. Caused by: ODI-1226: Step FAILED – Invalid Source System fails after 1 attempt(s).[[
ODI-1217: Session COMM_LOAD_BALANCES (37501) fails with return code 7000.
ODI-1226: Step FAILED – Invalid Source System fails after 1 attempt(s).
ODI-1232: Procedure COMM Finalize Process execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “<string>”, line 13, in <module>
File “<string>”, line 162, in finalizeProcess
RuntimeError: ODI-1226: Step COMM Pre Import Data fails after 1 attempt(s).
ODI-1232: Procedure COMM Pre Import Data execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “<string>”, line 7, in <module>
File “<string>”, line 4578, in preImportData
File “<string>”, line 455, in executeScript
File “<string>”, line 394, in doScriptInit
RuntimeError: The value for Profile Option ‘PROFILE_APP_ROOT_DIRECTORY’ is invalid: NULL

AIF Web Log

[2015-10-12T11:42:03.746+04:00] [EPMServer0] [ERROR] [] [oracle.apps.erpi.model] [tid: 22] [userId: <anonymous>] [ecid: 0058UDr854hEOP15zvg8yW0005Ji0001XQ,0:1] [APP: AIF#11.1.2.0] [SRC_CLASS: com.hyperion.aif.util.RegistryUtil] [SRC_METHOD: getProductParams] An unexpected error has occurred.[[
oracle.epm.fm.common.exception.HFMException: EPMHFM-65536: An unexpected error has occurred.

Log pointing to the exact Exception:

 

ODI-1226: Step COMM Export Data fails after 1 attempt(s).
ODI-1232: Procedure COMM Export Data execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “<string>”, line 9, in <module>
File “<string>”, line 5040, in exportData
RuntimeError: java.io.IOException: No such file or directory

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:580)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1931)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:513)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1073)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
at java.lang.Thread.run(Thread.java:662)
Caused by: Traceback (most recent call last):
File “<string>”, line 9, in <module>
File “<string>”, line 5040, in exportData
RuntimeError: java.io.IOException: No such file or directory

Exporting Essbase Data

DATAEXPORT command in Essbase provides a very useful way to extract data from the Essbase cube.

This exported file can be easily used to import data in case of data corruption or can be kept as a DataBackup for future purpose.

Usual usage of DATAEXPORT is
DATAEXPORT "File" "delimiter" "fileName" "missingChar"
e.g. DATAEXPORT "File" "," "Lev0Budget.txt" "#Missing"

This will generate a Datafile, with a comma delemeted format, with missing data specifies with #Missing.

One important consideration while using the DATAEXPORT command, which might lead to losing the usability of the file (In case you are hoping to use the data export text file to import back into the database sometime), is that for Free Form Import (without a Rule File), Essbase requires the DATAEXPORT to be processed in a unique way, as below:

DATAEXPORT "File" " " "Lev0Budget.txt" "#Mi"

The file which you want to import in free form without using a Rule file, MUST specify the delimeter as SPACE and Missingchar as #Mi.

FDMEE Errors: Writeback failure

In this series of Blogs, I am going to document few FDMEE errors, especially version 11.1.2.4, which I couldn’t find any reference about or there are no KB articles about.

The following error shows up in FDM, while importing Data from Planning to load to Oracle E-business Suite:

ODI-1226: Step COMM Validate Data fails after 1 attempt(s).
ODI-1232: Procedure COMM Validate Data execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “<string>”, line 9, in <module>
File “<string>”, line 4407, in validateData
File “<string>”, line 2622, in validateData
RuntimeError: [u”Error: Data rows with unmapped dimensions exist for Period ‘\u064a\u0646\u0627\u064a\u0631-2016′”]

The Error, although says, is due to Unmapped Dimension, but in fact it is not contributed because of any unmapped dimension, rather, an erroneous dimension  mapping, wherein one member is mapped more than once in the mapping table.

Example below:

On running a visual scan of all Mappings, and deleting the duplicate mappings, this error can be removed.

 

Additional Log Details:

ERROR [AIF]: Cannot calculate. Essbase Error(1200315): Error parsing formula for [FIX STATEMENT] (line 4): invalid object type
INFO  [AIF]: EssbaseService.extractData – END (com.essbase.api.base.EssException: Cannot calculate. Essbase Error(1200315): Error parsing formula for [FIX STATEMENT] (line 4): invalid object type)
DEBUG [AIF]: AIFUtil.callOdiServlet – END
FATAL [AIF]: Error in CommData.extractDataFromSource

 

 

Creating an Essbase Report Script

Creating a Essbase Report script is one of the ways used to export data out from the Essbase Database. Other ways being

1. Using a Calculation Script (Starting version 9.3.1)
2. Using the database export option and
3. Using a MDX (Multi Dimensional Expressions) query.

The report scripts are the simplest of all, to create an export file (either a .txt or .rep o/p file).

While creating the Report Script, first, we define some properties for the output file, like

1. Output File Properties
TABDELIMIT – to specify TAB as a delimiter for the output file
ROWREPEAT – To repeat the rows (just like we would use it in reports)
NOINDENTGEN – To indent or avoid indentation based on Generation
Suppression Options :
SUPEMPTYROWS, SUPMISSINGROWS, SUPCOMMAS (for thousand separators), SUPHEADING (if you don’t want to print the headings in the report output file

2. Page, Row and Column Dimension Definitions.
Having been specified the output file properties, the next step is to specify the ROW, COLUMN and PAGE dimension definitions.
Remember, the report script will process the output for the same member combinations that have been specified in this section.
An example of the ROW, COLUMN and PAGE listings is as follows (For ease, we have used the Sample:Basic DB)

<PAGE(“Measures”)
“Sales”
<COLUMN(“Scenario”, “Year”)
“Scenario”
“Jan” “Feb” “Mar” “Apr”
<ROW(“Market”, “Product”)
“New York”
“Product” “100” “100-10” “200-10″”300-10”
!

The output text file will look as follows:


The report script output can be formatted, arranged, sorted and modified to match the output requirements.

Lets see a simple scenario, where, we want to Reverse the sign of the output.

For achieving the sign reversal, we can add a column to the end of the Report Output file, like in the example below:

<COLUMN (“Scenario”)
“Actual”
{CALCULATE COLUMN “Inverse” = 5 * -1.}
{ORDER 0 1 2 3 4 5 6}

The output file has a newly added column (specified as column 6 in the ORDER command), as follows:

The most commonly used Report Script Commands can be found in the Administration Services Online Help, Here.

Essbase Automation using windows Batch files.

Lets discuss a scenario, where:
At every interval, we have to Export the data from the source Essbase database, and load it to some target Essbase Database. The entire task of Export and Import is to be automated so that there are minimal administration tasks.
The data flow is as follows:
1. Create the Report Scripts, to export Data from source Essbase Database.
2. Create the Rules files to map the source data with Target Essbase Database.
3. Load the Data file to the Target database.
The requirement is to create automation, where the user (or Administrator) will run a single file, which will accomplish above tasks.

Solution: Depending upon the level of automation, there are multiple options to achieve this automation. But in most cases, a combination of MaxL scripts and Windows Batch files should achieve this.

Steps for automating the above tasks,
1. Create a Report Script to Export Data from the source DB.

2. Create a MaxL script to Execute the Report Script, and load the data file generated by this export to the Target Database.

Sample MaxL

3. Create a Windows Batch File, which will invoke the MaxL She’ll, and execute the MaxL commands to achieve the required result. If the users are going to use a client machine for executing this batch file, then make sure that the Essbase Client (with Essmsk.exe) is installed into their machine. We will then be connecting to a Server machine, whose IP address or machine name will be specified in the batch file.
A sample batch file can be seen as follows:

The last line in the batch file is to delete the Essbase data export file, after the data is loaded into the target database.
Additionally, if the ID and Password of the Admin user is to be hidden from the user executing the batch, then we can also convert the Batch file into an executable file (.exe) using the freeware utility Batch to Exe Converter which can be downloaded from Here.

Thus a simple automation is set for the periodic tasks, which need to be executed without any administrative supervision.

Professional Services

Professional Services

In today’s dynamic world the executives and management in organizations are tested every moment by external factors like slashed prices, competition, innovation etc. which in turn leads to changes in strategies and policies. We help organizations extract valuable information from various

data sources. Our main area of focus is implementing business intelligence (BI) and enterprise performance management (EPM) solutions to enhance the decision making processes across the organizations. With a proven methodology Stractive has helped organizations across the world to solve th

eir information delivery problems help them evolve as market leaders.

iNITIATION, EVALUATION, REALIZ

ATION, MONITORING (i-ERM) methodology is broken into four high-level phases. The phases are designed to be run in sequence, as the outputs from one phase are required inputs for the next phase. Each phase focuses on systematic and proven approach to undertake BI and EPM implementations.
Implementation Phases
iNITIATION
This phase is a first step towards building enterprise solution to improve business performance. As a part of initiation process it enables Stractive to understand and define customer’s business needs at high level. Various pre-execution tasks are carried out with the help of customer’s participation to keep check on expectation from proposed business solution(s).
EVALUATION
The first activity in any project is to evaluate the existing environment and understand the design goals of the proposed solution within the customer enterprise. This phase is also focused on defining and validating the project objectives and key success criteria. It also includes the development of a comprehensive project plan that includes all major project milestones, identification of project team and project responsibilities.
REALIZATION
This phase examines three key areas: people, process and technology. The project requirements and knowledge of the organization are used to determine the required architecture. A solution design specification is developed that includes the physical, logical and security design for the proposed architecture, integration and monitoring requirements.

It also includes all key activities involved in moving into full production deployment. It involves building the environment, installation of core software, initial conference room pilot, and deployment of monitoring policies, reporting and alerting.

MONITORING
This phase of the project should be repeated regularly to ensure optimization and alignment with best practices. Along with ensuring undisrupted business (by maintenance program), it involves a review of new technical, business and functional requirements to determine required system changes.

It also includes all key activities involved in moving into full production deployment. It involves building the environment, installation of core software, initial conference room pilot, and deployment of monitoring policies, reporting and alerting.

i-ERM Benefits
This phase of the project should be repeated regularly to ensure optimization and alignment with best practices. Along with ensuring undisrupted business (by maintenance program), it involves a review of new technical, business and functional requirements to determine required system changes.

  • On time, in budget project delivery
  • A high degree of project visibility and documentation
  • Manage risk and change request with shorter iterations
  • Project Stakeholder’s involvement and governance to avoid unnecessary surprises
  • Scalable System architecture to accommodate future business requirements
  • Education programs for users adoption and their increased productivity.
%d bloggers like this: