How to expand Dataprovider SQL Query column in 360Eyes database

Symptom

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

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 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)

Keywords

database, SQL Server, Oracle

Product

Product or Product version

360Eyes

Languages

English