Advertisements

When Export to Excel (csv) in OBIEE 12c, rows are limited to 65000 (65K)

After upgrading to OBIEE 12c, you may have noticed that your downloads to Excel CSV files are now getting cut off at 65000 rows.  If you are experiencing this issue, this post may help.

In OBIEE 11g, there was a parameter (instanceconfig.xml parameter) called “DefaultRowsDisplayedInDownload” that controlled the number of rows downloadable to a CSV file.   So, if you had that set to a number higher than 65K, then you would have been able to download more than 65K rows in the past.

However, the parameter that now controls the number of rows downloadable to CSV in OBIEE 12c is “DefaultRowsDisplayedInDownloadCSV“.  You will need to set this parameter in the instanceconfig.xml file based on your needs.

This parameter DefaultRowsDisplayedInDownloadCSV is found within the <Table> section which may look something like this:

<Table>
< DefaultRowsDisplayedInDelivery>1000000</DefaultRowsDisplayedInDelivery>
< DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>
< MaxCells>10000000</MaxCells>
< MaxVisiblePages>50</MaxVisiblePages>
< MaxVisibleRows>1000000</MaxVisibleRows>
< MaxVisibleSections>100000</MaxVisibleSections>
< DefaultRowsDisplayed>100</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDownloadCSV>200000</DefaultRowsDisplayedInDownloadCSV>
< /Table>

As always, back up your files before making changes.  Then, change the parameter as needed, and restart OBIEE services.

 

Advertisements

Work-around Solution for ORA-01792: maximum number of columns in a table or view is 1000

When you get this message …

Error message: Caused By: java.sql.SQLSyntaxErrorException: ORA-01792: maximum number of columns in a table or view is 1000

 

This may be a solution for you …

There is a workaround … set the “_fix_control” parameter to ‘17376322:OFF‘.

You can use one of the following statements to set the parameter:

alter session set "_fix_control" = '17376322:OFF';

or

alter system set "_fix_control" = '17376322:OFF';

More information can be found in the following Oracle Documents –  Doc ID 1956038.1 and Doc ID 1951689.1.

Direct Links to various OBIEE User and Administration Presentation functions

These are some direct links to various OBIEE User and Administration functions.  These can be useful to know for efficiency and at times can also be useful for debugging security.

To go directly to “Home Page”
http://servername:9704/analytics/saw.dll?bieehome

To go directly to “Dashboard”
http://servername:9704/analytics/saw.dll?Dashboard

To go directly to “Manage Privileges”
http://servername:9704/analytics/saw.dll?PrivilegeAdmin

To go directly to “Manage Catalog Groups”
http://servername:9704/analytics/saw.dll?ManageGroups

To go directly to “Manage Sessions”
http://servername:9704/analytics/saw.dll?Sessions

To go directly to “Manage Agent Sessions”
http://servername:9704/analytics/saw.dll?ManageIBotSessions

To go directly to “Issue SQL”
http://servername:9704/analytics/saw.dll?IssueRawSQL&Done=%2fanalytics%2fsaw.dll%3fAdmin

To go directly to “Manage Map Data”
http://servername:9704/analytics/saw.dll?ManageMapData

To go directly to “Manage BI Publisher”
http://servername:9704/analytics/saw.dll?bipublisherEntry&Done=%2fanalytics%2fsaw.dll%3fAdmin&Action=admin

Using Custom Images in OBIEE 10g & 11g

In both OBIEE 10g and 11g, custom images need to be placed in 2 folders before use within reports/dashboards.

 In OBIEE 10g, images need to be placed in:

(1)     $BIHOME\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

Windows directory path example:
C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

Linux directory path example:
/usr/local/OracleBI/oc4j_bi/j2ee/home/applications/analytics/analytics/res/s_oracle10/images

-and-

(2)    $BI_HOME\web\app\res\s_oracle10\images

Windows directory path example:
C:\OracleBI\web\app\res\s_oracle10\images

Linux directory path example:
/usr/local/OracleBI/web/app/res/s_oracle10/images

 

In OBIEE 11g, images need to be placed in:

(1)    $OBIEE_FMW_HOME\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp\images

Windows directory path example:
C:\obiee11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\s_blafp\images

Linux directory path example:
/u01/product/middleware/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/s_blafp/images

-and-

(2)    $OBIEE_FMW_HOME\Oracle_BI\bifoundation\web\app\res\s_blafp\images

Windows directory path example:
C:\obiee11g\Oracle_BI1\bifoundation\web\app\res\s_blafp\images

Linux directory path example:
/u01/product/middleware/Oracle_BI1/bifoundation/web/app/res/s_blafp/images

Note: you may also create or use directories at the same level or below the images directories under the s_oracle10 (in 10g) and s_blafp (in 11g) directories, for example, if you wanted to put all your custom images into a separate directory.
 

Then in Answers / Analysis, you would reference the image as shown below.

Edit the Column Properties:
ImagesDirectories-ColumnProperties

Then, in the Style tab, within the Cell area, click on the Image object…

ImagesDirectories-ClickImage

Then in the Graphics dialog, select “Custom Image” and enter  fmap:images/your_custom_image.jpg.

Note: If your images are in a directory other than the images directory, you need to supply that path, but still starting below s_oracle10 or s_blafp directories.

And set the Image Placement property. Images Left (shows the Image to the left of the column data), Images Right (displays the image to the right of the column data), Images Only (displays the image only – no column data)
ImagesDirectories-ImageNameAndPlacement

If new images were placed into the directories after the last BI Server / Presentation Server restart, you will need to restart the BI Server and Presentation Server for the images to be displayed.

Oracle Business Intelligence Enterprise Edition 11g (OBIEE 11g) Books

There are a few Oracle Business Intelligence Enterprise Edition 11g (OBIEE 11g) books on the market currently.  For quite sometime, not many books have been available for OBIEE, and in particular OBIEE 11g.

Anyway, below are 2 books that I think are worthwhile, and 1 that you might want to look into … the links take you to Amazon, and shows the paperback versions. But you can easily navigate to the Kindle versions from there. 

Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial by Christian Screen, Haroun Khan and Adrian Ward (Jul 19, 2012)

Oracle Business Intelligence 11g Developers Guide by Mark Rittman (Sep 18, 2012)

Implement Oracle Business Intelligence (Volume 1) by Mr. Riaz Ahmed (Mar 30, 2012)

I read pretty much all of the Screen/Khan/Ward book and found it to be good, but experts might find it a bit basic.  I just started reading the Rittman book, got through the first 3 chapters, and so far it has been very good. 

I have not read the Ahmed book because it is not yet available on the Kindle, and that’s my preferred way of buying books due to its convenience. But it might be worthwhile checking it out.

Happy Reading!

EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set

If you use the Oracle EVALUATE function in OBIEE, then you will need to set a parameter in the NQSConfig.INI file in OBIEE 11g.

If you get this error below, then you are using the EVALUATE function somewhere inside OBIEE (in one of your analyses/requests) and the parameter needs to be set appropriately in the NQSConfig.INI file.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

This is what you need to do to correct the problem… 

Edit the NQSConfig.INI file, located at: OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

This is what you will see by default in the config file…
————-
# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 0; 
————-

Change the parameter as follows: EVALUATE_SUPPORT_LEVEL = 2;

Save and restart the BI Server.  Your requests should work fine now.

Welcome to OBIEEpedia!

Welcome and thank you for visiting the OBIEEpedia blog!

This blog is dedicated to covering topics related to Oracle Business Intelligence Enterprise Edition (OBIEE) and closely related technologies, such as Data Warehousing, Business Intelligence in general, Oracle Business Intelligence Applications (OBIA), and more.

You are invited to learn, post comments, and share content.