Business Intelligence – Oracle

Archive for February, 2008

Oracle BI EE 10.1.3.3/2 – Varying Columns Dynamically using Session Variables

Posted by Venkatakrishnan J on February 29, 2008

If you had gone through my blog entry here, you would have known how to go about dynamically varying the columns using presentation variables and dashboard prompts. But say you have a scenario wherein you are integrating BI EE into an external application using GO URL, then the above procedure would not be valid since presentation variables would work only from dashboards. Now, lets look at an approach wherein we can dynamically vary the columns in a report using GO URL. This approach is based on yesterday’s blog entry here. Our idea is to create a simple report wherein there would be 2 columns. One column will be dynamic based on what we pass in the GO URL and the other would be static. In our example, we shall be making use of a session variable called DSN while shall be initialized to some value through an init block. Now, lets create a simple report with 2 columns. One column would have the below formula

CASE WHEN VALUEOF(NQ_SESSION.DSN) = ‘0’ THEN Products.Type WHEN VALUEOF(NQ_SESSION.DSN) = ‘1’ THEN Products.Brand ELSE Products.Color END

So, basically what this does is, it chooses the columns based on the value of the session variable. The value of the session variable will be varied from the GO URL.

The other column will be a static column (Sales).

      

      

Now, save this report. Ensure that you have added the Session variable to the instanceconfig.xml using the procedure here. Now, if you issue the below GO URLs the report columns will change automatically.

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=0

      

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=1

      

The disadvantage of this approach is that you will be losing the default hierarchy drills. You can still use Navigate to achieve the drills though.

Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.3.2 – Updating Session Variables through GO URL

Posted by Venkatakrishnan J on February 28, 2008

Another excellent feature that i had wanted to blog about for sometime was pointed out by Swapan of the BI EE forums today here. If you had gone through my previous blog entry here, i would have shown you how to go about updating the session variables from within Answers. Also, i had blogged about one of the usage of updating session variables yesterday here. Now, if you notice the actual update happens through the SET VARIABLE clause in the prefix of the Answers SQL. But say when you want to integrate BI Answers to an external application using GO URL, you would not be able to use presentation variables. So, we would be needing a capability to update the session variable via some URL parameters. If you are not sure what i mean, lets start with creating a simple session variable called DSN which would be set by an initblock via row-wise initialization.

      

Now, lets go to answers and create a simple report which would just display the session variable using the below formula

CASE WHEN 1=0 THEN “Dimension – Flag”.IGNORE_RATE_FLAG ELSE VALUEOF(NQ_SESSION.DSN) END

      

Now, open another browser and use the below GO URL to see the above report.

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report&Options=rmf

      

Now, if you notice, the value of the session variable remains constant (ORCL in my case). Now, the question is how do we make this value change by passing values through the GO URL. In order to do that, go to {OracleBIData}web and open the instanceconfig.xml using a text editor. Between the ServerInstance tags enter the below set of tags

 <Auth>
<UserIdPassword enabled=”true”>
<ParamList>
<Param name=”NQ_SESSION.DSN” source=”url” nameInSource=”DSN”/>
</ParamList>
</UserIdPassword>
</Auth>

Generally the Auth tag is used for SSO impersonation. But in this case, we are adding one more set of tags UserIdPassword which tell the BI Server that one is using normal authentication. Also, the Param name tag accepts the name of the actual Session variable (NQ_SESSION.DSN in our case). Source=”url” tells the BI Server that we are passing the value to this variable via URL and the name of the parameter would be DSN.

Save the file after entering the above. Restart presentation services. Now, use the below GO URL

http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report&Options=rmf&DSN=Test

Again, what we are doing in the GO URL is we are assigning Test as the value to the DSN variable which in turn assigns it to the DSN session variable. Now, you should see that the report value would have changed.

      

A very important feature and is not documented.

Posted in All Posts, OBI EE Plus | 11 Comments »

Oracle BI EE 10.1.3.3/2 – Varying Data Sources from Answers – Same Metadata different Data Sources – Using Presentation Variables and Session Variables

Posted by Venkatakrishnan J on February 27, 2008

Lets look at a pretty interesting requirement today. I had a call yesterday with one user who basically wanted to know whether there is any way in BI EE Where he can reuse the existing Metadata that he has created for some other data source. He also mentioned that this data source would also contain the same set of tables. So, basically the user had the same set of tables in different databases(only the data would change but the data structure would be constant). He wanted to know of a way wherein he could dynamically vary the data source name instead of recreating the metadata for the other data source. Also, he wanted the data source to be listed as a drop down in the dashboard prompt so that the reports would fetch dynamic data from different data sources depending on what is chosen in the dashboard prompt. Lets look at an approach to achieve this requirement. In our example, we shall be using the EMP table of the SCOTT schema in 2 different databases. Both these databases can be accessed using the TNSNAMES entry ORCL and ORCL1 respectively. For the sake of difference lets delete one row from the EMP table of the SCOTT schema in one database.

      

      

Now, lets import the EMP table from any one of the SCOTT schema and design the Business Model and Presentation Layers. Ensure that you are disabling cache for the EMP table.

      

Now, the next step is to create a seperate Data Source and a connection pool which would just be used for initializing Init Blocks.

      

The next step is to create a Session Init Block which will be populating a Variable called DSN using Row-Wise Initialization. This is just for Initialization purposes. In our case, we are initializing DSN variable to ORCL (the base tnsnames entry).

      

Now, go to the connection pool of the data source containing the EMP table. Make the data source name within the connection pool to point to the DSN variable using VALUEOF(NQ_SESSION.DSN).

      

Once this is done, go to answers and create a simple Dashboard prompt on Ename. In the Results on this prompt, use the following SQL. Also, make it to set a presentation variable called Test. Also, ensure that this has a default value of ORCL.

SELECT CASE WHEN 1=0 THEN Employee.EName ELSE ‘ORCL’ END FROM “SELECT BM” UNION ALL SELECT CASE WHEN 1=0 THEN Employee.EName ELSE ‘ORCL1’ END FROM “SELECT BM”

      

Now, create a simple report containing only ENAME as the column. Go to the Advanced Tab of this report and in the Prefix section, enter the below command

SET VARIABLE DSN=’@{Test}’;

      

      

Now save this report and go to dashboards. Include the prompt and the report that we created above. Now you can notice that as you change the DSN in the dashboard prompt the data would also change (SCOTT ename would be missing in one report).

      

      

Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.3/2 – Between Prompts for Date Columns – Using Presentation Variables

Posted by Venkatakrishnan J on February 26, 2008

I just saw another question in the forums today where the users wanted to know how to go about creating Date Prompts with “in between” operator. So, basically the user had a report containing one date column and he wanted to create a dashboard prompt on that date column which can pass 2 values for the “in between clause”. One of the possible approaches to achieve this is to create the dashboard prompt on this column with “in between” operator and have that same column as “is prompted” in the report. This will pass down both the values chosen in the dashboard prompt. But the main problem with this approach is that there is no way you can know the 2 values passed. For example, say you need a report containing Start Date, Sales and Number of Days where Number of Days is the number of days between the values chosen in the dashboard prompt, then using the first method this is not possible since there is no variable to refer to the 2 values. Now, lets look at an approach today to achieve this using Presentation Variables. This is a variation of the approach that we saw earlier here. Lets start with creating a dashboard prompt. This dashboard prompt shall contain 2 prompts on any column. For both the columns, in the formula tab enter the below formulas

CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END

CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test1’ END

We have used the above formula in order to protect the columns from getting the filters. This is very important for setting presentation variables and at the same time not affecting any columns
Now, for both the prompts convert the Results to SQL Results and enter the below SQL

SELECT DISTINCT TIMES.CALENDAR_MONTH_START_DATE FROM SH2

In the above case, TIMES.CALENDAR.MONTH_START_DATE is my date column. Make both the above prompts to set 2 presentation variables Date1 and Date2.

      

Save this prompt. Now go to Answers and start creating a report containing 2 columns, TIMES.CALENDAR_MONTH_START_DATE and SALES1.SALES. Now create another column with the following formula

TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘@{Date2}{1900-01-01 12:00:00}’, TIMESTAMP ‘@{Date1}{1900-01-01 12:00:00}’)

So, what this does is, it calculates the time difference between the 2 dates which would be selected in the dashboard prompt.

      

Add 2 filters to TIMES.CALENDAR.MONTH_START_DATE (one with less than or equal to and the other with greater than or equal to). In both the filters refer the Date1 and Date2 respectively.

CALENDAR_MONTH_START_DATE is less than or equal to @{Date1}{1/1/1900 12:00:00 AM}

CALENDAR_MONTH_START_DATE is greater than or equal to @{Date2}{1/1/1900 12:00:00 AM}

      

Now, include both the Dashboard Prompt and the Answers request in the dashboard page. Now, you should see that as you change the 2 dates, the Number of Days in the report would change and also the filter would be applied accordingly.

      

Posted in All Posts, OBI EE Plus | 27 Comments »

Oracle BI EE 10.1.3.3/2 – Updating Session Variables from Dashboards using Presentation Variables

Posted by Venkatakrishnan J on February 25, 2008

This question keeps repeating so many times that i thought this in itself deserves a seperate blog entry. There are 2 parts to this question. They are

1. Updating Session Variables from Answers
2. Updating Session Variables using Presentation Variables from a Dashboard Prompt

The first one is pretty straightforward. Lets start with a simple example. Lets create a simple session variable called Test_Var and make that variable to be initialized from an init block. Enable that variable to be set by any user. If you are using row-wise initialization then by default the variable will allow any user to set the variable.

      

Now, go to Answers and create a report which will show the Session Variable.

      

      

      

Now, as you see the session variable has a value of Test. Now, in order to update this variable, go to the Advanced Tab->Advanced SQL Clauses in Answers. And enter the below formula in the Prefix section (Do not click on Set SQL. This is executed when you move away from this tab)

SET VARIABLE Test_Var=’Test1′;

      

Now, also ensure that you are bypassing Presentation Services Cache.

      

If you go to the Results tab, you should notice that your Session Variable Value would have changed.

      

This has a lot of use cases. For example, you can set LOGLEVEL variable dynamically for every report. I shall also be blogging about other possible use cases later this week. This was pretty straightforward. But what if we want to set these variables from a dashboard prompt. In order to do this, lets create a simple dashboard prompt on any column and lets make it to set a presentation variable named Test

      

Now go back to the report that we created above and navigate to Advanced Tab. Now in the prefix type in the below formula

SET VARIABLE Test_Var=’@{Test}’;

      

Now, save this report and go to the dashboard and include the prompt and the report that we created. Enter any value in the Edit Prompt and click on go. You would notice that the Session Variable Value changes based on the value that was entered in the dashboard prompt.

      

This is again very useful for making update calls from the presentation layer to the BI Server.

Posted in All Posts, OBI EE Plus | 21 Comments »

Oracle BI EE 10.1.3.3/2 – Rolling Filters – Inter-row calculations

Posted by Venkatakrishnan J on February 22, 2008

I got one pretty-interesting question the other day with regard to rolling filters and inter-row calculations. Lets understand the question first with a simple example. Lets consider a simple table containing Date, and the daily temperatures as shown below.

      

As you see, the above table records the daily temperatures. Now, the question is to create a report on this table in BI EE such that the report gives only those days and their corresponding temperatures where the temperatures are greater than 50 and also for 3 consecutive days. So, basically in the above table the report should have the following list of days and its corresponding temperatures.

      

In order to do this, there are 2 approaches. One is to create a view in the database layer and the other approach is to create a database function and then call that fuction from Answers using EVALUATE. Lets look at both the approaches. In the first approach, lets create a view with the following script.

CREATE VIEW DAILY_TEMP_VW AS select DAILY_DATE, TEMPERATURE,
CASE WHEN
(TEMPERATURE >= 50 AND LEAD(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LEAD(TEMPERATURE,2,0) over (order by DAILY_DATE) >= 50)
OR ( TEMPERATURE >= 50 AND LAG(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LEAD(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50)
OR (TEMPERATURE >= 50 AND LAG(TEMPERATURE,1,0) over (order by DAILY_DATE) >= 50 AND LAG(TEMPERATURE,2,0) over (order by DAILY_DATE) >= 50) THEN 1 ELSE 0 END AS CASE_STAT
FROM DAILY_TEMP

So, what this basically does is, it creates another column in the same table. This column will have a value of 1 if its corresponding temperature is greater than 50 and is part of 3 consecutive days. If you do a select * from this view you will be getting the below result

      

So, the next step is to import this table into the repository and then design the BM and Presentation Layer accordingly. Now, when you create the report in BI EE, add a filter of CASE_STAT = 1 which will give you all days with temperature > 50 for 3 consecutive days.

      

      

Now lets look at another approach where in, instead of using the above view, lets create a database function which would accept the daily date as an input.

CREATE OR REPLACE FUNCTION FUN_CASESTAT(P_DATE DATE) RETURN NUMBER IS
V_casestat number;
begin

SELECT case_stat into v_casestat from (
select daily_date, temperature,
CASE WHEN
(temperature >= 50 and lead(temperature,1,0) over (order by daily_date) >= 50 and lead(temperature,2,0) over (order by daily_date) >= 50)
OR ( temperature >= 50 and lag(temperature,1,0) over (order by daily_date) >= 50 and lead(temperature,1,0) over (order by daily_date) >= 50)
OR (temperature >= 50 and lag(temperature,1,0) over (order by daily_date) >= 50 and lag(temperature,2,0) over (order by daily_date) >= 50) THEN 1 ELSE 0 END AS CASE_STAT
from daily_temp) where daily_date = P_DATE;

RETURN v_casestat;

END;

Now, call this function from Answers using EVALUATE.

EVALUATE(‘FUN_CASESTAT(%1)’,CAST(DAILY_TEMP_VW.DAILY_DATE AS CHAR))

      

      

Now, if you notice this report would produce the same result as the view that we created in the approach one. Now, just create a filter on the 3rd column to restrict all rows where this column = 1. This will produce the desired result.

      

      

I know, both the above methods involve changes in the backend database. Feel free to share any other ideas that you might have to solve this in the comments section.

Posted in All Posts, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.3/2 – Presentation Variables in TOP filter

Posted by Venkatakrishnan J on February 21, 2008

I got an email in one of our help lists today stating that Answers does not seem to accept presentation variables in TOP filter. If you are not sure what i mean, look at the screenshot below.

      

So, basically the user wanted to have a filter like “Sales is in TOP N” where the N would be populated by a presentation variable from a dashboard prompt. Lets look at a workaround today to achieve the same. Lets start with creating a simple report containing 3 columns Country, City and its corresponding Sales.

      

Now, lets create another column with the below formula

TOPN(SALES1.Sales,@{test}{5})

      

What this does is it creates a TOPN aggregation on the column. test is our presentation variable and 5 is the default value of the presentation variable.

Now, create a filter on the newly created column as shown below

      

So, basically your final filter will look like the one shown below

      

This filter is equivalent to the one that we tried first (where we got the error). Now create a dashboard prompt with a column formula as shown below

CASE WHEN 1=0 THEN GEOGRAPHY.COUNTRY_NAME ELSE ‘Test’ END

Make this prompt to set the presentation variable test

      

Now, you should be able to pass the presentation variables to the report.

      

      

Posted in All Posts, OBI EE Plus | 8 Comments »

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

Posted by Venkatakrishnan J on February 20, 2008

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.

      

Posted in All Posts, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.3/2 – Passing Operators in Dashboard Prompts – GO URL and Presentation Variables

Posted by Venkatakrishnan J on February 19, 2008

As you might probably know, there is no out of the box way currently available in OBI EE to make an end user to choose operators(like , = etc) and then pass these operators to filters. Sometimes your end users might have a requirement say to see a report where Sales > 3000, Sales < 3000 and Sales = 3000. In such a case, we would need the capability for the end user to choose the operator from a drop-down in a dashboard prompt and then enter the value in an edit prompt. Since, there is no out of the box way to achieve this lets look at a simple work around to get this feature using GO URL and presentation variables. So, first lets start with creating a simple dashboard prompt. This dashboard prompt would contain a Drop down listing the operators and then an edit prompt for the sales. So, include any column in the dashboard prompt and enter the below formula.

CASE WHEN 1=0 THEN Markets.Region ELSE ‘Operator’ END

And make this prompt to set a presentation variable Operator. Also, in the results section convert the results to SQL and enter the below SQL.

SELECT CASE WHEN 1=0 THEN Markets.Region ELSE ” END FROM Paint UNION ALL SELECT CASE WHEN 1=0 THEN Markets.Region ELSE ‘=’ END FROM Paint

      

Similarly, include the Dollars column as a second column to this Dashboard Prompt. Convert this to an edit box and make it to set the presentation variable Dollars. Save this dashboard prompt. Now go to Answers and start creating a report containing Region, Brand and Sales Dollars. Add a filter on Sales Dollars with an “is prompted” clause.

      

Save this report. Now create another report with 2 columns. The 2 columns of this report would point to the 2 presentation variables in the dashboard prompt i.e Operator and Dollars. In the 1st column enter the below formula.

CASE WHEN ‘@{Operator}’ = ‘=’ THEN ‘eq’ WHEN ‘@{Operator}’ = ‘>’ THEN ‘gt’ WHEN ‘@{Operator}’ = ‘<‘ THEN ‘lt’ END

In the 2nd column enter the below formula,

CASE WHEN 1=0 THEN Markets.Region ELSE ‘@{Dollars}’ END

      

      

Now go to the narrative view of the report and enter the below code. This is basically an iframe code which will render the Report1 by passing the Operator and Value parameters to the GO URL.

      <iframe src=http://localhost:9704/analytics/saw.dll?GO&nquser=Administrator&nqpassword=Administrator&path=/shared/Paint+Demo/Operator+Select/Report1
&Action=Navigate&P0=1&P1=@1&P2=”Sales%20Measures”.Dollars&P3=@2 width=”650″ height=”400″></iframe>

      

Now, go to the dashboard page and include the prompt that we created first and the narrative view of the second report. Now, if you notice, we can change the operator and value simultaneously.

      

      

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Drilling on Measures – Passing Multiple Parameters during Drills – Go URL and HTML Formatting

Posted by Venkatakrishnan J on February 17, 2008

One of my colleagues within Oracle called me today with regard to a pretty interesting issue that he was stuck up on. Basically, he wanted to find a method of drilling on measure values by passing some of its dimensional attributes as filter to another report. As you would probably know, when we do drilling we would be passing the value of the drilled column as filter to the subsequent report. But in the above case, he wanted to pass 2 or 3 other dimensional attributes. If you are not sure about what i mean, look at the sample report below

      

As you see above, we have a simple report, containing Region, Country and the corresponding sales. Now the issue is how do we pass the Region and Country as parameter to another report while drilling on sales i.e in the above report when we click on 7,264,336 we should pass on Asia and Japan as parameters to another report. In order to achieve this lets start with creating the 2nd report containing the same Region, Country and Cost instead of sales. Now add 2 filters to this report on Region and Country with is Prompted clause.

      

Now, lets go back to the first report. The idea is to convert the sales column into a HTML href column and then pass the parameters to the 2nd report using GO URL. In the formula tab of the Sales column enter the below formula,

      ‘<a href=http://localhost:9704/analytics/saw.dll?GO&path=/shared/Dynamic%20GO%20URL/Report2&Action=Navigate&P0=2&P1=eq&P2=Geography.Region&P3=’||Geography.Region||’&P4=eq&P5=Geography.Country&P6=’||Geography.Country||’ style=”text-decoration:none;”>’||CAST(Sales.Amount_Sold AS CHAR)||'</a>’

And convert the above column into HTML format.

      

So, basically what we are doing is we are calling the 2nd report using GO URL and also passing the other row-dimensional attributes like Region and Country to the GO URL. The formula has such a format in order to make the column as a HREF link. Now if you click on the measure it would pass all the dimensional attributes to the target report.

      

      

A very simple technique but again can be very powerful since it can pass multiple parameters at the same time.

Posted in All Posts, OBI EE Plus | 22 Comments »