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>