OBIEE 11g: Save Current Customization feature

We often hear complaints from users saying they have to enter to many values before they could run the report and get their desired data set. This is more in cases where we have more Column prompts on dashboards. Oracle has once again saved us by providing a feature which exactly serves the need in this scenarios "Save Current customization" .This allow users to save and view dashboard pages in their current state with their most frequently used choices for items such as filters, prompts, column sorts, drills in analyses, and section expansion and collapse. By saving customizations, users need not make these choices manually each time that they access the dashboard page.

There are two levels at which Save Current Customization can be created.

• Create Save Current Customization for Individual.
• Create Save Current Customization for Group of Users

Create Save Current Customization for Individual.


Click on Page Options – Save Current Customization.


Check on “Me – Use this option to save the customization for your own personal use and If you would like to make this dashboard page as default with this customization, check on “Make this my default for this page”
Click OK.

Create Save Current Customization for Group of Users

Click on Page Options – Save Current Customization.


Select on the others – Use this option to save the customization for use by others. Set Permissions and include the Group of Users\LDAP Groups. If you would like to make this dashboard page as default with this customization, check on “Make this my default for this page”


Click on + sign to open the window.

Click on Search to get all LDAP groups list and Application roles.
Select appropriate LDAP and application roles and Click OK.
This customization will save for all users’ who are part of the selected LDAP groups.

Save Current Customization will be very helpful if there are different requirements to analyze the same report by different users.

If you are using the customization as Default by selecting the option “Make this my default for this page”. The Customization will be the default for this dashboard page. If you are not using the default option, the saved customization will be available in “Apply Saved Customization”. To delete the Save customization, use the “Edit Saved Customization”.

OBIEE 11g: Master Detail view and Limitations

Master detail linking of views enables you to establish a relationship between two or more views such that one view, called the master view, drives data changes in one or more other views, called detail views.

For example, If you have the following two views: A table that shows Revenue by Brand and A graph that shows Revenue by Product Category with Brand on the graph Prompt section.
Using the master detail linking functionality, you can link the two views so that when you click a particular Brand in the table, the Product Category on the Prompt section of the graph as well as the data in the graph changes to reflect the Brand that was clicked on the table.

Master Views: A master view is one which, primary interaction value in the column properties have set to Send Master-Detail Events using a channel name.

The following types of views can be master views: Graph, Funnel graph, Gauge, Map, Pivot table & Table.
The following types of views can be detail views: Graph, Funnel graph, Gauge, Table & Pivot table

Let us see How Master Detail Linking Works:

Let us take an example and select few columns including Brand, Revenue and Product Category.


Go to the Revenue Column properties and click on Interaction.


Select Primary Interaction “Send Master-Detail Events” and specify the Channel: Channel01. This channel name should match with channel name which we will update in the graph.

Create a simple table view or pivot view to show the Revenue by Brand.


Create a simple Graph view as below


Select on the graph properties


Check on the “Listen to Master-Detail Events” and provide the same channel name as given in Revenue column. This channel name will link the master view. Ensure to have the Brand column either on Graph prompts or on Section as Slider.

Add these two views into the compound layout.


When you click on any of the Brand Revenue Value, the Graph view will automatically change the view to bring the Product Category Revenue details for the Brand value clicked.

Note:

1.Master View:

• Master column cannot be displayed on the prompt section or on the section slider in the master view. It must be displayed in the body of the view. In our example, the revenue column is the master view and it is present in the body of the view.

2.Detail View:

• Can listen to master-detail events from multiple master views
• Can be in the same analysis as the master view or in a different analysis
• Cannot act as a master to another view

OBIEE 11g: Changing the RCU Schema to a new database

We are all well aware that before installing OBIEE11g, it is mandatory to install RCU because it creates repository metadata into the database. For some reason we might want to change the metadata repository to some other database,the question here is can we ?...Well yes, we can do it.

Say we have a  System 1 – OBIEE11g and database (orcl1) with RCU metadata repository and a System 2 – Database (orcl2).Now we want to move the RCU from orcl1 to orcl2  

Most importantly we can change the RCU repository metadata from one database to another database without impacting the current installed OBIEE11g environment.

Steps:
Firstly, ensure the RCU Repository metadata is created in the new database.

Logon to Weblogic Server Admin Console
Click on services


Click on Data sources



Click on bip_datasource and click on connection pool

Click on “Lock & Edit” button


Provide the new database details like host name, instance and password as shown above.

Do the same for all four data sources (EMPsystemregistry, mds-owsm, rtd_datasource), save and activate changes.

Logon to Enterprise Manager



Go to Business intelligence folder – coreapplication – deployment – scheduler.

Provide the new database details, apply and activate the changes.

Restart the services and run the analysis (analytics).

Note: The same steps can be followed when there is a name change of RCU database or hostname.

OBIEE 11g: The connection has failed while importing Data

The most common error when we ar ein initial stages of setting up the OBIEE 11g environment and started developing “The connection has failed” error especially while trying to import data from the database using OCI call interface in RPD, there must be nly one reason the BI server is not able to identify the connections using OCI. Just follow the steps to ensure it has all the information needed.

1. Add the tnsnames.ora file in the BI Home Directory

Add all the data source tns entry details in the tnsnames.ora file
Copy the tnsnames.ora file from Oracle Home (Root directory: \App\User\Product\Network\Admin) and paste the file to the below location in OBIEE11g directory
Root directory: \OBIEE11g\Oracle_BI1\network\admin and
Root directory: \OBIEE11g\oracle_common\admin

2. Add the tnsnames file location path in User.cmd file

Add the tnsnames file location path from one of the above location in the user.cmd file. The user.cmd file is found in the below location
Root directory: \OBIEE11g\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup



If you are still facing the issue please reopen the Admin tool and try, if it doesnt workout then try restarting the complete services.

OBIEE 11g: Page number display format

When we print from answers by default the page number under print options header and footer is @{pageNumber}, which will provide Page X format but if you need Page X of Y Pages, you will need to use the below format.

@{pageNumber} of @{numberOfPages}

OBIEE 11g: Interactions at Analysis Level

OBIEE 11g is truly user friendly this is weel known and we will now see one among the reasons, We are aware that we have analysis interactions when we right click ,but the beauty is you can specify which interactions are available when users right click in a pivot table, table, treemap, or trellis view at runtime. Not all right-click interactions are available for each view type. as a developer we can specify which right click interactions are available for an analysis. Following are the interactions available when user right click on the data at the run time in the dashboard reports.

Analysis editor->Edit Analysis properties->Interactions



Run time right click interactions on dashboard reports as shown below.



Customize the interactions based on the business requirements. If they need only one or few like below

The report will only show the Sort option when the user right click on the value as below.




There are additional interactions available for presentation hierarchy columns like expand, collapse etc.

OBIEE Setting Data Format for Dates

Most of the times when we are dealing with dates we end up in a situation where we have to decide whether to go for a date or date time. For sure you know the situation having a date field like Order Date which provides date information in Date Time format. In such a case the user should be able to decide if Date or Date Time format is used, because both possibilities may apply.

                                                                        

Thus, your OBIEE application will provide those date fields in Date Time format. Otherwise users won't be able to access the time information. Date data format for reports not needing time information needs to be changed via Column Properties.

When implementing reports for users using Oracle BI in different languages you will lose locale settings if you select a specific date format, e.g. MM/DD/YYYY  or any other from the date format list below.



To avoid lose of locale setting select Custom and insert as Custom Date Format:

[FMT:dateShort]

Tip: If you just want to see time information use [FMT:time]. Run your report, go to My Account, change the locate and reload OBIEE analysis results. Date format appears in locale selected.

Inline Prompts in OBIEE 11g

In recent times I have come across a question from beginners asking to explain how a in-line prompt can be applied in OBIEE 11g. I wondered how they got to know about in-line prompt where its usage is very limited to few reports and people don't talk about them very often.

However it does exist in OBIEE and has the same functionality as of a dashboard prompt except that it cannot be used as a dashboard prompt and is limited to the report in which it is created.

This is very simple to understand if you can create one and see for yourself. A dashboard prompt is a stand alone object and can be used independently anywhere in the answers and can be used for any report with the columns same as in prompt.

Whereas an in-line prompt is specific to the report and works only on the report and nowhere else. When you run the report with in-line prompt first the prompt is displayed and after the selection the result.

What is SQL

SQL - Structured Query Language is a language of database, simply put its the language which a database can understand. SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

• MS SQL Server using T-SQL, 
• Oracle using PL/SQL, 
• MS Access version of SQL is called JET SQL (native format) etc. 

Why SQL? 

• Allows users to access data in relational database management systems.
• Allows users to describe the data. 
• Allows users to define the data in database and manipulate that data. 
• Allows to embed within other languages using SQL modules, libraries & pre-compilers. 
• Allows users to create and drop databases and tables. 
• Allows users to create view, stored procedure, functions in a database. 
• Allows users to set permissions on tables, procedures, and views 

History:

• 1970 -- Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases. 
• 1974 -- Structured Query Language appeared. 
• 1978 -- IBM worked to develop Codd's ideas and released a product named System/R. 
• 1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle. 

SQL Process:

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.There are various components included in the process. These components are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files.

Following is a simple diagram showing SQL Architecture:
SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

Command	Description
SELECT	Retrieves certain records from one or more tables
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

FAILED_CONTACTING_OPMN Unrecognized oracle instance id OBIEE 11g

This error bumped in when we upgraded the OBIEE 11.1.1.7 to the latest patch 11.1.1.7.140225 ,the problem is simple, Presentation server is not coming up along with the scheduler.

All others are doing fine. When we checked out the logs came across this error

FAILED_CONTACTING_OPMN  Unrecognized oracle instance id: instance1 


We never got to the root cause but was able to find the workaround solution, since the error says it is not able to recognize the instance ,we thought of registering the instance once again. Luckily it worked for us.Steps to follow.

We can use the OPMN command line to register the instance. After registering, restart all the services.

$ORACLE_INSTANCE/bin/opmnctl registerinstance 

 [-adminHost hostname] 

 [-adminPort weblogic_port] 

 [-adminUsername weblogic_admin] 

 [-adminPasswordFile 'FILE_WITH_WEBLOGIC_ADMIN_PASSWORD']

for more details refer Managing Oracle Virtual Directory Server Processes - 11g Release 1 (11.1.1)

OBIEE 11g : What happens when a User Logs in ?

Did you ever wonder what is happening when a user logs in to OBIEE 11g , What exactly happens when he hits enter after entering credentials ,how the system components react to this action ,which component reacts first and what happens next, what is being invoked and what are getting retrieve and from where.  I used to have this question for a long time ,at last found the solution.

I just couldn't wait to share this....the picture speaks it all

OPSS - Oracle Platform Security Services

Hide Column Headings in OBIEE 11g

Sometimes there are some requirements where you have to hide the Column Headings, Now we will see how can we hide the headings with out doing anything unusual.


Just edit the View and go to the "Columns and Measures" section and go to the Layout Edit Properties. Uncheck or Deselect the "Display Heading" option.

This is simple option, to hide the heading with out doing any changes to the columns or their properties.

Set Log level and disable cache hit for single report in OBIEE 11g

Generally we do not have the Log Levels enabled in production environments because of the performance issues ,but when we are doing debugging we have the need for the query so we may have to enable log level for that particular report.

We could use the advanced tab to achieve this , just set the log level using the below line.

                  SET VARIABLE LOGLEVEL=5;

We may also have the need to disable cache hit. Use the below line for this

                 SET VARIABLE DISABLE_CACHE_HIT=1;
There will be time where we need to do both on same report ,then we can write as below

                 SET VARIABLE LOGLEVEL=5,DISABLE_CACHE_HIT=1;

Removing/Hiding Section borders in OBIEE 11g

This are always some requirements which want you to do some magic on the dashboard ...like hiding a section or a report for some purpose. The most common among these is removing the borders for a section.

This is a very straight forward requirement and also has a simple solution.

Add the display:hidden tag in the Css Style options in both the text boxes.

section properties -> Custom CSS style options ->  display:hidden

This will only disable the visibility of the borders ,this is not going to remove the border completely.

No Results Issue Post upgrade to OBIEE 11.1.1.6 from OBIEE 10.1.3.4

I like the way people having fun with upgrades and issues, because experience is what it matters at the end of the day. If you cant learn from your experience , at least learn from some one else's.

I came across a interesting thing because of my friend who just did a upgrade from 10.1.3.4 to 11.1.1.6 , other than the general issues like aggregation failures and formatting changes he came across an issue with some of the reports.

No Results even though its available in DB and the 10g version is getting things done just fine.

We have investigated and found its a BUG 14084943

Bug 14084943 - REQUEST FOR HELP : REPORTS SHOW NO RESULTS AFTER UPGRADE

However there is workaround provided by Oracle for temporarily fixing the issue. The cause of this BUG is due to a feature called "Grouping Sets" that has been bought in with 11g to increase performance.

To fix the issue modify the database features in Repository (RPD) physical layer to remove the feature GROUP_BY_GROUPING_SETS_SUPPORTED.

After disabling this feature, the SQL generated in 11g should be almost the same than the one from 10g. This is a temporary workaround and the bug has been logged to identify the root cause and provide a fix

OBIEE 11g Action Link Set on a Column and "Save As The System-wide Default" Is Not Working

This is most silly issue i have faced till date...i don't know how and why...but something that worked in previous versions is not working now. That is something which is very awkward,especially for a tool like OBIEE. Lets get to the point , this is related to action links.

I have created a action link on a column and selected the option "Save As The System-wide Default" and it doesn't work. The link is not saved and this feature is not working as expected.

This is working properly with 10g and earlier versions but now from 11.1.1.6 to 11.1.1.7.1 this is not working. Oracle identified this as bug.

Bug 13624013 - "SAVE AS DEFAULT" DOES NOT RETAIN ACTION LINKS

Interestingly it has been found working ok with the OBIEE 11.1.1.7.140715 Bundle Patch applied.

Hence, an upgrade to OBIEE Bundle Patch 11.1.1.7.140715 or later, should help to resolve the issue.

For latest Bundle Patches of OBIEE 11g please see:

Note 1488475.1 - OBIEE 11g: Required and Recommended Bundle Patches and Patch Sets


As the bug is marked as to be fixed in the Bundle Patch or version of OBIEE ,please test carefully once patch is applied.

Database properties for OBIEE installation

I came across a very interesting video about obiee installation, there is nothing new with the installation actually but there is something that has to be done before the installation that caught my attention.

setting the database with appropriate properties, as we all are aware that OBIEE is database driven so with out which there is no point in going further. Now lets see what i have learnt new in this aspect.

Firstly we should be aware that there are some prerequisites given below for the OBIEE installation w.r.t DB properties,

process 500
open_cursors 800
sessions 250

now lets see how to set these properties.Firstly we must connect to the database as a admin user ,generally sys and check the status of database using below command. Make sure you do this with Admin user ,if you don't have one then talk to your DBA and get this done.

select status from v$instance;

Now set the above parameters using the below commands

alter system set processes=500 scope=spfile;

alter system set open_cursors=800;

alter system set processes=500 scope=spfile;

And the final step is to restart the Database, which we can do using 

Startup force

Thats it youre done now go ahead with running the RCU against the database.

SQL Commands for Oracle Database

SQL commands are instructions which are used to communicate with the database to perform specific tasks, work, functions and queries with data.

SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) 

These commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML) 

These commands are used for storing, retrieving, modifying, and deleting data.
These Data Manipulation Language commands are:SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL) 

These commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL) 

These commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Go directly to Criteria Tab when Editing the Analysis - OBIEE 11g

Have you ever observed when you click edit on a analysis it takes you directly to results tab and from there we go to criteria to make the intended changes. I always hoped to have some kind of magic that takes me to Criteria tab directly instead of waiting for the results tab to load data even if its not necessary.

Oracle has at last bought in this feature with 11.1.1.6 on wards, The most important thing is you can choose what to happen and that too so easily from the provided drop down list.

To make the change just go to --> My Account --> Preferences --> Analysis Editor

Downgrading RPD version to support for Older version of Admin Tool

This is a very recent issue, one of my friend came across when he is doing some development activity with RPD. Lets get to the point directly,he has taken a RPD from Prod environment..which is 11.1.1.6.5 (V 320) to a development box where the installed client is 11.1.1.6(V 318).
As usual he tried opening the RPD with the Admin Tool , it threw an error as below

"[nQSError:36010] Server version 318 cannot read the newer version 320 of the repository"


Expected right , now lets see how this can be solved without changing the installation or having a new intsalltion which is always having Management problems.

The only way is to bring the RPD to a lower version, this can be done using nqgenoldverrpd.exe utility provided along with the installation.

This file is avilable in the below location

[ORACLE_HOME]\Oracle_BI1\bifoundation\server\bin

Syntax for this utility

-P (RPD Password)

-I  (original rpd file)

-O (RPD filename of the required version)

-V (Version you need)


Now lets say the RPD names are

 Original RPD file (V 320) - DemandPlanning_BI002.rpd
 RPD Filename of required Older version (V 318) - DemandPalnning_BI002oldver.rpd

so now lets write the command for this process of downgrading.

nqgenoldverrpd.exe -P Admin123 -I D:\DemandPlanning_BI002.rpd -O D:\DemandPalnning_BI002oldver.rpd -V 318

Last step is to run the command using the utility nqgenoldverrpd.exe,

Go to command prompt (Windows+R ==> type cmd ==> ENTER)

go to the nqgenoldverrpd.exe file location

[ORACLE_HOME]\Oracle_BI1\bifoundation\server\bin

paste the command and hit enter.....You're done.Now you will have a new RPD file with lower version in the specified location