Friday, November 23, 2012

[OBIEE 11g] Usage Tracking


Setting Up Usage Tracking in OBIEE
This Document will used to set usage tracking and create usage reports to monitor queries

Step1: Can use available User  in  SQL schema otherwise create new one with all permissions.


Step 2: Create the tracking table.The Tracking table Script can be found in
Run the script S_NQ_ACCT which is in SAACCT.Oracle.sql from the  user

Step 3: Make it available for the public

Step 4: Setting up the additional tables
You can find the scripts in D:\Obiee\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

Run the following scripts;
Oracle_create_nQ_Calendar.sql (To create table)
Oracle_create_nQ_Clock.sql (To create table)
Oracle_nQ_Calendar.sql (To Insert data into table)
Oracle_nQ_Clock.sql (To Insert data into table)

Step 5: 
Make it available for the public

Step 6: 
Create an extra view
CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login, user_name AS resp FROM s_nq_acct;

Step 7: 
Make it available for the public
GRANT SELECT ON  nq_login_group TO PUBLIC;

Step 8:
Merge the usage tracking RPD into your master_Rep RPD.

Open your master RPD offline.

then Save as "master_temp.rpd"

Click yes for Global Consistency

From the file menu select merge

Then  select

Original Repository: master_Rep.rpd

Modified Repository: usagetracking_11115_BI0002 

along with the Repository Passwords

Note:  Can get the correct usagetracking RPD and catalog from the following

then click on Next

Then click Next

Choose Modified option from Decision 

Then Click on Finish option

Click No option for Global Consistency then Merged Rpd will be 

Step 9: Upload this new merged RPD and Catalog in the Enterprise Manager (EM)

Step 10: OBIEE 11g Usage Tracking Server Configuration
a) Open Middleware\instances\instance1\config\OracleBIServerComponent\

coreapplication_obis1\NQSConfig.ini in Notepad and edit.

b) Next, set the usage tracking options. Scroll to the Usage Tracking section

PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";
CONNECTION_POOL = "OBI Usage Tracking"."Usage Tracking Writer Connection Pool" ;
Note: PHYSICAL_TABLE_NAME = “<Database>”.”<Schema>”.”<Table>”                 

          CONNECTION_POOL =“<Database>”.”<Connection Pool>”

f) Save and close the NQSConfig.ini configuration file (Run the services if required using Opmnctl)
g)  Copy the Usage Tracking Presentation Catalog into the existing Presentation Catalog and Extract it

Merge  Master Catalog and Usagetracking Catalog using Catalog Manager 
1.  Go to Oracle Business Intelligence > Catalog Manager to lunch Catalog

2.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.

3.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select Archive 

     4.  A popup window appears. Click on “Browse” and then provide name you want to save this archive and then click on “open”. Once it is done name appears as shown and then click on “Ok”.


    5.  Once it is successful click on “Ok” and a file will be created in the folder shown.

OBIEE HOME >> instances >> instance4 >> bifoundation >> OracleBIPresentationServicesComponent >> coreapplication_obips1 >> catalogmanager >> mycatalog_archive 
Archived file is shown below

     Un-Archive – Catalog

    1.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.
2.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select unarchive

3.  Click on Browse and select the file which you want to un-archive and then finally click on OK. Once done successfully click OK.

4.  Refresh the Analytics browser
Login into User Interface(UI) and the check that the Usage Tracking Dashboard is added

Monday, November 19, 2012

[OBIEE 11g] Summary Of Enhancements and Features

OBIEE 11g-Summary of Enhancements and Features
The OBIEE - a recent release of Oracle BI Foundation delivers a Complete BI Platform specially designed to leverage 

·         Exalytics Related Features
Oracle Exalytics hardware’s large memory, processors, concurrency, and other hardware features and system configurations.
Oracle Exalytics is a complete solution that includes hardware, an enterprise BI Platform and in-memory analytics software optimized solution
Business Intelligence Foundation Suits supports following Exalytics features 

Summary Advisor 
 – Oracle Business Intelligence Administrator Feature which monitors Business Intelligence usage patterns analyzes them and recommends the optimal data mart for best overall performance improvement.

Aggregate Persistence for Times Ten In-Memory Databases for Exalytics 
 – Aggregate Persistence (“CREATE AGGREGATES” and “DELETE AGGREGATES” commands) enables automated creation of data marts via BI Server commands. This feature was already in place for relational database, now it is extended for Times Ten In Memory database. Still it is not being supported for Essbase or Other MDX databases.

Exalytics Optimization by Enabling Oracle Hardware Acceleration Flags


OBIEE Server
 – Exalytics specific optimization enabled by Oracle Hardware acceleration flags to match system architecture resulting in higher query throughput, lower latencies in query processing along with enhanced memory usage for cache.

OBIEE Presentation Server 

- Optimized to use large footprint memory to speed up page rendering and caching

Oracle Essbase 
Memory usage optimizations for loading entire data into memory, concurrency improvements to match Exalytics architecture enabling efficient distribution of processing.

Note: - These features are specific to Exalytics and do not work in absence of Exalytics hardware.

 Times Ten for Exalytics Related Features

Enhancements to Times Ten for Exalytics for analytical processing at in-memory speeds, the new features are listed as follows 
o    OLAP Grouping Operators: CUBE, ROLLUP & GROUPING SETS
o    Columnar Compression
o    WITH Clause
o    Support to Analytic Functions: RANK, DENSE_RANK, SUM, AVG, ORDER BY NULLS FIRST| LAST
  Essbase Enhancement for Exalytics 

o    Performance Optimization
o    Higher level of parallelism across all processes
o    MDX query performance
o    Scalability
o    Support for larger than 4GB cache size( 64-bit) 
 Dashboard and Answers Enhancements 

Dynamic user interface enhancements that complement large amounts of data to present business information in meaningful and compelling ways
Optimization of Dashboard and ad hoc capabilities for Exalytics
New Dashboard and Answers Features to make it easier for end users to analyze data and enable insights at the speed of light (thought?)

“Go-less” Prompts 

This enables the rapid feedback and responsive user interaction and also provides performant cascading updates of dependent prompts. 

This feature is optional and can be enable/disable at different level
·        “Apply/Reset” button at page, dashboard and prompt level by report developer/analyst
·         Enable/Disable via instanceconfig entity by Administrator
·         Can set always ON

Partial Page Refresh 

This re-loads only views listening to prompt rather than refreshing everything on page/dashboard. It enables highly peformant updates along with modern application feel.

Master Detail Linking 

The master detail linking is now scalable and available for more data points from 10’s values in earlier version to 100’s values in the latest version. The data range can be specified in instanceconfig file.

Auto-complete Prompts 

Auto-complete from dashboard prompts and prompt search dialog, this is similar to recommendation on web. It is useful for retrieving desired values from huge prompt pick lists. 

This is available for choice list with typing enabled and it allows alpha-numeric search only.  This option can be enabled or disabled by Administrator in instanceconfig file. 

 Administrator can specify case sensitive/insensitive, matching level (e.g. StartsWith, WordStarts and Matchall) and result limit. 

If BI Machine Flag=True then Auto-complete prompt is also set to True.

Contextual/Right click interactions 

The contextual/right click interactions deliver ad hoc capabilities to all end users. It provides number of options on the right click for selections, totals, including/excluding columns, top-N queries, calculated items etc. Each of right click option has ability to be turned on/off per analysis. This can be done by using ‘Analysis Properties – Interaction’ option on the request.


 Selection Steps Enhancements 

The selection steps enhancements brings new multidimensional functionality which is common across most of the multidimensional tools e.g. Essbase. 


The front end provides the option to mark frequently used objects as favorites for easy retrieval. The end user got an option by which favorites can be specified and managed by end users for an optimized BI experience.

Scorecard Views and BI Mobile Enhancements 

BI Mobile certified against BI EE
Scorecard and Strategy Management views now render in true-fidelity mode
Scorecard views as HTML5 Canvas touch-based visualizations

Source Control Management Integration 

The BI Server Administration Tool offers integration option with the third party or Oracle ADE Source Control Management system for RPD development.

A new option is provided to save repository as a set of MDS XML files

Each repository objects like connection pool, physical table, logical table etc are stored in its own XML file

New “Source Control” option under Tools >> Options to edit SCM Configuration Editor 

BI Publisher Enhancements 

BI Publisher performance, lifecycle, workflow and report creation enhancements
·         Interactive View Performance Enhancements
·         Favorites
·         Edit Scheduled Job – Option to mange report jobs
·         Schedule Triggers – Conditional execution of scheduled jobs
·         Enhanced Parameters and List of Values – menu parameters displayed as check box or radio buttons
·         Create Report Workflow
·         PDF/A (ISO-standard for the digital preservation of electronic documents) and PDF/X (ISO- standard for digital printing and graphic arts) Output
·         Preserve HTML formatting in data
·         Security Enhancements – Different Server for authentication and authorization

BI Search

·         Full text search available from UI
·         Search criteria/context passed to report when drilling 

Client Installation Enhancements

·         Simplified access / download option is available from Home Page
·         Support both 32/64 bit
·         Extensible and Secured
·         The client software includes Admin tool, Catalog Manager and Job Manager

Security – Diagnostics Enhancements 

·         Diagnostic web application / Logging Enhancements/ Security Troubleshooting Guide and Documentation Enhancements

Security – Additional Authorization Options

·         SQL Group Lookup Authenticator – which allows user authentication against LDAP to be combined with authorization from a database

Certification and System Management Enhancements

·         New Certification Added
·         Number  of system management enhancements 

OBIEE Screen snapshot

The screen snapshot for a dashboard page from is depicted below.