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:
image
For Hardware requirements the Guide recommends the following specification depending on Project size for the Database & Oracle BI Server:
image

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

data volume (Volumetric)
image
workloads
image

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

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 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.

image

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

Description

Person (or Contact)

S_CONTACT

Person

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

User

S_USER

Person

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

Partner User

S_USER

Person

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.

Employee

S_EMP_PER

Person

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

Position

S_POSTN

Position

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.

Account

S_ORG_EXT

Organization

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.

Division

S_ORG_EXT

Organization

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.

Organization

S_ORG_EXT

Organization

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.

Household

S_GROUP_ORG

Household

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

S_USERLIST

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

S_PARTY_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

S_BU

Organization

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.

Person/Contact:
image
Organization:
image

What are the Entities for Party Type: Persons?

image

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?

image

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:

image

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).

image

Detailed Screen of a single Employee Record:

image

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
from S_CONTACT;

image

For Users:

select LOGIN,USER_FLG,PASSWORD
from S_USER;

image

For Employees:

select CNTRCTR_FLG,BURDENED_RATE,CURR_SALARY_AMT,TERMINATION_DT,
EMP_STAT_CD from S_EMP_PER;

image

And for the Party of Type Person:

select ROW_ID,PARTY_TYPE_CD,PARTY_UID,PAR_PARTY_ID,NAME
from S_PARTY
where PARTY_TYPE_CD = 'Person';

image

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.birth_dt,S_CONTACT.work_ph_num,S_CONTACT.login,
S_CONTACT.emp_flg,S_CONTACT.emp_num,S_CONTACT.sex_mf,
S_CONTACT.priv_flg,S_CONTACT.status_cd,
S_CONTACT.prospect_flg --from S_CONTACT
,S_PARTY.PARTY_TYPE_CD,S_PARTY.Name AS "PARTY NAME" --from S_PARTY
,S_USER.LOGIN,S_USER.USER_FLG,S_USER.PASSWORD --from S_USER
,S_EMP_PER.CNTRCTR_FLG,S_EMP_PER.BURDENED_RATE,
S_EMP_PER.CURR_SALARY_AMT,S_EMP_PER.TERMINATION_DT,
S_EMP_PER.EMP_STAT_CD --from S_EMP_PER
from S_CONTACT INNER JOIN S_PARTY
ON S_PARTY.ROW_ID = S_CONTACT.PAR_ROW_ID
LEFT OUTER JOIN S_USER ON S_USER.PAR_ROW_ID = S_CONTACT.EMP_ID
LEFT OUTER JOIN S_EMP_PER ON S_PARTY.ROW_ID=S_EMP_PER.PAR_ROW_ID;

image

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?

image

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:

image

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.:

image

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

image

And the Organizations List Screen:

image

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

image

The Divisions can be listed from the Divisions Screen:

image

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

image

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

image

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,
pay_type_cd
from S_ORG_EXT
where int_org_flg = 'N';

image

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.

select S_BU.BU_FLG,S_BU.NAME,S_BU.ROW_ID,S_BU.PAR_ROW_ID,
/*S_ORG_EXT.ACCNT_FLG,*/S_ORG_EXT.ACTIVE_FLG,S_ORG_EXT.ROW_ID,
S_ORG_EXT.PAR_ROW_ID,S_ORG_EXT.BU_ID,S_ORG_EXT.DIVISION,
S_ORG_EXT.DIVN_CD,S_ORG_EXT.INT_ORG_FLG,S_ORG_EXT.NAME,
/*S_ORG_EXT.ACCNT_TYPE_CD,*/OU_NUM,OU_TYPE_CD,PAR_BU_ID,
PAR_DIVN_ID,PAR_OU_ID
from S_BU INNER JOIN S_ORG_EXT ON S_ORG_EXT.BU_ID = S_BU.ROW_ID
where BU_FLG ='Y' and INT_ORG_FLG = 'Y' ;

image

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
;

image

Where are the relationships maintained?

image

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;

image

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 7.9.6.4 (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. http://de.slideshare.net/kpipartners/webinar-manufacturing-analytics-for-oracle-bi-ebusiness-suite or http://www.kpipartners.com/webinar-depot-repair-analytics/ .

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: http://www.gartner.com/technology/reprints.do?id=1-2ACLP1P&ct=150220&st=sb

Thoughts on Customizing BI Applications

For Transactional Commercial of the Shelf (COTS) Product the recommended path is to (re)use as much as Possible of the Out-of-the-box Logic in order to minimize the Customization Effort.

For Analytical Systems this is also possible, however creating new Objects and Leaving the Standard Completely separate has Advantages during Upgrades since only the new created Objects for the previous release might just be copied and pasted to the new Release.

Hence, there are two aspects associated with this Question:

Does the Customer want to:

  • Optimize the First Implementation effort by re using the Analytics Object, too.
  • Optimize the ongoing Effort by separating the OOTB Logic and the Customized Logic.

This Question should always be asked at the beginning of a new Greenfield Project since it will steer the Implementation in either direction.

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.

INDEXCOL( VALUEOF(NQ_SESSION."HIER_LEVEL"), 
"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.

The correct Java Connection String

It’s often required to use JDBC Connection to the Oracle Database. In BI Apps it’s possible to Connect from the BI Server to the Database and from Informatica to the Database. Please find below examples of the correct JDBC Connection String:

From Oracle BI Server to the Datbase:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=hostname)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename.local)))

And from Informatica to the Database via a JDBC Connection:

jdbc:informatica:oracle://hostname:port;ServiceName=servicename.domain.de