Business Intelligence – Oracle

Archive for the ‘OBI EE Plus’ Category

Oracle BI EE 10.1.3.3/2 – Understanding Dates in Answers

Posted by Venkatakrishnan J on October 1, 2007

One of the typical queries that we generally get in the forums is how do we control the date format in BI Answers. Also, the general question is in what format do we ask users to enter dates into a dashboard prompt so that the filters are applied properly. If you are used to Oracle Date formats of DD-MM-YYYY or DD-MON-YYYY etc, just be aware that BI Answers treats the date formats a little bit differently. Lets try and understand this using a sample report. Lets create a sample report from the SH schema (assuming you have the Business Model and Presentation layer setup properly). This report would basically give us the amount sold in all the years.

Lets look at the report output.

As you see above, the date field by default has changed the format to include the time part of the date. Let us look at this in much more detail. Edit the column properties of the date column and go to Data Format tab.

As you see above, by default BI Answers has chosen the date format as FMT:timeStampShort. Now click on the date format and change it to custom. We will now try to understand the formats that we generally use. For example, we might want the format to be of the form mm/dd/yyyy. In order to do this we would have to enter the format as FMT:dateShort. This would display the date in the format that we desire.

The above picture shows the date fields with the new date format. There are lots of date formats that one can have. All these are documented in page 75 of the User Guide here.

Remember, if you have any date prompts, the format that you would have to enter should have to match the format that you have set in the data format tab of column properties in the report.

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

Oracle BI EE 10.1.3.3/2 – Changing logos in the Answers Title View

Posted by Venkatakrishnan J on September 27, 2007

We had a question in one of our forums today, wherein the question was “How do we add a custom picture as a logo to a reports Title View?”. Though it is very much documented, i am not sure whether it works the same way as documented. Lets see how to go adding a custom oracle logo to the Answers Title View. The first step that one would have to do is, copy the image to 2 folders.

1.   {OracleBI}\web\res\app\s_oracle10\images

2.   {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

I am assuming that you are using the default style of s_oracle10. Restart your presentation services and the OC4J.Then open a report where you would like to add the image to and click on the edit title view.

      

In order, to use the images in the title view, one would have to enter the relative path of the image that we just copied. It would look like this

fmap:images/oralogo_small.gif

fmap is a keyword and is case sensitive. Then we are good to go. Let me know if anyone has got their title views working by following the documentation.

      

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

Oracle BI EE 10.1.3.3 and mapviewer – Step by Step Integration Phase2 / Phase3

Posted by Venkatakrishnan J on September 26, 2007

We saw yesterday here on how to go about implementing the phase 1 of integrating OBI EE and Mapviewer. Now lets discuss the next 2 phases. I would rather focus on the phase 3 which shows how to about passing parameters from Mapviewer to OBI EE.

Phase 2 is primarily on the mapviewer front wherein one can have multiple basemaps and change the basemaps when we click on any foi region dynamically in order to simulate a drill down. We would be using the same example as we used in phase1. But our aim is to showcase the way of passing parameters from mapviewer to OBI EE. So, lets begin with the simple map that we created yesterday. Instead of passing parameters to the map, we would change the map in such a way that it would show the customers in 2 cities i.e OAKLAND and HAYWARD. The idea is to show the sales of a city while clicking on any customer within that city. For example, if we click on any customer within OAKLAND our OBI EE sales report should change and show the sales of OAKLAND. The same should happen for HAYWARD city.

1. Lets create a BI EE report that would basically show the sales of all the cities. Create a filter on city that would be entered via prompts (i.e prompted).

      

      

2. Once this is done, the next step is to create a narrative BI EE report that would basically call the above report using iframes. Ensure that iframe is given a name and also check the GO URL parameter for the BI EE report that we created earlier. It should look like this

http://localhost:9704/analytics/saw.dll?GO&NQUser=Administrator&NQPassword=Administrator&Path=/shared/Paint+Demo/Mapviewer/City+Sales&Options=md&Action=Navigate&P0=1&P1=eq&P2=”Dimension%20-%20Customer”.CITY&P3=”OAKLAND”

The above url will pass OAKLAND as a parameter to the BI EE report which in turn would be displayed in a narrative iframe element named BI EE.

      

3. The next step is to create a map that would basically show the customers within the 2 cities OAKLAND and HAYWARD. We shall use the same map that we created yesterday. Save the same map as a new map and modify it to suit our needs.

      

Lets discuss the structure of the map html today. I have commented out the part wherein we had passed Region as a parameter to the map yesterday. Also, i have hardcoded 2 regions OAKLAND and HAYWARD using the below parameter variable.

var parameters = “\”” + “OAKLAND” + “\”,” + “\”” + “HAYWARD” + “\””;

Once this is done, we need to add a listener in order to make the map listen for mouse clicks on the customers. This is done by this part of the code.

var marrayPara = new ArrayParameter(parameters,’sarray’,’city_list’);
var themebasedfoi=mapview.getThemeBasedFOI(“themebasedfoi1″);
if (themebasedfoi==null)
{
themebasedfoi = new MVThemeBasedFOI(‘themebasedfoi1′,’mvdemo.customer_by_cities’);
themebasedfoi.setQueryParameters(marrayPara);
themebasedfoi.setBringToTopOnMouseOver(true);
mapview.addThemeBasedFOI(themebasedfoi);
}
else
{
themebasedfoi.setQueryParameters(marrayPara);
themebasedfoi.setBringToTopOnMouseOver(true);
themebasedfoi.refresh() ;
}

themebasedfoi.addEventListener(“mouse_click”, foiClick);
}

After adding the mouse click event, we need to define the foiClick function that would basically obtain the city name (stored in the foi) and pass it on to OBI EE.

function foiClick(point, foi)
{

var drillURL =
http://vejanaki-pc:9704/analytics/saw.dll?

GO&NQUser=Administrator&NQPassword=Administrator&Path=/shared/Paint+Demo/Mapviewer/City+Sales&Options=md&Action=Navigate&P0=1&P1=eq&P2=\”Dimension%20-%

20Customer\”.CITY&P3=”+foi.attrs[1];

objWin = window.open(drillURL, “biee”,”height=480,width=240,scrollbars=yes,resizeable=yes”);

}

The foi.attrs[1] would have the city name and this is passed to OBI EE in the form of GO URL parameter. OBI EE report would change dynamically since window.open points to the biee (name we gave earlier to the iframe) iframe.

      

When one clicks on the OAKLAND customers, the OBI EE report would give the sales of OAKLAND city. Same is the case for HAYWARD.

      

      

Posted in All Posts, Mapviewer, OBI EE Plus | 9 Comments »

Oracle BI EE 10.1.3.3 and Mapviewer – Step by Step integration – Phase1

Posted by Venkatakrishnan J on September 25, 2007

Update: Phase2 and Phase3 are available here

I had mentioned in my previous blog entry here that i was working on documenting a step by step approach for the integration between Mapviewer and BI EE. Well, the integration is not pretty much out of the box and involves quite a bit of understanding primarily from the Mapviewer front. So, just be aware that the integration is not out of the box and would require some amount of coding. The entire integration process involves understanding 3 major pieces.

1. Understanding how to pass parameters between Mapviewer and a Dashboard Prompt (Maps must change as we change the prompt).

2. Understanding how we can simulate drill down within a map (This is the harder part that does not involve BI EE).

3. Understanding how one can change the prompts of BI EE as we navigate in a Map.

We will see the first part today as part of a 3-phase series wherein i would try to document this in a step by step fashion. But before i go there, one would have to have these pre-requisites done.

1. Deploy Mapviewer in the OC4J that holds BI EE.

2. Get all the demo setup of Mapviewer up and running. One must be able to view the demo maps that comes along with mapviewer.(This needs a schema called mvdemo that would basically contain all the spatial data in an Oracle Database).

3. A little background of javascript is desired.

If you have all of these pre-requisites then you can try the following. We would be seeing the phase 1 of the integration process. The idea of this integration is to create a dashboard prompt that would basically contain all the cities and based on the city that we select, we must get a map which would show all the customers within that city.

This involves the following steps.

1. Import the table called customers(without the location column) into OBI EE Administration Console. Do the corresponding mapping in BM and the Physical layer.

2. Once this is done. We would have to create a dashboard prompt that would basically list down all the cities and also set a presentation variable called Region based on what we have selected.

As you see above, i have created a dashboard prompt that would basically set a presentation variable called Region.

3. Now lets go to the mapviewer and open up the default demo map called “Array Parameter and Templated Theme Based FOI”.

The map should look like this.

Our idea is to build upon an existing map. The above map basically varies the map based on input city names.

Now lets view the source of the above map and change the source based on our requirements. I have used the following code wherein i have removed unnecessary parts. Remember we would be passing the presentation variable as the URL parameter to the mapviewer. I have added a function called getURLParameter which would basically store the value of the URL parameter in a variable called Region.

Test out whether the URL parameter is getting passed properly to mapviewer. The URL to pass into the mapviewer would look like this http://localhost:9704/mapviewer/fsmc/tutorial/samples/DynarrayThemeBasedFOI.html?Region=OAKLAND

4. The next step is to pass down this Region variable to the mapviewer from the dashboard prompt. We can do that using iframes in a narrative view report. Create a new narrative view report and enter the data as shown below. The iframe URL would be like this http://10.176.246.99:9704/mapviewer/fsmc/tutorial/samples/mapParameter.html?Region=@{Region} where @{Region} is the presentation variable that we had created in step 2.

5. Once this is done, we would have to call the both the reports from a dashboard i.e the Dashboard Prompt report and Narrative report.

I will follow this one up with phase-2 and phase-3 later. But the above should get you started.

Posted in All Posts, Mapviewer, OBI EE Plus | 24 Comments »

Oracle BI EE 10.1.3.2/3 – Ref Cursors and Pipelined functions

Posted by Venkatakrishnan J on September 21, 2007

Another interesting question that generally comes up is whether Oracle BI EE supports Ref Cursors. Well, currently only BI Publisher supports the use of ref cursors to pass the data to the output (Remember, BI Publisher uses jdbc connections and jdbc supports ref cursors.). I tried using the following ref cursor function on both BI Publisher and BI EE. Lets see what the results are and of course lets see what is the workaround available for OBI EE. To start with i created a function that basically returns a ref cursor of the dept table in scott schema.

create or replace function refcursor_dept return sys_refcursor as
TYPE v_cursor is REF CURSOR;
p_cursor v_cursor;
begin
open p_cursor for
select * from dept;
return p_cursor;
end;

      

In BI Publisher this would work like a breeze. All one would have to do is to call the function. Tim Dexter has already blogged about this. One can get more details here.

Now, lets try this in BI EE. I will be testing this using the direct database requests option on BI EE since it provides an easier way of testing things out. If you have not set direct database requests, i have blogged about it here showing you how to set it up. I have created a connection pool that basically points to my scott schema.

      

Now, lets fire the query using direct database requests using the connection pool that i had created earlier.

      

As you see, we get an error saying that this data type is not supported (this is the first time i am trying this out. If anyone has found a way of directly calling reference cursors from OBI EE please do let me know. I would be more than happy to share it here). So, for users who want to leverage their existing ref cursor functions, one option is to use pipelined functions. Lets see for the above example, how one can go about creating a pipelined function and of course how one can call them from OBI EE.

The first step is to create an object type that would basically contain all the columns of the dept table.

create or replace type dept_objecttype as object
(
DEPTNO NUMBER,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

Once this is done, lets create a table type of the above object.

create or replace type tabletype_dept as table of dept_objecttype;

Once this is done, lets create a pipelined function that would basically take in the ref cursor function that we created earlier as an argument.

create or replace function pipelined_dept
(p_cursor in sys_refcursor)
return tabletype_dept
PIPELINED
as
v_records dept%rowtype;
begin
loop
fetch p_cursor into v_records;
exit when (p_cursor%notfound);
pipe row(
dept_objecttype(v_records.deptno,
v_records.dname,
v_records.loc) );
end loop;
return;
end;

Now lets test this function from sqlplus.

      

Once this is done, lets test it out in BI EE.

      

      

For more detailed explanation on pipelined functions, i would recommend Tom Kyte’s article here. And yes of course, if you have other methods of calling ref cursors, feel free to put them in the comments section.

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

Oracle BI EE 10.1.3.3/2 – Write Back Option – Budgeting/Planning

Posted by Venkatakrishnan J on September 20, 2007

OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.

      

1.   The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.

      

2.   The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.

      

3.   Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.

      

4.   Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.

      

5.   Once this is done, go to the column properties of sales_quota and enable writeback.

      

6.   After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is

The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.

7.   Go to the table properties of the report and click on the write back option. Enter in the template details.

      

      

8.   Once this is done, enter in some data and test out the write back.

      

      

More details can be obtained from the BI Server and Presentation Services guides.

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

Oracle BI EE – Execute Direct Database Requests

Posted by Venkatakrishnan J on September 18, 2007

This article is just to show the one of the features of OBI EE. It is not recommended to use this feature since it bypasses the metadata layer and executes direct database requests. If it has to be used in your environment, do it with utmost care and caution taking all the security into consideration.

OBI EE can execute user provided SQL on a database and can get the results back to the end user. By default this feature is turned off. In order to enable it, there are 2 steps that one would have to follow.

The first step is to enable the database in your physical layer to allow for direct database requests.

      

      

Once this is done, we would have to login to the presentation services and give the necessary “execute direct database requests” permission to the user. This is turned off by default even for the Administrator. Lets turn it on for the administrator using the manage privileges.

Go to Answers->Settings->Administration->Manage Privileges

      

Then enable direct database requests privilege for the user group that you need. In my case, just for demonstration, i have given that permission to everyone.

      

Once this is done, go to Answers and click on Execute Direct Database Requests and then enter the query along with the connection pool name.

      

     

As you would see here, one can enter any query that the database would support. For more details on securing this feature refer BI Server Guide and Presentation Services Guide

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

Oracle BI EE 10.1.3.3 – Configuring Delivers – iBots

Posted by Venkatakrishnan J on September 13, 2007

I had one OBI EE user today who had specifically asked about configuring iBots. Though it is pretty straight forward, the customer was looking out for a screenshot by screenshot tutorial on how to go about setting delivers. Well, i had some time to put this one together. Hopefully, you would find it of some use.

1.   The first step is to configure a schema that would hold all our scheduler tables. If you do not have a schema create or use an existing one. In my case i have a schema called S_NQ_SCHED wherein i would create all my scheduler tables.

2.   Navigate to your {ORACLEBI}/Server/Schema folder and run the scheduler scripts. If you are on an Oracle Database, you would have to run SAJOBS.Oracle.sql. This would basically create the scheduler tables that Oracle Delivers would use later.

3.   The next step is to open your Job Manager. Go to File – > Configuration Options and enter the schema and connection details for the scheduler schema. In my case it would be S_NQ_SCHED schema.

      

4.   Go to the General tab. If you had followed the default install, then leave everything the same. Just enter in the Administrator usernames and passwords.

   

5.   Go to the Mail tab and enter in your mail server details.

      

6.   Open your instanceconfig.xml and add in the scheduler details. This step basically lets the presentation server know about the machine and port details of the scheduler. By default the scheduler port is 9705.

      

7.   The next step is to run a cryptotools utility that would basically store the username and password of the scheduler into an XML file called credentialstore.xml.

8.   Go to {OracleBI}/web/bin from command prompt. Then enter in the following command.

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml

   

Ensure that you have the alias as “Admin”.

9.   The next step is to add the details of this credentialstore.xml file into the instanceconfig.xml.

   

Once this is done you should be able to schedule and create new ibots. The best and quick way to check whether the configuration has been done properly is to save a sample ibot. If you are able to properly save one then your configuration has been properly set. For more details refer the docs here.

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

Oracle BI EE 10.1.3.3 – Customizing look and feel – Styles and Skins

Posted by Venkatakrishnan J on September 12, 2007

OBI EE provides options to do customizations to the look and feel of dashboards and answers. One can change the images and put in custom images depending on what they need. I had one of the customers today saying that it does not work in the same way as it has been documented. Before we go into that, lets first explore the customization options. There are 2 types of customizations. One is by using Styles and the other is by using Skins. Both of them have their own purposes and have to be used in coordination with one another.
   
   1.   Styles
   2.   Skins

A style controls how dashboards and answers are displayed to an end user at run time. Where Skins are assigned to an end user while logging in. Generally, styles can be changed and skins consist of objects that are non-alterable like corporate logos etc. One can find all the styles and skins under the {ORACLEBI}/web/app/res folder. All the folder under this folder that start with s_ are styles and those that start with sk_ are skins. Now lets see how to modify the styles for an end user. Log into BI Dashboards and go to the edit dashboard page and click on dashboard properties.

   

   

Lets try modifying the style to siebel77. This would give the old siebel look and feel to the reports that we have created and also for the dashboards. If you are an user who likes the siebel look and feel, its still there but has been pushed to the background by the sleek new Oracle style. Lets choose the siebel style and see how it looks like.

   

Now, that we know how to change the styles, lets start with creating a new style. Just to be clear here, changing styles and skins might vary from on App server to another. The one that i am demonstrating is for the default OC4J. So, if you are on the default install you can go ahead and follow the list of steps. If not, you can still try and let me know whether it works since i have not tried. I am not sure whether there is a documentation bug since it somehow does not seem to work when you follow the documentation. Lets start creating a new style from out old siebel77.

1.   Lets copy the s_Siebel77 folder under {ORACLEBI}/web/app/res under the same directory.
2.   This would result in a copy of the style which we shall rename to s_newSiebel. For styles, it is mandatory to have the folder name starting with s_.
3.   Now lets copy the new renamed s_newSiebel folder to {ORACLEBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res folder.
4.   Lets make some changes to the style like changing the logo etc. I have changed the bg_banner.gif (logo in dashboard).
5.   Remember, the changes you make have to be done to the folder under the oc4j directory.
6.   And you would have to restart the OC4J for this to get reflected in your dashboards.

   

The above is way different from what has been stated in the docs. Either the older docs have not been updated or i might be missing something. The customizations that you have made above would go when you do an upgrade since all the styles which are created under {ORACLEBI}/web/app/res would be overwritten during the upgrade.

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

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

Posted by Venkatakrishnan J on September 10, 2007

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

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers