Starting and stopping servers from command prompt - OBIEE 11g

When the Enterprise Manager fails to come up then command prompt is the only way of stopping and starting the servers.
Go to folder e:\Middleware\instances\PBIinstance\bin and run the opmnctl.bat, opmnctl.bat status shows the status of the servers, opmnctl.bat startall will start up the servers.

Just opmnctl.bat will give the syntax of what we could do with the bat file.

In the init block if execution precedence is not set we get the error "Start row is set higher than the row count". Below is screenshot.

The error wasn't explanatory and a beginner on 11g, it took a while for us to understand what it meant.

Synching the GUIDs - 11g

I had Presentation Administrator privileges on 11g. But suddenly I lost the privilages. In EM the log said “The GUID for user has changed. This could result in catalog access issues…………” .

As I googled I realized that users are identified by GUIDs and not names. So when migration occurs from one environment to another then GUIDs change so the users lose their privileges.

To fix this issue, in NQSconfig, we need to set the parameter FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES and restart the servers. After setting we need to reset the parameter to NO and restart again.

Importing tables in 11g

In windows server where OBIEE 11g was installed, no oracle client was installed. I was unable to import tables because DSN to oracle Database cannot created.
To overcome this problem we have to create a tnsnames.ora file in the  directory E:\OBIEE11g\Oracle_BI1\network\admin. Tnsnames.ora file will have the TNS entries to the Database.

E is drive on which I have oracle BI installed and OBIEE11g is where I have installed OBIEE 11g or the middleware home.

Getting a non-measure column into the measures section in Pivot table

1. Add the non-numeric column "Status" into the rows section. This column can be a column from Dimension.
2. In the properties of "Status", click on duplicate layer. Same column will get created.
3. Drag the new column and drop it into measures section.
4. Hide the "Status" column in measure section.

Suppress the OC4J window that appears upon login

It is pretty annoying , to see OC4J window launch everytime I login to my machine.
Finally I found out a way to get rid of it.

Start:
1. Launch regedit
2. Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
3. Remove the OC4J entry

Pagination for Pivot table

OBIEE doesn't give pagination for pivot table by default. There is a simple work around for that, using rcount.

I implemented it in two ways.

Creating on UI:
1. Create a dummy column called "Page". Add the formula truncate ((rcount(1)-1)/5,0)+1. 5 is the number of rows that has to be displayed.
2. In the pivot table, add the dummy "Page" column to the pages section of the pivot table.
3. When the report is run, the page dropdown appears at the top of the table.


Creating in RPD:
1. The report would have atleast one lakh of records to display. So I gave the user the option of choosing the number of rows.
2. Created a logical column "rsum" in the fact. The formula used was rcount(1).
3. Created a prompt with rsum and used the "Between" operator. So the rsum would show values from 1 to maximum rows.

4. Created a column in the report which is max(.rsum)
5. In the report rsum had the default value of 21 and made rsum prompted in the report.

6. When the report would run, only 21 rows would be displayed. The user could choose the range of rows needed and check the report.