OBIEE - Testing the Repository Using NQCMD

You have finished making your changes to the repository and the consistency check reports no errors or warnings. Great, it's all working as planned. Unfortunately all that the consistency checker does, is make sure that logically the repository hangs together, not that your business logic is correct. A consistency check means that most common problems with the construction of the physical layer and the facts and dimensions.

Now to test that the outputs are as expected. (see also Generating an OBIEE performance Test Harness)

NQCMD

We can automate this using nqcmd and sample reports.

Before starting NQCMD run bi-init (found in ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup) to give a properly configured command line environment to run the OBI commands.

A look at the help for the command reveals a load of options:

C:\obiee11g\Oracle_BI1\bifoundation\server\bin>nqcmd -?

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


Argument error near: -?
Command: nqcmd - 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 in the output result file
         -utf16 (a flag to enable UTF16 for communicating to Oracle BI ODBC driver)
         -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)
         -ONFormat<FormatString, i.e. TM9, 0D99>

Before we go further go to answers and build a test query against the part of the repository you wish to test, in the advanced tab copy out the query and save this to a file. This will be the SQL file used in the test.

Run the following, your connection data source name will be different, as hopefully will your username and password.

nqcmd -d coreapplication_OH206444865 -u weblogic -p weblogic -s C:\Test\test.sql -o C:\Test\test.txtv -D; -utf16

Note that the -D is the delimiter between SQL statements in the input file if more than one select statement is in there.

Your output file should contain the query and end something like
....
....
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 250001 ROWS ONLY

-------------------------
s_0          s_1         
-------------------------
0            00/000008/  
-------------------------
Row count: 1
-------------------------


Processed: 1 queries

If you set up a DSN to the other server you can run the same command line and only change the contents of the -d flag, then compare the results.

Always useful if you want to test that nothing is broken prior to releasing a change.

Update :
See here for an worked example of how to automate all this in Generating an OBIEE Performance Test Harness