Smooth(er) OBIEE Administration

I am one of those people who likes to know how things work. I'm constantly digging through the Oracle guides for OBIEE and trawling the net to find those nuggets of information that will make my life easier.

I have a confession to make at this point. I will go out of my way to avoid doing repetitive (boring) work. Like most people I would rather do  something new and exiting rather than push the same buttons in the same sequence day after day.

OBIEE administration is one of those jobs that could suck up hours and hours of your day unless you think smart. Unfortunately, most environments expect you to 'get stuck in' and then before you know it ......... the same thing day-after-day. Every OBIEE site has common issues that they struggle with; migration across environments, performance, testing, development, lineage.....

Part 1. OBIEE Documentation: 

Not the most glamerous of suggestions surely, but your boss and sysadmin will love you. Start by documenting where to find stuff middleware home, logfiles, config files (instanceconfig.xml etc.), the URLs used for administration.

Then move on to documenting the config changes made after the install, what settings were changed and if known what the default values were before and what they are now. What are the connection details for your RCU schemas (BIPLATFORM and MDS). Capture things like server names and directory structures.

Now comes the living documents which you can generate periodically and version. Run off a repository report and catalog reports. This will give you (and your colleagues) a better understanding of what the repository looks like and also what in the catalog is where on the dashboards.

As an added benefit you can now work backwards from the catalog reports through the repository reports to see where data comes from to fill reports, and vice-versa, from a physical column work your way through the repository to the catalog report to see where the data is used.

Remember, having no documentation means a risk to your business. View sample documentation here.

Part 2.  OBIEE Automation:

You don't already? You will need to think of all those tasks you do and tackle them one-by-one. It may take you some time to get to a state where executing a single script will do an entire day's work, but you can certainly make a start by taking simple startup and shutdown tasks and automating them. Join them together shutdown then startup, there you go you have a script to 'bounce' the environment. How much time did that save?

You will need to have scripts to help with testing connection pools and migrating RPDs and catalogs. You can generate scripts that 'auto-correct', they start up an environment if it is down or restart components of OPMN. How about migrating users and groups across environments.

When you create a script you put all the effort in up front and reap the benefits in the days that follow.

Part 3. OBIEE Procedures:

There is nothing wrong with doing all your tasks in an ad-hoc manner for a Proof of Concept Project. The nature of the POC is to rapidly change things to reflect the change in your customer's requirements.

But what ad-hoc work does is distract you and eats up your time. Before you know it, you've spent an entire day ad-hoccing (is that a word?) and have nothing to show for it.

If you have a thread of automation or a deployment script for promotion across environments then with a little planning and communication you can ease the workload.

So what do you need to know to deploy? What are you deploying, from where to where and what are the configuration details and security settings?

This doesn't have to become something heavy and cumbersome with reams of documentation, a couple of lines and provides project history and documentation as a byproduct.

Each project will have it's own lifecycle and demands but setting up a process and schedule for releases, do you absolutely have to release the latest developer build to test immediately or can it wait until an agreed time where several pieces of work can be batched and planned in. Immediately the level of distraction for you has dropped.

In conclusion, as you start to fill in some of the parts of administration, you start a feedback cycle that will over time reduce your workload, increase your inderstanding of OBIEE and make you more productive

Generating a OBIEE Performance Test Harness

You know you should be testing you OBIEE stack for performance, but what exactly should you be doing, and how can you do it?

Another question may be what is your baseline? Do you have a known baseline for performance? What tools do you use?

What we want is a method of generating a test plan where we can run (repeatably) the reports on our dashboards. Ideally we would like to generate a series of tests that we can run before a release to generate a baseline and after a release to ensure that nothing (that we are testing) is broken.

So what is available to us "out of the box" that we can plug in and start using straight away?

We will use a couple of components to build up a test.

• Usage Tracking
• NQCMD

Usage tracking: 

Turn on usage tracking for one of your users and open a few dashboards and reports. If you have things configured correctly you should see some entries in the S_NQ_ACCT table (I have made the assumption that you will enable database recording of usage tracking - see how to set this up here.)

The S_NQ_ACCT table tells us what components were run when you viewed the dashboard. To find out what components make up usage tracking query the "QUERY_SRC_CD" column. A sample of results may show something like:
DashboardPrompt
Report
ValuePrompt
Memeber Browser Display Values
Soap
rawSQL

The logical query run for each component is in the "QUERY_TEXT" column, though this truncates the full query after a number of characters and, if like me you have some very large logical queries, it may be worthwhile getting the query out of the "QUERY_BLOB" column.

NQCMD:

I have already discussed NQCMD here but it may be worthwhile going over some of the concepts briefly. NQCMD is a mechanism for executing a logical query against a repository for a user. This means we can execute a logical query against a repository of our choice and run the query as a named user.

The command is in the format
MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -d coreapplication_OH140000583 -u <username> -p <password> -s <Query input file> -T -q  1> output.out

Running the help gives the following output
MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -help

-------------------------------------------------------------------------------
          Oracle BI ODBC Client
          Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------


Argument error near: -help
Command: MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe - a command line client which can issue SQL statements
                 against either Oracle BI server or a variety of ODBC compliant backend databases.
SYNOPSIS
         nqcmd [OPTION]...
DESCRIPTION
         -d<data source name>
         -u<user name>
         -p<password>
         -s<sql input file name>
         -o<output result file name>
         -D<Delimiter>
         -C<# number of fetched rows by column-wise binding>
         -R<# number of fetched rows by row-wise binding>
         -a (a flag to enable async processing)
         -f (a flag to enable to flush output file for each write)
         -H (a flag to enable to open/close a request handle for each query)
         -z (a flag to enable UTF8 instead of ACP)
         -utf16 (a flag to enable UTF16 instead of ACP)
         -q (a flag to turn off row output)
         -NoFetch (a flag to disable data fetch with query execution)
         -NotForwardCursor (a flag to disable forwardonly cursor)
         -v (a flag to display the version)
         -SessionVar <SessionVarName>=<SessionVarVAlue>

To use NQCMD we need to pass a logical query SQL in a file.
The logical query file can look something like

SET VARIABLE PRODUCT_NAME='T00103', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q0213EB1783381CBEC92AB1166D5FF8FC', TEST=1:SELECT .................. FETCH FIRST 25001 ROWS ONLY

Now we have looked at the two parts we'll use let's put it all together.

To start we need to mine the usage tracking table for the logical queries of the reports on our dashboards that we wish to test.
Each query should be in its own file.
Ideally each query should be uniquely identifiable, so we can trace a query back to a relevant report or dashboard and also as a means of identification when running the test.

Generating the test logical query input files: I have knocked up a SQL script that extracts the queries from the Usage Tracking Table and saves them in a series of files. Because PL/SQL cannot spool to multiple files dynamically, the following script generates an intermediate file, which is then called and executed.

SET TERMOUT OFF
SET SERVEROUTPUT ON
SET ECHO OFF
SET DEFINE OFF
SET FEEDBACK OFF
SET LINESIZE 5000
SPOOL intermediate_file.sql

DECLARE
  CURSOR cr_qrys  IS
      SELECT DISTINCT TO_CHAR (query_blob) AS query_text, query_key
        FROM s_nq_acct
       WHERE     query_src_cd = 'Report'
             AND dbms_lob.getlength (query_blob) <= 3900
             AND ERROR_TEXT IS NULL
             AND row_count > 0
    ORDER BY query_key;

  i          NUMBER := 0;
  filename   VARCHAR2 (5);
  p1         VARCHAR2 (5000);
BEGIN
  dbms_output.enable (NULL);

  FOR lr_qrys IN cr_qrys LOOP
    i          := i + 1;
    filename   := LPAD (TO_CHAR (i), 5, '0');
    p1         :=
         'SET VARIABLE PRODUCT_NAME=''T'
      || filename
      || ''', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME=''Q'
      || lr_qrys.query_key
      || ''', TEST=1:'
      || lr_qrys.query_text;
    dbms_output.put_line ('SET COLSEP ,  
SET PAGESIZE 0
SET TRIMSPOOL ON 
SET HEADSEP OFF
SET FEEDBACK OFF
SET DEFINE OFF
SET ECHO OFF
SET TERMOUT OFF
SET LINESIZE 5000
SPOOL test_file_' || filename || '.inp
SELECT q''[' || p1 || ']''
FROM DUAL;
SPOOL OFF');
  END LOOP;
END;
/

SPOOL OFF
PROMPT     executing intermediate file
@intermediate_file.sql;
SET SERVEROUTPUT OFF

This generates a series of files with the following structure test_file_xxxxx.inp where the xxxxx matches the test PRODUCT_NAME variable of the query.

This is our the first version of our baseline series of tests. As new dashboards and reports are released you will need to update the test input files with the new report queries and potentially remove those reports that are no longer relevant.

The next step is to pass each file through the NQCMD in turn and capture the output. Assuming the logical query test input files are in a subdirectory called testcases (and using DOS) we can loop through the .inp files as follows

for /r %%i in (testcases\*) do (
<mw_home>\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -d <dsn to repository> -u <username> -p <password> -s %%i -T -q >%%~ni.out
)

Once the above loop has finished we will have a series of files called test_file_xxxxx.out

Peering into an output file you will see they have a structure as detailed below


-------------------------------------------------------------------------------
          Oracle BI ODBC Client
          Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------


SET VARIABLE PRODUCT_NAME='T03818', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q4DFD2F426D93BC2DA37036577533BF8B', TEST=1:SELECT ........... ASC NULLS LAST, 3 ASC NULLS LAST FETCH FIRST 200001 ROWS ONLY

SET VARIABLE PRODUCT_NAME='T03818', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q4DFD2F426D93BC2DA37036577533BF8B', TEST=1:SELECT .............................

--------------------------------------------------------------------------------------------------------------------------------------------------------
s_0          s_1                                                                                                  s_2          3 s_4                    
--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
Clock time: batch start: 10:34:21.000 Query from: 10:34:22.000 to: 10:35:22.000 Row count: 15
 total: 59.920 prepare: 0.016 execute: 59.889 fetch: 0.015
Cumulative time(seconds): Batch elapsed: 60 Query total: 59.920 prepare: 0.016, execute: 59.889, fetch: 0.015, query count:  1, cumulative rows:  15

--------------------------------------------------------------------------------------------------------------------------------------------------------


Processed: 1 queries

You can see from the contents we have a wealth of information and using a couple of scripts you can extract the values for PRODUCT_NAME and the timings near the end of each file. When you process them together you should have something like

Date Test No Batch Start Query From Query To Row Count Total Time Prepare Time Execute Time Fetch Time Batch Elapsed Prepare Query Total Fetch Query Count Cumulative rows
21/01/2014 'T00005' 15:24:08 15:24:11 15:24:17 2 5.906 0.016 5.859 0.031 8 0.016 5.859 0.031 1 2
21/01/2014 'T00007' 15:24:21 15:24:22 15:24:23 9 0.593 0 0.578 0.015 1 0 0.578 0.015 1 9
21/01/2014 'T00008' 15:24:24 15:24:25 15:24:38 35 12.671 0.031 12.624 0.016 13 0.031 12.624 0.016 1 35

Which you can load into your favourite database and perhaps even build a dashboard on.

OBIEE BUNDLE PATCH 11.1.1.7.140114

Patch 17886497: BI BUNDLE PATCH 11.1.1.7.140114

The latest patchset for OBIEE 11.1.1.7 has just come out.

The Oracle BI EE Suite Bundle Patch 11.1.1.7.140114 under the top-level patch 17886497 consists of the following component patches:

Patch	Abstract
16913445	Oracle Business Intelligence Installer (BIINST)
17922352	Oracle Business Intelligence Publisher (BIP)
17300417	Enterprise Performance Management Components Installed from BI Installer 11.1.1.7.0 (BIFNDNEPM)
17922552	Oracle Business Intelligence Server (BISERVER)
17922596	Oracle Business Intelligence Presentation Services (BIPS)
17300045	Oracle Real-Time Decisions (RTD)
16997936	Oracle Business Intelligence ADF Components (BIADFCOMPS)
17922577	Oracle Business Intelligence Platform Client Installers and MapViewer

This weighs in at a hefty 2.5Gb and includes 8 patches

Don't forget to clear cache and read the readme documentation.

OBIEE BI Mobile App Designer Installation

Christmas is out of the way, New Years resolutions made (and some broken - sigh).

I promised myself last year to take a look at the OBIEE Mobile App Designer module as soon as time permitted, and to cut a long story short, I made time.

What are the prerequisites for installation.

You must be running OBIEE 11.1.1.7.1, if you are not then you will need to patch up to this version:
1. If you are on versions below 11.1.1.6.* or less then patch to 11.1.1.7.0 first
2. Apply the patches 16556157 which is a collection of patches which you apply one after the other and the a required patch 16569379.
3. you are now in a position to apply patch 17220944 which is the BI Mobile App.

The first patch on to my 11.1.1.7.0 system was a hefty 2.5Gb download on Windows 64 and contained 8 patches and when we include the required patch managed to eat up a good couple of hours of my day to install.

Make sure you follow the readme documents, there's nothing surprising there, but pay attention and you should be fine.

The final step is to install the BI Mobile App (patch 17220944).

1. Stop the OBIEE components OPMN, Managed Server and then Admin Server.
2. Apply the patch using opatch apply (see your documentation for environment variable settings).

The following steps are to deploy and configure the Application and the surrounding security.

You will need to run the WebLogic Congiguration Assistant to deploy the OBI Mobile App Designer.

Start--> Oracle Weblogic--> Weblogic Server 11gR1--> Tools--> Configuration Wizard.
1. Select extend an existing domain.
2. Select a WebLogic Domain Directory (bifoundation_domain).
3. Select Extension Source (select Extend My domain using a template and select <mw_home>/Oracle_BI1/common/templates/applications/oracle.bimad_template_11.1.1.jar)
4. The next few screens do not apply and you can click through using Next to the Configuration Summary screen where you click Extend.
5. You are finished with the configuration Assistant and can close the wizard.

Now you will upgrade the security configuration.

1. start only the WebLogic Administration server, Not Managed server or OPNM. (<mw_home>\user_projects\domains\bifoundation_domain\bin\startweblogic.cmd)
2. Run the python WLST script to integrate with Oracle Fusion Middleware
cd <mw_home>/Oracle_BI1/install
now run
<mw_home>/Oracle_BI1/common/bin/wlst.cmd addMADCodeGrants.py t3://<yourservername>:7001 weblogic

Enable the Mobile App Designer from Presentation Services.

1. Edit the instanceconfig.xml file in (<mw_home>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1)
2. Add the <EnableMAD> element above the closing </ServerInstance> tag as follows
  <ServerInstance>
    .....
     <AdvancedReporting>
         <EnableMAD>true</EnableMAD>
    </AdvancedReporting>
  </ServerInstance>

Start the Managed Server and OPMN.

Set up the local App Library 

1. Create a folder in the Catalog to be the local Apps Library. Under Shared Folders create a folder called named "Apps Library".
2. Open the xmlp-server-config.xml file. It is located under <DOMAIN_HOME>/config/bipublisher/repository/Admin/Configuration.
3. Add the property "APPS_LIBRARY_FOLDER_LOCAL"and set the value to be the path to the folder you created in Step 1. For example: <property name="APPS_LIBRARY_FOLDER_LOCAL" value="/Apps Library"/>
4. Restart the bimad (BI Mobile App Designer) application.

You should now see the Mobile Application on the "Home" page and from the "New" dropdown menu. Recent changes to apps appear in the Others section under Recent Dashboards.

Let me know if you have any problems.

OBIEE - Javascript and CSS locations

In OBIEE 11g, the path for Javascript and Custom Style Sheet is located at-

OBIEE\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\

And for using them-
<script type="text/javascript" src="res/test.js"></script>
<link href="res/test.css" type="text/css" rel="stylesheet">

Refererence- https://forums.oracle.com/forums/thread.jspa?threadID=2453825&tstart=0