DENSE_RANK as a filter in OBIEE

  When we are using other than OBIEE function in a filter, we  will be getting an error saying that, "ora-30483 window functions are not allowed here at OCI call" .

Here is the solution for DENSE_RANK in a filter.

RANK is the OBIEE funtion, so that we can use RANK in filter condition.
Criteria :

Result :

Query : Physical query for the above report from Mangae sessions as below

DENSE_RANK is not an OBIEE function, so that we use EVALUATE function.

Criteria : Get Rank1 & 2 results using DENSE_RANK

Result : 

Query : Physical query for the above report from Mangae sessions as below. Query also throws same error as Results, getting in OBIEE.

Solution : From the 1st criteria, we will get Rank 1 result. We want Rank 1 & 2. It is from 2nd criteria.

If we get the query same as 1st Query (RANK function physical query), then we can apply DENSE_RANK in a filter i.e DENSE_RANK filter should come as inner query but not in Having condition.

Apply both RANK and DENSE_RANK in a filter with "OR" operation. Issue resolved.

After applying both the filters with "OR" operation, then we can see the query same as RANK function.

Logic behind : When User want to see the TOPN results, there is no chance to enter 0 (Zero) . He should enter 1 or more than that. As we use RANK with "OR" operation, even though user enters 1 then RANK and DENSE_RANK gives the same result. 

Import data from EXCEL to Oracle table using PL/SQL Developer

After long time I am writing this article.

Here I am explain how we can import EXCEL data into Oracle table by using PL/SQL developer.
Here is the sample EXCEL sheet.

Now create a table with the columns, "EID","ENAME" and "Salary" in the database as below.

Choose "ODBC Importer" from Tools Menu. 

Select "Excel Files" from the drop down and click on "Connect". Now choose the Excel file and click "ok".

Note : Drives can be choose from the drop down as marked in the above image

Listed the all sheets in selected Excel file. Choose the sheet which having data. We can see preview of the data from "Result Preview".

Click on "Data to Oracle" tab and select "Owner" , "Table" from drop down.

In this case I created table "Test" under "SYS" owner. After selecting the table fields will be mapped automatically, because we created the columns withe same name of the excel columns. Check the preview and click on "Import".

Now Excel data transfer into Oracle table, "TEST".