How to expand Dataprovider SQL Query column in 360Eyes database

How to expand Dataprovider SQL Query column in 360Eyes database

Symptom


When running 360Eyes Impact Analysis Reports (such as Report 52), users observe that the SQL query stored in the 360eyes_REPORTS.unx / Eye Document / Dataprovider / Dataprovider Sql Queryfield is incomplete, partial or truncated. This often results in search operations failing to locate expected documents because the full SQL statement is not stored or displayed.

Typical user cases include searching for specific SQL patterns or keywords in reports, but results are missing due to this field size limitation.

In your 360Eyes Webi and Crystal job logs you are seeing data being truncated in EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY field.

Environment

  • 360Eyes
  • SAP BusinessObjects Enterprise XI 3.1
  • SAP BusinessObjects Business Intelligence 4.0
  • SAP BusinessObjects Business Intelligence 4.1
  • SAP BusinessObjects Business Intelligence 4.2
  • SAP BusinessObjects Business Intelligence 4.3

Reproducing the issue

In the 360Eyes Webi or Cyrstal job log you will see entries similar to this:

 2018-10-02 11:51:32 -0400 INFO Value [SELECT   ACTG_DIM.ACTG_...] has been truncated to 4000 characters but has 11526 characters in total (field EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY)

Cause

By default, the column allows for 4000 characters, which may not be enough to store the entire SQL statement from the dataprovider in your report(s).

Resolution 

Follow the steps below, for your specific database type, to increase the column length.

SQL Server

      ALTER TABLE EYE_DATAPROVIDER ALTER COLUMN DATAPROVIDER_SQL_QUERY VARCHAR(MAX) 

Oracle

Option #1: For oracle 12c and higher, limited to 32k and some db parameters :  http://www.oracletutorial.com/oracle-basics/oracle-varchar2/

Increase to a specific length:

       ALTER TABLE EYE_DATAPROVIDER MODIFY (DATAPROVIDER_SQL_QUERY VARCHAR2(#####));   enter specific length for #####

Option #2: Increase column length and to CLOB datatype:

      ALTER TABLE EYE_DATAPROVIDER ADD (DATAPROVIDER_SQL_QUERY_BIS CLOB);

      UPDATE EYE_DATAPROVIDER SET DATAPROVIDER_SQL_QUERY_BIS=DATAPROVIDER_SQL_QUERY;

      ALTER TABLE EYE_DATAPROVIDER DROP COLUMN DATAPROVIDER_SQL_QUERY;

      ALTER TABLE EYE_DATAPROVIDER RENAME COLUMN DATAPROVIDER_SQL_QUERY_BIS TO DATAPROVIDER_SQL_QUERY;

Update Universe - for Oracle #2 Option
The following object in the 360Eyes_REPORTS Universe will need to be updated in the Universe. The datatype for this object will need to be changed to LONG type if using option #2 for Oracle databases: 
      - Dataprovider Sql Query (in the Dataprovider Class)

Please run the next Webi or Crystal job in FULL mode to extract again the SQL queries for all reports. If this does not work, run the job in FORCE FULL mode.

Keywords

database, SQL Server, Oracle, truncated, partial

Product

Product or Product version

  • 360Eyes

Languages

  • English