Category Archives: 11G

Different Tool tip for different column values in OBIEE11g/12c

In OBIEE 11g or 12c, we can add ToolTip to the report on columns. Last week i had a requirement to display different tooltips/text on every column value to users when user hovers on different row in that column of a report.
This is not an OBIEE feature. To get a solution to it, we can try using Javascript in Narrative view. But i tried a different solution.

Req: We have to display different text over different column values. For example below, we have to show Response values in ToolTip, when users hovers over the Product column. Responses can differ.

Step 1: So we created a Dummy KPI in RPD, called Responses and stored the different values (Good/Fair etc ) in it.
Step2: Now in Obiee Report, over the Product column made few changes:
     a) In the ‘Column Properties’ , under ‘Data Format’ tab, I am choosing HTML
     b) In column edit formula wrote below:

‘ || “Dim”.”Product” ||’

i) You can skip using HTML.      
ii) You can write a Case formula in place of Product column, ie column on which you want the hover text.
iii) For Numeric column value, use Cast function to convert it to Char().

Courtesy: Google


Creating Word / Tag Clouds in Obiee 11g

Like we discussed in my other blog over D3 visualizations in Obiee 11g, we have another data visualization technique called “word clouds”, that is used to graphically show word distributions in unstructured data sets through font sizes and orientations. For example, the word cloud shown below display the company names in different sizes/fonts.

It can be implemented in OBIEE11g reports, without using any extra help. We just have to create a dummy column using any logic to get dummy values ranging between 5 to 70 and then in Narrative View we can use this column in font size of Company column.

Try below steps:

  1. Add your column which needs to appear in Cloud, ie Product name
  2. Create a dummy column Font . Edit it and write any logic to get value range from 5 to 70 ( you can range your values depending on Font size required in Cloud) , like (count Of Transaction) * 5) / Max (count Of Transaction)  or if Char column is there, you can formula like (count(name)*1000 )/ 30 *9.5 or anything.
  3. Now edit column properties of Font column to set Data format as non decimal
  4. Now create 1 more dummy column as Color .Edit it to write formula as   Case when Font is between 1 to 10, then ‘Red’ when Font is between 11 to 30 then ‘Green’ etcc.
  5. Now go to results and see how table view appears. If everything seems fine, save this report. Now add a Narrative View to it to display the Company/Product name column,  like below and its done.
style= "font-size:@2pt; color:@3;"> 

Do try it.

How to calculate Week Start Date and Week End date using DAY_DT in Obiee 11g

In OBIEE 11g, many times we have to calculate on the fly, the various dates functions like Week_Start_date, Week_End_Date, Previous_Yr_Date etc,  just using the Date column. Few projects have Date Dimensions not properly structured to have all columns. So here we have the logical sql’s to calculate the same:
We can calculate the week start date using the below sql:
TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))
For Week End Date, use the below Sql:
TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))
DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.
Courtesy: Google 

New Features in OBIEE release:

 Some more new features have been introduced in latest OBIEE release:-

Analysis Enhancement:

1.     You can initiate a search within the subject area displayed in the subject area pane; the option is “Subject Areas Pane”.
2.     You can save columns to Presentation Catalog and reuse the same for other analysis.You can also change the properties of the new column in the Criteria tab but be aware that conditional formatting and conditional action links for the column are not saved to the catalog. Regular action links are saved for the column as well as the format properties.The saved column can be edited from the catalog pane and catalog page. All the changes that you make in the catalog are applied to every analysis that used this column. If you modify it inside a particular analysis, these changes are valid only for the analysis that you are working on.To be able to save new measures as columns you should have the Save Column privilege.
3. In this version we find a new new type of variables: the Global Variables. Global Variables can be defined in the context of an analysis and can be used in other analysis. Useful to do some intermediate reusable calculations.
4. Radio button and check box dashboards prompts can be showed horizontally. When you create a new dashboard prompt using check box or radio button under Options, you can select between horizontal or Vertical Layout appears under Options.

Export & Print options:

1.     In this version, you will find more options in the Print and Export Options dialog. You can select if you want to include charts, images and formatting or to specify column properties like the column width and to wrap the text in columns.

2.     Export menu no longer includes the Excel 2003 and PowerPoint 2003 options
3.     It is possible to set a limit to the number of rows downloading as CSV format

New RPD checkout option:

1.     If you need to check out an entire repository rather than using projects for multiuser development, you can use the Whole Rpd Checkout option

New Option in Administration Tool:

1.     Display Translation Key in presentation tree and Edit Presentation names options are now available in Administration Tool option for this release. Translation keys have been added to all presentation objects. The translation key is automatically populated and by default it matches the presentation object’s name; however, you can modify the translation key. When you externalize display names in the Presentation layer and run the Externalize String utility, the results contain the translation key. You can right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and choose Externalize Display Names > Generate Custom Names or Externalize Descriptions > Generate Custom Descriptions to externalize strings.

Report/Dashboard Changes:

1.     Once you are in editing dashboard, you might see the Advanced Page properties option that set the scope of parameters in cases of Go URL, Prompted URL and Navigate to BI content:

 2. In Tables, Pivot and similar you can see Column properties from the edit window as well.


Courtesy :  Google 🙂


Usage of Key Performance Indicators In OBIEE

To assess your business performance, you must define success via Key Performance Indicators (KPIs), benchmark metrics against which current performance can be measured.
OBIEE 11g allows you to define and customize KPIs, and to then integrate them into your dashboard. In this way you can assess current performance at a glance. Your KPI Watchlist can also be used to create analyses and agents that trigger based on certain threshold conditions and actions.

To create the KPIs, take the following steps:

1.    Log in to Oracle Business Intelligence Enterprise Edition 11g, using your username and password. From the home page, select New -> KPI from the common header area. When prompted to select a subject area, for example select any Sales/Revenue related.
2. The KPI Entry screen will then appear. This screen enables you to define the actual value and target value measures for the KPI, as well as an optional time offset to show performance as a trend.

3. Then, you specify the dimensionality of the KPI,by for example making it analyzable by two levels in the store hierarchy, and two in the time hierarchy or
you want users to be able to switch the product category for which the KPI is displayed but you want the year, 2010, to stay constant (or “pinned”). To do this, click the Add button on the Dimensionality screen, select the “Times”.”Year”attribute, and select 2010for Value. Then click Addagain to add the “Stores”.”Product” attribute column, but leave Value as Not Pinned. Click Next to proceed to the next step in the process: defining the thresholds. 
The values used for these dimensions can either be “pinned”,so that they stay static for this KPI, or
they can be dynamic, allowing the user to change them to vary their “point of view”. As well as using
regular  “attribute” columns to define these dimensions, you can also use complete hierarchies in the
form  of hierarchical columns from the semantic model presentation layer.

4. On the Thresholds screen, you can specify values that will determine whether the performance achieved warrants an OK, Warning, or Critical indication. In this example, performance will be considered OK if it is within 90 percent of the target and critical if it falls below 70 percent.


5.  Click Next to proceed to the next step, where you can define related documents for the KPI. This enables users to reference other analyses, external Web pages to support the KPI. For this example, however, there are no related documents, so click Finish to move to the final steps, where you can name and save the KPI.
 You can create more KPI in similar manner.

Displaying KPIs, Using a KPI Watchlist

Now that you have defined one or more KPIs, you can give users a means of displaying these on a dashboard through a KPI watchlist. To create a KPI watchlist, take the following steps:
1.    From the Oracle Business Intelligence home page, select New-> KPI Watchlist.
2.    From the Catalog view on the left-hand side of the screen, drag and drop the  KPIs you created previously onto the watchlist on the right. The selected KPIs will then be displayed in a grid format, showing the actual and target values, along with the variance and the percentage variance.
Courtesy : google 🙂

OBIEE Architecture Design based on type of Installation

OBIEE Architecture Design  based on type of Installation:

The Oracle Business Intelligence 11g logical architecture comprises a single integrated set of manageable components which can be installed and configured to work together on a single host or can be clustered across multiple hosts for performance and availability
3 Types of Installation are available:
Simple Install
Purpose: Demonstration and Evaluation Single-User Development
Definition: Installation with the default settings on a single computer in the minimum number of steps.
Managed Server is not installed. Mainly used for demonstration, or PoC
Enterprise Install
Purpose: Enterprise Deployment for Hosted Development and Production
Definition : Enterprise Install type enables you to specify several more configuration settings than the simple
Software Only Install
Purpose: Enterprise Deployment for Highest Levels of Availability and Security
Definition: Installation of the binary files in Middleware home and useful if you want to have multiple domains or products share a common Middleware Home

Oracle Business Intelligence Components :

Java Components (WebLogic Domain)
Deployed as JEE applications
To service SOAP, HTTP, and other forms of requests
System Components (BI Instance)
Deployed as server processes
Provide the core services that enable OBI
Other Domain Contents
Includes all the necessary software, metadata, configuration files, RPD files, Oracle BI Presentation Catalog, and connection and database configuration information that are
required to run an Oracle Business Intelligence system.
They are managed mainly by 2 administrative user interface :

WebLogic Server Administration Console — which provides advanced management for Weblogic, Java components (BI Publisher, MapViewer, …), and security. 
 Fusion Middleware Control (Enterprise Manager) — which is used to manage the system components (C++, JSE) (BI Server, BI Presentation Services, Scheduler, ….)

Oracle Business Intelligence Components:

Administration Server
Components for administering the system are :
Web Logic Server Administration Console
Monitoring the health and performance of JEE servers
Configuring WebLogic server domains
Stopping and starting JEE servers
Viewing JEE server logs
Managing users in the LDAP Server of the WebLogic Server
Fusion Middleware Control
Starting, stopping, and restarting all system components
Scaling out of system components
Managing performance and monitoring system metrics
Performing diagnostics and logging
JMX Mbeans
Provides programmatic access for managing a BI domain
Managed Server and Node Manager
Managed Server
Provides run-time environment for the Java-based services and applications within the system
Node Manager
Process management services for the Administration Server and Managed Server processes
Managed Server Components
Oracle BI Presentation Services Plug-in
Routes HTTP and SOAP requests to Oracle BI Presentation Services
Oracle BI Action Services
Web services required by the Action Framework
Oracle BI Security Services
Integration of the Oracle BI Server with the Oracle Fusion Middleware security platform
Oracle BI Publisher
Enterprise reporting solution for authoring, managing, and delivering all types of highly formatted documents
Oracle Real-Time Decisions (Oracle RTD)
Analytics software solutions allowing companies to make better decisions in real time
Oracle BI SOA Services
Invoke OBI functionality from Business Process Execution Language (BPEL) processes
System Components
System components are deployed as non-JEE components (C++ and J2SE)
Oracle BI Server
– Query and data access capabilities at the centre of OBI
– Provides services for accessing and managing the enterprise semantic model. Also RPD resides here
Oracle BI Presentation Services
– Framework and interface for the presentation of BI data to Web clients
Oracle BI Scheduler
– Scheduling for analyses to be delivered to users at specified times
Oracle BI JavaHost
– Supports various components such as Java tasks for Oracle BI Scheduler,Oracle BI Publisher, and graph generation
– Also enables Oracle BI Server query access to Hyperion Financial Management and
Oracle OLAP data sources
Oracle BI Cluster Controller
– Distributes requests to the BI Server
Ensuring requests are evenly load-balanced across all BI Server process in the BI Domain
Oracle Process Manager and Notification Server (OPMN)
Manage system components for Oracle Business Intelligence (for advanced users)
Supports both local and distributed process management
OPMN and Fusion Middleware Control
Fusion Middleware Control is the recommended approach for starting, stopping,
and viewing the status of components
OPMN is suitable only for advanced users
Basic Directory Structure of OBIEE :

WebLogic Server home
WebLogic Server home
Contains Java components
User Projects
contains product domains (including one or more Oracle Business Intelligence domains),
Administration Server, and one or more Managed Servers
Oracle BI Home
Contains all the binary files (read-only) that are specific to Oracle Business Intelligence
 Some Useful Directories of the BI Instance:

%Middleware Home%\instances\instance1\bifoundation\OracleBIServerComponent\
Web Catalog
%Middleware Home%\instances\instance1\bifoundation\OracleBIPresentationServerComponent\
%Middleware Home%\instances\instance1\config\OracleBIServerComponent\
%Middleware Home%\instances\instance1\config\OracleBIPresentationServerComponent\
BI Server Logs (nqserver.log, nqquery.log)
%Middleware Home%\instances\instance1\diagnostics\logs\OracleBIPresentationServerComponent\
See you next tym 🙂    (Courtesy : Google)