Initialization blocks Execution Precedence in OBIEE 11g

Execution Precedence:

When a repository has multiple initialization blocks, you can set the order (establish the precedence) in which the blocks will be initialized.

First, you open the block that you want to be executed last and then add the initialization blocks that you want to be executed before the block you have open. 

For example, suppose a repository has two initialization blocks, A and B. You open initialization block B, and then specify that block A will execute before block B. This causes block A to execute according to block B's schedule, in addition to its own.

To establish execution precedence:

1. In the Administration Tool, select Manage, then select Variables.
   
2. In the Variable Manager dialog, double-click the last initialization block that you want to be initialized.
   
3. In the [Repository|Session] Variable Initialization Block dialog, click Edit Execution Precedence.
   
4. In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add.
   Add is only available if there are initialization blocks that have not yet been selected.
   
5. In the Browse dialog, select the blocks that should be initialized before the block that you have open, and then click OK.
   
6. To remove a block, in the [Repository|Session] Variable Initialization Block Execution Precedence dialog, select the block you want to remove and click Remove.
   
7. Click OK.
   
8. If you want the initialization block to be required, in the [Repository|Session] Variable Initialization Block dialog, select the Required for authentication option.
   
9. Click OK.

Screen Shot :....

-Asifkhan P.

Merging and Importing Objects from Different Repositories by Using the Oracle BI 11g Administration Tool

Merging and Importing Objects from Different Repositories by Using the Oracle BI 11g Administration Tool :

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115/import_merge/merging_importing_11g.htm


-Asifkhan P.

Agents (or iBots) in OBIEE 11g

Oracle BI delivers:

• Oracle BI delivers are useful to create schedules (or) Agents.
• Automates Business Intelligence
• Creates alerts based on Business Intelligence results
• Detects specific results and immediately notifies the appropriate person or group through Web, wireless, mobile, and voice communications channels.

1) Configuring scheduler tables:

• In 11g configuring of Oracle BI Scheduler Server will be done with the installation Of OBIEE 11g product [in 10g it is manual process]
• Scheduler tables by default created in a schema DEV_BIPLATFORM with installation of RCU(Repository Creation Utility).

2) Scheduler tables are

• S_NQ_JOB
• S_NQ_JOB_PARAM
• S_NQ_INSTANCE
• S_NQ_ERR_MSG

3) Creating delivery device:
Device will be created by each & every user his own

Process:
1) Login to Analytics by using weblogic user
2) Click on weblogic --> click on my account --> click on delivery options --> Click
on Create Device --> name : Testing --> Device Type : HTML
Email --> Adress/Number xyz@gmail.com --> Click on ok

4) Develop a Agent:

Process:
click on New --> under Actionable Intelligence --> click on Agent --> click on
Schedule tab --> select frequency as once --> click on select date & time --> give
10 mins forward from current time --> select Delivery Content tab --> select
Analysis --> click on browse --> select agent report --> click on ok --> Save into
Shared Folder folder with the name of first agent

Other Related Posts:
Find the Status of the iBot Develiry through Back-End Table


-Asifkhan P.

Configure Write Back In OBIEE 11g

  Write back is a feature that allows users to type values into the dashboard and then copy them back to the underlying source database.

Follow the steps to enable it in your environment......

Step1: Enable Writeback at system level.

Add the following line between the tags <WebConfig> and </WebConfig> in the instanceconfig.xml located under $INSTANCE_HOME/config/OracleBIPresentationServicesComponent/coreapplication_obips1:

<LightWriteback>true</LightWriteback>

Step2: Create Template for write back.

Create your own xml for your report and save the file in below location with any name with .xml format.
Path: $INSTANCE_HOME\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

Sample File :  emp.xml

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
   <WebMessageTable lang="en-us" system="WriteBack" table="Messages">
      <WebMessage name="SetWriteBackValue">
         <XML>
            <writeBack connectionPool="OBIEEDB_POOL">
              <update>UPDATE SCOTT.EMP SET SAL=@2 WHERE ENAME='@1'</update>
    </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>


Note: OBIEE 11.1.1.5 and above versions already has a template SQL for write back under above location with name writebacktemplate.xml. 


Step 3 : Give the permissions
check and give the write back permissions to the user in Analytics->Administration> Manage Privilege as shown below:

Step 4:  Enable Writable option at logical column level.

Step 5 : Allow Execute Direct Database Requests to the user.

Step 6 : Bounce your OBIPS and OBIS to reflect your changes.

Step 7 :  Now create report with ENAME, SAL columns and go to SAL column properties --> Write Back tab --> and select Enable Write Back check box.

Step 8 :  Finally edit table view properties and check the tab 'Enable write back'.In the template name field,make sure that you have provided the SetWriteBackValue  as documented in WebMessage Tag in emp.xml file

Step 9 :  Then click Update , modify values and click Apply  button. That set, updated values should have gone to database , if you have done above steps correctly :).

Other Related Posts : 
Write-Back Limitations in OBIEE 11G
OBIEE 11g: Writeback Done Button Does Not Refresh Data Displayed


-Asifkhan P.

OBIEE 11g: Writeback Done Button Does Not Refresh Data Displayed

Today I faced this issue in 11g....

After implementing writeback, data is not refreshing at front-end (i.e. report or dashboard), but data is updated in database. To get updated data, we need to purge OBIPS and OBIS cache.

This is a bug in OBIEE 11g, here is metalink prof....


APPLIES TO:

Business Intelligence Server Enterprise Edition - Version: 11.1.1.5.0 [1308] and later   [Release: 11g and later ]
Business Intelligence Suite Enterprise Edition - Version: 11.1.1.5.0 [1308] and later    [Release: 11g and later]
Information in this document applies to any platform.

SYMPTOMS

In OBIEE 10g, after you click on the Update button you could see the results of the writeback in the dashboard. Now, in 11g it does not seem to refresh. You can see the new data in the database but not in the dashboard.

CAUSE
This is expected behavior in 11g. In 11g dashboards do partial refresh because it's more efficient. 

SOLUTION
A dashboard page refresh (from top right Page Options) refreshes all the dashboard's content and shows the new data. 

There is an enhancement request to re-introduce the functionality of OBIEE 10g.  For more information see Bug 13058029 - write back auto-refresh in 11g.

Other Related Posts : 
Configure Write Back In OBIEE 11g
Write-Back Limitations in OBIEE 11G

-Asifkhan P.

ID Column (Or Double Column) in OBIEE 11g

With OBIEE11g we have the ability to assign ID columns for descriptive columns more commonly known as Double Columns. This feature has following advantages

1. In BI EE 10g, there was no automated way of filtering on ID’s when end users chose the description values in the prompts. The Double column feature provides this ability in 11g.
2. In many implementations where data is captured in multiple languages, the descriptions might be stored in different languages. But the filtering of data will be on ID’s (which will be the same across languages). Double column feature provides that ability now.
3. Descriptor ID feature is also very useful when you want data to be grouped by code along with name in report without selecting the code in report. 
4. This can be used to avoid summing up of data under single name value where same name belongs to different codes.

-Asifkhan P.

Implicit Fact Column

The implicit fact column is used for queries that only select dimension attributes (no facts) from two or more dimensions. This setting will guide Oralce BI server in case of multiple paths.

A User may request a report where it may have only Dimensions and no Fact columns such as No of Customers in an Year or Product Category and Product Description. When these are requested the server may sometimes gets confused as from which fact table should it join to fetch the Data. So it joins to nearest fact table and pulls the data through it. So the result obtained may be different from what user is expecting.

So, to avoid this sort of confusion  We can create in a Dummy Fact table in a Physical Layer and Join all Dimensions to this Fact table in BMM Layer. Then in the Presentation Catalog we assign dummy fact table column as implicit fact column . So, Whenever a report on Dimensions are requested it joins through dummy fact table and fetch data.

To set an implicit fact column:

•   In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
•   In the general tab, find the Implicit Fact Column section.
•   Click on Set… button, this will open the browse window to select a fact column from the fact tables available in the presentation catalog.
•   Select the column.
•   Click OK.
•   Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
•   Click OK.
•   Save your changes in the repository.

Screen Shot ...

-Asifkhan P.

LTS Priority Ordering in OBIEE 11g

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have user queries that can be fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is "expensive," while access to a data warehouse is "cheap." In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Priority Group	Designation
0	the highest-priority source (the default)
1	a lowest-priority source

-Asifkhan P.

Content Level of LTS in obiee

To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model. Therefore, you need to define aggregation content for each logical table source of a fact table. 

The aggregation content rule defines at what level of granularity the data is stored in this fact table. For each dimension that relates to this fact logical table, define the level of granularity, making sure that every related dimension is defined.

Steps:

1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical table source.
2. In the Logical Table Source dialog, click the Content tab. 
3. If a logical source is an aggregate table and you have defined logical dimensions, select Logical Level from the Aggregation content, group-by list. Then, in the Logical Level list, select the appropriate level for each logical dimension table to which the logical fact table is joined.

-Asifkhan P.

Row-Wise Initialization

Row-Wise Initialization

You can use the row-wise initialization option to create session variables dynamically and set their values when a session begins. The names and values of the session variables reside in an external database that you access through a connection pool. The variables receive their values from the initialization string that you type in the Initialization Block dialog.

For example, suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. The table contains three columns:

• USERID, containing values that represent the unique identifiers of the users
• NAME, containing values that represent session variable names
• VALUE, containing values that represent session variable values

Sample Session Variables Database Table

USERID	NAME	VALUE
JOHN	LEVEL	4
JOHN	STATUS	FULL-TIME
JANE	LEVEL	8
JANE	STATUS	FULL-TIME
JANE	GRADE	AAA

To use row-wise initialization, create an initialization block and select the Row-wise initialization option. For this example, you would provide the following SQL statement for the initialization string:

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

Note that NQ_SESSION.USERID has already been initialized using another initialization block.

The following session variables would be created:

• When John connects to the Oracle BI Server, his session contains two session variables from row-wise initialization: LEVEL, containing the value 4, and STATUS, containing the value FULL_TIME.
• When Jane connects to the Oracle BI Server, her session contains three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; andGRADE, containing the value AAA.

Initializing a Variable with a List of Values

You can also use the row-wise initialization option to initialize a variable with a list of values. You can then use the SQL IN operator to test for values in a specified list.

For example, using the table values in the previous example, you would type the following SQL statement for the initialization string:

SELECT 'LIST_OF_USERS', USERID
FROM RW_SESSION_VARS
WHERE NAME='STATUS' AND VALUE='FULL-TIME'

This SQL statement populates the variable LIST_OF_USERS with a list, separated by colons, of the values JOHN and JANE (for example, JOHN:JANE). You can then use this variable in a filter, as shown in the following WHERE clause:

WHERE TABLE.USER_NAME = valueof(NQ_SESSION.LIST_OF_USERS)

The variable LIST_OF_USERS contains a list of values, that is, one or more values. This logical WHERE clause expands into a physical IN clause, as shown in the following statement:

WHERE TABLE.USER_NAME IN ('JOHN', 'JANE')

Note : This information is from Oracle documentation, Thanks to Oracle!!

-Asifkhan P.

Difference between Alias Table vs Duplicate Table

Today my colleague asked me "what is difference between Alias Table and Duplicate Table?"

Here is the answer.....

Alias Table	Duplicate Table
Its a mirror image of the Parent Table	Its a Copy of the Parent Table
We can't edit or modify Alias Table	We can edit/modify/delete columns in Duplicate table
Changes made in Parent Table is automatically reflected in Alias table	Changes made in Parent table are not reflected in Duplicate table
Used : When there is a need to avoid self-join or circular join	Used when there is a need to use few columns of Parent table.

Its always good to use an Alias table, as it is more convenient in maintaining the RPD .


-Asifkhan P.