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:

Target Levels: What do they steer and what not? Part 3/3

Within this third and final article I’ll extend the Target Level Concept with the selection Rules feature.The first article of the series introduces and explains the concept of Target Levels. The second article showed how Target Level linked the Segment Designer, the Campaign Load and the Campaign Execution (Launch).

Target Level and Selection Rules

For this, I’ll re-use the same Segment as before:

The concept of Selection Rules allows to associate another Target Level (as a kind of secondary Target Level) to the selected (Primary) Target Level, to influence or constrain the Load of the Campaign Members with related items to the Primary Target Level for the Campaign Membership. For complex scenarios this helps to specify additional logic within the segment, in case the segment should not only include filtering logic for the Target Level, but also a wider logic for the Campaign participation. In this case another Target Level.

Use Case example for Selection Rules

For a Campaign, the target group consists of Individuals (Contacts) where the current Assets (e.g. a cell phone contract) should also be listed in the Campaign participation and Campaign export. However, the Assets of the individual should be constrained to only include contracts which have a end date within the next 3 Month (hence not all Contracts of the Target Level = Individual).

Since the Scenario contains Contacts as well as the Assets belonging to the Contact. The concept of Selection Rules must be used here. Without a selection Rule the Contacts which have a ending Asset are identifiable using the “normal” segmentation criteria, but the Asset Number of the respective ending Contract could not be associated with the Campaign Load (participation) of the Contact.
Thus, selection Rules are used in scenarios where another Target Level item should be associated as Secondary Target Level with the Target Level of the Segment.

Without a selection rule the following SQL is generated for the Campaign Load:

SQL statement without selection Rule.

The Selection Rule is added to the Segment to associate the Account (Company) to the Contact Target Level for the Campaign Load:
The Selection Rule also contains a constrained for the Secondary Target Level to only include Accounts within a certain Region.
This Example might be used as a Scenario within the Health-care Industry where a Doctor (Contact) might work for multiple Hospitals (Accounts) and only Hospitals within a certain Region should be Addressed for the Contact (Doctor).

After adding the Selection Rule the following SQL is generated for the Campaign Load:

SQL statement with Selection Rule.

Since a Selection Rule has been added to the Segment, the counts of the Segment Designer do not match anymore with the Campaign Load counts (as seen in previous article both are independent) as the selection Rule Expression is only evaluated for the Campaign Load but not within the Segment Designer:

The logic added with the Selection Rule for the Segment can be phrased as:

Only address Contacts (Target Level) having an Account within a certain Account Region (Selection Rule).

The Target Level concept and count presented within the Segment designer does not “protect” Members from disappearing from the load:

  • When the metadata logic of the Load List is different to the logic of the Segmentation Catalogue metadata (previous article)
  • When a Selection Rule is added to a Segment (Selection rule expression is only evaluated/executed on the Campaign Load)

When loading the Segment using the correct Load List Format (to load Accounts as well as Contacts) the Contact is loaded not with all possible Accounts but only the constrained and relevant Accounts to the Campaign Membership Table. The constraining of related items to the Target Level can only be achieved by using Selection Rules. Otherwise this logic would need to be added to the Export List requiring a different Export List for the Segment.

Selection Rule expression are only evaluated for Load List Formats (Campaign Load) but not the Segment Designer (Segmentation Catalogue).

To summarize

Selection Rule expression are only evaluated for Load List Formats (Campaign Load) but not the Segment Designer (Segmentation Catalogue) leading to a scenario where the count of the Segment Designer will not match the count of the loaded Segment to the Campaign Membership.


For a summary of the SQL generation for the Segment designer, Campaign Load and Campaign Launch, click here.

Target Levels: What do they steer and what not? Part 2/3

This is the second Post about the influence of Target Levels. The realm of the first post was limited to the Segment Designer. This post will focus on the Load of Segments to Siebel in conjunction with the Load List Format and compare this behaviour again with the Segment Designer.

Target Level and Segment Designer

As already described in the previous Post, the Segment designer allows counting the target level members according to the segmentation criteria of the segment, the count of each block and the the gross count is displayed, hence indicating the members which are part of the segment and will be loaded into the campaign as members once the campaign is loaded.

The SQL statement generated by the Oracle BI server can be inspected from the Marketing Job Log View:

selecting the “Get Count” Marketing Job:

The below SQL statement was generated by the Oracle BI Server for the Segment Designer to count the Target Level Members within the Business Analytics Warehouse:


Target Level and Campaign Load

When loading the Segment into the Siebel Campaign, the segment is “invoked” and the Members currently fulfilling the criteria are loaded to the Campaign Membership (S_CAMP_CON Table).

The Oracle BI server will write a text file to the Siebel Shared File system and Siebel will load the text file using a EAI Workflow to the Campaign Membership (S_CAMP_CON Table).


To provide the text file in the expected format by the Siebel EAI Workflow, the Oracle BI server provides the Load List Formats designer to provide the means of loading the individual members to the Campaign Membership Table so that they are participating within the Campaign.

The generated text file contains the individual members and has the following format (adhering to the Load List format definition):

The Load List Format itself is defined on a special Subject area and Business Model for each Target Level, such as Contacts / Accounts:

Every Segment is linked to a single Load List Format via the Advanced Options Tab of the Segment designer:

Once the Campaign is loaded Siebel sends a SOAP request to the Oracle BI server to execute the segment and generate the text file with the Campaign Members using the Write List Job (visible with the Marketing Job View):


The Marketing Job is executed by the Oracle BI server with the following statement to the Business Warehouse for the same sample Segment as before:

When comparing the SQL statements executed from the Segment designer with the SQL Statement for the Campaign load it can be seen that they are not the same. This is partially due to the different nature since within the Segment designer, the User is interested in the count, hence a SELECT COUNT(*)while the load requires the individual records, hence no aggregation.
Even though the statements are not the same, the logic (in terms of underlying Tables and Joins) for the Load List format (plus underlying RPD Metadata definition) is matching the logic of the Segmentation Catalogue. Thus, the generated text file and the segment count have the same count of Members (# of Records) for the Segment:

To summarize: The Target Level links the Segmentation catalogue with the Load and Export List Formats, but the generation of SQL statements is independent for the areas and only depends on the metadata definition on which they are based:

Since the generated statements for the Segmentation Designer (based on the Segmentation Catalogue) is independent of the generated statement for the Campaign Load (based on the Load List Format) it’s possible to create a Scenario in which both do not provide the same results (count for Segment and Load) even though both are linked to the same Target Level. This will be the focus of the next article.

For some scenarios this might be desirable since the Campaign Load Format is aware of the Campaign (it is loaded to) and the Treatment (Channel), thus it would be possible to perform certain additional checks e.g. does the Campaign Member have a valid Address for the Channel. However, this additional “segmentation” logic makes it very hard for a Marketing End User to compare the results of the Segment Designer with the actual Members loaded to the Campaign.

Launching the Campaign

When launching the Campaign the impact of the Target level is also very minor. As described before  The Export List format definition is also based on the BI data model and determines the actual SQL generated on the BI System (Data Warehouse). In order to only generate the respective Members of the Campaign the BI System references the Siebel Transactional Campaign Membership Table (S_CAMP_CON) by passing Parameters from Siebel to the Oracle BI System to identify the respective Campaign via the Campaign Id and Load Number. 

Export List Format, based on Transactional Campaign Membership Table:image

Sample Export Format based on above Subject area to Export the Campaign Members for the Execution of the Campaign e.g. using a external Marketing Agency:

Target Levels: What do they steer and what not? Part 1/3

For effective Marketing Campaign it’s important to define and find the best target group that should receive the Campaign messaging and/or Campaign Offer. Within Oracle Siebel CRM the target group is implemented with the concept of Target Levels.

This first article will focus on the basic’s of the Target Level concept and configuration within the Application (Oracle BI)

The concept and configuration of Target Levels

Target Levels (also referred to as QLI “Qualified List Item”) are used in the Marketing module of Oracle BI to identify and count the members of a segment that should be contacted within a marketing campaign. The complete Marketing Configuration and the Target levels concept in particular adds an additional layer of abstraction over the pre-built BI data model, allowing to re-use the complete data model of the Oracle Business Analytics Warehouse for the purpose of segmentation and campaigns.
The above diagram shows this with a few examples. The Conforming Dimension of e.g. “Customer” is being setup amongst multiple Facts (Orders, Campaigns, Service Requests) as Target Level. The criteria of the star (dimension & facts) are then available for Segmentation as Segmentation catalogue. When using the Segmentation catalogue within the Segmentation designer to define the Segmentation criteria, the Oracle BI Server translates the segment criteria into SQL for performing the counting of the Target level members which are meeting the segmentation criteria.

The Marketing Manager allows to define the Marketing specific configuration (“as additional layer”) by referencing the pre-built Presentation Layer data model for the specific Marketing metadata such as Target Levels, Load List Formats or Export List Formats:

While target levels can technically refer to any logical object of the RPD metadata layer, they should only be defined upon dimensions, since a typical target level such as “customers” might be used/referenced in almost all facts of the warehouse as conforming dimension. Thus, the target level members (e.g. Customers) can be identified and counted within the facts of the Analytical data model.
Technically, the target level is linked with a particular presentation column (field) of the dimension, usually the Primary Key of the Dimension:

Target Level within the Application

The following example shows the required User steps within the Application and the execution by the System (Orcle BI):

  1. choose Target Level Contact (Individuals)
  2. define segment criteria based on Job Title
  3. Translation into SQL for counting of Target Level Members


step 1 & 2 are done by the Marketing User within the Segment Designer

and step 3 handled via the Oracle BI Server by executing the SQL statement within the Oracle Business Analytics Warehouse:


Hence, the Target Level steers the object and column that should be used for counting the (Target Level) Members. The translation to generate the SQL statement within the Oracle Business Analytics Warehouse is done based on the Marketing metadata configuration and the BI metadata model. Below is a example of the “path”:

Target Level within Segment Designer, Load Lists and Export Lists

The Target Level is not only associated with the Segmentation catalogue for building the segment, but also with Load Lists Formats and Export Lists Formats. Where the Load List Format define how the Segment members should be loaded to the Campaign Management System (Siebel Marketing) and the Export Lists what kind of information should be exported to the Campaign Execution (Agency) to address the Segment Member.
For the purpose of Segmentation the Street Address and House number are not important (Segmentation Catalogue), for the load of the Customer as Campaign Member only the Customer Id is relevant (Load List Format), but for the execution the complete Address of the Customer is required, but not the Customer Id (Export List Format).

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.

Gartner recently published new BI Magic Quadrant

The Analyst at Gartner who evaluate the Business Intelligence Market recently published a new Analyst Report on Business Intelligence. It is Important to note that this Analyst Report only evaluates the Vendor Capabilities within this Market. Related Markets or Vendor Capabilities in are not considered within this Analysis. Thus, the capabilities of a Vendor to Offer e.g. Big Data Solutions will not be mentioned within this Report. From an Oracle perspective the Solution just manages to be within the leader Quadrant, but slightly behind the major Competition of SAP, SAS, Mircrosoft, IBM and MicroStrategy. Leaders by far are Tableau.

The Leader Position for Tableau is justified by the fact that this Vendor has changed the customer expectation of what is possible within the BI and especially Data Discovery “with a focus on helping people see and understand their data. In particular, analytic dashboards, free-form exploration, business-user data mashup and cloud deployment are platform strengths.” Additionally the easy and direct access to Structured Data in Form on SQL, Mutidimensional Data with MDX and unstructured data on Hadoop has been noted. As limitation Tableau has a very limited set of Advanced Analytics Capabilities and only just recently support for R Integration was added. Some other very Interesting Aspects of the Report was in Terms of actual use of the Software. Noting that: “Customers of IT-centric platforms that have a broad range of BI platform capabilities report using them narrowly, most often for production reporting. On the other hand, business-centric platforms such as Tableau, Qlik […] have a more narrow set of capabilities, but are used more broadly for a range of BI and analytics functions.” This very Interesting Observation can be found throughout the Report. Even though the Report notes that the set of Capabilities is lower compared to other Vendors the Use Case of Tableau/QlikView are actually broader, due to the focus on the Business User (rather than the IT Users). The more Business User and Data Discovery focused Vendors (such as Tableau/QlikView) manage to displace some incumbents particularly in SMB’s, but this has not yet happened on a wider Scale since “these platforms lack the necessary enterprise features in relation to governance, administration and scalability (among other things)”.

Gartner expects Customer currently to evaluate whether the traditional BI Vendors which are currently mostly used for their system-of-record Reporting will add the business User oriented capabilities for Data Discovery to their Product Suite or if they should replace their current system with a Data Discovery Pattern in mind from the beginning. Hence, Gartner pretty much sees two Sub-Markets within Business Intelligence: traditional KPI/Metric Reporting for reliable Business Information and a new Data Discovery Market which unveils new Insight and Value within the Data.

From an Oracle Perspective the advantages for the BI Suite remains being the Pre-built Data Models and Adapters of the Oracle BI Application Suite for a wide range of Horizontal and Vertical Oracle Applications.

Providing BI within the Cloud is also mentioned within the Report, where Saleforce is seen with a promising Offer compared to the other Startup competitors who are currently the Market Incumbents. Oracles cloud Offering hasn’t been mentioned within the Report even though its BI Cloud Service launched at roughly the same time last Year.

As Always the criteria’s for this Evaluation are documented too, within Note 2 and Note 3 of the Report.

The full Report can be read at:

Primary Position/Owner based Security

Oracle BI Applications allow using the same Visibility Concept like Siebel. The following possibilities are supported within the Standard:

  • Primary Position/Owner Security
  • Organisation Security

The following Article focuses on the First Option “Primary Position/Owner Security”. It’s Important to understand a few Basics about Siebel:

  1. A Position defines a specific job Slot within a Company and usually starts with the Position of the CEO, having the CIO,CFO, CTO etc. reporting to him. The next Position e.g. Vice President Sales then Reports to the CFO.
  2. A Position can have a particular Incumbent (or Employee). E.g. “John Smith” is the current Incumbent of the Position Sales Manger West for Automotive. There might be other employees before “John Smith” how aren’t active in this Position anymore
  3. Most Objects in Siebel like Opportunities, Marketing Campaigns etc. are associated to a Position and not the Employee. However, a few Objects which are shorter lasting like Activities, Service Request etc. are associated to an Employee directly.

It is Important to note that the Concept is called Primary Position/Owner based Security. Thus, there is no either or. Depending on the Object either the Position linkeage or the Owner Linkeage will be used and the Position Hierarchy will always be respected even if Objects are linked to Owners. It’s also possible to have two Employees sharing a Position at the same Point in Time, but only the Primary Employee will be able to see the data within the Analytical Application (BI Apps). This is because the Position Hierarchy (W_POSITION_DH) will be used to limit the Visible data based on the Position within the Hierarchy and the Primary Employee is an Attribute of that Position. Thus, it’s not possible to have a many to one Relationship in this Hierarchy, but only one Employee (the Primary) as a Descriptive Attribute of the Position Hierarchy.

E.g. the West Rep1 Position does only have the Primary Incumbent in the Position as a descriptive Attribute to the Position.

This is stored in the W_INT_ORG_DH in the following way:

To Configure the Security correctly one needs to administer the Incumbents within a Position in the following Screens in Siebel CRM:

The following is the Administration Screen to select an Employee within a Position:

The Child Position needs to be configured accordingly:

After setting up the Position Hierarchy and Incumbents in Siebel the Hierarchy will be loaded into the BI Applications data model. Based on the Hierarchy the BI Server will create a Query like the following:

The above Query is for the Position that is located at the top of the Hierarchy. Note that the Login is used to compare to the login, thus the Position Incumbent. Hence, if multiple Employees share the same position only the login (Employee) that is the Primary Employee will have access to the data. Other Employee’s won’t have any!

For the Position that is located below the following Query will be created:

And for the Position below:

And for the Position that is located at the very bottom, the Base Position will be used:

All of this is steered through the Position Security Hierarchy within the Business Model Layer of the BI Admin Tool (.rpd) using the INDEXCOL Function on the “Dim – Position Security” Dimension.

"Core"."Dim - Position Security"."Current Base Level Login", 
"Core"."Dim - Position Security"."Current Level 1 Login", 
"Core"."Dim - Position Security"."Current Level 2 Login", 
"Core"."Dim - Position Security"."Current Level 3 Login", 
"Core"."Dim - Position Security"."Current Level 4 Login", 
"Core"."Dim - Position Security"."Current Level 5 Login", 
"Core"."Dim - Position Security"."Current Level 6 Login", 
"Core"."Dim - Position Security"."Current Level 7 Login", 
"Core"."Dim - Position Security"."Current Level 8 Login", 
"Core"."Dim - Position Security"."Current Level 9 Login", 
"Core"."Dim - Position Security"."Current Level 10 Login", 
"Core"."Dim - Position Security"."Current Level 11 Login", 
"Core"."Dim - Position Security"."Current Level 12 Login", 
"Core"."Dim - Position Security"."Current Level 13 Login", 
"Core"."Dim - Position Security"."Current Level 14 Login", 
"Core"."Dim - Position Security"."Current Level 15 Login", 
"Core"."Dim - Position Security"."Current Level 16 Login", 
"Core"."Dim - Position Security"."Current Top Level Login")

This Function uses the Session Variable “HIER_LEVEL” which determines the Level one Particular Employees is in to steer the correct Data Visibility. This Session Variable is Initialized using the following Authorization Block:

This means that one Employee can see always “his” correct data even for Objects which are linked to positions e.g. like Opportunities.