Regression Testing in OBIEE 12c

Regression Testing in OBIEE 12c

Regression Testing depends from two reason

• which level  did you change RPD ?
• did you change data in DM?

Use Usage Tracking.

For easy generating Logical SQL  and Physical we use Usage Tracking. After that you can check you queries and compare dataset. It's easy way, but not complex.  For full algorithm of regression testing
go to Full algorithm   article.

Switch on Usage Tracking.

First of all switch on Usage Tracking. Add tables from DEV_BIPLATFORM to Physical level in OBIEE. Rename Connection Pool and Database like a picture below.

Change NQSConfig.INI to switch on Usage Tracking

cd /oracle/product/fmw1221/user_projects/domains/bi/config/fmwconfig/biconfig/OBIS

vim NQSConfig.INI

Check that Usage Tracking is on.

[USAGE_TRACKING]

ENABLE = YES;

DIRECT_INSERT = YES;

Uncomment and add  

PHYSICAL_TABLE_NAME = "Usage Tracking Database"."BI12DEV_BIPLATFORM"."S_NQ_ACCT";  # Or "<Database>"."<Schema>"."<Table>" ; - This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control

CONNECTION_POOL = "Usage Tracking Database"."Usage Tracking Pool";  # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control

INIT_BLOCK_TABLE_NAME  = "Usage Tracking Database"."BI12DEV_BIPLATFORM"."S_NQ_INITBLOCK";  # Or "<Database>"."<Schema>"."<Table>" ;

INIT_BLOCK_CONNECTION_POOL   = "Usage Tracking Database"."Usage Tracking Pool";

Restart OBIEE.

Check that Usage Tracking works now. Login to OBIEE, go to a dashboard and after that look at db tables.

select s1.SAW_SRC_PATH, s1.SAW_DASHBOARD , s1.QUERY_TEXT ,  s1.QUERY_BLOB, s2.QUERY_TEXT, s2.QUERY_BLOB from S_NQ_ACCT s1, s_nq_db_acct s2 where s1.id=s2.LOGICAL_QUERY_ID and s1.SUBJECT_AREA_NAME='';

Full algorithm 

Compare Logical SQL and Physical Dataset.  

We use 2 tool for it

• runcat.sh - Presentation Services Catalog Manager tool
• nqcmd.sh - ODBC Client tool

1. Generate Before Logical SQL by runcat.sh from a report.
2. Generate Before DataSet by nqcmd from Usage Tracking.
3. Update OBIEE repository.
4. Generate After Logical SQL by runcat.sh from a report. 
5. Compare After Logical SQL and Before Logical SQL by diff command.
6. Generate After DataSet by  nqcmd from Usage Tracking.
7. Compare After DataSet and Before DataSet.

Go to BI Tool file, in this server folder we have runcat.sh and nqcmd.sh

cd /oracle/product/fmw1221/user_projects/domains/bi/bitools/bin

ls -al

Logical SQL

Generate Logical SQL by runcat.

 ./runcat.sh -cmd report -online http://146.240.224.194:9502/analytics/saw.dll -credentials cred.txt -forceOutputFile /home/oracle/Downloads/RecNatVY.lsql -folder "/shared/HRBI/Recruitment/Analysis/NationalVisionYear"  -type "Analysis" -fields "SQL"

For help use a command
./runcat.sh -cmd report -online -help

Attributes credentials - store login/password in a file
login=weblogic
pwd=password

DataSet

Generate DataSet by nqcmd
./nqcmd.sh -d AnalyticsWeb -u weblogic -p password -s /home/oracle/Downloads/RecNatY.lsql -o /home/oracle/Downloads/RecNatY.out

• -d - BI Server DNS, for server - AnalyticsWeb, for local - DNS System Name

Compare file

diff ReCIDIVY.lsql ReCIDIVY_1.lsql