Cache Management

Cache Management in OBIEE


1. Admin tool Manage > Cache > Purge

You can purge all the cache or cache by Subject Area or Cache by query


2. Physical Layer

Physical Table Properties > Cacheable, Cache persistence time


3 Event Pooling Table


4. EM(Enterprise Manager) > Capacity Management >Performance > Cache Enabled


5. Analysis > Advanced tab > Bypass presentation cache


6. Analysis > Advanced tab >

 Prefix     set variable DISABLE_CACHE_HIT=1;


7. When dynamic variable value changes the cache associated with that subject area purges

8.. Creating a batch file and scheduling it

XML Source Qualifier Transformation

Explain about XML Source Qualifier Transformation

XML Source Qualifier transformation is a passive transformation which allows you to read the data from XML files
Every XML source definition is by default associated with XML Source Qualifier transformation

Transaction Control Transformation

Explain about Transaction Control Transformation 


Transaction Control transformation  is an active transformation which allows you to control the transactions which are bounded by "commit" and "rollback" transaction control operations
The powercenter supports 2 different ways to control the transactions
The conditional Transaction Control expression can be developed using Transaction Control transformation at mapping level
A commit interval is the number of rows at which an integration service applies commit to the target 

1.  Using Transaction Control transformation  at mapping level
2.  Using commit interval property at session level

IFF(SAL>3000, commit, rollback)

In Properties tab

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

IIF (condition, value1, value2)

The expression contains values that represent actions the Integration Service performs based on the return value of the condition. The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction. Use the following built-in variables in the Expression Editor when you create a transaction control expression:

TC_CONTINUE_TRANSACTION. The Integration Service does not perform any transaction change for this row. This is the default value of the expression.  

TC_COMMIT_BEFORE. The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.  

TC_COMMIT_AFTER. The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.  

TC_ROLLBACK_BEFORE. The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.  

TC_ROLLBACK_AFTER. The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.  

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.


This property can be set at session level
The default commit interval is "10000"

Union Transformation

Explain about Union Transformation

Union Transformation is an active transformation which combine the data record s vertically from multiple sources having same metadata
The Union transformation also supports heterogeneous joins

Heterogeneous join Ex: Emp(Oracle) + Employee (SQL Server)

The Union Transformation is created with two groups

1. Input Group: It can receive the data from source pipeline
2. Output Group: It provides the data for further processing or loading

All the input and output groups should have matching ports. The precision, datatype and scale should be same for all the groups

The Union Transformation functions as "UNION ALL" operator in SQL

UNION ALL allows duplicates

Differences between Sorter and Union Transformation
http://prashanthobiee.blogspot.in/2013/02/difference-between-joiner-and-union.html

Difference between Joiner and Union Transformation

• Joiner Transformation can join only two input groups whereas Union Transformation can join multiple input groups
• Joiner Transformation combines the records horizontally based on the join condition whereas Union Transformation joins the records vertically
• Joiner Transformation supports Normal join, Master outer join, Detail outer join and Full outer join whereas Union Transformation does UNION ALL
• Joiner Transformation removes duplicates whereas Union Transformation doesn't   

Both these transformation supports heterogeneous joins(Join between two different data sources Ex: Oracle + SQLServer)

Joiner Transformation

Explain about Joiner Transformation

Joiner Transformation is an active transformation which allows you to combine the data records horizontally based on join condition i.e.., you need a matching column to join the data.
Joiner transformation combines the data from two different sources having different metadata
A Joiner transformation is created with following types of ports
1. Input port (I)
2. Output port (O)
3. Master port (M)

A Joiner transformation can only join two input data stream per joiner
The sources to the Joiner transformation designated as "Master source" and "Detailed source"
If you have more than one source, you need to connected output of a joiner to another input pipeline.

A source which is having the lesser number of records is designated as "master source" and the other source is designated as "detailed source"
A Master source is the source which occupies least amount of space or memory in cache

Joiner transformation supports homogeneous and heterogeneous joins

A join which is made on same data source is known as homogeneous join
Ex: Oracle + Oracle  or    SQL server + SQL server


A join which is made on different data sources is known as heterogeneous join
Ex: Oracle + SQL server

The Joiner transformation is created with the following types of joins
1.  Normal Join (equi join or inner join)
2.  Master outer join
3.  Detail outer join
4.  Full outer join

The default join type is Normal join

Consider the following data

EMP1 - MASTER SOURCE

NAME                     DEPTNO                CITY                      
Mark                             10                     London   
Antony                          20                     Chicago
Ram                               30                     Hyderabad


EMP2 - DETAIL SOURCE

NAME                     DEPTNO                AGE
Mark                            10                         42
Ram                              30                        28
Garry                           40                         39


NORMAL JOIN 
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Ram                               30                  Hyderabad                28

MASTER OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Ram                               30                  Hyderabad                28
Garry                           40                         NULL                   39

DETAILED OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Antony                          20                     Chicago                  NULL
Ram                               30                  Hyderabad                28

FULL OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Antony                          20                     Chicago                  NULL
Ram                               30                  Hyderabad                28
Garry                           40                         NULL                   39

When you run a session with a Joiner transformation, the Integration Service blocks and unblocks the source data, based on the mapping configuration and whether you configure the Joiner transformation for sorted input.



Unsorted Joiner Transformation
When the Integration Service processes an unsorted Joiner transformation, it reads all master rows before it reads the detail rows. To ensure it reads all master rows before the detail rows, the Integration Service blocks the detail source while it caches rows from the master source. Once the Integration Service reads and caches all master rows, it unblocks the detail source and reads the detail rows. Some mappings with unsorted Joiner transformations violate data flow validation.

Sorted Joiner Transformation
When the Integration Service processes a sorted Joiner transformation, it blocks data based on the mapping configuration. Blocking logic is possible if master and detail input to the Joiner transformation originate from different sources. 

The Integration Service uses blocking logic to process the Joiner transformation if it can do so without blocking all sources in a target load order group simultaneously. Otherwise, it does not use blocking logic. Instead, it stores more rows in the cache.
When the Integration Service can use blocking logic to process the Joiner transformation, it stores fewer rows in the cache, increasing performance.

The Joiner transformation doesn't support non-equijoin
The Joiner transformation supports only "AND" operator
You cannot use a Joiner transformation when both the input pipelines contains an Update Strategy transformation.  
You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation. 


Differences between Sorter and Union Transformation
http://prashanthobiee.blogspot.in/2013/02/difference-between-joiner-and-union.html

Ragged and Skipped Hierarchy

OBIEE 11g supports Ragged and Skipped hierarchies. Let see what they actually are


Ragged Hierarchy: A Ragged hierarchy is a hierarchy which has unbalanced child attributes

In the example below C1 doesn't have a child(level D)attribute while C2 has child attributes(level D). It is unbalanced

Skipped Hierarchy: A Skipped hierarchy is a hierarchy in which some child doesn't have a direct parent attribute i.e., some attributes missing at some level.

In the example below D3 and D4 has direct parent attribute(level C) while D5 doesn't have a direct parent attribute(level C). Level C is skipped for the child D5

To know what a hierarchy is check this
http://prashanthobiee.blogspot.in/2013/01/hierarchy.html