Obiee CSV Export Limit Change

Issue Description:

When we export a Report in CSV  format in Obiee11g, by default it will export 65,000 rows.

Even if we increase the below parameter in "instanceconfig.xml" it won't work for CSV format .

"<DefaultRowsDisplayedInDownload>200000</DefaultRowsDisplayedInDownload>"


Solution:

We need to add another parameter for CSV separately, it won't be available in config file by default.
This should be a new tag under <Views> <Table> 

<DefaultRowsDisplayedInDownloadCSV>300000</DefaultRowsDisplayedInDownloadCSV>

1) After this change, take backup of existing instanceconfig.xml

Path: $MW_HOME/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1

2) opmnctl stopall

3) Replace instanceconfig.xml

4) opmnctl startall

5) Download now beyond 65,000 rows in CSV



Note: 

Do not add this parameter under <Pivot> section, this will not let presentation services up.
Parameter under table section, will also work for Pivot Table download.

SQL Query Tuning

Non-Column Expressions 

-----------------------------------

Write the query as

SELECT id, name, salary 

FROM employee 

WHERE salary < 25000;

Instead of:

SELECT id, name, salary 

FROM employee 

WHERE salary + 10000 < 35000;

Usage of DECODE to avoid multiscan

----------------------------------------------

To avoid the scanning of same rows or joining the same table repetitively. 

DECODE can also be made used in place of GROUP BY or ORDER BY clause. 

For Example: Write the query as

SELECT id FROM employee 

WHERE name LIKE 'S%' 

and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 

WHERE name LIKE 'Ramesh%';

Tar a directory in Linux Server

Archive a File or Directory

=====================================

tar -czvf name-of-archive.tar.gz /path/to/directory-or-file

• -c: Create an archive.
• -z: Compress the archive with gzip.
• -v: Display progress in the terminal while creating the archive, also known as “verbose” mode. The v is always optional in these commands, but it’s helpful.
• -f: Allows you to specify the filename of the archive.

If no compression required (i.e., no gzip)

----------------------------------------

tar -cvf name-of-archive.tar.gz /path/to/directory-or-file

Multiple Directories at a time

--------------------------------------

tar -czvf archive.tar.gz /u01/app  /u01/appv2/tnslatest.txt

Exclude any files or directories

----------------------------------

tar -czvf name-of-archive.tar.gz /u01/app --exclude=/u01/app/oracle/backups --exclude=/u01/app/oracle/Middleware/BI_bkp

tar -czvf name-of-archive.tar.gz /u01/app --exclude=*.txt

Extract the Archive

=================================

Just replace "-c" switch with "-x" switch

tar -xzvf name-of-archive.tar.gz /path/to/directory-or-file