OBIEE 11g: Priority Groups

OBIEE 11g: Priority Groups

Priority Groups is being used to prioritized the LTS to BI server while creating the SQL query. 

 

For example, lets consider a case where there are 2 aliases for the same dimension CHANNELS as shown below

In the business model and mapping layer, lets assume that both the LTS are mapped to all the columns. Both the LTS are at the same detail level. Now, lets assign a priority group of 0 for the Channel Alias – B LTS and a priority group of 1 for the Channel Alias – A LTS. Also the SALES LTS of the Fact table will also be assigned to the priority group 0. In 11g, the LTS with the least number is considered as the one that has to be chosen for querying (highest priority)

Now, lets create a report containing columns from Channels dimension and the Sales fact.

If you look at the query of the report, you will notice that Channels Alias – B will be chosen as it is the one having the highest priority.

Lets now go back to the repository and reassign the highest the priority to Channels Alias – A as shown below

For the same report, if you look at the query now, you will notice that Channels Alias – A will be chosen instead of the B alias.

Need and Ways for Performance Optimization in OBIEE

In all my years of working with OBIEE, I have seen a special interest in performance testing and tuning in the recent years. I have been involved in recent past with one of the active engagements for an OBIA system for performance optimization. This lead to a lot of interesting reading and some on the job application, thought should share it with you all.

While Optimizing Performance the most challenging task is to know where to begin. Most of the time Performance issues are raised or known only when we are actually in the performance test phase of the project. Post Analysis we should take the right approach for optimizing the performance and not some shortcut ways. If the analysis suggests that the rpd metadata / dashboard design/ built is the suspect or the underlying data model is not modelled properly optimization techniques are not going to work and redevelopment should be considered as it is rightly said

If a system that has a fundamental design flaw cannot be optimized - it has to be redesigned.

By default whenever OBIEE reports take a long time to return results, end users tend to say "OBIEE does not perform well". There might be different reasons for it other than OBIEE some of them might be the underlying database, network or infrastructure. We will discuss more about what we can do within OBIEE for performance optimization considering we have all other levers correctly placed.

OBIEE Reports creates a SQL query with the help of BI server and that is purely driven by how the data modelling is done is executed directly against the underlying database. A quick look at the SQL query getting generated can give us an idea whether best practices of OBIEE design are followed or not. By following the best practices in OBIEE design the SQL query generated can be made free of unnecessary clauses and nesting statements.

OBIEE itself has many out of box functionality for performance optimization such as usage of aggregate tables, using cache, changing the log levels and so on but are not enough to handle the performance issues.

The components involved in Performance Tuning are

·         Weblogic Server

·         BI Server

·         Presentation Server

·         Data Warehouse

With the help of monitoring tools like Enterprise Manager Metric Palette, Performance monitor, Server related statistics are collected and should be acted accordingly.

Would list some of the performance optimization techniques less known yet effective

·         Try Tuning the Connection backlog buffering and Statement Cache on Oracle Weblogic server.  Try adjusting the accept backlog value which might be set too low  due to which connections are dropped or refused at the client.

·         Oracle BI Server performance can be improved by prohibiting RPD updates. By doing this Oracle BI server is not needed to handle lock control.

·         Various configuration options can be used to limit the maximum number of rows that can be downloaded, included in mail or processed.

·         Limit the maximum number of rows that can be processed to render a table. This significantly improves performance as it reduces the system resources that will be consumed by user session.

·         Providing Permission to objects by groups/roles.

·         Union requests, consumes more memory as they generate more temp file , hence should be avoided.

·         Load to BI resources can be reduced by avoiding usage of guided navigation

·         Session variables use should be minimized.

·         Dimensional Hierarchy should be modelled accurately so that Oracle BI optimizer can select the most economical source

·         Content tab of logical table sources should be used to limit the number of rows returned from the database

·         Exploiting aggregate tables, pre-calculated measures aggregated over a set of  dimensional attributes are stored in aggregated tables

·         Make NULL and NOT Null column in sync with RPD and Database to avoid full outer join with NVL

·         Bitmap Index for all Foreign Key columns (for Summary reports) and Bitmap Join Index (lowest granularity based query for detailed data)

·         Usage tracking should be switched off if not needed as it will avoid extra DB Operations that run against each query.

·         Remove Snow flaking and Create Foreign keys for each dimension into Fact

I would like to emphasize that no single solution or configuration can take care of all performance issues.  Other than listed above there are many more settings / ways that helps in performance optimization, my aim was to list some of them covering highlights. Performance optimization might target Database, Environment parameters, Data model, Repository Design or Report Design having the same objective. While performance cannot be optimized by applying certain set rules and standards, having faced different challenging environment and experience helps..

It's all about working around and coming up with the best solutions for the project understanding the project flows and business scenario's. To have an optimized performance application we need to follow the best practices and design standards.

How to Pass custom parameters when Navigating in OBIEE

How to  pass custom parameters when Navigating in OBIEE

One of the most frequent challenges run into when designing reports is providing relevant drill downs. Sometimes it is not enough to provide just a hierarchical drilldown. You need to navigate to another report which might be coming from some other subject area to show all the details. In this post we will look into how we can use the action framework to navigate to any report and pass parameters from the main report to the drill down report.

The Action Framework was introduced in OBIEE 11g that provides functionality to invoke actions directly from within OBIEE. The Action Framework is a component of the Oracle BI EE architecture. It consists of the following items:

• Actions Web Services for creating and invoking actions that are deployed in the application server.
  
• Components that reside within the Presentation Server and Scheduler Services.
  
• Actions-specific JavaScript in the presentation tier for creating actions and invoking certain action types directly from the browser.
  

Actions are created and managed in the Oracle BI Presentation Services user interface and can be included within analyses, dashboards, agents, KPIs, and Scorecard objectives. There are several different types of actions, for example, Navigate to a BI Content or Web Page, Invoke a Web Service, and Invoke a Browser Script.
Some action types are automatically available when Oracle BI EE is installed, while others require specific configuration to make them One of the action types requiring additional configuration, you must provide information about the external systems hosting functionality to be invoked by the actions, including the location of the target functionality and access details.
One of the actions that is available out of the box is the “Invoke a Browser script” that provides a way to initialising a pre-defined javascript function stored in the server, retrieving it, adding parameters and finally executing it in the client’s browser. The flow of the this actions as documented by Oracle is

The following are steps required to setup a navigation using PortalPageNav
Define the custom function The list of Javascript functions that can be invoked is defined in the userscripts.js file located under $BIEE_INSTALL_LOCATION/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/b_mozilla/actions

Custom functions in the UserScripts.js file consist of a function to contain the actual code to be called when the action is invoked, and, optionally, an associated "publish" object used by the Action Framework to browse for the function when creating the action and for mapping values to the function parameters.

The Java script function to called the standard PortalPageNav function is defined as follows

USERSCRIPT.customPortalPageNav = function(arg_array){
        var str="parent.PortalPageNav(1";
        for(args in arg_array){
                var arg_name=args;
                var value=arg_array[arg_name];
                str+=",'"+value+"'";
                }
        str+=")";
        eval(str);
}

The example below shows the definition of a "publish" object that defines parameters for the Action Framework. All functions within the USERSCRIPT namespace that include a "publish" object can be browsed for selection when creating an action. The publish object defines the array of parameters to be passed by an action to the target JavaScript function. Note that functions that do not have an associated "publish" object may still be invoked by an action, but the Action Framework is not able to browse to these private functions, and therefore parameters need to be added to the action definition manually.

USERSCRIPT.customPortalPageNav.publish={parameters:[
        new USERSCRIPT.parameter("1","Dashboard","/shared/FOLDERNAME/_portal/DASHBOARDNAME"),
        new USERSCRIPT.parameter("2","Page","Insert your Page"),
        new USERSCRIPT.parameter("3","Table","Insert Table"),
        new USERSCRIPT.parameter("4","Column","Insert Column"),
        new USERSCRIPT.parameter("5","Value","Insert Value")]
};

Note: If you want to pass more than one parameter just keep repeating the following rows for the number of parameters you need to pass
        new USERSCRIPT.parameter("3","Table","Insert Table"),
        new USERSCRIPT.parameter("4","Column","Insert Column"),
        new USERSCRIPT.parameter("5","Value","Insert Value")]};
After restarting the OBIEE Services you should be able to see it in the list of supported functions when you select “Invoke a browser script”



Using the CustomPortalPageNav function To use the function in your report setup the action link as shown in the screenshot below 


Once setup correctly when navigating from the main report to the drill down report the columns values from the main report can be used to filter in the drill down report.
Summary:
There are several ways of achieving interactions ins a report such as hierarchical drill downs, master detail linking and navigation. OBIEE action framework is a generic way to navigate when the standard ways of navigate does not work for e.g. when using a UNION report or when drill navigating from source to target built from different subject areas.
Contact us find out more about how we can help you to get the most out of your investments in Oracle's Analytics platorm.

Apps Associates Interview Questions

1) How Aliasing helps in Data modelling?
2) What is a role playing dimension and non confirmed dimension?
3) What are pre-requisites for joining a physical table  directly with Logical Table?
4) How to use a variable in where clause in BMM Layer?
5) What are the pre-requisites for setting the content level in fact table?
6) What is a level based measure?
7) What is a parent child hierarchy and tell the example?
8) What is Master Detail Linking?
9) What is a Factless fact table?
10) Difference between Group by and Partition by?
11) What is ETL and ELT?
12) Architecture of OBIEE 11g?