Create KPI's

OBIEE 11g - Creating Key Performance Indicators (KPI)

What is a KPI:

A KPI is a measurement of a particular business process which is compared against specific goals and objectives of an organization. KPI’s are typically tracked over time as a way to monitor performance and drive business decisions to help improve upon that performance. KPI’s typically have both a goal and a status that reflects performance relative to the goal. In addition, KPI’s normally are “dimensionalized” – meaning they can be analyzed by any number of defined dimension attributes associated with the KPI.

KPI in OBIEE 11g can be used an analysis dashboard to allow a user to view KPI performance and drill into the details to understand why a KPI value may be off target. KPI’s can also be used to automatically trigger alerts to specific users using OBIEE’s agent mechanism when KPI target values reach critical status levels.

OBIEE KPI’s also can be configured with another new feature – Action Links – which allow the user to initiate a action based on the status of the KPI. The action link can reference another analysis, send an email, kick off a workflow, or any other process to assist in making the KPI the basis for “actionable intelligence”.

How To Create a KPI:

One of the important new feature is Performance Management objects in the new version of OBIEE 11g. These kind of features were not implemented in the previous version.  I’m going to demonstrate how to create KPI object in OBIEE 11g. 

After you login to OBI, you’ll see create section on the left side. You’ll just click “Create a new KPI” link and it’s going to ask to select the subject area as usual. I use “Sample Sales Lite” subject area in my demo.

There are 4 parts for creating the KPI:

1- General

2- Dimensionality

3-Thresholds

4- Related Documents

In the “General Section” you’ll have to specify the “Business Owner”. Based on the KPI values, you’ll be able to contact with the “Business Owner”.  You must select one measure column for the “Actual Value” and another measure column for the “Target Value”. Just a simple example: Units Ordered and Units Shipped. Another option is “Trending”. If you’ve created a Time dimension in the repository, you’ll gain benefit of trending option. By using this option, you’ll be able to compare periods. 

In the dimensionality page, you’ll select the dimensions and levels that you want to focus on. And if you don’t want to select a value for the member, you just write “Not Pinned”.

In the thresholds page, you specify the threshold values. By default, you can specify only 2 values but it’s customizable. You can create more threshold values and also you can use custom images. 

In the last page named “Related Documents”, you just specify the links for additional documents, web pages or sample reports. This is optional but good have descriptions for the KPI.

Then you’ll save the KPI. 

After you save the KPI in the Presentation Catalog, you have several options to publish the KPI. Here are some examples:

• Pusblish in the dashboard.
• Use in Balanced Scorecard.
• Publish in the KPI watchlists.

Set Logging Levels

OBIEE 11G Set Up Query Logging

1 .	Return to the Administration Tool, which should still be open.
2 .	Select File > Open > Online to open the repository in online mode. You use online mode to view and modify a repository while it is loaded into the Oracle BI Server. The Oracle BI Server must be running to open a repository in online mode.
3 .	Enter BISAMPLE1 as the repository password and enter your administrative user name and password.
4 .	Click Open to open the repository in online mode.
5 .	Select Manage > Identity to open Identity Manager.
6 .	In the left pane, select BI Repository.
7 .	Select Action > Set Online User Filter.
8 .	Enter an asterisk and click OK to fetch users from the identity store.
9 .	In the right pane, double-click your administrative user to open the User dialog box. In this example the administrative user is weblogic.
10 .	In the User dialog box, on the User tab, set Logging level to 2.
11 .	Click OK to open the Check Out Objects dialog box.
12 .	In the Check Out Objects dialog box, click Check Out. When you are working in a repository open in online mode, you are prompted to check out objects when you attempt to perform various operations.
13 .	Select Action > Close to close Identity Manager.
14 .	Select File > Check In Changes. Alternatively, you can click the Check In Changes icon on the toolbar.
15 .	Save the repository. There is no need to check consistency.
16 .	Select File > Copy As to save a copy of the online repository with the security changes.
17 .	In the Save Copy As dialog box, save the file as BISAMPLE.rpd, replacing the existing BISAMPLE repository.
18 .	Click Yes when asked if you want to replace the existing BISAMPLE repository. This will create a new BISAMPLE repository with query logging set for the weblogic user.
19 .	Select File > Close to close the repository.
20 .	Click OK when you receive the following message: "In order for your online changes to take effect, you will have to manually restart each non-master Oracle BI Server instance in the cluster."
21 .	Leave the Administration Tool open.

Alias vs Duplicate

Alias vs Duplicate

A point of confusion sometimes arises over the difference between a Table Alias and a Duplicate Table.

The Alias Table

A Table Alias is created from a physical table, in the physical model of the repository, and any changes in the physical are immediately reflected in the alias. It is good practice to only use alias tables to build the Business model in the repository. You can rename your alias to be something more meaningful that will help other users understand it's purpose. Another use for alias tables is to resolve circular joins.

Creating an alias creates a copy of the table in metadata that will be referenced in SQL with that alias name. It will have its own ID, distinct from the parent table.

For example:
Parent Table Invoice -- id 1000
Alias Table CancelledInvoice -- id 2021

when the OBIEE engine writes the physical query it resolves the true table names, but looking at the query generated you will see:
.......
FROM INVOICE T1000 ,
INVOICE T2021 /*   CancelledInvoice  */
WHERE ...........

The Duplicate Table

Duplicate Tables on the other hand allow us to create the structure or template of a table, but at the database level you will find no physical structure. When creating an opaque view where the structure is the same as a table in the physical layer then we duplicate the table.

Using the Invoice example from above we can create a duplicate table in the physical layer. We will change the name of the table to CancelledInvoice and then the table type is set to "SELECT", and the details as "select * from Invoice where canc = 'Y'". This gives us an opaque view whic is available to be used but not deployed in the database.

Configure the Write-back in Connection Pool

OBIEE 11g Configure Writeback

Write Back

• It provides users of a dashboard page or an analysis with the ability to modify the data that they see in the table view. 
• It is the ability in OBIEE to allow the user to enter a value or values directly into a report and have those values written into the database.
• Values are written back to an actual physical table in the database.
• Only if the user has the “Write back to database privilege”, then the writeback fields in the reports will editable.

Steps to configure write back:
1.    Create a physical table with write back columns.
2.    Import the write back table.
3.    Enable write back for the connection pool.
4.    Enable write back for logical columns.
5.    Set the write back permission’s in the presentation layer.
6.    Enable write back in the instanceconfig.xml file.
7.    Create the write back template.
8.    Store the write back template.
9.    Grant write back privileges.
10. Create an analysis with columns enabled for write back.
11. Override the default data format.
12. Enable write back in the table view.
13. Verify results.

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml

Step 1: In the Physical Layer

• Select a table to which you would like to writeback (for now we are doing product)
• Right click
• Check “Override Source Table caching properties” in order to uncache.

Step 2: In the BMM Layer

• Go to the table column (same as the physical layer)
• Double click column to open the logical column properties (Product Column)

•  Under General Tab – check Writeable

Step 3: In the Presentation Layer

• Double click the column product and click on Permissions in the General Tab
• Apply Read/Write to a user

• Drag the product key to the product table in the presentation layer.

(Note: Product key is necessary in order to writeback since we are making changes in the product column)

Step 4: Give Permission to the User/Application Roles

• On the top page Click Manage – Identity

• Click on Application Roles on the right pane and then double click on the User (In our case we have   BIAdministrator)

• Click on permissions and then the Query limits tab
• Scroll to the end right and in the Execute Direct Database Request drop down and make it to ALLOW

• Save the repository and click Yes, on Check Global consistency.

Step 5: Make Changes in XML

• Open the instanceconfig.xml from the following path

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\ instanceconfig.xml

Add:< LightWriteback>true</LightWriteback>

To the XML file between< ServerInstance>

Step 6: Go to OBIEE

• Click on Administration
• Under Security click on Manage Privileges
• Scroll all the way down to Write Back , Under Write back to database click Authenticated User

• On the right pane Permission drop down and select Granted.
• Similarly,  under Manage Write Back click BI Administrator Role
• On the right pane Permission drop down and select Granted

Deploy the rpd since changes were made.

Step 7: OBIEE Analysis

• Create Analysis on the same subject Area
• Under Products double click Product key,  Product and Revenue

In the Selected columns on the right pane you should have Product key, Product and Revenue columns

In Criteria, selected columns Click Product Column Properties and click Write Back tab

• And Check Write Back with Text Field Width n no. of columns.
• Click OK and then go the results tab
• In the compound layout Click on edit view and table view properties.
• Go the Write back tab and enable write back and Name the Template

Step 8: Create a xml file (writeback.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="testwriteback">

<XML>

<writeBack connectionPool="Connection Pool">

<insert></insert>

<update>

UPDATE SAMP_PRODUCTS_D SET PROD_DSC=’@2’ WHERE PROD_KEY=@1

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

</WebMessageTable>

</WebMessageTables>

OR

<?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="WriteBack">

<XML>

<writeBack connectionPool="Connection Pool">

<insert></insert>

<update>

UPDATE SAMP_PRODUCTS_D SET PROD_DSC = '@2' WHERE PROD_KEY = @1

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

We have selected 3 Columns in our analysis,

Product key, product and revenue.

Since product key is the first column it will be named as @1

Product as @2 and revenue as @3 respectively.

Save the XML as WriteBack.xml (Choose all files) and save it under

C:\BI11g\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

Go back to the analysis and rename any of the products and check in the sql developer.