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.

Creating a "Clear my selection"

Most often the users wouldn't want to use the "Clear My Selection" option under the Page-Options, but they would want it right above the prompts. To implement that we need to write a small piece of HTML code.

Steps:
1. Create a text object in the dashboard. In that paste the content as in the image below.

2. "Clear choices" will work just like the "Clear My Selection". Place the text object right above the prompt.

"New calculated Item" in Pivot Table

I came across this intresting option sometime back.

I have five sales stage 1 to 5. But I needed to display in the same row / column the sum of stages 1 and 2. To achieve it I used the calculated item option which is provided in the
pivot table.

Steps:
1. Click on any row / column for which you need a new calculated item.

2. In the window that appears, give a name as it must appear in the UI. Create the forumla.I have given sum of two sales stages.

3. The result will be as shown below. We have a new value which has sum of the two sales stages.


But, we need to remember that the calculated items appear in the same order as they were created.

Pagination for Pivot table

There is no pagination for pivot table. One way of achieving this is below.
1. In report add a column and change the formula to TRUNCATE ((RCOUNT(1)-1)/100, 0). 2. Where 100 is the number of rows that should be displayed in the pivot table.
3. Change the column heading to "Page Number"
4. Rcount is equivalent to rownum in oracle.
5. Place this column in the Pages section of the Pivot table.

Usage tracking on OBIEE

Usage tracking on OBIEE can be enabled to write on to files or to to make direct inserts into the DB.

To enable usage tracking on DB

1. Run the script {ORacleBI}\server\Schema\SAACCT.Oracle.sql
or
Run the script below :
create table S_NQ_ACCT
(
USER_NAME VARCHAR2(128),
REPOSITORY_NAME VARCHAR2(128),
SUBJECT_AREA_NAME VARCHAR2(128),
NODE_ID VARCHAR2(15),
START_TS DATE,
START_DT DATE,
START_HOUR_MIN CHAR(5),
END_TS DATE,
END_DT DATE,
END_HOUR_MIN CHAR(5),
QUERY_TEXT VARCHAR2(1024),
SUCCESS_FLG NUMBER(10,0),
ROW_COUNT NUMBER(10,0),
TOTAL_TIME_SEC NUMBER(10,0),
COMPILE_TIME_SEC NUMBER(10,0),
NUM_DB_QUERY NUMBER(10,0),
CUM_DB_TIME_SEC NUMBER(10,0),
CUM_NUM_DB_ROW NUMBER(10,0),
CACHE_IND_FLG CHAR(1) default 'N' not null,
QUERY_SRC_CD VARCHAR2(30) default '',
SAW_SRC_PATH VARCHAR2(250) default '',
SAW_DASHBOARD VARCHAR2(150) default '',
SAW_DASHBOARD_PG VARCHAR2(150) default '',
PRESENTATION_NAME VARCHAR2(128) default '',
ERROR_TEXT VARCHAR2(250) default '',
RUNAS_USER_NAME VARCHAR2(128) default '',
NUM_CACHE_INSERTED NUMBER(10,0) default null,
NUM_CACHE_HITS NUMBER(10,0) default null
);

create index S_NQ_ACCT_M1 on S_NQ_ACCT
(START_DT, START_HOUR_MIN, USER_NAME);

create index S_NQ_ACCT_M2 on S_NQ_ACCT
(START_HOUR_MIN, USER_NAME);

create index S_NQ_ACCT_M3 on S_NQ_ACCT
(USER_NAME);

2. Ensure there is write permission on this table.

3. Import the Table S_NQ_ACCT into the repository as below:

4. Change the NQSConfig.ini.
Set Enable = YES

[ USAGE_TRACKING ]

ENABLE = YES;
DIRECT_INSERT = YES;
PHYSICAL_TABLE_NAME = "Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";
CONNECTION_POOL = "Usage Tracking"."Usage_tracking";
BUFFER_SIZE = 10 MB ;
BUFFER_TIME_LIMIT_SECONDS = 5 ;
NUM_INSERT_THREADS = 5 ;
MAX_INSERTS_PER_TRANSACTION = 1 ;

PHYSICAL_TABLE_NAME must be "Database"."Catalog"."Schema"."Table" or "Database"."Schema"."Table"
CONNECTION_POOL must be "Database"."Connection Pool"

5. Restart the servers.

Repository Variables in Dashboard Prompts

We needed to set the default value of the fields in dashboard prompts with Repository variable. The syntax mentioned in the book shelf didn't work.

Finally what worked is in the image below:

Performance Tuning in OBIEE

Performance has always been an issue in all the OBIEE implementations I have seen till date. Below are the learnings from my experience.

1. Keep the number of init blocks / session variables to the minimum.

2. During development the loglevel for a user can be at any number, but once in production make the loglevel to 0. Logging hurts the query response time.

3. In connection Pool we have to take care to set the Maximum connections to the right value.
Maximum connections to the dashboards = 10% of simultaneous users X number of requests on dashboard. But total connections have to be less than 800.
For the connection pool dedicated to an initialization block it should be the number of concurrent users.

4. Specify Driving table: Driving table can be specified when creating logical table joins. Making the table with less number of records as the driving table will lead to query optimization.

5. VIRTUAL_TABLE_PAGE_SIZE: This is a parameter in NQSConfig.ini file. Operations like Sort, Join, unions and fetch require lot of memory. OBIEE users virtual table mechanism to manage this. When data exceeds the VIRTUAL_TABLE_PAGE_SIZE, then remaining data is placed in temp files, and brought to the virtual table as processing continues. When this paramter value is increase it will reduce the number of I/O operations. Default value is 128 Kb.

6. NULL_VALUES_SORT_FIRST: This is a parameter in NQSConfig.ini file. This specifies if NULL values are sorted before other values (ON) or after (OFF). The value of of this parameter should conform to the underlying database. Default value is NULL_VALUES_SORT_FIRST = OFF. If there are multiple underlying databases that sort NULL values differently, set the value to correspond to the database that is used the most in queries.

7.I used the CURSOR_SHARING parameter. CURSOR_SHARING is an init.ora parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.
In the init blocks, as the second init block I added another init block.
Added the statement ALTER SESSION SET CURSOR_SHARING = SIMILAR and initialized a dummy variable.
CURSOR_SHARING=SIMILAR significantly reduces the number of plans in shared pool, and improves performance.

This will be learning for ever, so will keep updating this article when ever I learn something new.

Showing default dashboard

We had two roles for the users of application - "Manager" and "Reportee". We needed to land them at different dashboards - "Manager" and "My Allocation" dashboard. To achieve this we used the variable PORTALPATH.

In the Initblock we set the variable PORTALPATH with a query like
Select '\shared\pfm\_portal\Manager’ from TABLE_ROLE where role = 'Manager'
Union
Select '\shared\pfm\_portal\My Allocation' from TABLE_ROLE where role = 'Reportee'

When a user logs in based on the role the default dashboard will be shown.

This PORTALPATH setting will not work if the user goes to My Account and sets the Default Dashboard. In such cases the value the user sets overrides the PORTALPATH value.

Changing the size multiselect text box

We had multiselect where the users would pick the week which is in the format FY2009-Q3-MAR-W3. The multiselect are size of 10. So in the textbox complete string couldn't be seen, as in image.

The user wanted to see the complete string of one selected string. OBIEE didn't have any options of increasing the width. As a round about this is what I did.
1. I copied the promptviewtemplates.xml from {OracleBI}\web\msgdb\messages to {OracleBIData}\web\msgdb\customMessages folder.
2. I searched for the below tag in the above copied the xml file.

3. Changed the size value to size="16".
4. Restarted the presentation services.
The sizes of the prompts were changed as below.

Alter the GrandTotal caption

Though this seems silly, but we did loose 10 minutes of our time trying to figure out where it is done.

1. Go to edit mode of the pivot table.
2. In the properties of rows click on "Format Labels"

3. In the heading put in whatever label is needed.

Customizing the Download Links

We have a report where we set the values of the Session Variables using presentation variables in the Prefix section of the report.
And we use presentation variables in the columns of our report.
All the download options work except "Download to Data".

We get the error:
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <@>: Syntax error [nQSError: 26012] . (HY000)
SQL Issued: Set Variable Position_Name='@{PositionChange}', Position_Level = @{LevelChange}; SELECT INDEXCOL( 0, ISP."Position"."Territory Name", ...............
We decided to hide the “Download to Data” link.

By default for every OBIEE report will have Download to Excel / PowerPoint / Excel 2000 / Data and MHTML.

To achieve our requirement:

1. We got the viewscontrolmessages.xml file from {OracleBI}\web\msgdb\messages folder.
2. copied it to {OracleBIData}\web\msgdb\customMessages
3. Found the below tags under idDownloadLinksMenu@{viewID}

4. Remove the approriate line from <a> to </a>.
To remove the Download to Data remove the 4th line. So code looks like:

5. Restart the saw

Disabling "Create Bookmark Link"

In OBIEE "Create Bookmark Link" is enabled by default.In order to disable this, we need to make the below entry in instance config.xml file.

<Dashboard>
<EnableBookmarkURL>false</EnableBookmarkURL>
</Dashboard>