Business Intelligence – Oracle

Archive for January, 2008

Oracle BI EE – Nesting Folders in Presentation Layer and Answers

Posted by Venkatakrishnan J on January 31, 2008

I just saw another question pop up in the forum today with regard to nesting of folders in BI EE. This is where i believe BI EE documentation has to be ramped up a bit. I thought i would blog about it since it can cause confusion amongst users who are upgrading from Siebel 7.8 or lower to BI EE. In the earlier releases, the best practice to nest the folders was to add “-” to the front of a folder name. But the best practice has changed in the recent releases. Look at the screenshot below.


Our aim is to nest Geography Folder under Channels. In order to do that, open the repository and double click on the Geography Folder. Add “->” to the description. This will automatically nest the 2 folders. Remember ordering of the folders are very important for nesting to work properly. Now save it and reload the server metadata. Now you should see that the Geography Folder would have been nested under Channels.




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

Oracle BI EE – Adding Custom URLs to the Dashboard

Posted by Venkatakrishnan J on January 31, 2008

We will be looking at another simple feature today. Generally, when we give BI EE access to the end users, it is always good if the dashboard contains the frequently accessed URLs or the intranet URLs that are common within the organization. For example, some users might want to have a Google link on the top of the dashboard which should be available in whichever component of BI EE that they go into. If you are not sure what i mean, look at the screenshot below.


If you notice, generally we only have Dashboards, Answers, More Products, Settings(if you have privilege) and Logout URLs in everyscreen. What if the organization policy is to have the company home URL and other commonly used URLs to be part of the same set of URLs. So, our aim today is to look at a simple way of adding some static URLs like Oracle Home page and Google to the other set of URLs. The steps are listed below

1.   Go to {OracleBI}\web\msgdb\messages and copy the file commonuitemplates.xml to {OracleBIData}\web\msgdb\customMessages (if you dont have this folder create a new one). Now open this file in a text editor.

2.   Search for the web message kuiMainBarActionsTable and add the below listed lines immediately after the tr tag.

      <td class=”DashBarProductCell”><a href=”” target=”_blank”>Oracle Home</a></td>
<td class=”@{classPrefix}Sep”>-</td>
<td class=”DashBarProductCell”><a href=”” target=”_blank”>Google</a></td>
<td class=”@{classPrefix}Sep”>-</td>


So, what this does is it adds 2 new URLs to the existing layout. But remember this will affect the UI for all the users.

3.   Save the file and restart presentation services. Now, you should have these 2 new links added to the dashboard.


This can be useful if you dont have any other option but to add the links for all the users.

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

Oracle BI EE – Changing default Color Selector

Posted by Venkatakrishnan J on January 30, 2008

I had an email today asking for instructions to change the default color selector that you get in the Column Properties. Basically the user wanted to add some extra colors that was not part of the color selector. Though this is rarely asked/used, i thought i would talk about it today since the request itself was very unique. If you are not sure what i mean, look at the screenshot below


So by default we have 48 different colors in the Color Selector window. Now our aim today is to add 2 more rows of extra colors which you think your end users might like. Lets look at the steps one by one below.

1.   Go to {OracleBI}\web\msgdb\messages and copy the file commonuitemplates.xml to {OracleBIData}\web\msgdb\customMessages (if you do not have this then create the folder). Now open this copied file to a text editor.

2.   Once this is done search for the web message kuiColorSelectorColors. This is the message that handles all the colors that are shown in the color selector. Now change the knFormatColorsRows value to 8 (We are adding 2 more rows of extra colors.


knFormatColorsRows = 8;

3.   Now add 16 extra colors to kaFormatColors. For example, like the one shown below


4.   Restart presentation services and you should now have the extra colors to your color selector.


Not sure how many would actually need this, but again if at all you need it, the above procedure should give you an idea.

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

Oracle BI EE – Getting different view link shortcuts in the Criteria Tab

Posted by Venkatakrishnan J on January 29, 2008

Lets look at a nice customization feature that can be useful in quite a lot of situations. If you go the Criteria tab of any report you would see that you would basically have 4 different shortcuts for different views. For example, look at the screenshot below,


As you see above, there are 4 out of the box shortcuts to go directly to the Compound View, Table View, Pivot View and Chart View. But what if you need another shortcut to go directly to the Narrative View or the Filter view. Lets see how to go about achieving that today.

1.   Go to {OracleBI}\web\msgdb\messages. Copy the file answerstemplates.xml from here to {OracleBIData}\web\msgdb\customMessages (if you do not have this directory, create one). Open up the copied file in a text editor.

2.   Search for the XML message kuiAnswersCriteriaEditor. Now if you go through this XML message you can find that this XML message points to the various shortcuts that you saw in the 1st screenshot. For example, the following XML message within kuiAnswersCriteriaEditor shows the pivot view

      <sawm:if name=”privileges[‘View.pivotTableView’].EditView”>
<td class=”AnswersBarButtonCell”><sawm:messageRef name=”kuiAnswersBarImage”>
<sawm:setParam name=”buttonImage”><sawm:fileMap path=”Answers/abbPivot.gif”/></sawm:setParam>
<sawm:setParam name=”buttonTip”><sawm:messageRef name=”kmsgPivotViewDescription”/></sawm:setParam>
<sawm:setParam name=”buttonScript”>AnswersCriteriaEditor.onSelectViewType(‘<sawm:param name=”eid”/>’,’pivotTableView’)</sawm:setParam></sawm:messageRef>

Now lets add a similar message like this for Narrative view as shown below

      <sawm:if name=”privileges[‘View.narrativeView’].EditView”>
<td class=”AnswersBarButtonCell”><sawm:messageRef name=”kuiAnswersBarImage”>
<sawm:setParam name=”buttonImage”><sawm:fileMap path=”Answers/abbother.gif”/></sawm:setParam>
<sawm:setParam name=”buttonTip”><sawm:messageRef name=”kmsgNarrativeViewDescription”/></sawm:setParam>
<sawm:setParam name=”buttonScript”>AnswersCriteriaEditor.onSelectViewType(‘<sawm:param name=”eid”/>’,’narrativeView’)</sawm:setParam></sawm:messageRef>

So, your final kuiAnswersCriteriaEditor web message would look something like this. Once this is done restart your presentation services. Now you should see a shortcut extra for the narrative view.



Again, this goes to show you the customization capabilities of BI EE. As shown above you can add shortcuts to all the views that you need.

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

Oracle BI EE – Integration into External Applications/Portals

Posted by Venkatakrishnan J on January 28, 2008

This question generally comes up in almost all the customer calls that i go into. So, i thought i would blog about all the possible options for integrating BI EE into any external application. Though i have talked about the individual options before i thought it would make more sense if one has a complete list of all the possible options(let me know if you find more). The various possible options are listed below

1.   Integration using GO URL and URL Parameters.

2.   Integration using SOAP APIs(BI EE Web Services).

3.   Integration by deploying BI EE JSR-168 compliant portlets to a WSRP standard Portal

4.   Integration by calling BI Server metadata using ODBC from other tool sets.

Now lets discuss the advantages and disadvantages of using all the above 4 options.

1.   GO URL is the easiest and best approach for integrating BI EE into any kind of a web environment. Currently, BI EE is integrated into Siebel CRM using this methodology. One of the major advantages of using this approach is that it gives the end user a lot of flexibility. One does not lose any kind of in-built features of BI EE and at the same time one call the reports and the dashboards from an application using a single URL. Parameters can be passed back and forth between the application and BI EE since both have HTML based UI. So, if you are one of those users who want to integrate some BI EE reports into one of your own custom application i would recommend you to evaluate this approach first before going into any of the other 3 approaches. For the list of all known and unknown parameters check my blog entries here and here.

2.   SOAP APIs or the BI EE Web Services also provide very good integration capabilities. But just understand that these are APIs that expose the nuts and bolts of OBI EE. In most scenarios, these might not be useful at all. But this can be useful in situations wherein you would like to leverage some of the BI EE reports and would like to do something in your application based on the output of a report. For example, assume that you have a web application that creates and manages workflows. Now, assume that an end user creates a workflow request asking for 5 days of vacation. This workflow request would be sent to the manager if he has more than 5 days of vacation balance left for approval else it will go one more level up for approval. Whether the end user has enough vacation is determined from a BI EE report that can accept parameters. In such a case, WSDL SOAP APIs can be very useful. Also, it can be useful in situations wherein you would like to synchronise user authentication in your custom application and BI EE. For more details check my blog entry here.

3.   JSR-168 portlets – This method of integration is useful in those situations if you have a standard portal that is WSRP compliant. In such a case you can deploy these portlets directly into a WSRP container of the portal and can view BI EE reports directly from your portal (for example Oracle Portal). But i would recommend going through GO URL approach first and see whether it is applicable in your situation. If it is not only then should this approach be used. For more details on how to deploy these portlets check my blog entry here

4.   Using BI Server ODBC – This is not an integration methodology per se. But again i have seen users using this approach for leveraging BI Server Metadata. So, if you have any other reporting tool that can create reports using a ODBC connection then and if you want to leverage BI Server metadata then this option is for you.

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

Oracle BI EE – Report Comments in Dashboards and Chat Options – Using Writebacks

Posted by Venkatakrishnan J on January 27, 2008

I was asked by an end user the other day about the possibility of having an option in BI EE wherein users can enter comments about the reports and the report layouts. The user mentioned that this would be very useful during the initial project roll outs wherein end-users might need some changes to the final reports. He also mentioned that this can be useful for handling change requests. Though there is no out of the box feature in BI EE to make this functionality available, lets see how to mimic such a requirement using Writebacks. So, basically the idea is to create an external table that would store the feedback from the end users. Ensure that you have gone through my previous blog entry here which would give you details on how to enable Writebacks in BI EE. So, lets start with creating a simple table which would contain 2 columns (Username and Comments).


Insert a dummy row inside this table so that it does not throw out a No Data Found error in Answers(even better just insert Null for both the 2 columns).

Once this is done, import this table into the repository and design the corresponding Business Model Layer and Presentation Layer. Ensure that caching for this table is turned off.


Now go to answers and start creating a simple report containing the Username and Comments. In the Comments Formula Enter


The above is to ensure that only one row is returned even when the data in the table is more than 1. In the User Column,enter USER() and hide this column.



Also, ensure that you are disabling the presentation services cache. Now, lets design a Writeback Template. The writeback template would look like the one below,


Ensure that you have the following SQL in your writeback template.


Now go back to the report and ensure that you are enabling writeback for the COMMENTS column.



Now, the final step is to create another report which would again contain both the mentioned columns in the above report. But in addition this would contain a filter to remove the dummy row that we inserted earlier. Ensure that this has the presentation services cache disabled.


Add both these reports to the dashboard. So, now users would be in a position to enter their comments. As and when they enter the comments, it would be displayed in the other report. Administrators can make changes accordingly to any of the reports mentioned.



One other functionality where this can be useful is if you want end users to have a basic chat feature. You can use a combination of Java Script Page Refresh and writebacks to enable users to chat between themselves. The writeback column would enable users to enter a chat message and the report window would show the incoming message. This report window can be refreshed periodically to show incoming messages.

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

Oracle BI EE – Dynamic Column Headers using Presentation Variables, Sets and Conditional Formatting

Posted by Venkatakrishnan J on January 25, 2008

Yesterday we saw how to dynamically vary the columns in a report based on the column that we choose in a dashboard prompt. But if you had noticed, the column header in that report would have had a constant value. I got this question yesterday via email, whether there is any way we can make this column header dynamic. Well, today we shall see how to go about achieving dynamic column headers. Actually if you go to table or column headings in a table view you would notice that it accepts only static text. One cannot enter presentation variables and make it to convert them to their actual values. So, in order to achieve the dynamic column headers, we start with the same report from where we left off yesterday here. The idea is very straight forward. It consists of the following steps

1.   Hide the actual Column and Table Headers.
2.   Cast all the columns to CHAR.
3.   Decide on the name for your static column headers.
4.   Create conditional formatting on the all the columns and make them to display in a specific color whenever data matches the Column Header Names.
5.   Add a new criteria and combine it with the older one using UNION ALL. The columns in this criteria would contain the Actual Column Header values that you want (dynamic).
6.   Make these values to display at the top of the report.

I know it might be a bit confusing why we are doing certain operations above. We shall go through each of the above in detail. Lets take the same dynamic report that we created yesterday.

1.   Go to the table view properties and hide the actual column and table headers. We are doing this because our plan is to use a data element from the table itself(which we shall generate later using UNION ALL) as the column header instead of the actual column headers.


2.   Cast all the numeric columns to char since we would be appending non-numeric column headers which would come from another data set. Now decide on a column header name for all the columns that you have in your report. For example, in our report, one column header name would be dynamic (which would come from the presentation variable @{Geography} and the other Column header would SALES


3.   Once you have the column header names decided, now add conditional formatting to all the columns in the report to something like this i.e. whenever SALES1.SALES = ‘SALES’ (the static column header name that we decided earlier) then add a background color of light green. In my case. i have added the below conditional formatting to both the columns.


4.   Now that we have added the conditional formatting, next is we need to add the criteria that will add our necessary column header values to the report data. So, use UNION all to combine the new criteria with the older one.


Add some dummy columns and in the formula enter the column header values. In my case ‘{@Geography}’ and ‘SALES’. Ensure that you include atleast one column in the formula as shown below.



5.   The last step is to make the new data that we added to display at the top of the report. So, now add 1 new column to both the criteria and enter 2 in formula tab for the one containing the data and 1 in the formula tab for the one containing the column header values. Sort the results on this column.



6.   Now save this report. And go to the dashboard. You would have dynamic column headers.



I know the above procedure is too tedious for achieving a very small functionality. But again, if your users absolutely need it and you dont have any other options just use this approach. Just remember this works only for table views.

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

Google – Something Strange – Decimal Characters and HTML

Posted by Venkatakrishnan J on January 24, 2008

I noticed something very strange today(atleast strange for me). While i was looking at some XML WebMessage code in BI EE, i noticed the usage of some decimal HTML codes like  . Well i thought i would search for them in Google to find out what exact HTML characters they point to. I know for a fact that they are actual decimal character references for special characters in HTML. But when i wanted to search for them in Google, all i got was their actual characters. If you are not sure what i mean look at the screenshots below or enter in your Google search. All i wanted was to list all the pages that had reference to this decimal character .



But all i got was an apostrophe i.e the actual HTML character to which this decimal character reference pointed to. I tried this in other search engines and they dont seem to have a problem displaying these special characters. Is this expected? Well, if you are wondering how to search for these characters then just search for them without their semi-colons. This website here has more details on these special characters

Posted in General | 2 Comments »

Oracle BI EE – Dynamic Report Columns using Dashboard Prompts and Presentation Variables – Part 2

Posted by Venkatakrishnan J on January 24, 2008

If you had gone through my previous blog entry here, you would be knowing how to go about dynamically varying the columns in a report. But one drawback that i mentioned there was, one had to manually enter the column names and any wrong entry would give out an error. Now lets look today how to go about achieving a drop down of column names. Though this can be achieved easily using column selector, the approach that we shall see today would affect all the reports in your dashboard (if you want). This is just an extension of what we saw the other day here. So, our aim is to basically have a list of columns in a dashboard prompt and based on what we choose in the dashboard prompt, all the reports should change having the selected column. Lets start with building the dashboard prompt first. Our aim is to make the dashboard prompt display 2 columns, GEOGRAPHY.COUNTRY_NAME and GEOGRAPHY.CITY_NAME. So, use any column in the dashboard prompt and in the function window enter the following function,


We are including the above formula in order to ensure that the dashboard prompt does not affect any column.


In the SQL Results section enter the below formula. Also make the dashboard prompt to set a presentation variable say Geography. Ensure that you have any one of the column names, for example, GEOGRAPHY.CITY_NAME as a default.


Now lets create a new report whose one of the columns would be either CITY or COUNTRY depending on what we choose in the dashboard prompt and the other column would be sales. In order to vary the first column, enter @{Geography} without the single quotes.



Now include the dashboard prompt and the report in a dashboard page. Now you would notice that when we change the value in the dashboard prompt, the corresponding column in the report changes.



This can be useful in situations where you would like to change the columns across many reports. If all you have is a single report, then you would be better off with a single column selector view.

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

Oracle BI EE – Total Number of Rows and Pagination

Posted by Venkatakrishnan J on January 23, 2008

Let us look at something very simple today. If you have used pagination in BI EE table views, you would have noticed that there is no way you can find out the total number of records which might sometimes be essential for an end user. It is always desired that the end user knows how many number of pages he is scrolling through.


Now in order to have the total records displayed to the end user, there can be a lot of ways. But lets look at a simple approach. Create an extra column in the criteria and put the following formula in the formula tab.


This will give us the total number of records as part of each and every row.



In my case, there are totally 48 records out of which 25 are displayed. Now hide this new calculated column and go to narrative view. Refer this column in the narrative view as @n, give some message so that the end users know. Also restrict the number of records to 1. Now include this narrative view to the compond layout and arrange it in such a fashion that it is just below the table view.




Very simple but again can be useful in quite a lot of situations

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