Friday, September 26, 2014

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 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:
CommandDescription
CREATECreates a new table, a view of a table, or other object in database
ALTERModifies an existing database object, such as a table.
DROPDeletes an entire table, a view of a table or other object in the database.
DML - Data Manipulation Language:
CommandDescription
SELECTRetrieves certain records from one or more tables
INSERTCreates a record
UPDATEModifies records
DELETEDeletes records
DCL - Data Control Language:
CommandDescription
GRANTGives a privilege to user
REVOKETakes back privileges granted from user

Monday, September 15, 2014

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']



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

Friday, September 12, 2014

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.

Thursday, September 11, 2014

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

Wednesday, September 10, 2014

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.

Monday, September 8, 2014

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.

Thursday, September 4, 2014

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