Creating single virtual domain for Siebel and OBIEE

If Siebel CRM and Oracle BI EE are running on two different Servers (which should be the case for most implementations), then the embedded Oracle BI screens may not function properly for all actions such as, opening the properties for List Formats, requiring a second click for editing within the formula editor of the segment designer due some issues with cross-site scripting. This behavior is also documented within the following SR: avoid UI navigation issues associated to Siebel Marketing – OpenUI integrated to OBI (2008422.1) and within the Official documentation of the Installation guide:

Since these various issue are caused by some cross-site scripting, the workaround is to pretend to the client that Siebel and Oracle BI are running on the same host, hence creating a single virtual domain for Siebel and OBIEE.

This can be achieved by configuring a proxy reverse setting on the HTTP Web Server where Siebel web extensions are running. This proxy reverse configuration will exchange the hostname of OBIEE e.g. with the hostname of the Siebel host e.g.

Within the support note of the SR (2008422.1) OHS is used for this proxy reverse configuration. The following diagram shows the updated request flow for the single virtual domain:

A short step-by-step guide for the Workaround using OHS can be found here.

understanding calculation for Days of Absence

To Analyse the Days of Absence for Workers, the Absence information is extracted and loaded into a dedicated Star (W_ABSENCE_EVENT_F) of the BI Applications data model. A simple Report on the days of Absence and absence occurrences looks like the following from the Report Builder (Answers):


This can also be restricted to a individual worker and a specific time period by applying additional filters in the Report Builder:


This Report then return the following filtered results:


This is based on the Fact for Absence:


The Absence Days are calculated based on the DAYS_DURATION column for the selected period from 2017/07/06 until 2017/07/17. Thus, each duration will be counted for the selected period regardless of the start or end date of a an Absence event (First column). If the worker was not scheduled to work of a certain days such as 7.7. and 8.7. then these days will not be counted as days of Absence. The Absence occurrences are calculated on the ABS_OCCRNC_DAY column.

Determining the version of BI Applications 7.9.6.x

Oracle BI Applications provides a pre-built Analytical content (KPIs) and Data Adapter for Oracle’s Applications like Siebel, PeopleSoft, eBS etc.
This Analytical content is installed into the metadata repositories of Oracle BI EE as .rpd and webcat file and as Informatica Repository for Informatica PowerCenter ETL for the Data Adapter. Since, it’s the pre-built content in the form of Repositories, the Version is not mentioned explicitly in any place of the respective Repositories.
Only the installer will place a text file containing the Version and installed modules e.g. Sales Analytics.

Depending on the BI Application Version, the file can be found at:

Oracle BI Applications performance recommendations & Project example

Oracle offers a technical whitepaper to guide customers in planning their technical hardware and infrastructure to deploy the Oracle BI Applications suite. This Whitepaper is available via Oracle Support and from the following link:
For Hardware requirements the Guide recommends the following specification depending on Project size for the Database & Oracle BI Server:

For our current Siebel Marketing and Marketing Analytics Project we have the following

data volume (Volumetric)

To facilitate this data volume and workload, the Project uses the following Hardware specification:

What is Income|Outcome?

Income|Outcome is a business simulation delivered as part of an in-class training series to teach the basics of business using a round based board game. The teams need to create certain products which are traded with the other teams at the end of each round. Having to high prices might lead to no sales whereas to low prices might secure sales but prohibit the ability to investment into efficiency measure. Teams might also offer deferred payments at a higher prices but these are then only due some rounds later.

class layout

The aim is to illustrate the coherence of business and the impact of business decisions. While there is no prescriptive winning strategy, it highlights the importance for understanding key (financial) indicators such as Cash flow, Receivables, Payables, Profit & Loss or Return on Assets.

VF 1 KPI Return on sale

Oracle also sees this importance’s for their Business Applications which hold all the transactional data and summarizes it as follows:

« Business Intelligence provides the methods to use the information that you have gathered in your E-Business/Siebel/Peoplesoft operational system to drive better business outcomes by executing your business processes more effectively. »

Most of the key (financial) indicators mentioned within the game are pre-calculated for the Oracle Applications with Oracle BI Applications, such as Financial Analytics.


The Standard Day Dimension of BI Applications

The Oracle Business Analytics Warehouse provides a standard Date Dimension, which provides all days (much like a calendar) e.g. from 1.1.1980 to 31.12.2030. The Day is the lowest level of granularity for this Standard Dimension. Hence, the naming “Day Dimension”.

Standard Day Dimension

It contains many additional attributes about the Calendar Day e.g. how the Calendar Day correlates to the Financial Calendar or if a Day is the last day of a Quarter, often useful for calculations based on the Date.

This Dimension is used by all the Fact’s of the Standard Data Model. The Dates often (mostly) reside within the Fact and link to the corresponding Day of the Day Dimension. The below figure shows the Standard Marketing Star with these links:Standard Campaign Fact

The Physical Diagram of the Campaign Membership Fact (W_CAMP_HIST_F) shows the Joins from the Fact for all dates to the Day Dimension (W_DAY_D). For each date a alias is created e.g. Created Date, Campaign Start Date, Completed Date.

The End User is presented with the standard subject area which allows to use the Date Dimension (Time Dimension):


But how is this Time Dimension defined?

The Time Dimension available for End Users links the Fact of the selected Subject Area (Marketing – CRM Campaigns) with a “Common” Day Dimension. This Common Day Dimension is the representation of the Time Dimension within the Metadata Repository.

Standard Campaign Time Dimension Source

Each Fact might define the Time Dimension with (random) date of the Star Model. In case of the Marketing – CRM Campaigns Subject Area this is defined as the “Created Date” of the Campaign as shown below:

Standard Campaign Time Dimension

This can also be verified when inspecting the generated SQL by the Oracle BI Server against the Business Warehouse:

Standard Campaign Time Dimension Inspecting SQL

The Created Date of the Fact is joined with the “Common” Day Dimension (available as Time Dimension within the Report Designer UI).

How are the other Dates available?

Other Dates are either available under the sub-folder “Secondary Dates” of the Time Dimension:

Standard Campaign Time Dimension Secondary Dates

Which link to the corresponding Date of the Fact:

Standard Campaign Time Dimension Secondary Dates - sources

Or: the dates are available from the Dimension Folder itself, e.g.:

Standard Campaign Fact - Remaining Dates

To conclude. The Time Dimension often refers to the “created” Date. Other Dates are either provided as Remaining Dates or on the Dimension Folder itself.

The Siebel Party Model and Oracle BI Applications – Part 1/2

In this post I’m focussing on the Siebel Party Model and how this is relevant and brought over to the pre-built Oracle BI Applications for Analysis.

From a CRM perspective it is very important to model:

  • the own Organization
  • Internal Employees
  • Employee Hierarchy
  • Customers (of the own Organization)

Customer’s can be other Company’s (in Siebel known as Accounts) in B2B  or Individual Persons in B2C Scenarios (in Siebel known as Contacts).  This is relevant as e.g. a specific Sales Employee of the own Organization sells a Product to a purchase authorized Contact of another Company (Accounts).

To organizes all this, Siebel uses the Party Concept to relate all above mentioned (and some more) entities such as: Person, Organization, Position, Household etc. within a single entity: the Party.


All Entities surrounding (extending) the Party have at least one record stored in the Party entity itself. The Party also has a Party Type (S_PARTY.PARTY_TYPE_CD), which provides some further aggregation/classification for the Party Entities.

The following Table describes the Party Entities (Extension Entities to the Party Entity), the Physical table references and the associated Party Type’s (S_PARTY.PARTY_TYPE_CD) in the Party Entity:

Party ext. Entity

Phy. Table

Party Type in Party Entity


Person (or Contact)



A Person is an [real world] individual who is represented by a Person, Without additional attributes, a Person has no access to your database. E.g. An employee at a customer’s company




A User is a Person who can log into your database and has a responsibility that defines what application views are accessible

Partner User



An employee at a partner company, who is associated with a position in a division within an external organization. Therefore, a Partner User is also an Employee, but not an internal one.




An Employee is a User who is associated with a position in a division within your company.




A job title within your company that exists for the purpose of representing reporting relationships. Every position is associated with a division. The position is then also automatically associated with one organization: the organization with which the division is associated. Thus, a position within your company is associated with a division and is associated with the organization to which that division belongs. Data is associated with a Position, which drives the Position Visibility.




A company with whom you do business. An account is not a division, an internal organization, or an external organization. Can have Parent and Child Accounts.




An organizational unit within your company such as Manufacturing or Corporate e.g. operating within a particular country. Exists for the purposes of mapping a company’s physical structure into the Siebel database and for providing a container for position hierarchies. A division can have a Hierarchy with parent division and have child divisions. A division can be associated with only one organization. Data cannot be associated directly with a division. (Divisions that are not designated as organizations do not drive visibility.




An organizational unit within your company. The Organization is a division that is designated as an organization. Positions are not assigned directly to Organizations to drive Visibility, but only via Divisions. An organization exists for the purpose of providing a container in which positions can be associated with Data to drive Position and Organization Visibility. Organizations can also have a Hierarchy like divisions.




A group of people, typically a family, who reside at the same residence, who are economically affiliated and share a common purchasing or service interest.

User List


User List

A user list is a group of some internal employees and some partner users. It can have any combination of contacts, users, employees, and partner users as members

Access Group


Access Group

An access group is a group of any combination of parties of type Position, Organization, and User List. That is, it is a group of groups

Business Unit



Not officiall mentioned: Internal Organizations are also represented as Business Units.

To  model relationships between the own Organization (and own Employees) and Customer Organizations (and their Employees) the most important “Party Types”  are: Organization and Person/Contact.


What are the Entities for Party Type: Persons?


The Entity Contact/Persons represents everything necessary for Siebel to model an Individual e.g. with First Name, Last Name, Date of Birth, Phone Number. That might be a Purchasing authorized Person within a customer’s company. A User can be assigned with a User Id to the Person to specify the credentials like Login and password. If the User is an internal, it is represented as Employee by assigning the Contact with a Position within the Organization.

How does this look in the Siebel UI?


To administer this, one needs to navigate to “Administration – User” Screen for setting up Persons/Contacts, Employees and Users.

As can be seen below, certain Person/Contacts also have been configured with a User for Logging into the Siebel Application:


Using the Employee Screen it is also possible to assign the Employee to a Organization and a Position for steering the Visibility. (Additionally the Employee can also be assigned to a Division, however this doesn’t affect Visibility).


Detailed Screen of a single Employee Record:


Even though a Employee Record exists, the Employee needs to be assigned with a User for being able to Login to the Siebel Application.

-The setup of Partner Users is not covered within this Article-

How is this stored in the Siebel Database?

For Contacts:

select ou_id,fst_name,last_name,birth_dt,work_ph_num,login,
emp_flg,emp_num,sex_mf, priv_flg,status_cd,prospect_flg


For Users:

from S_USER;


For Employees:



And for the Party of Type Person:

from S_PARTY
where PARTY_TYPE_CD = 'Person';


The following is an example of joining all above mentioned the Information of the Entities via the S_PARTY:

Select S_CONTACT.ou_id,S_CONTACT.fst_name,S_CONTACT.last_name,
S_CONTACT.prospect_flg --from S_CONTACT


For this I’ve used Outer Joins for User and Employee Entity since not every Contact belongs to this Entity.

What are the Entities for Party Type: Organizations?


The Account is the representation of a Business Customer’s (also known as “Company” in real life terms). The own Company is represented as: Organization’s (for steering Visibility), Division’s (not influencing Visibility) and Business Unit’s.

How does this look in the Siebel UI?

Accounts (or Business Customers are administered from the Account List View:


The Contact/Persons within the Business Customer Company can be seen below the Account (when clicking a particular Account) and each contact has a certain role or Job Title within the Customer Account.:


The own Organization can be Administered using the (internal) Organization (and Business Unit) and Divisions, from the “Administration – Group Organization” Screen:


And the Organizations List Screen:


And the detailed Screen e.g. to setup (internal) Organization Hierarchy:


The Divisions can be listed from the Divisions Screen:


The main Screen shows the association of Divisions with one (and maximum only one) Organization:


And on the detailed Screen shows the Positions which are assigned to the Division. (And from this link implicitly to the Organization):


How is this stored in the Siebel Database?

External Organizations, which represent Business Customers as Accounts use the S_ORG_EXT Table.

select bu_id,par_bu_id, name,cmpny_name,legal_form_cd, accnt_flg,
accnt_type_cd,prospect_flg, int_org_flg,prtnr_flg,active_flg,
from S_ORG_EXT
where int_org_flg = 'N';


Previous Version of Siebel used a Table called “S_ORG_INT” for Internal Organizations, however this Table is not used/part of the shipped Standard Model any further. It’s functionality is merged into S_ORG_EXT with INT_ORG_FLG = “Y” and an associated S_BU record.

where BU_FLG ='Y' and INT_ORG_FLG = 'Y' ;


Divisions are also just stored in the S_ORG_EXT with INT_ORG_FLG = “Y” but have no record in the S_BU table. :

select *
from S_ORG_EXT --no Record in S_BU Table


Where are the relationships maintained?


The relationships between the own Organization and Employees and the customer’s Organization and Employees is maintained within the Party Relationships and Party Members Table “S_PARTY_REL” and “S_PARTY_PER”. The S_PARTY_REL must be used to model multiple relationships between the same Entities e.g. when the same Contact has multiple roles (Purchase Authorized, Business Owner, Fleet Manager) within the same one Account. Because the S_PARTY_PER has unique indexes defined not allowing this multiple Relationships.

How is this stored in the Siebel Database?

The S_PARTY_REL and S_PARTY_PER Table are used to relate the Party Entities.

select * from s_party_rel;


How is this modelled in BI Applications?

Within a next blog Post I’ll cover how this is brought to BI Applications covering:

  • How Contacts and Accounts are represented in the Oracle Business Analytics Warehouse (OBAW) Data Model as Dimensions
  • How the Fact  to relate Account and Contacts (W_PERSON_F) is extracted using the S_PARTY_PER
  • How other Facts use the Account and Contact Dimensions, like W_CAMP_HIST_F

Other Helpful resources are:

Data Validation with BI Applications and Siebel

The Informatica based BI Applications Version (from 7.8.x) were shipping a dedicated DAC (DataWarehouse Administration Console) Execution Plan to validate the data within the Siebel Application that could be used to ensure the data consistency when/before loading the data into the Warehouse.

DAC flatfiles conn

However, this was only available for the Siebel Data Source and not for any other of the Oracle Business Applications.

In general, this was always something customers wanted to have for their other Oracle Business Applications, but was never made available. Probably with the reason that a Standard Application (even if customized) should not make any data inconsistency’s possible.

However, most Projects could benefit from an out-of-the-box available data validation.

So, what did the Exception Reports check within the standard Source?

  • List of Values – Identifies gaps and overlaps for certain LOV types
  • Exchange Rates – Currency Exchange rates that do not change over a period of time
  • Hierarchies -that have circular references are flagged as exceptions. Additionally, since the Data Warehouse supports only a certain number of levels for hierarchies. If there are Hierarchies that have more than that defined, they are flagged as exceptions.

(The Official PDF Documentation can be found here). As can be seen from above, these checks were focused on Application Administration areas or technical limitations. Thus, areas were mistakes can happen more easily.

For the new ODI based Version these standard checks are not available anymore – also for Siebel. The new mechanism to write and perform these checks is called “Flow Control”.

A Full feature Implementation would probably not only check the source for the purpose of loading the Business Warehouse, but the source in general. Within Oracles offering, this is the Oracle Enterprise Data Quality.

3rd Party Modules for Oracle Analytical Applications

Oracle already provides a wide range of Pre-built Analytical Applications. The Portfolio in this Area can be categorized in Horizontal and Vertical Analytical Applications. The Horizontal Applications cover most Domains of the three main acronyms of CRM, CRM and HCM:

And the Vertical Analytical Applications cover Retail, Communication, Financial Institutions, Airline Industry or Healthcare Industry:

But, even such a large Vendor like Oracle can’t provide a pre-built Solution for every Industry or for every Business Problem and following the switch from the BI Application Release (based on Informatica) to the new BI Application release 11g (based on ODI) some Vertical Modules have been discontinued. Hence, there is some space for the Oracle Partner Community to develop and sell additional Modules based upon the Oracle BI Analytics Platform. Currently I’ve found two Partners are offering additional Modules: KPI Partners and FCS-Inc.

The additional Modules from KPI Partners enhance mostly the ERP Area and can be found here:

One Example of this Extension can be found below for the FSG Reporting, which Generates a Financial Statement Report based on Data that is stored within the eBS ERP System and Integrated with the Financial Analytics Modul:

Most of these Modules are even Demonstrated or Documented using Webinars or Presentations. E.g. or .

The Second Partner that produces Extensions is FCS-Inc. With a focus on Manufacturing Analytics:

This shows that even tough one Vendor maybe can’t provide a Pre-Built Solution for every Industry Area; the Architecture of the Product is so flexible that other specialized Partners can fill the Market gap.