BI Publisher Guides

1. https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM149

XML Publisher Tag Components

1. https://docs.oracle.com/cd/E28280_01/bi.1111/e22254/create_rtf_tmpl.htm#BIPRD2391 
2. http://oracleapps88.blogspot.com/2012/05/xml-tags.html

IF Condition:

<?if:P_PM_YN=’N’?> Yes <?end if?>


IF ELSE Condition:

Method 1:

<?xdofx:if element_condition then result1 else result2 end if?>

Example:

<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>

Method 2:
Syntax:

<?xdoxslt:ifelse(condition,true,false)?>

Example: 

<?xdoxslt:ifelse(20=21,’yes 20 and 21 are equal’,’No 20 and 21 are not equal’)?>

Ans: No 20 and 21 are not equal

Using OR Condition in XML RTF Template:

Syntax:

<?if:XMLfield=value1 or XMLfield=value2?> display value <?end if?>

Example:

<?if:sum(AVALUE)=0 or sum(BVALUE)=0?>0<?end if?>

You can use whichever is applicable to your requirement.

 -------------------------------------------------------------------------------------------
 VALUE-OF
This element is used to retrieve a value from a specified Element or Attribute. For example, if you used this element specifying ‘/DATA/DEPARTMENT/NAME’ then you can retrieve a value that is presented in the NAME element in the XML document.

With BI Publisher tags, you can just type the following to do the same.

<?/DATA/DEPARTMENT/NAME?>

 --------------------------------------------------------------------------------------------

FOR-EACH

With BI Publisher tags, you can type the following to do the same.

<?for-each:/DATA/DEPARTMENT?>
  <?NAME?>
<?end for-each?>

------------------------------------------------------------------------------------------------------------------------------------------------------------

XSL:SORT
Inside the previous ‘for-each’ loop you might want to sort the data by alphabetically or based on the ID, etc. You can use this ‘sort’ element to do the sorting.

With BI Publisher you can do the following to achieve the same. 

  <?sort:NAME;’ascending’;data-type=’text’?>

---------------------------------------------------------------------------------------------------------------------------------------------------------------- 

IF
You can use this element to have a condition in the XSL transformation logic. This is also pretty much the same as other programming language’s ‘if’ condition. For example, if you want to display manager name only when Department name is ‘Consulting’ you can specify something like the below.

You can do the same with BI Publisher tags as follows.

<?for-each:/DATA/DEPARTMENT?>
  <?if:NAME=’Consulting’?>
    <?NAME?>
  <?end if?>
<?end for-each?>

---------------------------------------------------------------------------------------------
 CHOOSE
As an alternative or for better reasons you can also use CHOOSE/WHEN elements to do the condition. One thing to note is that XSL doesn’t support IF/ELESE condition as native, so if you have multiple conditions to use together in a form of IF/ELSE then CHOOSE/WHEN/OTHERWISE elements would serve you better.

With BI Publisher you can do the below to have the same condition. 

<?for-each:/DATA/DEPARTMENT?>
  <?choose:?>
  <?when:NAME=’Consulting’>
    <?MANAGER_NAME?>
    <?DEPARTMENT_NAME?>
  <?end when?>
  <?otherwise:?>
    <?DEPARTMENT_NAME?>
  <?end otherwise?>
  <?end choose?>
<?end for-each?>

-------------------------------------------------------------------------------------------


 

Oracle Data Integrator 12c Installations and Configuration in Windows 7 (64x)

ODI 12.2.1.2.0 Enterprise Installation
----------------------------------------------
https://www.youtube.com/watch?v=L5NAkvzEIEM 

Important BLOGS ,Web URL's for OBIEE

       https://www.rittmanmead.com/blog/





                               http://www.redstk.com/blog/







                          http://addidici.com/

Surrogate Keys, Natural Keys, Candidate Keys, Composite Keys and Super Keys

Candidate Key

A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. In general, one of these candidate keys is selected as the table primary key.

Example - From the above table EMPLOYEE_ID, EMPLOYEE_SSN_ID, and EMPLOYEE_DEPT_ID can be considered as candidate keys

Primary Key

A primary key is a single column or combination of columns that uniquely defines a record. None of the columns that are part of the primary key can contain a null value. A table can have only one primary key.

Example - EMPLOYEE_ID or EMPLOYEE_SSN_ID can be considered as primary keys

Unique Key

A unique key or primary key [is a candidate key] to uniquely identify each row in a table. It be comprised of either a single column or multiple columns.

The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL.

Differences between Primary Key and Unique Key

Primary Keys -
1. It will not accept null values.      
2. There will be only one primary key in a table.      
3. Clustered index is created in Primary key.      
4. Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.      

Unique Keys -
1. Null values are accepted.
2. More than one unique key will be there in a table.
3. Non-Clustered index is created in unique key.
4. Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Alternate Key

A candidate key that is not the primary key is called an alternate key.

Example - If EMPLOYEE_ID is considered as primary keys then EMPLOYEE_SSN_ID is an alternate key.

Superkey

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

A primary key is therefore a minimum superkey.

Examples - Any combination of the following can be considered as a Super key

- EMPLOYEE_ID - Minimal Super Key

- EMPLOYEE_ID and EMPLOYEE_SSN_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME

- EMPLOYEE_SSN_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME

Foreign Key

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.

Composite Key

A primary key that made up of more than one attribute is known as a composite key.

Example - [ EMPLOYEE_ID and EMPLOYEE_SSN_ID ] can together be treated as (one of) composite keys. Another combination can be [ EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID ]

Surrogate Key

Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table.

Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).