Smart View Error “Cannot connect to the provider. Make sure it is running in the specified host/port. Error(503) ” When Opening Hyperion Planning Connection.


When attempting to connect from Smart View to Hyperion Planning the following error message is displayed:

Cannot connect to the provider. Make sure it is running in the specified host/port. Error(503)

 

This occurs due to The OHS server Timeout for APS is set too low. In configuration file mod_wl_ohs.conf the values for  “WLIOTimeoutSecs” and “WLSocketTimeoutSecs” are too low and the connection to Smart View is timing out.

  1. Shutdown the Essbase Server service or process.
  2. Backup the mod_wl_ohs.conf configuration file. The file is found in the following location on your APS server:
    C:\Oracle\Middleware\user_projects\epmsystem\httpConfig\ohs\config\OHS\ohs_component
  3. In mod_wl_ohs.conf increase the timeout in seconds for the “WLIOTimeoutSecs” and “WLSocketTimeoutSecs” properties within the location section for APS:
    SetHandler weblogic-handler
    WeblogicCluster :13080
    WLIOTimeoutSecs 1800
    WLSocketTimeoutSecs 1800
  4. Save the modified mod_wl_ohs.conf file.
  5. Restart the Oracle HTTP Server and the WebLogic Managed server service or process for APS.

 

Advertisements

Smart View Error 1020043 – You do not have sufficient access to perform a lock on this database


When attempting to submit data through Smart View the following error occurs:

ERROR
———————–
Error 1020043 – You do not have sufficient access to perform a lock on this database.

STEPS
———————–
The issue can be reproduced at will with the following steps:
1. Open Excel.

2. Connect to the Essbase server using Private connection.

3. Perform Adhoc analysis.

4. Enter data and submit

 

To Fix-

Delete the security the filter in EAS for that user in the application after taking backup of the same.

Steps Followed:

1. Open EAS Console > Expand Essbase server > Expand Application > Right Click on the database > Edit > Filters.

2. Select the user from the Filter Name  and edit.

3. Take backup of the “Filter Rows” and delete Read and Write access only.

4. Save it

Log into the Planning as admin and create a security filter for that user.

Now that user can submit data from Smart View without any issue.

 

After upgrading Essbase v11.1.2.x to 11.1.2.4, Unable to Start Essbase in an Active/Passive Cluster, “Fatal Error: Failed to start ESSBASE. Lease Manager Initialization Failed.”


After applying the Essbase 11.1.2.4.0 maintenance release to 11.1.2.x in an active/passive environment, Essbase is unable to start.  The error below is shown in the Essbase.log and is generated when starting node1 via OPMN:

Fatal Error: Failed to start ESSBASE. Lease Manager Initialization Failed.

The opmn.log shows the error:

[2015-09-18T14:42:51-04:00] [opmn] [NOTIFICATION:1] [75] [ons-connect] Connection 1a;167.22.157.151;6712 connect (Connection refused) [2015-09-18T14:44:51-04:00] [opmn] [NOTIFICATION:1] [75] [ons-connect] Connection 1b;167.22.157.52;6712 connect (Connection refused)

The leasemanager.log shows the error:

2015-09-21T12:51:20.91-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-9] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Attempt to connect to database failed with error [[DataDirect][ODBC lib] Data source name not found and no default driver specified].
[2015-09-21T12:51:20.92-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-9] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Attempt to connect to database failed with error [[DataDirect][ODBC lib] Data source name not found and no default driver specified].
[2015-09-21T12:51:20.92-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-1] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Failed to acquire the lease after [6] consecutive attempts.
[2015-09-21T12:51:20.92-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-16] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Lease is being surrendered.
[2015-09-21T12:51:20.92-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-11] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Preparing to shutdown abort.
[2015-09-21T12:51:22.93-04:00] [ESSBASE0] [NOTIFICATION:16] [LM-12] [LM] [ecid: 1442854174685,0] [tid: 140298866731328]  Terminating the process.

 

This issue has been verified as the following unpublished bugs:

Bug 20411298 – RECONFIGURE ESSBASE FOR MAINTENANCE UPGRADE REMOVED SETTING FROM ESSBASE.CFG.
Bug 20405799 – RECONFIGURE ESSBASE FOR MAINTENANCE UPGRADE REMOVED FAILOVER SETTING IN OPMN.XML

 

To fix- 

After the upgrade is done, there are two options:

1.  Update the files manually with the settings from previous version, leaving the driver and ODBC information pointing to 7.1.

2.  If the files are copied from a previous version, update them with the new driver information.  For example,

In the essbase.cfg file, update the drivers to 7.1:

BPM_Oracle_DriverDescriptor “DataDirect 7.1 Oracle Wire Protocol”
BPM_DB2_DriverDescriptor “DataDirect 7.1 DB2 Wire Protocol”
;BPM_SQLServer_DriverDescriptor “DataDirect 7.1 SQL Server Native Wire Protocol”
BPM_SQLServer_DriverDescriptor “DataDirect 7.1 SQL Server Wire Protocol”
;BPM_Netezza_DriverDescriptor “NetezzaSQL”
BPM_Teradata_DriverDescriptor “Teradata”
;BPM_ORACLEBI_DriverDescriptor “Oracle BI Server 11g_OHXXXX”
BPM_ORACLEBI_DriverDescriptor “Oracle BI Server”
BPM_MySQL_DriverDescriptor “DataDirect 7.1 MySQL Wire Protocol

In the opmn.xml file, update the ODBC variables:

<variable append=”true” id=”LD_LIBRARY_PATH” value=”$EPM_ORACLE_HOME/common/ODBC-64/Merant/7.1/lib”/>

<variable id=”ODBCLIBHOME” value=”$EPM_ORACLE_HOME/common/ODBC-64/Merant/7.1/lib”/>
<variable id=”ODBCINI” value=”$EPM_ORACLE_HOME/common/ODBC-64/Merant/7.1/odbc.ini”/>
<variable id=”ODBCINST” value=”$EPM_ORACLE_HOME/common/ODBC-64/Merant/7.1/odbcinst.ini”/>

 

OBIEE 11g: Answers Report With Essbase Datasource Gives Error: “[nQSError: 46008] Internal error SQOIGeneratorBuiltIn.cpp”


When running a report with Essbase as the datasource, the following error occurs:

“Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 46008] Internal error: File server\Query\Optimizer\ServiceInterfaceMgr\Src\SQOIGeneratorBuiltIn.cpp, line 770. (HY000)”
————–x————–

If a TRIM funcion is added to the column “Account”.”Gen6,Account” on the Answers report, the report works fine.

TRIM(“Account”.”Gen6,Account”)

 

 

This report worked before. The only change that has been done, is the re-import of an Essbase cube to the OBIEE repository and in the Essbase cube the Custom4 dimension has changed. Also some dimension members in the Account dimension in the Essbase cube have moved from one hierarchy level to another.

If you are not dealing with external aggregate, e.g. sum, then you can turn off the db feature QUALIFIED_DATA_REFERENCE_SUPPORTED.In general, it is not supported searching conditions using scalar functions such as substring, unless the search expression is also on the projection.

Turn off (disable) the db feature QUALIFIED_DATA_REFERENCE_SUPPORTED.

Steps:

1) Open the repository using the Administration Tool. In the Physical Layer, select the ‘Essbase’ database, double click, go to Features tab, and UNCHECK the QUALIFIED_DATA_REFERENCE_SUPPORTED setting.

2) Restart BI Server.

3) Test again the report in OBIEE.

 

Essbase load from ODI fails with: com.hyperion.odi.essbase.ODIEssbaseException: Error Records Reached The Maximum Error Threshold : 1


When running a load to Essbase from ODI the load fails and the following is seen in the ODI Operator log:

com.hyperion.odi.essbase.ODIEssbaseException: Error records reached the maximum error threshold : 1
at com.hyperion.odi.essbase.ODIEssbaseDataWriter.loadData(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyMethod.__call__(PyMethod.java)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyInstance.invoke(PyInstance.java)
at org.python.pycode._pyx15.f$0(&amp;amp;amp;amp;amp;amp;amp;lt;string&amp;amp;amp;amp;amp;amp;amp;gt;:23)
at org.python.pycode._pyx15.call_function(&amp;amp;amp;amp;amp;amp;amp;lt;string&amp;amp;amp;amp;amp;amp;amp;gt;)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at org.python.core.Py.exec(Py.java)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
at com.sunopsis.dwg.codeinterpretor.k.a(k.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
at com

 

 

In cases like this it is usually pretty simple to determine the root cause of the failure if you enable error logging on the Essbase IKM in your ODI interface as per note 1088139.1.

This will generate a log containing the errors returned from Essbase. The errors are usually pretty explicit about the root cause of the issue. Errors like these are commonly associated with missing metadata in the Essbase target. The log entries are in csv (comma separated) format so you can import the csv into a spreadsheet such as Microsoft Excel and the error will be in the final column. If the error mentions a missing member then the column header shows the dimension name so usually allows you to see which dimension the member is in.

It can sometimes be helpful when debugging to temporarily increase the value of the MAXIMUM_ERRORS_ALLOWED option of the Essbase IKM in ODI so that you can see more of the errors which are being produced (if multiple errors seem to be being produced) so that you do not need to correct problems one at a time (for potentially hundreds of failures or more).

 

FDMEE: Locations, Import Formats and Scripts


Locations

In FDMEE, a Location is a level where a data load is executed.  In other words, users define locations to specify where to load the data and each location is assigned an Import Format, Data Load Rules and Data Load Mappings.  As discussed below, users must create Import Formats before Locations are created. The same Import Format can be used for numerous Locations. To add a Location:

  1. Click Add
  2. Assign a name
  3. Assign the corresponding Import Format
  4. Assign the Target Application
  5. Assign the Source System type: File
  6. Assign the Functional Currency: Location currency
  7. Data Value: <Entity Currency>
  8. Parent Location: To share mappings between two locations, assign a Parent Location
  9. Logic Account Group: [NONE]
  10. Check Entity Group

Note: When adding a new location, a Check Entity Group must be created.

  1. Check Rule Group
  2. Click Save

 

Import Formats

As mentioned above, each Location must be assigned an Import Format. The Import Format defines the structure of the source file and is executed during the source file import process. File based imports are supported for companies who do not have direct connections to their source data, but have data available from their source in a text file. A corresponding Import Format must be created before creating a Location. As shown in the image below, to add an Import Format:

  1. Click Add
  2. Assign a name
  3. Source System: File
  4. File Type:

-Fixed
-Delimited: If delimited, specify the type of delimiter (comma, pipe, semicolon, etc)

  1. Specify the Target Application
  2. Add a description if needed
  3. Click Save

Defining an Import Format

The Source Column should replicate what is shown under the Target column

Note: Multiple source columns of the same dimension is acceptable. For instance, there can be three Account source columns.

-The Field Number signifies the column in the source file

-When using a comma delimited structure, the following rows can be added to define the Import Format under Mappings:

-Currency Row
-Attribute Row
-Description Row
-Dimension Row: Select the arrow to view all of the dimensions

-When using a fixed structure, the following rows can be added to define the Import Format under Mappings:

-Skip Row: This option is used to indicate rows in that file should be skipped
-Currency Row
-Attribute Row
-Description Row
-Dimension Row: Select the arrow to view all of the dimensions

-Import expressions operate on the value that is read from the source file enabling FDMEE to read and parse information in the data file. Expressions include:

-Script: Click on the pencil icon to add a script
-Sign=,-: Used to manage nonstandard numeric sign conventions, such as negative numbers in a source file
-Fill=EurotoUS: Used with the Amount field to trigger a number format conversion from ., to ,.
-Fill000: Used to fill fields with trailing characters such as zeros

For example:

Account number: 203
Expression: Fill=00000
Post Import: 20300

Script Editor

The Script Editor in FDMEE uses scripts written in Jython to assist files during import processes. FDMEE supports three types of scripts: Event, Import and Custom scripts.

Event scripts are executed when a particular event runs. For instance, to execute a script before any import, choose the BefImport event script.

1. Navigate to Script Editor (Setup) under Scripts.
2. Specify the Target Application and Technology- Jython.
3. Select Event > BefImport.
4. Select New
5. Create or edit a script
6. Click Save

This following script has been applied to remove apostrophes from data files.

import os,re

if fdmContext[“LOCNAME”] == “Location Name “:
filename = fdmContext[“FILENAME”]
inbox = fdmContext[“INBOXDIR”]
fdmAPI.logInfo(filename)
fdmAPI.logInfo(inbox)
file = inbox + ‘\\’ + filename
file_out = inbox + ‘\\’ + filename + ‘.out’
fdmAPI.logInfo(file)
fin = open(file,’r’)
fout = open(file_out,’w’)
for line in fin:
line = line.replace(“‘”, “”)
fout.write(line)
fin.close()
fout.close()
os.remove(file)
os.rename(file_out,file)

Import scripts are created when Import Formats are defined.

1. Repeat steps 1 and 2 above
2. Select Import
3. Select New
4. Create or edit a script
5. Click Save

The following script has been applied to successfully load Plan-Periodic files.

def CheckPeriodic(strfield, strrecord):
iFind = strrecord.find(‘Periodic’)
if iFind > 0:
sValue=’Periodic’
else:
sValue=’YTD’
return sValue

To apply a script to an Import Format:

  1. Navigate to Import Format under Integration Setup
  2. Select the Import Format
  3. Under Mappings, select the pencil icon
  4. In the Add Expression window,
  5. Expression Type: Script
  6. Expression Value: Choose the script
  7. Select OK

Jython Script Limitation in FDMEE


When Jython script reaches the limit, the following error occurs.

arrAccnt = (‘xxx’,’xxx’)
ICP = fdmRow.getString(‘ICP’)     ENTITYX =
fdmRow.getString(‘ENTITYX’)    if
fdmRow.getString(‘ACCOUNTX’) in arrAccnt:

 

As a workaaround the below steps might be helpful.

1) Split the code into 2 mappings.
2) Combine multiple if clauses
e.g.:elif ICP[-6:] == ‘xxx’ or ICP[-6:] == ‘xxx’: fdmResult = ‘xxx_N’
3) Change logic:
if ICP[-6:] == ‘xxx’ or ICP[-6:] == ‘xxx’: fdmResult = ICP[-6:]
4) Explore other mapping types e.g. SQL based scripts, format mask etc.

 

What caused the Data Relationship Management (DRM) Environment to Restart Reboot Suddenly


The IIS Application pool Recycling was not configured or is miss-configured . Without this configured IIS will continue to use memory for the web application until its allotted memory is full. It will then recycle to clear the memory. This happens all existing session are terminated. Since DRM work primarily in memory all data not saved to the database will be lost.

To configure this parameter follow these steps:

To open IIS Manager from the Start menu

Click System and Security, and then click Administrative Tools.

In the Administrative Tools window, double-click Internet Information Services (IIS) Manager.
To open IIS Manager from the Search box

Click Start.
In the Start Search box, type inetmgr and press ENTER.

In the Connections pane, expand the server node
Click Application Pools.
On the Application Pools page, select an application pool generally drm_pool,
Click Recycling in the Actions pane
Click the box next to Specific time
In the box below enter a non business time ( IE 2:30 AM)
Click next
Click Finish
Click Recycle on the right hand side ( this will activate the changes)

Close IIS Manager

OBIEE 11g – How To Remove Null Values In Prompts After 11.1.1.9.0 Upgrade


 When you have a prompt that shows a “drop-down” list to select from, it can include a “null” selection. 

This can be suppressed by a single configuration setting for all prompts:

  •  Open your “instanceconfig.xml” file, and locate this entry:
    •  <ShowNullValueInPromptsWhenDatabaseColumnIsNullable>always</ShowNullValueInPromptsWhenDatabaseColumnIsNullable>
  • Change “always” to “never”
  • Save the file
  • Restart OBIEE

 

OBIEE 11g – How To Upgrade From OBIEE 11.1.1.7 To OBIEE 11.1.1.9


Please review the documentation here:

Section “Patch Oracle BI from an Earlier 11g release”

Please also review:

https://docs.oracle.com/middleware/11119/core/FUGBI/bi_plan.htm#FUGBI522

Oracle® Fusion Middleware Upgrade Guide for Oracle Business Intelligence

1.7 Moving from 11.1.1.3, 11.1.1.5, 11.1.1.6, or 11.1.1.7 to 11.1.1.9