OBIEE11g: Managing the Oracle BI Presentation Services Cache Settings

When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.
The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP. The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory:
ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp
The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.
The Presentation Services cache is different from the cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.
The following procedure provides information about configuration changes with which you can manage the Presentation Services cache.


To manually edit the settings for managing the cache:

1. Open the instanceconfig.xml file for editing
   
2. Locate the section in which you must add the elements
   
   Note:

   Avoid specifying values of less than 3 minutes for the elements that affect minutes. At such a low amount of time, refreshes can occur frequently, which can negatively affect performance and cause flickering on the screen.
   
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

   <ServerInstance>
     <Cache>
       <Query>
         <MaxEntries>100</MaxEntries>
         <MaxExpireMinutes>60</MaxExpireMinutes>
         <MinExpireMinutes>10</MinExpireMinutes>
         <MinUserExpireMinutes>10</MinUserExpireMinutes>
       </Query>
     </Cache>
   <ServerInstance>
   

4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence.
   

Table 7-7 Elements for Configuring the Cache for Presentation Services

Element	Description	Default Value
MaxEntries	Specifies the maximum number of open record sets that Presentation Services keeps open at any one time. The minimum value is 3. For systems under significant loads, you can increase this value to 700 or 1000.	500
MaxExpireMinutes	Specifies the maximum amount of time, in minutes, that an entry in the cache can exist before it is removed. Depending on the number of analyses being run, an entry might be removed before the time limit expires.	60 (one hour)
MinExpireMinutes	Specifies the minimum amount of time, in minutes, that an entry in the cache can exist before it is removed. The setting for CacheMinUserExpireMinutes can force an entry for a particular user to exist for a longer time than that specified by the CacheMaxExpireMinutes element.	10
MinUserExpireMinutes	Specifies the minimum amount of time, in minutes, that an entry in the cache can exist after it has been viewed by a user. For example, if CacheMaxExpireMinutes is set to 60 minutes and a user views the entry during the 59th minute, the entry exists for that user for an additional 10 minutes. The user can continue paging through the data without requiring a new analysis to be run.	10

OBIEE11g: Changing the Configuration Limits of PROMPTS in INSTANCECONFIG.XML file of OBIEE11g and Auto Complete Prompt Values

You can configure settings that affect the way that users work with prompts, as described in this section.
To configure for prompts:

1. Open the instanceconfig.xml file
   
2. Locate the sections in which you must add the elements
   
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

   <ServerInstance>
     <Prompts>
       <MaxDropDownValues>256</MaxDropDownValues>
       <AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
       <AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
       <AutoCompletePromptDropDowns>
         <SupportAutoComplete>true</SupportAutoComplete>
         <CaseInsensitive>true</CaseInsensitive>
         <MatchingLevel>MatchAll</MatchingLevel>
         <ResultsLimit>50</ResultsLimit>
       </AutoCompletePromptDropDowns>
     </Prompts>
   </ServerInstance>
   

     
   Note: This example does not include elements that might exist in the file, but that are centrally managed by Fusion Middleware Control and cannot be changed manually.
   
4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence.
   

Table 18-4 Elements for Configuring Prompts

Element	Description	Default Value
AutoApplyDashboardPromptValues	Specifies whether to display various fields, as described in the following list: If True, then The Show Apply Button and Show Reset Button fields are displayed on the Edit Page Settings dialog. The Prompts Apply Buttons and Prompts Reset Buttons fields are displayed in the Dashboard Properties dialog. The Prompt Buttons on Current Page option is displayed on the Dashboard builder's Tools menu. If False, then The Show Apply button and Show Reset button fields are not displayed on the Edit Page Settings dialog. The Prompts Apply Buttons and Prompts Reset Buttons fields are not displayed in the Dashboard Properties dialog. The Prompt Buttons on Current Page option is not displayed on the Dashboard builder's Tools option.	True
AutoSearchPromptDialog	Specifies whether search results are displayed and highlighted when the user types the search parameter (without clicking the Search button).	True
CaseInsensitive	Specifies whether the auto-complete functionality is case-insensitive. If set to True, case is not considered when a user enters a prompt value such as "Oracle" or "oracle." If set to False, case is considered when a user enters a prompt value, so the user must enter "Oracle" and not "oracle" to find the Oracle record. The system recommends the value with the proper case.	True
Matching Level	Specifies whether the auto-complete functionality uses matching to find the prompt value that the user enters into the prompt field. These settings do not apply when the user accesses the Search dialog to locate and specify a prompt value. Use the following settings: StartsWith — Searches for a match that begins with the text that the user types. For example, the user types "M" and the following stored values are displayed: "MicroPod" and "MP3 Speakers System". WordStartsWith — Searches for a match at the beginning of a word or group of words. For example, the user types "C" and the following values are displayed: "ComCell", "MPEG Camcorder", and "7 Megapixel Digital Camera". MatchAll — Searches for any match within the word or words.	MatchAll
MaxDropDownValues	Specifies the maximum number of choices to display in the following locations: In choice lists in dashboard prompts. In the Available list of the Select Values dialog that is displayed when the user clicks the Search link in a prompt and the More link to display additional choices. In the Available list of the Select Values dialog when the user performs a search in that dialog.	256
ResultsLimit	Specifies the number of matching values that are returned when the auto-complete functionality is enabled.	50
SupportAutoComplete	Enables or disables the auto-complete functionality of prompts. A setting of True turns auto-complete on, which means that the Prompts Auto-Complete field is displayed and is set to On in the My Account dialog and in the Dashboard Properties dialog. A setting of False turns auto-complete off, which means that the auto-complete fields in the My Account and Dashboard Properties dialogs are not available.	False, unless you are running Oracle BI EE on the Oracle Exalytics In-Memory Machine

OBIEE11g: Changing the Configuration Limits of Different Views(Table,Pivot Table,Trellis,Charts etc) in INSTANCECONFIG.XML file of OBIEE11g

Scenario:

The number of records that should be shown in the Views(Table,Pivot Table,Trellis,Charts etc) will be configured in the INSTANCECONFIG.XML file. There are some default settings which is applied to the Views, You can change those default settings if you want to increase/decrease the limits by editing or adding few tags in INSTANCECONFIG.XML file. For example the default number of records displayed in any of the views would be 65,000 if the reports is getting more than the default limit then you get an error message saying that max number of cube records are exceeded. In this kind of situations we can fix this issue by changing the default configurations.

<ODBC>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ResultRowLimit>700000</ResultRowLimit>
</ODBC>

NOTE: 
1) You should take a back up of Instanceconfig.xml file before editing.
2) These tags should be added in between <ServerInstance> </ServerInstance> only
3) Few of the tags will be present in the instanceconfig.xml file by default in that case we dont need to add them again just go and change the number in between the tags as per requirement. If you dont find the tag related to a specific view then you can add it as shown below.

 To manually edit the settings:

1. Open the instanceconfig.xml file for editing (
   ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn
   For example:
   \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1)
   
   
2. Locate the Cube section, in which you must add the following elements: If you dont find the cube section then add it.
   
   • CubeMaxRecords — Specifies the maximum number of records that are returned by an analysis for the view to process. This roughly governs the maximum number of cells that can be populated in a view; unpopulated cells in a sparse view do not count. The default is 40000.
     
   • CubeMaxPopulatedCells — Specifies the maximum number of cells in a view that can be populated with data from the Oracle BI Server. The default is 120000.
     
3. Include the elements and their ancestor elements as appropriate, as shown in the following example:
   

    <ServerInstance>

   <ODBC>
   <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
   <ResultRowLimit>700000</ResultRowLimit>
   </ODBC> 

     <Views>
       <Cube>
         <CubeMaxRecords>40000</CubeMaxRecords>
         <CubeMaxPopulatedCells>120000</CubeMaxPopulatedCells>
       </Cube>
         <Table>
           <MaxCells>10000</MaxCells>
           <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>500</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
           <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
           <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
         </Table>
         <Pivot>
           <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
           <MaxVisibleColumns>300</MaxVisibleColumns>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>500</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
           <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
           <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
         </Pivot>
         <Trellis>
           <Simple>
               <MaxCells>1000</MaxCells>
               <MaxVisibleSections>10</MaxVisibleSections>
               <MaxVisiblePages>1000</MaxVisiblePages>
               <MaxVisibleRows>100</MaxVisibleRows>
               <MaxVisibleColumns>75</MaxVisibleColumns>
               <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
               <DefaultRowsDisplayed>10</DefaultRowsDisplayed>
               <DefaultRowsDisplayedInDelivery>100</DefaultRowsDisplayedInDelivery>
               <DefaultRowsDisplayedInDownload>6500</DefaultRowsDisplayedInDownload>
           </Simple>
           <Advanced>
               <MaxCells>5000</MaxCells>
               <MaxVisibleSections>50</MaxVisibleSections>
               <MaxVisiblePages>1000</MaxVisiblePages>
               <MaxVisibleRows>250</MaxVisibleRows>
               <MaxVisibleColumns>150</MaxVisibleColumns>
               <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
               <DefaultRowsDisplayed>25</DefaultRowsDisplayed>
               <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
               <DefaultRowsDisplayedInDownload>10000</DefaultRowsDisplayedInDownload>
           </Advanced>
         </Trellis>
         <Charts>
           <MaxVisibleColumns>2000</MaxVisibleColumns>
           <MaxVisiblePages>1000</MaxVisiblePages>
           <MaxVisibleRows>2000</MaxVisibleRows>
           <MaxVisibleSections>25</MaxVisibleSections> 
           <EmbedFonts>True</EmbedFonts>
           <SectionSliderDefault>150</SectionSliderDefault>
           <SectionSliderLimit>300</SectionSliderLimit>
           <JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>
         </Charts>
         <Narrative>
           <MaxRecords>40000</MaxRecords>
           <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
         </Narrative>
         <Ticker>
           <MaxRecords>40000</MaxRecords>
         </Ticker>
     </Views>

    </ServerInstance>
   

4. Save your changes and close the file.
   
5. Restart Oracle Business Intelligence Services (OPMN Services).

Element	Description	Default Value	Applicable Views
DefaultRowsDisplayed	Specifies the default number of rows to display in views in analyses and dashboards. This number should not exceed the number that is specified for the MaxVisibleRows element.	25 (10 for Simple Trellis)	Narrative, Pivot Table, Table, Trellis
DefaultRowsDisplayedInDelivery	Specifies the default number of rows that can be included in the view when it is displayed on a dashboard.	100 for Simple Trellis; 250 for Advanced Trellis, Table, and Pivot Table	Pivot Table, Table, Trellis
DefaultRowsDisplayedInDownload	Specifies the default number of rows that can be included in the view when it is downloaded, such as to a PDF file.	65000 (6500 for Simple Trellis; 10000 for Advanced Trellis)	Pivot Table, Table, Trellis
MaxCells	Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows, which is what the system attempts to render.	50000 (1000 for Simple Trellis)	Pivot Table, Table, Trellis
MaxPagesToRollOutInDelivery	Specifies the maximum number of pages that can be included in the view when it is displayed on a dashboard.	1000	Pivot Table, Table, Trellis
MaxRecords	Specifies the maximum number of records that can be included in the view.	40000	Narrative, Ticker
MaxVisibleColumns	Specifies the maximum number of columns to be displayed in a view.	300 (75 or Simple Trellis; 150 for Advanced Trellis)	Graph, Pivot Table, Trellis
MaxVisibleRows	Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value. For tables and pivot tables, specifies the following: The number of rows that is displayed on the tooltip for the Display Maximum Rows per Page paging control button. The uppermost value to specify for the Maximum Number of Rows to Download and the Maximum Number of Rows Per Page to Include options in Fusion Middleware Control.	500 (100 or Simple Trellis; 250 for Advanced Trellis)	Graph, Pivot Table, Table, Trellis
MaxVisiblePages	Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.	1000	Graph, Pivot Table, Table, Trellis
MaxVisibleSections	Specifies the maximum number of sections to be displayed in a view. This element does not apply when a slider is in place for a graph. The SectionSliderDefault and SectionSliderLimit elements apply to limit section values when a slider is in place.	25 (10 or Simple Trellis; 50 for Advanced Trellis)	Graph, Pivot Table, Table, Trellis
JavaHostReadLimitInKB	Specifies the maximum amount of data that is sent to the browser for a single graph.	4096	Graph

OBIEE 11g: Cannot Copy Data from Reports and Paste to the Windows Clipboard

Scenario:  After placing the reports on the dashboard or in the Results tab the user may want to select a specific cell or complete table/pivot table and copy it to the clip board subsequently they would like to copy the clipboard content to excel or csv or text file etc...

We raised a service request to Oracle Support and got the following answer.

OBIEE 11g: Cannot Copy Data from Reports and Paste to the Windows Clipboard (Doc ID 1287265.1)

---

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Information in this document applies to any platform.

Symptoms

You are not able to copy text from tables or pivot views and paste to the Windows clipboard in OBIEE 11g. It was possible in OBIEE 10g by clicking and marking the area of the content of a table and copying it into the Windows clipboard.

Cause

It is a feature not yet available in OBIEE 11g.
There are two enhancement requests for this functionality:

Bug: 9927279 - 11g beta : unable to copy data from 11g table or pivot table like in 10g
Bug 10406798 - unable to copy data from 11g table or pivot table like in 10g

Solution

Please be advised that the following bugs are still open; however, see if the following work-around steps help:

1. Place the mouse cursor outside the view and click.
2. Drag the mouse across the view to highlight the data you want to copy.
3. Use control+C or copy from the edit menu or right mouse menu to copy the text to the clipboard.

Please note this workaround only works with Internet Explorer.  It does not work with Firefox or Chrome.

References

BUG:9927279 - 11G BETA : UNABLE TO COPY DATA FROM 11G TABLE OR PIVOT TABLE LIKE IN 10G

OBIEE11g: Changing Default Views in the Compound Layout of OBIEE11g

Scenario:

We all know that the default views in the Compound Layout of OBIEE (10g & 11g) are Title and Table. But i would like to have Title and Chart as default views in the compound layout. The following can explain how to get this requirement.


Step 1:

Create a custom xml message file called compoundlayout.xml and save it under the custom messages folder.

Note: If you don’t find the CustomMessages folder in {MW_Home}

\Oracle_BI1\bifoundation\web\msgdb path then create a folder with name “CustomMessages”. 




Step2:

Then create one xml file with the below code and save it in the same path.

Folder location-

{MW_Home}\Oracle_BI1\bifoundation\web\msgdb\customMessages

If you have not done any customization you have to create this folder under msgdb.

<?xml version="1.0" encoding="utf-8"?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">

 <WebMessageTable system="Answers" table="ViewDefaults">

<WebMessage name="kuiCriteriaDefaultViewElements" translate="no"><HTML>

 <view signature="compoundView" ><cv signature="titleView" />   

<cv signature="dvtchart" />

  </view>

</HTML>

</WebMessage>

 </WebMessageTable>

</WebMessageTables>

Note that the views will be created in the same order as you define in your xml file.



As per our XML file first Title View will be created and then Chart view.
In the same way you can add multiple views like Narrative,View Selector,Table etc...

You can use the following html tags for those views

<cv signature="narrativeView" />

 <cv signature="viewSelector" />

 <cv signature="tableView" />



Step3:

Restart BI Services and start building reports with new default layout. 

OBIEE11g: Purging Cache Using the Agents in Obiee11g

Purge Cache Using Agents in Obiee11g:

Create a new database by doing a right click on the empty space in the physical layer as shown in the below screenshot

Now you can see a window with the database properties as shown in the below screenshot

 Give a name to the database and select ODBC Basic from the Database drop down.Check in the option Allow direct database requests by default. Please follow the below screenshot.

 Click on Connection Pools tab and then click on Add(+) to create a connection pool for the database

Give the name for the connection pool(In my case i have given the name as PurgeCache) and set the values as per your requirement.

Now click on the Permissions tab then you can see a window as follows

 

Give the access for the users and application roles as per the requirement as shown in the below screenshot 

Click on OK then click on Check Out

Give the User Name and password in the connection pool, Once you enter the password then it will ask you to retype the password. Please follow the below screenshots.

Now you can see the connection pool is successfully created

Check the RPD consistency and then save the repository

Deploy the new  repository in the BI Server (Enterprise Manager) and then restart the services.

Now login to the presentation services and create a new report by using Create Direct Database Request as shown in the below screenshot.

Give the Connection Pool Name and type the following query in the query box

Call SAPurgeAllCache();

  Now Click on Results tab  and then save the report

 

 

We need to create an agent to scheduled this report now. So go and create one new agent to schedule this report.

 

OBIEE 11g: Error: '[Nqserror: 43126] Authentication Failed: Invalid User/Password' when Logging into Answers

To Bottom

---

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Information in this document applies to any platform.

Symptoms

The BISystemUser in OBIEE 11g (the Administrator User set up during the installation) is unable to log into Answers. But you can log in to Enterprise Manager (EM) and WebLogic Server (WLS) Administration Console without problem as this user.
The BI Server generates the following error:

[nQSError: 43126] Authentication failed: invalid user/password

The Managed Server generates the following error:

<Nov 11, 2010 12:54:49 AM PHT> <Notice> <Security> <server1.companyname.com> <bi_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1289408089797> <BEA-090078>

ver1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1289408089797> <BEA-090078> <User bisystemuser in security realm myrealm has had 5 invalid login attempts, locking account for 30 minutes.>

Cause

The BISystemUser account has become locked.

Solution

You have to create a new user with the same privileges and permissions:

1. Log in to the WebLogic Server Administration Console.
2. Create a new user and assign it to the Administrators Group.
3. Log in to Enterprise Manager to Add this new user to 'BISystem' Role.
4. Navigate to 'Weblogic Domain > bifoundation_domain'
5. Right click on bifoundation_domain.
6. From the resulting menu, click on Security/Credentials menu item
7. In the Credentials Screen expand oracle.bi.system.
8. Choose 'system.user' and click 'Edit'
9. In the resulting pop-up window, enter the new user name and password
10. Re-start the OBIEE components, Managed Server and Admin Server.
11. After ten minutes log in to Answers as the new user.