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.