Sunday, 12 June 2011

OBIEE 11g Creating Simple and Self Sustaining KPIs

KPIs?

Key Performance Indicators (KPIs) help organisations understand how well they are performing in relation to their strategic goals and objectives. In the broadest sense, a KPI provides the most important performance information that enables organisations or their stakeholders to understand whether the organisation is on track or not.
A KPI represents the result of business measure evaluated against a target for that measure. It is very useful to assess the performance of the strategic objectives and initiatives that form levels of organizational strategy.
KPIs Characteristics?
KPI must have measurable values that usually vary with time; it can be evaluated against targets to determine score and performance status and can be compared overtime for trending purposes for to identify performance patterns
KPI and Scorecard are an integrated BI Components in OBIEE 11g. This blog is an attempt to describe creating simple and self sustaining KPIs by using OBIEE 11g.


Simple KPI

The task is to create a simple KPI, the problem description is as follows

-       Which compares ‘Billed Quantity’ vs. ‘Target Quantity’ with thresholds at 98%, 96% of target
-       Dimensionalized by Yr, Qtr, Month, Products and Offices
-       Create BI Navigation for 96% thresholds
Open KPI by clicking New and then KPI from the OBIEE 11g Dashboard.














Choose the subject area from the list, e.g. ‘Sample Sales’ out of box subject area which comes as default.
Set General Properties
Set Business Owner, which will have the privilege of overriding the status. Set the actual value to ‘Base Fact - > 2-Billed Quantity’ and target value to ‘Base Fact -> 6-Target Quantity’.
Check Enable Trending and select compare to prior ‘Per Name Month’ which sets the grain of the KPI which will be used to compute the trend. The tolerance of 2 % change has been set.









Set Dimensionality
Select the dimensionality of the KPI by choosing the Per Name Year, Per Name Quarter, Per Name Month, Product Hierarchy and Office Hierarchy









Set Thresholds and BI Navigation as Action Link
Set the thresholds to ‘6-Target Quantity * 0.98’ and ‘6-Target Quantity * 0.96’. This is to set conditional formatting to tell system to color anything from 0-96% to red, 96-98% of target to Yellow and over 98% to Green. Use appropriate icons for thresholds to display in KPI.
Add BI Navigation to ‘Target Quantity’ detail report for 96% threshold by using action link option.





 
Add related document  
Include name and location for the related documents.








To open the KPI, choose open from OBIEE home page and choose the KPI, the KPI output generated as below. The output has dimensional columns, actual, target, status, variance and % variance for build vs. target quantity.
















Self Sustaining KPI
In simple KPI design the actual and target values for KPIs exists in the database referenced by the subject area. These values can be calculated using formulas or by entering a literal values also.
To elaborate this further, a self sustaining KPI is defined as below.

-       Which create a KPI  that compares current years billed quantity against a target of last year(s) billed quantity plus 10%

Create duplicate of Simple KPI by going to the catalog browser/UI and highlighting Simple KPI, then choose Copy, the Paste it and Rename it as ‘Example Self Sustaining’
 Open KPI and edit the Target Value to read - > Ago (‘Base Fact. 2-Billed Quantity’,1) * 1.10  , this looks at last year billed quantity and add 10%.











This is called a self-sustaining KPI as it does not require any special target values to be set or specified in the database. This feature is very useful where target values are not fixed and not stored in database.











Oracle Scorecard is the next I would like to blog about.

Friday, 3 June 2011

OBIEE 11g Metadata Dictionary Configuration

This blog describes the configuration and implementation of the OBIEE 11g metadata dictionary on web logic server.

There are three major steps required to undertake for metadata dictionary configuration and implementation for OBIEE 11g.

·         Deploy an application with web logic admin console which provides access to a static directory (analyticsRes)
·         Generate the metadata dictionary with BI Administrator and put it under analyticsRes folde
·         Update instanceconfig.xml with subject area metadata tag

Deploy Static Directory

A directory can be exposed in Web Logic Server (WLS) by deploying it as a J2EE application. By default the installation sets up the following directory which is used for customization (skin, style, custom messages, images etc).  This directory is used to put the OBIEE metadata dictionary files and folders.

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/
coreapplication_obips1/analyticsRes

The following steps expose this directory to WLS:
Open Web logic console application, log in and select bifoundation_domain
For bifoundation_domain, navigate deployment area









Click the “Lock &Edit” button in top left to enable the install for the deployments.
Click the Install button to install a new web application.










In the Install Application Assistant dialog, provide the path

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/
coreapplication_obips1.

This gives the user an option to select analyticsRes as a valid application to deploy. Select analyticsRes and continue by clicking the Next button.














Use default settings on subsequent screens.













Select deployment target ‘AdminServer’ for application












Select the option to make the deployment accessible from specified location and then click finish.









Choose the analyticsRes application deployed and then start with ‘servicing all request’ option.









Generate Repository Metadata

Open the repository in Offline mode, Navigate Tools>Utilities and then select Generate Metadata Dictionary









Select a destination directory 










Navigate to your {OBIEE MetaData Dictionary}\ {repository_name}








Open NameIndex/TreeIndex in a browser to check the readability.








Copy repository metadata dictionary to /analyticsRes folder once readability is tested. 
Configuration File Changes
Open the file instanceconfig.xml and add following entries between <WebConfig> tags.
<SubjectAreaMetadata><DictionaryURLPrefix>http://hostname:7001/analyticsRes/</DictionaryURLPrefix> </SubjectAreaMetadata>
Testing of Metadata Dictionary Implementation

Log in as administrator in Answers, select the subject area











Click on the dictionary symbol:







Monday, 30 May 2011

Writeback for dashboard commentary for OBIEE 11g

This is a first-pass solution for a question that invariably gets asked by all customers to add the capability to for a user to store comments against reports for OBIEE 11g.  It creates a “discussion thread” on a dashboard page to allow users to add comments to a page.

The solution is basic and requires a couple of requests to be added to each dashboard page that will have commentary enabled. The report need to duplicate and modify for each page as the page name needs to be added as a parameter. A session variable might be an option to implement this solution generically.

Database Changes

Create a write back table to store comments in Oracle DB schema, I have used the ‘BISAMPLE’ schema.

CREATE TABLE "WRITEBACK"."DASHBOARDCOMMENTS" (   "COMMENT_TS" TIMESTAMP (6) DEFAULT sysdate, "USER_NAME" VARCHAR2(50 BYTE),"DASHBOARD_NAME" VARCHAR2(200 BYTE),"COMMENTS" VARCHAR2(4000 BYTE));

-- Single Null row required for the insert writeback

insert into DASHBOARDCOMMENTS(USER_NAME, DASHBOARD_NAME, COMMENTS)values (null,null,null); -- Single Null row required for the insert Writeback

Repository Changes

The repository changes in steps for the configuration to write back to dashboardcomments table are detailed as follows. These steps remain same as far as the configuration steps are concerned for any table to make it be used for “Writeback” option.

Open the “BI Administration” tool and then go to File >> Import Metadata

Provide connection information/ import ‘DASHBOARDCOMMENTS’, then disable the cache for the table

Make the table columns “writeable” of the table ‘DASHBOARDCOMMENTS’ in Business Model layer

Assign the Groups to the  “writeable” columns in Presentation layer

Configuration File Changes

Create a folder called “CustomMessages” in the path shown in the below picture, if the folder is not available, then put an XML message file with write back tags as below.


<?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="wb_comment">
- <XML>
- <writeBack connectionPool="Sample Relational Connection">
  <insert>INSERT INTO DASHBOARDCOMMENTS(USER_NAME,DASHBOARD_NAME,COMMENTS) VALUES (
'@1','@2','@3')</insert>
  <update>INSERT INTO DASHBOARDCOMMENTS(USER_NAME,DASHBOARD_NAME,COMMENTS) VALUES (
'@1','@2','@3')</update>
  </writeBack>
  </XML>
  </WebMessage>
  </WebMessageTable>
  </WebMessageTable> 
 
Add tag ‘LightWriteback” in instanceconfig.xml 

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
- <!--  Oracle Business Intelligence Presentation Services Configuration File -->
- <WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
- <ServerInstance>
  
<LightWriteback>true</LightWriteback>
- </ServerInstance>
  </WebConfig>

Write Back Access and Permissions

Set webcat permission for writeback for selected groups as below.















Commentary Answer Request

Create two reports, one report for data entry for comments while other one to display the comments entered by users.

Data Entry Report
Go to Criteria” tab and create a request for comment entry screen as below.

Change the User Name column formula to VALUEOF (NQ_SESSION.USER) and Dashboard Name to ‘2.1 Simple Demo Dashboard' ( Name of dashboard/Page)

Set the write back property of column as shown below and set table properties as shown below.
Create a report to display entered comments by users by simply adding column user name, comment and time stamp.








Expose both the reports on dashboard, it looks something as below. This allows to enter the commnets on report by user and read the comments added by all users.



















Sunday, 13 February 2011

Welcome

This blog is an attempt to share my experiences and interests in business intelligence , data warehouse and EPM areas which I have gained during last 10-12 years of delivery and consulting experience across many verticals.

Business Intelligence and Data-Warehousing
Solutions Architecture
Sibel Analytics /OBIEE 10g /11g

ETL
OBI Apps
Data Modelling

OFSAA, ORDM & OCDM
Data Mining

Technical Project Management