Reverse function in obiee

Today,i came across with a scenario to reverse values in a column 

'abc' is one for a column where as in another column have to show 'cba'

for this i used DB REVERSE function using evaluate 

Syntax : EVALUATE('REVERSE(%1)', "Dim Customer".Customer_Name)

Rank and Dense Rank Functions:OBIEE

Yesterday, I was working on some issue and had to make use of Rank and Dense Rank in some reports in OBIEE. Here I am sharing the same with you as I think this might help some of you.

First of all lets understand what is Rank and Dense Rank. Let me explain the same with an example.

If in a class there are 5 students and in an exam 2 got 95 and other 2 got 93 and the last one got 91. So, using the Rank function will give you the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             3

 student 4     93             3

student 5      91             5

And if we make use of the Dense Rank Function it will give the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             2

 student 4     93             2

student 5      91             3

Hence, Dense Rank doesn’t skip the values and provides a continuous series of numbers as compared to Rank.

Now, lets see how can you write a query for the same on the DB.

Rank in DB

SELECT Student, Marks, Rank() over (PARTITION BY Marks order by student) as Rank  from Class

 

Dense Rank in DB

SELECT Student, Marks, Dense_Rank() over (PARTITION BY Marks order by student) as Dense_Rank  from Class

Now, lets understand how to implement the same in OBIEE.

Rank in OBIEE

OBIEE has an internal function called Rank and can be used directly for this. We can use the below syntax in the fx of the column:

RANK(“class”.”student” by “class”.”marks”)

  

Dense Rank in OBIEE

Since, Dense Rank is not an internal function of OBIEE we need to make use of the Evaluate function to use this. As explained in the previous posts also that Evaluate function is used when we have to make use of the DB functions in OBIEE. We can use the below syntax in the fx of the column:

EVALUATE(‘DENSE_RANK() OVER (PARTITION BY %2 ORDER BY %1)’AS INTEGER,”class”.”student”,”class”.”marks”)

The above two syntax show that how we can make use of Rank and Dense Rank functions in OBIEE.

Now, one important thing to note here is that we can make use of Rank as the filter in a report but we can’t use Dense Rank as the filter in a report. This is because Rank is an internal function to OBIEE and hence, when we make use of Rank as the filter in a report, OBIEE forms a subquery to calculate the Rank and filters the data in the outer query.

eg: RANK(class.student by class.marks) <=3

But, the same doesnt happen with Dense Rank. For Dense Rank OBIEE doesnt perpare and subquery and tries to use the entire Dense Rank expression in the where clause in the query which Oracle doesnt permits. It gives an ORA error stating that ‘Window Functions are not permitted’

I hope this will help you to understand the Rank and Dense Rank functions and their usage in OBIEE.

Rank and Dense Rank Functions:OBIEE

Yesterday, I was working on some issue and had to make use of Rank and Dense Rank in some reports in OBIEE. Here I am sharing the same with you as I think this might help some of you.

First of all lets understand what is Rank and Dense Rank. Let me explain the same with an example.

If in a class there are 5 students and in an exam 2 got 95 and other 2 got 93 and the last one got 91. So, using the Rank function will give you the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             3

 student 4     93             3

student 5      91             5

And if we make use of the Dense Rank Function it will give the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             2

 student 4     93             2

student 5      91             3

Hence, Dense Rank doesn’t skip the values and provides a continuous series of numbers as compared to Rank.

Now, lets see how can you write a query for the same on the DB.

Rank in DB

SELECT Student, Marks, Rank() over (PARTITION BY Marks order by student) as Rank  from Class

 

Dense Rank in DB

SELECT Student, Marks, Dense_Rank() over (PARTITION BY Marks order by student) as Dense_Rank  from Class

Now, lets understand how to implement the same in OBIEE.

Rank in OBIEE

OBIEE has an internal function called Rank and can be used directly for this. We can use the below syntax in the fx of the column:

RANK(“class”.”student” by “class”.”marks”)

  

Dense Rank in OBIEE

Since, Dense Rank is not an internal function of OBIEE we need to make use of the Evaluate function to use this. As explained in the previous posts also that Evaluate function is used when we have to make use of the DB functions in OBIEE. We can use the below syntax in the fx of the column:

EVALUATE(‘DENSE_RANK() OVER (PARTITION BY %2 ORDER BY %1)’AS INTEGER,”class”.”student”,”class”.”marks”)

The above two syntax show that how we can make use of Rank and Dense Rank functions in OBIEE.

Now, one important thing to note here is that we can make use of Rank as the filter in a report but we can’t use Dense Rank as the filter in a report. This is because Rank is an internal function to OBIEE and hence, when we make use of Rank as the filter in a report, OBIEE forms a subquery to calculate the Rank and filters the data in the outer query.

eg: RANK(class.student by class.marks) <=3

But, the same doesnt happen with Dense Rank. For Dense Rank OBIEE doesnt perpare and subquery and tries to use the entire Dense Rank expression in the where clause in the query which Oracle doesnt permits. It gives an ORA error stating that ‘Window Functions are not permitted’

I hope this will help you to understand the Rank and Dense Rank functions and their usage in OBIEE.

Calling Database function in OBIEE

Day before we came across a new requirement in which we had to call a function created in Oracle DB to OBIEE. Thought of sharing the same with you.

It is very simple if you know the trick. We made use of the EVALUATE function in OBIEE to call the function created in the Oracle DB.

Create a function in Oracle DB and use it in EVALUATE function as shown below:

EVAULATE(‘dbfunction_name(%1,%2)’, parameter list)

In the above expression %1& %2 are number of parameters to be passed to the DB function. Parameters can be more than or less than 2 depending on the requirement. These can also be constant values.

eg: EVALUATE(‘func1(%1)’,'TRADE_ID’)

So, the above expression will pass the TRADE_ID parameter to the DB function(func1) and hence give the result based on the output of the DB function.

One important thing to remember is we can’t call a stored procedure using the EVALUATE function.

We have also tried calling a stored proc using EVALUATE function but it didn’t work for us. If anybody has implemented the same kindly share the way to implement the same.

We can make use of this EVALUATE function for most of the DB functions and it finds a vast usage in OBIEE.

I hope this post will be helpful to you.

Oracle BI EE 10.1.3.3/2 – Executing Stored Procedures/Functions before Reports – Before Report Triggers and Global Temporary Tables

All users who have used BI Publisher or Oracle Reports would be aware of the fact that you can execute stored procedures as before report triggers and after report triggers. Lets look at a similar feature in BI EE today. Though i have discussed about this earlier in the VPD related blog entry, i just thought this in itself deserves a seperate blog entry since a lot of users seem to have this same question. The scenario for today is as shown below,

      

So, basically the idea is to execute a stored procedure just before running a report. This stored procedure would basically populate the temporary tables and the report would be rendered based on this data. In our case, we shall start with a simple example. We will be creating a Global Temporary table(GTT) which would be an exact copy of the EMP table of scott schema. We shall be creating a function to load the data from the EMP table to this GTT. And this GTT can retain the data for the session using PRESERVE ROWS clause. So, lets first start with a GTT as shown below

CREATE GLOBAL TEMPORARY TABLE EMP_GTT
(
EMPNO NUMBER,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ON COMMIT PRESERVE ROWS

As you see above, it has the same columns structure as the EMP table. Now lets create a simple function which will insert data from the EMP table to the above GTT table(remember this has no error handling, this is primarily for test purposes).

CREATE OR REPLACE FUNCTION LOAD_GTT RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMP_GTT(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
(
SELECT
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM EMP);

COMMIT;

RETURN ‘TRUE’;
END;

      

The PRAGMA AUTONOMOUS_TRANSACTION allows us to do DML operations within the function. Once this is done, go to BI EE Administration tool and import this GTT table. Also design the BM and Presentation Layer.

      

Now, double click on the Connection Pool and go to the Connection Scripts tab. In the Connection Scripts tab -> Execute before query, enter the below query

select load_gtt() from dual

      

Now, save this and go to the Answers and create a simple report. You would notice that the GTT would now have data since the function is executed first and then the report is rendered.

      

Also, if you check the logs, you would notice that the select statement that we included in the connection scripts tab would also have been fired. Do not worry about the order of appearance of the select statement in the logs. If you have any existing stored procedures then just create a function and call that procedure from this function.

      

Oracle BI EE 10.1.3.3/2 – Using IndexCol Function – Varying Underlying Columns based on User Login

One of the interesting features of BI EE is its ability to bring up different Business Model columns for a single Presentation Layer column based on different user logins. For example, say if you are creating the following report (all columns point to presentation layer columns),

GEOGRAPHY            SALES

you can specify which column in the BM/Physical layer should be used for the query in the place of GEOGRAPHY Presentation column (CEO’s can see region level data. Manager’s can see country level data etc), based on a user login. This can be done by using a simple function called as IndexCol. Before moving on to the syntax of this function lets try to understand what we are trying to do in the example below. Our aim is to create a report like the one below.

      

Now, whenever the user Region logs in, he must get a report containing the AMOUNT for all the regions (GEOGRAPHY_LEVEL will get the data from the column REGION_NAME) like the one below.

      

And, whenever the user Country logs in, for the same report he must get the data containing the AMOUNT for all the countries( GEOGRAPHY_LEVEL will get the data from the column COUNTRY_NAME ) like the one below.

      

Now lets look at the IndexCol function which forms the basis of what we are trying to do. Syntax of this function is

SYNTAX:      IndexCol( integer literal, expr1, expr2, … )

   Where
         Integer Literal can take integral values like 0,1,2 etc and
         expr can have different BM Columns.

What this function basically does is, it dynamically chooses different columns based on the Integer Literal value. Lets consider the example below

       INDEXCOL( VALUEOF(NQ_SESSION.”LEVEL”), BISE1_TUTORIALWH.GEOGRAPHY.REGION_NAME, BISE1_TUTORIALWH.GEOGRAPHY.COUNTRY_NAME)

In the above function, the INDEXCOL function looks for the LEVEL session variable. If the value of this variable is 0, it will choose the REGION_NAME column. If the value is 1 then it will choose the COUNTRY_NAME column. So, to implement the above example, we shall create a simple table in the database which would have 2 columns as shown below

      

And lets populate it with the username values and the corresponding integer literal values. So, our idea is to populate the LEVEL variable from a init block. So, the user Region, when he logs in will set LEVEL to 0 and hence the REGION_NAME column will be used in the report. Similarly for user Country, when he logs, the variable will be set to 1 and hence the COUNTRY_NAME column will be chosen.

      

      

The LevelInit init block will set the LEVEL variable as shown below.

      

Once this is done, your report should reflect the 2 columns based on the user who has logged in.

As user Region

      

As user Country

      

The above is not the best of examples. But this should atleast give an idea of what IndexCol can do.

Oracle BI EE 10.1.3.3/2 – Varying Aggregation based on Levels – Analytic Functions Equivalence

I just learned about a new method of aggregation that is available in BI EE. Thanks to Kurt Wolf. It is pretty basic and this requirement comes up pretty often. Let us try to look into the requirement first. The requirement is to create a report that has the following columns

TOTAL_NAME REGION_NAME SUBREGION_NAME COUNTRY_NAME AMOUNT_BY_REGION

Well, the above columns are actually part of the GEOGRAPHY and SALES tables of the bise1_tutorialwh schema. In our report, AMOUNT_BY_REGION needs to have the summation of amount only till level Region_Name. In SQL terms, this can be achieved easily using analytic functions. The SQL would look like this

SELECT TOTAL_NAME, REGION_NAME, SUBREGION_NAME, COUNTRY_NAME, SUM(AMOUNT) OVER (PARTITION BY TOTAL_NAME, REGION_NAME ORDER BY SUBREGION_NAME, COUNTRY_NAME) AS AMOUNT_BY_REGION
FROM
GEOGRAPHY A,
SALES B
WHERE
A.DIMENSION_KEY = B.GEOGRAPHY

As you see above, our requirement could be easily achieved by using analytic functions. But question was, how do we do that using OBI EE without using analytic functions. Lets build the report first without the AMOUNT_BY_REGION column.

Now, lets add a new column and wrap the column using the function SUM(SALES.AMOUNT BY GEOGRAPHY.TOTAL_NAME, GEOGRAPHY.REGION_NAME).

And, lets look at the report now.

As you see, it is very simple but very powerful.

Oracle BI EE 10.1.3.3/2 – Variation of YTD function

Another interesting question came up in the forums last week. Though the question was pretty straight forward, i thought of blogging about it here since it involves the use of evaluate function. The question was similar to the one below. I have rephrased it for clarity sake.

“We are in the process of creating some YTD reports wherein the users can choose mutiple date ranges of the format ‘yyyymm’. So, if a user chooses ’200605′ and ’200708′, then we need to produce an output which would be sum of all the data(measure of the report) from ’200601′ to ’200604′ and ’200701′ and ’200707′”

So basically, it is a modification of a simple YTD report. But what makes this interesting is that the months that user chooses can vary across years, as shown above in the question. So if a user chooses ’200605′ and ’200708′ our output should be a summation of our desired measure across the first 5 months in 2006 and the first 7 months in 2008. Now lets start approaching this problem from a plain sql point of view. Lets create a sample table called Dateentry.

CREATE TABLE DATEENTRY(DATEFIELD VARCHAR2(6), MEASURE NUMBER);

For the sake of simplicity, i have specified the date fields as varchar. Also, note that we only have a month level granularity to keep it simple. To understand this better, i have the value of all the measures in the year 2005 as 100, 2006 as 200 and 2007 as 300. Now lets get the above report using a simple sql. The sql would look like this

SELECT DATEFIELD, MEASURE
from DATEENTRY a
where
EXISTS (SELECT DATEFIELD from DATEENTRY WHERE a.DATEFIELD < DATEFIELD and SUBSTR(DATEFIELD,1,4) = SUBSTR(a.DATEFIELD,1,4) and DATEFIELD in (’200605′,’200708′))

SELECT SUM(MEASURE)
from DATEENTRY a
where
EXISTS (SELECT DATEFIELD from DATEENTRY WHERE a.DATEFIELD < DATEFIELD and SUBSTR(DATEFIELD,1,4) = SUBSTR(a.DATEFIELD,1,4) and DATEFIELD in (’200605′,’200708′))

As you see above, this query basically involves a set by set comparison of the data and then applying filtering on top of that. Now lets see how to do this in OBI EE. Lets start with building a dashboard prompt. Remember, we cannot have a multi select dashboard prompt here since currently it cannot set presentation variables. We would have to go with a dashboard prompt wherein the users would have to enter the dates in comma seperated format.

      

In the dashboard prompt, we would set a presentation variable called “datefield”. Now lets create a function in the database which would basically use the above query to generate the sum of “Monthly YTD”. The function would look like this

CREATE OR REPLACE FUNCTION DATEFUN(FILTER IN VARCHAR2) RETURN NUMBER AS
v_sum number;
BEGIN
EXECUTE IMMEDIATE ‘SELECT SUM(MEASURE) from dateentry a
where
exists (select datefield from dateentry where a.datefield < datefield and SUBSTR(DATEFIELD,1,4) = SUBSTR(a.DATEFIELD,1,4) and datefield in ‘||’(‘||FILTER||’)'||’)’ into v_sum;
RETURN v_SUM;
end DATEFUN;

Now lets create a report which would basically call this function and pass the presentation variable “datefield” to this function. We will be using the EVALUATE function.

      

Remember, to encapsulate the presentation variable with IFNULL clause. If you do not, then you will receive some socket errors and strangely your BI Server would stop immediately. I am not sure whether this is a bug but the ifnull clause will make it work. Once this done, lets test it out by building a simple dashboard with the dashboard prompt and the report.

      

I am not sure whether this can be implemented out of the box using BI EE since it involves a set by set comparison. If any one has any other ideas, feel free to let me know.

Oracle BI EE 10.1.3.3 – Support for Native database Functions and Aggregates

I was going through the new features guide in OBI EE 10.1.3.3. I happened to notice one other killer feature that everyone would like(me included). OBI EE 10.1.3.3 now natively supports the use of database functions directly from answers or from Metadata Repository. It supports the use of both one-to-one functions and also aggregate database functions. So if you want to use database specific functions directly from within answers, you can do that to your heart’s content now  . Let’s see how to go about doing that here in this article. There are 3 types of functions that you can call within a database.

1.   Functions that just do some data modification. These are one to one functions. For example, substr, instr etc are all one to one functions.

   In order to call these functions from BI Answers, a new function called EVALUATE has been added to the function dictionary of OBI EE. This EVALUATE function would take your database specific function as its argument and can also pass specific columns into the function. Lets see the syntax of EVALUATE(from the docs).

Syntax: EVALUATE(‘DB_Function(%1)’, <Comma seperated Expressions>)

As you see above, the columns are passed into the database specific functions like parameters in DOS. If you have more than 1 parameter to your function then you can use arguments like %2, %3 etc depending on how many parameters that you pass. But i believe currently it does not support parameters of different data types.

      

In above report, i am creating a new column called dense rank that would basically do a dense rank and order by the Amount.

      

As you see above, FACT.Amount would be passed as %1 to the dense rank function of the oracle database.

      

One can also call user-defined functions directly from the database. For example, i have created a very simple function test1 that would basically do a summation of the Amount and Effort.

      

      

2.   Functions that do some aggregation. For example, one can call functions to do a group by on the resultant query like sum, count etc.

Syntax: EVALUATE_AGGR(‘DB_Aggregate_Function(%1)’, <comma separated Expressions>)

      

      

3.   Functions that return a boolean value.

Syntax: EVALUATE_PREDICATE(‘DB_Function(%1)’, <comma separated Expressions>)

These functions are used in the where clause of a function. These are evaluated and help in filtering the resultant recordset.

But be aware that this supports only sql functions. It still does not support MDX or MOLAP data sources. For more information one can get the details here