Changes required to export all rows to Excel

1)Following changes are done in instanceconfig.xml file between <View> tages

<View>
<Pivot>
<MaxCells>1920000</MaxCells>
<MaxVisibleColumns>30</MaxVisibleColumns>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>25</DefaultRowsDisplayed>
</Pivot>
<Table>
<MaxCells>1920000</MaxCells>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>25</DefaultRowsDisplayed>
</Table>
<Charts>
<JavaHostReadLimitInKB>6144</JavaHostReadLimitInKB>
</Charts>
</View>

2)Login to EM

Business Intelligence->coreapplication->Capacity Management->Performace
change Maximum Number of Rows to Download to 64000.

Apply,Activate changes and restart all services.

Event Polling Table to Purge the cache in OBIEE

An event polling table is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.
Generaly we will purge the cache in two ways
Manage – Cache
From dashboard also we can purge the cache
Settings – Issue SQL — Call Sapurgeallcache()
Instead doing like this on regular interval with the help of Event table we can purge the cache.
Procedure :
Create table S_NQ_EPT
(
UPDATE_TYPE INTEGER DEFAULT 1 NOT NULL,
UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL,
DB_NAME VARCHAR2(40),
CATALOG_NAME VARCHAR2(40),
SCHEMA_NAME VARCHAR2(40),
TABLE_NAME VARCHAR2(40) NOT NULL,
OTHER VARCHAR2(80) DEFAULT NULL
)
Once the table is created we have to import the table into Physical layer.
Goto Tools — Utilities

Click OracleBI Event Tables and Execute

Will find the list of tables.Select the S_NQ_EPT as a Event table and also set the Polling frequency Time.

Once we selected the event table we can able to see that in the physical layer as below

Now create a sample report based on Dept Dimension to create the cache

Check the Cache Folder

Once this is done we need to insert data into the S_NQ_EPT table

After the Polling frequency time we mentioned just check the Cache folder it will be Purged

How to change the RPD and Webcatalog in OBIEE 11G

How to change the RPD and Webcatalog in OBIEE 11G.In OBIEE 10G we will directly change in the instanceconfig.xml and NQSconfig.INI but in OBIEE 11G we have to this in EM

http://localhost:7001/em/

1. Login in to http://localhost:7001/em/
2. Expand ‘Business Intelligence’ node on the left and choose Coreapplication.

3. Now click on the CoreApplication.
4. Now click on the Deployment tab on the right. Next, go to the Repository tab.

5. Click on ‘Lock and Edit Configuration’ to enable you to change the default settings.
6. In the ‘Upload BI Server Repository’ section, click on the Browse button and locate your new RPD file
7. Enter RPD password
8. Keep on the same configuration screen to update webcat selection
9. You need to update the path at the bottom of the same EM screen, with the location looking like this.

Reuse Static Content in BIPublisher

If we want to reuse a particular content in BIPublisher we can use the predefined syntax For example if want to print some text at each page after the table like

So at each page it will be printed like the header and footer.

Change banner in OBIEE11g

In order to have banner of your own,change bg_banner at these location -
Drive:\Oracle\Middleware\Oracle_BI1\bifoundation\web\app\res\s_blafp

Drive:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\.appmergegen_1291264099332\analytics.ear\ukjjdc\res\s_blafp

Drive:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp

Under each s_blafp you can find b_mozilla_4 folder.Change bg_banner image.

Stop BI Services before doing changes.
Dont forget to clear browser cache.

Calculations on Time in OBIEE

Very good blog on Time Calculations.This helped me alot.
Refer : http://www.obieetalk.com/obiee-date-expressions-reference

Deploying OBIEE on Apache Tomcat Server

In order to deploy OBIEE on Apache Tomcat Server,we need to deploying two war files analytics.war (BI Answers, Dashboards, and BI Delivers) and xmlpserver.war (Bi Publisher) to make Apache Tomcat as Server.

Deploying analytics.war file -

Step1: Stop your Tomcat Server (if it’s already in started mode)

Step2: Take a copy of analytics.war file from following location
“Drive: \OracleBI\oc4j_bi\j2ee\home\applications\analytics”

Step3: Create a folder with name analytics under “C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps” path. Paste analytics.war file into this newly created folder.

Step4: Open your command prompt. Go to your newly created folder structure.
Enter the following command: Jar –xvf analytics.war

Next, Start your Tomcat Server (Start->Run->Services.msc)
Start Menu->All Programs->Apache Tomcat 5.5->Welcome
Click on Tomcat Manger .It may ask username and password you need to give right credentials (if u specify at installation of apache)

Under the Application’s you will see “/analytics”
Click on analytics it will take you to our OBIEE Login screen

Deploying xmlpserver.war file –

Step1: Stop your Tomcat Server (if it’s already in started mode)

Step2: Take a copy of xmlpserver.war file from following location
“Drive: \OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver”

Step3: Create a folder with name analytics under “C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps” path. Paste xmlpserver.war file into this newly created folder.

Step4: Open your command prompt. Go to your newly created folder structure
Enter the following command: Jar –xvf xmlpserver.war

Step5: Now change {file path} in xmlp-server-config.xml file located at
“C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\xmlpserver\WEB-INF”
Point the path to OBIEE installed xmlp folder, here it is {file path=”C:\OracleBI\xmlp\XMLP”}

Next, Start your Tomcat Server (Start->Run->Services.msc)
Start Menu->All Programs->Apache Tomcat 5.5->Welcome
Click on Tomcat Manger .It may ask username and password you need to give right credentials (if u specify at installation of apache)

Under the Application’s you will see “/xmlpserver”
Click on xmlpserver it will point to Bi Publisher Login screen

URLs -
Answer: http://localhost:8080/analytics
Bi Publisher: http://localhost:8080/xmlpserver