Webcenter Analytics Services Returns Irresolvable Error(S) while Using User Property Filter

(Doc ID 1363476.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle WebCenter - Version: 11.1.1.4.0 and later   [Release: 11g and later ]
Information in this document applies to any platform.

Symptoms


When storing the Analytics schema in Microsoft SQL Server the Webcenter Analytics service shows Logins, Search Metrics, Spaces Metrics, Traffic properly without any error. When adding any User property Filter such as Phone or Employee Id or IM User it and clicking on Refresh Data it shows the following popup error:


Irresolvable error(s) occurred. You may want to contact Administrator with the error reference.


The following exception is logged in the same time to the WC_Spaces-diagnostic.log:


[2011-05-27T04:25:10.257-04:00] [WC_Spaces] [ERROR] [] [oracle.webcenter.webcenterapp] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: weblogic] [ecid: 63af4146be450cb8:1fe98aea:1302c7b2df3:-8000-0000000000004691,0] [APP: webcenter#11.1.1.4.0] getDisplayMessage Called=org.eclipse.persistence.exceptions.DatabaseException[[
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.2.v20101206-r8635): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]'TRIM' is not a recognized built-in function name.
Error Code: 195
Call: SELECT COUNT(t0.ID) FROM ASDIM_USERPROPERTIES t4, ASDIM_USERPROPERTYVALUES t3, ASDIM_WC_APPLICA_0 t2, ASDIM_USERS t1, ASVFACT_WC_LOGINS_0 t0 WHERE (((((((t0.TIMEID >= ?) AND (t0.TIMEID <= ?)) AND UPPER(TRIM(t3.VALUE)) LIKE ?) AND (t2.NAME_ = ?)) AND (t3.USERID = t1.ID)) AND (t4.ID = ?)) AND (((t2.ID = t0.APPLICATION_) AND (t1.ID = t0.USERID)) AND (t4.ID = t3.PROPERTYID)))
	bind => [65905536, 65905860, %PETER%, webcenter, 1]
Query: ReportQuery(referenceClass=WC$$LOGINS$$ASVFACT_WC_LOGINS_0 sql="SELECT COUNT(t0.ID) FROM ASDIM_USERPROPERTIES t4, ASDIM_USERPROPERTYVALUES t3, ASDIM_WC_APPLICA_0 t2, ASDIM_USERS t1, ASVFACT_WC_LOGINS_0 t0 WHERE (((((((t0.TIMEID >= ?) AND (t0.TIMEID <= ?)) AND UPPER(TRIM(t3.VALUE)) LIKE ?) AND (t2.NAME_ = ?)) AND (t3.USERID = t1.ID)) AND (t4.ID = ?)) AND (((t2.ID = t0.APPLICATION_) AND (t1.ID = t0.USERID)) AND (t4.ID = t3.PROPERTYID)))")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:683)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
	...
	at weblogic.work.ExecuteThread.run(ExecuteThread.java:176)
Caused by: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]'TRIM' is not a recognized built-in function name.
	at weblogic.jdbc.sqlserverbase.dda4.b(Unknown Source)
	...
	at weblogic.jdbc.sqlserverbase.ddb9.executeQuery(Unknown Source)
	at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:888)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:598)
	... 219 more

]] 

Also, the following exception is logged in the same time to WC_Utilities.out and WC_Spaces.out:



<May 26, 2011 4:25:10 PM EDT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1 FROM DUAL" set up for pool "ActivitiesDS" failed with exception: "java.sql.SQLSyntaxErrorException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Invalid object name 'DUAL'.".>

The error goes away when removing the user property filter and clicking on Refresh Data again.
Also, in an Oracle RDBMS, there are no errors when specifying User Property filter.

Steps to reproduce
===================
1. Load the Analaytics schema (dev_activities) into an MS SQL Server 2005 or 2008

2. Configure WebCenter to use the analytics schema in the SQL Server

You may need to implement the following workaround as per
<Bug 12313379> - REPORTS FAIL WHEN DATASOURCE IS CHANGED
in case you had to change the data source:

I. Export analytics first to get the right path:

exportMetadata(application='webcenter',server='WC_Spaces', docs='/oracle/webcenter/analytics/**', toLocation='/tmp/')


II. Delete the analytics MDS with the right path that was found in the export:

deleteMetadata('webcenter','WC_Spaces','/oracle/webcenter/analytics/scopedMD/s8bba98ff_4cbb_40b8_beee_296c916a23ed/analytics/*mnascnsl_4cbb_40b8_beee_296c916a23ed.xml');


III. Restart WC_Spaces and WC_Utilities

3. Login to WebCenter as an admin

4. Access the Analytics page at
http://host:port/webcenter/spaces/page/Analytics

5. Notice that the reports work fine if not setting any User Property

6. Go to Summary Metrics -> Logins

7. Set any User Property

8. Click on Refresh Data

and notice the error

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms