Logical Table Source(LTS) Override – A multiple LTS logical fact with Count and Count Distinct Measures

One of my colleague has encountered a strange problem which I even have not noticed ever because the problem is bit ridiculous for me . The problem is about applying count distinct and count aggregation rule while building a measure . The measure will be mapped to multiple logical fact table source .

The problem in nutshell is, while applying the Count on column NUM_OF_CONTACT it is doing SUM(NUM_OF_CONTACT) instead of COUNT (NUM_OF_CONTACT) whereas using Count Distinct on the same column will be driving the Distinct count on Lowest level of LTS all time(i.e query hitting single/one LTS)  irrespective of the dimension and dimensional content specification .

Initially it is bit strange for me . After investigating across metalink I got couple of such issue encountered by users . Some of them are : Doc ID : 510223.1 SR 38-1054325181, Doc ID 526854.1 SR 38-1505875337 . But it does not give me the proper info about the workaround or solution.

Re-engineering on it reveals that there are some interesting and unknown feature left behind by Oracle/Siebel to overcome such problems where measures need to be totally driven by the content specification of fact table LTS. Its nothing but “Logical Table Source Override” . I proof this I replicate the problem in my env and done a POC and outcome/workaround of it as below :

1 . Create the below tables and insert the handcrafted data .

LTS Override1







LTS Override2






2. Configure the repository as below :

  LTS Override3




3. The Logical Fact table source content has been mapped as applicable . The 2 LTS content mapping as below . Similarly other LTS has been mapped .

LTS Override4






4. Now see how the Count distinct has been defined .

  LTS Override5 





However the definition prove to be wrong and erroneous while you drag the measure with the dimensional column .Here I have drag LVL2 and as per the content mapping the query should be routed via “DIM_POSITION_FLM” . But strange enough whatever column you choose from dimension table the query always routing via “DIM_POSITION_KAM” .Have a look on below :

LTS Override7







5 . Definitely the above result is wrong . A correct approach is to redefine the measure by LTS overriding as below :

LTS Override8







6. And finally see the correct result and the query log .

LTS Override9






7. Whereas a totally different kind of behavior has been observed while you are dragging “NUM_OF_CONTACT_Count” with measure . A simple count aggregation has been defined against it . The result would be below :

LTS Override10







So this create another major problem as any dimension you choose always SUM aggregation will be applied whereas as per Repository definition it should be Count .

8. A correct approach/workaround of the problem is to redefine the measure . However for Count aggregation you haven’t any choice of LTS overriding . Here you have to go by Based On Dimensions . Lets see this and the issue has been resolved !

LTS Override11





9. So apparently it seems everything is working fine with some tweaking as LTS override and based on Dimensions funda . But wait there are some more strange thing awaiting  and I am really bit astonished how this would happened ! If any body could explain me the reason I will be grateful .

I have deleted the measure “NUM_OF_CONTACT” and “NUM_OF_CONTACT_Sum” from logical fact table as it is of no use for me for this POC . However after removing this I observed only the measure “NUM_OF_CONTACT_Count_Distinct_Override” is functional and rest of them is not behaving what it should be , as explained above .

Dragging “LVL2” and any of the measure (apart from “NUM_OF_CONTACT_Count_Distinct_Override”) leads to inconsistent query firing in DB :

select distinct T41.LVL2 as c1,
     T38.NUM_OF_CONTACT as c2
order by c1

And more astonishing fact is that for all 3 measures the query is same !!! How come this possible ? I dont know if I am missing something .But this is quite strange how removing an independent measure impact the rest of the measures query ! Still can’t find any justification ……

OBIEE Delivers-Schedulers-iBot Setup and Configuration

This necessarily need to be gone through couple of below steps :

Create Oracle BI Scheduler Tables

 Execute the ‘SAJOBS.Oracle.sql’ file located on ‘..\OracleBI\server\Schema’ to the schema where you like your Scheduler table has to be . The scripts will be different for the different database. Mentioned one is for Oracle database.In my case the script has been deployed under a schema called ‘S_NQ_SCHED’ .

Configure BI Scheduler Schema in Job Manger

 Go to Manage -) Jobs -) Job Manger -) Configuration Options and put the below information :

Delivers via iBot Delivers via iBot2

Will talk about The ‘SchedulerAdmin’ later.

Delivers via iBot3   Delivers via iBot4

You can change any of the information at above by directly editing ‘instanceconfig.xml’ file located at ‘..\OracleBIData\scheduler\config’  .


Configuring Oracle BI Scheduler Administrator

As part of the process of setting Oracle BI Scheduler configuration options, you will need to specify a username and password for the Scheduler Administrator. The Scheduler administrator must be a user in the Oracle BI repository (.rpd file) and have the Administrator group membership assigned.If you do not want to create a new administrator for Scheduler, you can set the Scheduler Administrator credentials to those of the Oracle BI user, Administrator that exists in the repository.

NOTE: The Scheduler Administrator is referred to in the following topics as the SchedulerAdmin.

Delivers via iBot5


Configuring Oracle BI Presentation Services

 Open the instanceconfig.xml file located under ‘..\OracleBIData\web\config’  .Between the tags, locate the tag pair and . (If they do not exist, create them.) Between the Alerts tags, create the tag pair and .

Delivers via iBot5-1

If the Scheduler port has been changed from the default of 9705, specify the Scheduler port number.

For example:


. . .




Configure BI Presentation Services Credential Store

Oracle BI Presentation Services must be able to identify the Scheduler administrator and obtain the credentials to establish a connection with the Scheduler. Presentation Services stores the credentials that it uses in a Presentation Services Credential Store. The Scheduler administrator credentials must be added to the credential store, under the alias admin. To obtain the Scheduler Administrator credentials, Oracle BI Presentation Services searches the credential store for a username-password credential with the alias admin.

Use the following procedure to add the Scheduler administrator credentials to the Presentation Services Credential Store with the admin alias. This procedure adds the Scheduler administrator credentials to a proprietary XML file credential store called credentialstore.xml. The default location of this file is ‘..\OracleBIData\web\config’  on Windows .

Delivers via iBot6

Execute the CryptoTools utility to add the Scheduler Administrator credentials to the Presentation Services Credential Store:

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml

>Credential Alias: admin

>Username: SchedulerAdmin

>Password: SchedulerAdmin

>Do you want to encrypt the password? y/n (y):

>Passphrase for encryption: secret

>Do you want to write the passphrase to the xml? y/n (n):

>File “OracleBIData_HOME/web/config/credentialstore.xml” exists. Do you want to

overwrite it? y/n (y):

The CryptoTools utility updates the credentialstore.xml file .

Configuring Oracle BI Presentation Services to Identify the Credential Store

 Oracle BI Presentation Services must be directed to the credential store that contains the Scheduler administrator credentials. This is done by setting parameters in the Oracle BI Presentation Services configuration file, instanceconfig.xml. In addition, if you have not stored the passphrase in the credential store, then the passphrase to decrypt the password credential must also be specified. In the above example, the passphrase was not stored in the credential store and needs to be specified in the instanceconfig.xml file.

Modification, the instanceconfig.xml contains entries as shown in the following example:

<?xml version=”1.0″?>



<!– other settings … –>


<CredentialStorage path=””OracleBIData_HOME/web/config/credentialstore.xml” passphrase=”secret”/>

<!– other settings … –>


<!– other settings … –>



Delivers via iBot6-1

Create iBot in BI Presentation Interface

Set profile details for the user account .In my case Presentation admin will invoke iBot :

Delivers via iBot7

Add Devices to Email and put email address where you want to receive the deliverable content .

Delivers via iBot8

Change the permission for Delivers from ‘Manage Privileges’ as below:

Delivers via iBot9

Click on Delivers and Create new iBot .Set Schedule to immediate .

Delivers via iBot10

Delivers via iBot11

Set recipients as Me and additionally you can choose from user groups listed here .Select the deliverable content to Dashboard or Report .Select Destination to ‘Email’ .

Delivers via iBot12Delivers via iBot13Delivers via iBot14

Now once you save the iBot the process will be triggered immediately and you can detail log from ‘..\OracleBI\server\Log\iBots’. It would be like this :The strike thorough portion followed by “smtp/” would be replaced by the Email ID that you have configured for delivers .

Delivers via iBot15  Delivers via iBot16

Delivers via iBot17

************* Modified on 01/22/2013 **************

Couple of NOTES to make sure your scheduler and email delivery works perfect:

1) You need to configure scheduler table otherwise you can’t make the scheduler service up and running

2) Make sure SASystemSubject Area is not enabled otherwise in some configuration you will see delivers not working properly . There was a issue with Timezone settings and hence SASystemSubjectArea required to be disabled (if not critical) .Otherwise you need to fix the Timezone issue to make sure none of the value become NULL .




In some of the Oracle documents I have found below:

<SubjectAreaMetadata> <SystemSubjectArea>false</SystemSubjectArea> </SubjectAreaMetadata>

But the previous one works great for 10g .
3) Make sure you configure the delivery profile from My Accounts . Otherwise triggering iBot will neither show any “Alerts” link on Dashboard  nor email delivery can be done.

4) For successful email delivery make sure your SMTP relay server in Job Manager is good and reachable and necessary ports are open

5) CredentialsStore configuration will necessary otherwise you will see a lot of NQiBot.xxxxxx.err file under   ..\OracleBI\server\Log\iBots

6) A successful iBot execution generates no err log file . Any iBot issue can be tracked down from this err file or NQScheduler.log file(from server diagnostic perspective)

7) If you need only “Alerts” link displayed under dashboard beside “Answers” no SMTP relay setup required but make sure iBot destination select as Active Profile.

Migration from Siebel Analytics to OBIEE

The below text outlines the self-defined process and approach why and how we need to migrate Siebel Analytics to OBIEE . There might be some gaps in the entire definition of the process as the entire view is of mine and I did not face any typical issue while doing the migration , though there are couple of known bugs flying around in OTN discussion forum and in Metalink3 (Oracle Support) . So definitely there must be something !

If there are any specific points(pros/cons) somebody could like to add I will be happy to make the alteration !!!


Oracle Business Intelligence (BI) is a portfolio of technology and applications that provides the industry’s first integrated, end-to-end Enterprise Performance Management System, including category-leading performance management applications, MIS applications, BI applications, BI foundation and tools, and data warehousing. Oracle Business Intelligence Enterprise Edition consists of components that were formerly available from Siebel Systems as Siebel Business Analytics Platform, with a number of significant enhancements.


 As the  “Oracle First” procurement strategy has been defined by some big companies after a close tie up with Oracle hence it force them to decide,move and built the reporting platform on newly release BI component by Oracle called Oracle Business Intelligence(OBI) .This require migration of existing reporting platform from Siebel Analytics to OBI enterprise edition .

Why to Migrate

There are ample reasons to leverage the benefits of new OBIEE framework after doing migration of the existing platform. Some of the benefits have been described below:

User Interface Integration

 1)    OBIEE is an emerging, popular, enhanced BI system to provide the intelligent interface for both front and back office reporting

2)    Oracle BI Enterprise Edition is improved to support the business and technology requirements of application integration, not only from a user and data perspective, but also in the light of business process integration: moving from reactive looking-back-to-what-happened reporting to a pro-active, guiding, alerting intelligent business requirement

3)    Technically speaking lots of new feature with the enhancement of existing functionalities have been introduced for better reporting

4)     For the benefit of end use business communities lots of flexibility that can be the reason to adopt this

5)    Because of the 100% web-based and standards based architecture (SOAP) of the OBIEE platform it is very easy to integrate the OBI Presentation Services with a web based application like Siebel CRM or EBS .

Security Integration

 1)    Besides the critical requirement of single sign on and authentication, an important and time saving characteristic of a BI Application is the integration with the base application security and visibility rules. Typically large organizations have the requirement to secure company information and transactional data based on not only the user’s role or responsibility (Sales Rep or Sales Manager) in the organization but also on his or her position (CEO vs. CFO ). Based on the user’s responsibility, he should have access to certain analytical content, i.e. dashboards, reports, alerts, subscriptions etc.; based on the user’s position; he should have access to certain areas of the database, e.g. the data belonging to division A or B, team 1 or 2

2)    Oracle BI EE fully supports this role and position based security rules using the technique of row-wise initialized session variables and initialization blocks. Upon login and after authentication, queries are being executed against the database in a certain order to retrieve the security information which is stored in session variables like GROUP or POSITION. These variables are then used to control access to objects in the OBIEE repository or web catalog but also to build business model filters to restrict access to critical records. This integration becomes critical in a volatile organization where user change role and/or position frequently

Data Integration

       The pre-built Business Analytics Warehouse (BAW) is one single but modular data warehouse model to support one or a combination of the source systems mentioned. The BAW is compliant with the Ralph Kimball dimensional modelling methodology and supports slowly changing dimensions, aggregate tables, hierarchy tables and many others.

        The pre-built OBIEE repository and web catalog are aligned with the Business Analytics Warehouse and contain the domain specific and end user facing dashboards, reports and KPI’s , metrics, drill down paths, guided navigation, alerts, action links, traffic light alerts, etc .

Supporting Technology

 Without going into detail the most important characteristics of the Oracle BI EE Platform to support successful BI Applications are:

 1)    The caching technology which enables a significant performance improvement for standard dashboards and reports

2)    The open repository ODBC API which enables other enterprise ODBC compliant tools to access the OBIEE repository presentation layer and therefore the pre-built KPI’s, dimensional attributes, preserving the security business rules

3)    The ability to join multiple different data sources and data source types in the OBIEE repository

4)    The multilingual capabilities of the OBIEE platform that gives the ability to use it across wide enterprise

5)    Ability to use multiple RPD hosted on a single server and distributed across multiple presentation services in parallel which offload a large subject area


Scope of this document is to orchestrate for key steps while migrating existing Siebel Analytics platform to OBI .However this is very High level plan and lots of dependency and hidden low level strategy need to gradually define. This document is not intended to define the low level features, enhancement, flexibility that has been introduced in latest OBIEE version

Step by Step Activities

                     There are couple of activities that need to taken care of considering the roadmap of migration from existing Siebel Analytics Platform to Oracle BI EE platform.


1) Acquiring the latest OBIEE licensed version for client from Oracle . Confirm the version to be downloaded from eDelivery metalink provided URL
2) Removing existing Siebel Analytics installation across all development and test servers and carry out the fresh installation of OBIEE
3) Do the same installation at Performance test environment to check the Single-Sign on features
4) Finally install that into production servers by Oracle Architect and configure the related key business components

RPD Changes

1) Take the latest production copy of RPD at Dev Environment and do the version up- gradation.
2) Do the sanity and consistencies check across all Business model and rectify any repository consistency error, metadata warning etc.
3) Migrate it across test environment and Single Sign-On Performance test environment.

Webcat Changes

1) Take the latest webcat copy for the respective RPD at Dev Environment and do the version up-gradation.
2) Do a sanity check and sort out all the invalid objects and there references, broken links etc after running sawmigrate utility .This need to be taken care of recursively and in reactive approach.
3) Go through all the reports, prompts and links to see everything working properly.
4) Revisit Subject Area, Dashboard, Page, Shared folder, Objects level permissions.
5) Housekeeping for invalid, redundant dashboards/reports/objects.
6) Change the ‘Help Information’ HTML paths aligning with new OBIEE installed folder structures.
7) Migrate it across test environment and Single Sign-On Performance test environment.

Environment Configurations

1) Provides a choice to deploy the Presentation Services and Presentation Services Plug-in either standalone Oracle Containers for J2EE (OC4J) or in Microsoft IIS. If J2EE will be decided to be the container then IIS need to be removed from all environments and OC4J will be act as HTTP Web server to communicate with Oracle BI Web Client .However existing IIS can act HTTP Web server but this is subject to POC to remove the whole set prior to install the OBIEE copy afresh.
2) Couple of benefits are there using J2EE as Web Container rather IIS which is out of scope of the documentation.
3)  Configure the HTTP Webserver using the existing dev/test environment URL and the respective port. Configuring this into existing URL reusing the port is subject to POC and might be altered.
4) Configure the same into Production like SSO enabled environment e.g. Performance test environment.
5) Check the SSO functionality for authentication and authorisation. Also verify the same for any data and object level of security.
6) Deploy the RPD-Webcat at Production environment having basic functionality in place.
7) Any URL Port change for J2EE HTTP Server configuration need to reflect to Order Gateway URL, BI Arena URL and need communicate across users.
8) Alternatively IIS Web server can be redirected to new HTTP Server configured URL which is of no impact to anywhere. This is subject to POC again.
9) Revisit production server hardware configurations to see the capabilities.

Additional Required Configurations

1) Change the Skin file path default configuration parameters as per the new windows folder structure of OBIEE deployment.
2) Move the customized CSS, JavaScript’s, images into the Skin directory path.
3) Make the respective changes on OBIEE Analytics and Presentation Server configuration files.
4) Take the Oracle best practice approach to define the performance parameters as per the current Production hardware environment setup and Production DB Configuration parameters.
5) Configure the clustered environment setup for BI Server at production for replication.
6) Define the strategy for failover/ load balance presentation services.

Additional Optional Configurations

  Below additional components is not available in default setup and can be installed and configured depending on the requirement to leverage the other business benefits and experiencing the new version features at maximum.

1) Install & Configure OBIEE Scheduler’s for Delivers .Acquire the relay service and configure the details
2) Install & Configure OBIEE Publisher and the BI Publisher desktop plug-in
3) Install & Configure OBIEE Disconnected client
4) Install & Configure OBIEE Open Intelligence ODBC interface
5) Install & Configure OBIEE Briefing book reader
6) Configure the Caching Strategy.

Dynamic switch DB or Schema on OBIEE User Login

We have a requirement to switch between the DB’s based on the user login. We have two identical/similar databases with different connection string and different DB user/Schema name and password corresponding to that schema . But based on the users who logged in the report, the connection should switch. Also one more objective we need to fulfill here is that the password of the database schema that will be stored should be encrypted so that nobody can tamper this and obviously due to reason of security violation and it should be decrypted by the OBIEE before connect to that database schema .

To cater this reqr. I have followed the below steps :

1) Create a table “DB_DETAILS” in any db with the following details:

Column Name Data Type 
DBNAME  VARCHAR2 (30 Byte)  

2) Here I use a package called : ENCRYPTION to encrypt and decrypt the password in DB_DETAILS table


  FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2;



   — Declare the values for the key.
   — The package will be wrapped to hide the encryption key.
   key_string          VARCHAR2(1000)  := ‘12345678’;
   encrypted_string    VARCHAR2(2048);
   decrypted_string    VARCHAR2(2048);

   — FUNCTION ENCRYPT encrypts encrypted string
      FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS

      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESEncrypt(input_string => input_string,
                                            key_string => key_string,
                                             encrypted_string => encrypted_string );
       RETURN  encrypted_string;

   — FUNCTION DECRYPT decrypts encrypted string
      FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS

      decrypted_string    := null;
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESDecrypt( input_string    => input_string,
                                            key_string       =>key_string,
                                            decrypted_string => decrypted_string );

      RETURN  decrypted_string;


2) Add the ‘DB_DETAILS’ table against ‘Proxy_Conn’ .

User Switch on DB1




3) Add the relevant data against ‘DB_DETAILS’ table .

User Switch on DB2




4) Make sure you have two database configured properly with above credentials .Update the DBPASSWORD_ENCRYPT field by running . Note that the DBPASSWORD column would have no use for this POC . This is for reference only .


5) Create the below Session Var Initialization Block . Here I am decrypting the password and assign it to a variable called ‘dbpassword’ . User Switch on DB3






6) Now for the connection pool for which we need dynamic switching as per the logged in users, set the below properties in connection pool of that database in physical layer:
Data Source Name: VALUEOF(NQ_SESSION.dbname)
User Name: VALUEOF(NQ_SESSION.dbuser)

User Switch on DB5 






7) Create the Subject Area ‘EMP_DEPT_DB’ depending on the database ‘ORBIT’, Connection pool ‘Conn_Pool’ . Assign the Physical join , Logical join properly in the conventional method.

8) Now create two users in repository as ‘deba’ and ‘rosy’ . According to that setup when you entered thorough ‘rosy ‘ you should be able to see data for ‘EMP’ and ‘DEPT’ table from database : ‘ORBIT’ while user ‘deba’ will be able to see the data from database ‘OREIPR’.Note that in both cases only one Subject area has been used but the data differs depending on logged in Users from different databases.

User Switch on DB6

User Switch on DB7

Using Repository Session and Presentation Variable in Title/Subtitle/Narratives

Sometime Business demands to show the Repository Session and Presentation variable in Title , Subtitle , Narrative etc . Now let see the way to achive this :

Using Presentation Variable in Title,Subtitle,Narratives

Defined a variable called ‘var_ename’ as Presentation Variable.

Variable in Title1

Define the title with ‘var_ename’ and use it as below : @{var_ename} .Also same can be defined in Subtitle and Narrative .

Variable in Title2

The default looks of the report will be below where Ename has been “Is prompted”  in report criteria.

Variable in Title3

After choosing the prompt Title, Subtitle and Narrative would be changing automatically :

Variable in Title4

Using Repository Session Variable in Title,Subtitle,Narratives

Customer requirement was to show dynamically the Currency type of the country in report title to show the Sales currency of the individual country in multi national deployment in the same report .

To achieve this I have written a Session variable initialisation block called “Country_Int” and return the currency value for the user from LOV type and make a Non-System session variable target called “CURRENCY” .

Now in my Sales Report it has been picked up by Presentation services after defining the below syntax under Subtitle :

The currency displayed is @{biServer.variables[‘NQ_SESSION.CURRENCY’]}.

Variable in Title5

Similarly variable could be used in Narratives as RPD dynamic variable  ‘var_country’ added under Prefix section of the BI Narrative view .  

Variable in Narrative

Automating OBIEE Startup and Shutdown Service

As we all know that there are couple of steps that we need to adhere before starup OBIEE services and also same for shutdown . How if it will be automated through two scripts only …i.e one for Startup and other for Shutdown OBIEE service . This utility necessarily free us from juggling around windows services .

Lets find how the tid-bit applicable for IIS Web server first . I have two batch file in desktop that do the stuff for me one is : OBIEE Start-IIS.bat having below script behind it .

echo “Starting BI ……”
net start IISADMIN
net start W3SVC
net start “Oracle BI Server”
net start “Oracle BI Java Host”
net start “Oracle BI Presentation Server”
start iexplore “http://localhost/analytics/saw.dll?Dashboard&NQUser=Administrator&NQPassword=Administrator

Another one for stopping BI Services i.e OBIEE Stop-IIS.bat having underline script .

echo “Stopping BI ……”
net stop “Oracle BI Presentation Server”
net stop “Oracle BI Java Host”
net stop “Oracle BI Server”
net stop IISADMIN /Y

N.B : In above scripts while starting the BI service it will kickoff IIS and its dependent process first and finally the command window unloaded after invoking iExplore process .Now while stopping the BI service it will forcefully stop IIS and its dependent process like WWW Publishing etc .

Lets find the solution for the same in OC4J Web controller .

OBIEE Start-OC4J.bat

cd D:\OracleBI\oc4j_bi\j2ee\home
echo “Starting BI ……”
start D:\OracleBI\oc4j_bi\bin\oc4j.cmd -start
sleep 10
net start “Oracle BI Server”
net start “Oracle BI Java Host”
net start “Oracle BI Presentation Server”
start iexplore “http://localhost/analytics/saw.dll?Dashboard&NQUser=Administrator&NQPassword=Administrator

OBIEE Stop-OC4J.bat

echo “Stopping BI ……”
net stop “Oracle BI Presentation Server”
net stop “Oracle BI Java Host”
net stop “Oracle BI Server”
cd D:\OracleBI\oc4j_bi\j2ee\home
java -jar admin.jar ormi://  oc4jadmin oc4jadmin -shutdown force
taskkill /IM cmd.exe

Only thing you need to know is that for OC4J you need to change the j2ee home directory as per your enviornment settings .

So finally this make life bit easy for me as you are also far away from 2 simple click on two readymade batch file 😀