Business Intelligence – Oracle

Archive for October, 2007

Oracle BI EE – Import through Server

Posted by Venkatakrishnan J on October 31, 2007

I learnt about another interesting feature today. If you open the Administration Console and go to import, you would basically find 4 options. They are

1.   Import from Database
2.   Import through Server
3.   Import from Repository
4.   Import from XMLA

The options 1,3 and 4 are pretty self explanatory. But so far i have never used the Import through Server option. This option again has some interesting features. And this works only if you have open a repository in the online mode. Let us try to understand this here. Consider that we have 2 machines A and B as shown below.


So in effect an user in Machine B is opening the repository in the server machine A in online mode. So, lets see what happens when we open Import from Database.


As you see when we click on import from Database it shows all the set of System DSNs that have already been created in Machine B(local to Machine B). So, now lets see what happens when we click on Import through Server


Oops what has happened now. The list of DSNs have changed, havent they? So what Import through Server does is it exposes the Machine A’s DSNs, TNSNames entries etc to Machine B. So, any user who is involved in the repository creation process does not have to create local DSNs or TNSNames entries. All they have to do is to connect to the repository on the Server(in Machine A) in online mode and then click on Import through Server. This is very much used in the Multi User Development of the Repositories.

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

Oracle BI EE – IQY and Auto Generation of GO URL

Posted by Venkatakrishnan J on October 31, 2007

I would like to write today about two interesting but un-noticed features of Oracle BI EE. The first one is called as IQY or intelligent Web Query which will generate an export of the data in Excel that can be refreshed from within Excel. Of course, now that we have BI EE Office add-in this is available out of the box if you are on or above. But for users who are on the lower versions, IQY is a very good feature which would basically help financial users to get the refreshed report data directly from and into Excel. In order to generate the IQY file, go to any report that you have created in Answers and then go to the Advanced tab of the report. There you would see 2 links. If you click on the second link it would basically ask you to save the report as an IQY file.


After saving this file open it in Excel. Lets try to understand what this IQY file actually does. After opening the file you would basically see that the report which you have exported would be available in Excel(it does not support charts). But what you would not notice is that this uses the Web Query feature of Excel. Go to Data->Get External Data menu in Excel. There click on Edit Query


There you would basically see a URL to the report. Here you can use GO URL or the URL generated by IQY.


As you see it is a very simple feature but again that can be used on a regular basis for refreshing your data. The next feature that i wanted to talk about is the GO URL Auto Generation. If you do not want to remember the GO URL formats then you have another feature in the advanced tab of a report wherein you can get the GO URL link for the report. The first link that you see in the Advanced tab is the GO URL link for that report. Pretty simple features but again generally not noticed.

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

Oracle BI Publisher – Web Services using APEX

Posted by Venkatakrishnan J on October 30, 2007

If you had read my previous blog entry here on BI Publisher SOAP APIs, you would have noticed that i had used a evaluation tool called Mindreef SOAPScope to test out the web services. I was reminded of a much better in house Oracle tool yesterday by Adrian which is none other than the Application Express. Though i have used APEX before to test Web Services, i forgot to mention about it in that blog entry. So, if you want to test out BI Publisher Web Services, APEX would be best tool to start with. I thought i would write about it here today since APEX is a free tool and you can test web services out of the box using this.

1.   Log into your workspace in APEX. Create a new application with some blank pages. Navigate to the shared Components and click on Web Services References. Let us create one for BI Publisher. Ensure that you are not searching the UDDI registry since we have a WSDL already defined.



As you would see, APEX would show all the methods that are exposed. Remember that APEX currently supports WSDL with only one SOAP binding. One cannot use(i am not sure whether one can use manual registration to register multiple SOAP binding WSDL) multiple SOAP binding WSDL like the BI EE WSDL.

2.   Lets test out the simple validate login method. Lets create a simple form based on this Web Service in APEX.


Now lets enter the username and passwords to see the login method actually works. When i click on submit, the Validateloginreturn will get the value of true which basically indicates that this method has successfly got completed.


The above is pretty straightforward. But it is worth a mention since you can use APEX to test out all the methods and of course it would come very handy if you want to build a sample application which would call all these web services.

Posted in All Posts, APEX, Bi Publisher | 3 Comments »

Oracle BI EE – Presentation Layer Translation

Posted by Venkatakrishnan J on October 29, 2007

Another interesting question came up in the forums today for which there is no proper documentation or rather incomplete documentation. The question was how do we Externalize Presentation Layer i.e enable translation of Presentation Layer depending on the User Logged in Language. If you had gone through my previous blog entry here, you would have noticed that this option is primarily only for sections like Dashboard Names, Report Names etc. But this does not do the translation for the Subject Areas and their associated tables/columns. In order to do this we would have to follow another procedure called Externalizing Presentation Layer. Lets look at the steps one by one here today.

1.   Open Administration Console and right click on the Presentation Subject Area that you would like to Externalize. You would see 2 options there to externalize names and descriptions. For now lets externalize Names alone. So, click on Externalize Display Names.


2.   Once this is done go to Tools->Utilties and Click on Execute to Externalize Strings.


After this, you would see a screen that would look like this. What this basically shows you is the Names of the presentation catalogs. Save this as a csv file.


3.   Open up this Excel file and lets understand the structure of the csv. The first column represents Actual Names prefixed by their types of the Presentation Layer. The second column represents a list of Session variables prefixed by CN_. Remember that by default CN_ would be prefixed. The 3rd column represents the actual translation. Lets modify this csv and lets add one more column called language. This language would have values of Languages supported by OBI EE(abbreviations).


As you see above we have done translation of individual presentation layer table names and columns to languages. English and french.

4.   The next step is to load this CSV file into a database table.


In our case, the name of the database table is external which contains the data of the CSV.

5.   Now lets import this table into the Physical layer..


6.   The first step after importing this table is to create an initialization block to initialize the LOCALE variable based on the language selected by the user in Dashboard login. To do this create a session initialization block having the data source from a database. The database sql would have a sql like this


Also assign the target to the system session variable LOCALE. What this basically does is, whenever a user logs into dashboards the WEBLANGUAGE session variable is set. Then this variable sets the LOCALE variable using the initialization block.


7.   The next step is to create another session initialization block which would basically create a set of session variables using a database specific sql. The sql would look like this


As you see above, this block will create all the variables whose Language match the user’s login language. Set the target of the init block to Row Wise initialization and then select the previously created init block in the execution precedence. i.e we want the 1st block created earlier to execute first.




8.   Then login to Dashboards using French as the language.




As you see above all the translations corresponding to the french language on presentation layer has happened based on the user login. The documentation for this is not extensive though it is a bit straightforward once you know how to do it.

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

Oracle BI EE – Changing Passwords from Presentation Services

Posted by Venkatakrishnan J on October 28, 2007

OBI EE is so vast and has so many capabilities that i tend to learn at least one new information on this tool on a daily basis. For example, even though i knew about UDML generation using the nqUDMLexec.exe and nqUDMLgen.exe(i use this on a regular basis), i never realized that copying of objects within a repository actually generates UDML which can indeed be seen if you paste it in say notepad/wordpad. I came to know this from Mark’s blog entry here. Similarly, so far i was under the impression that passwords of users can be changed only from the admin tool. But i was proved wrong by Daan Bakboord who had given a wonderful reply to this forum question here. Let us try to understand this in detail. Generally, there is no option within Dashboards ,Answers (Presentation Services in general) to change the passwords by default. But if at all there is a requirement wherein we would need to have the capability of individual users changing the password, then the solution is to uncomment a commented line in a System XML file. To do this, one would have to follow the steps as below.

1.   Go to {OracleBI}/web/msgdb/messages. Open the controlmessages.xml file in a text editor.

2.   Search for kmsgChangePasswordLink in the file and uncomment the HTML tags. Sample, controlmessages.xml file is given below.


3.   Restart Presentation services and the BI Server.

4.   Login to the dashboards. Go to Settings->My Account. You would see a change password link using which you should be able to change the passwords.


I am not sure whether this would work for LDAP and database authentications. I dont think it would( a guess here, others can correct me) and thats why i believe it has not been enabled by default.

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

Oracle BI EE – Styles and Skins – Firebug to your Rescue

Posted by Venkatakrishnan J on October 26, 2007

Currently, there is no proper documentation on the various java scripts, css files etc that come along with the styles in BI EE. So, if you are part of the UI team and would like to customize OBI EE but do not know where to start, do not despair. There is a wonderful little extension called Firebug(only for firefox), that will let you know which css, html, java scripts are used where in the dashboards, answers etc. This extension is available only for firefox. If you want a similar one for IE, you can get the instructions of using firebug on IE here.  You can download the Firebug extension for Firefox here


So, if you want to know which part of the screen a particular CSS is being used, just scroll over the CSS and that part of the screen will be highlighted as shown below. This utility is very handy if you want to make some minor modifications to your CSS files.


Just thought i would blog about it since this is a very common question on the customization front.

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

Oracle BI Publisher – Web Services – First Impressions

Posted by Venkatakrishnan J on October 25, 2007

I was going through the new BI Publisher feature of which is the Web Service APIs. Till the earlier release we only had Java APIs. But in the latest release we have the SOAP APIs or the WSDL APIs so that any end user can access the reports remotely using standard web service calls. To try this out, i had installed JDeveloper 11g Tech Preview 2. But somehow i was not able to make the Web Services work from JDeveloper. I constantly got “not a recognized SOAP header error”. So, i downloaded another tool called Mindreef SOAPScope. They provide an evaluation copy for 13 days. Well, i was literally amazed by the power of this tool. It would be good to have similar features in JDeveloper. You can download SOAPScope here.

Coming back to BI Publisher WSDL, there are 7 main methods that are exposed to end users. They are

1.   getFolderContents   –   This method helps in retrieving all the contents within a folder.

2.   getReportDefinition   –   As the name suggests this retrieves the report definition for the report which you give as a parameter.

3.   getReportParameters   –   This retrieves the parameters of the report.

4.   hasReportAccess   –   This checks whether a particular user has access to the report specified.

5.   runReport   –   This is the method which would help in running the report. One can retrieve the contents in the form of PDF, HTML etc.

6.   scheduleReport   –   This method exposes the scheduler functionality of BI Publisher.

7.   validateLogin   –   This checks whether a specific user can log into BI Publisher.

The below picture gives all the methods as seen by SOAPScope.


validateLogin: Now lets test out all the methods starting from validateLogin. As shown below this method accepts two parameters which is the username and the password. The return field of this function is a boolean data type which would give a true or false depending on the user credentials.



getFolderContents: The next method which we shall test is the getFolderContents. This method accepts three parameters. Username, Password and the absolute path of the foldername. For example,

Folder Name: /HR Manager
Username: Administrator
Password: Administrator

The absolute path is actually relative to the URL. I am not sure whether one can access my folders content since the absolute path seem to take all the folders only inside the Shared Folders. The return data set of this function is all the contents of this folder including subfolders and reports(not the recursive reports).



getReportDefinition: This method accepts username, password and the absolute path of the report as its input. The return type is its definition that would include the parameters, templates, data sources etc. The reportAbsolute path should include the xdo path. Eg. /HR Manager/Employee Salary Report/Employee Salary Report.xdo



getReportParameters: This method accepts username, password and the reportRequest as its parameter. reportRequest is a structure that has arguments like
      Parameter Array

In our case we would just include reportAbsolutePath, username and the password. This would return 2 parameters of the Employee Salary Report which is the department and employees.



hasReportAccess: This accepts username, password and the reportabsolute path as the set of parameters. It checks whether the user specified by the username parameter has the privilege to run the report.



runReport: This is the method which would help in running the report. It accepts the same set of parameters like the getReportParameters. The output would contain a Doublebinary byte which would have to be converted to the corresponding output like html, pdf etc.



scheduleReport: This method exposes the entire scheduler functionality. There are a lot of parameters which i would not go in details since most are self explanatory.


I tried creating a sample application. But i am not sure whether the documentation has specified all the jars required in the invoking client as i had to include 3 more jars.

Posted in All Posts, Bi Publisher | 3 Comments »

Oracle BI EE – Audio, Video and Maps

Posted by Venkatakrishnan J on October 25, 2007

I came across a very interesting piece of video wherein interaction between Maps and OBI EE was shown. I am not sure what Map is being used here(i think it is Google Maps or Mapviewer. Not sure exactly). But a wonderful video neverthless.

Now the next question is how do we embed this video in BI EE. It is pretty straight forward. Open a report. Add any dummy coumn and hide the column. Now go into Static Text view. Put the embed object into the Static Text view. Also, enable the HTML Markup check box. Now you should be able to see the video.


The same applies for audio too. So, in effect you can listen to content specific songs while viewing the reports :-).

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

Oracle BI EE – Custom Drill Path Navigation

Posted by Venkatakrishnan J on October 24, 2007

One of the common questions that we typically get in the forums is how do we enable multiple custom drill paths. For example, check the forum entry here wherein the user wanted to have multiple drill paths. The user himself came up with an answer which i actually had forgotten about. Its a very interesting way of creating multiple drill paths. I thought i would blog about both the methods(including the one that i had suggested) as this might prove useful for others who want to have custom drill paths.

Lets first take an example. I will take the same example as was asked in the forum entry above. The rephrased question for the sake of simplicity is “How do we enable users to traverse the entire drill hierarchy? For example, if we have Year->Quarter->Month->Day, how can we make the users jump from Year to Month or even Year to Day directly”. Well, this is a pretty straightforward request but this capability is not available out of the box using OBI EE So, lets take the first approach of using column selector views. Lets start with creating a sample report with Brand, Region and Sales. We will include the Period dimension once this report has been created.


Lets pull in the Year column into the report and go into the Column Selector view. In this view add both Month and Week columns as shown below. We are basically creating a column selector view with Year, Month and Week columns so that we have the flexibility of selecting any one of them as they are part of the same hierarchy.


Include this column selector view into the compound layout. Now we have achieved the flexibility have both the custom drills and choosing any drill levels in a hierarchy. Its basically a column selector view implementation but used for select hierarchical attributes.


Lets look at the second method wherein for each drill path we will be creating a new report. Then we will be adding these reports as navigation targets for the Year column. So, when clicking on year we would basicaly have all the reports displayed as drill paths that we can choose to achieve the necessary drills. This method is not that flexible in the sense that one would have to keep on adding new reports for each and every drill path. For our example, in addition to the base report having Year, Region, Brand and Sales as columns, we would be creating 2 more reports. First report would have Year, Month, Region, Brand and Sales as columns to achieve Year->Month drill. Second report would have Year, Month, Week, Region, Brand and Sales as columns to achieve the Year->Month->Week drill. For both these reports create a criteria on Year with is prompted clause.


Once this is done. Go to the Year Column properties of the base report and add these 2 reports as the navigation targets.


So basically when we click on Year now we would get 2 drill paths having the report names that we just created.


Of course, there are also other methods depending on specific requirements. But the above should give a basic idea if you have a similar requirement.

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

Oracle BI EE – Open Intelligence Interface

Posted by Venkatakrishnan J on October 23, 2007

One of the major advantages of BI EE is that BI Server exposes itself in the form of ODBC so that any other utlity/reporting tool can leverage the metadata of BI EE. In fact, any other reporting tool(which can use ODBC) can leverage this metadata. Let us take a sample scenario. Lets say we have installed BI EE on one machine A(including BI Server and Presentation Services). And say you have another reporting tool on Machine B. So, the question is how do we make the reporting tool on Machine B leverage the metadata of BI EE, which is on machine A? To understand better i have depicted the scenario using a diagram below.


So, coming back to question of how do we make BI EE available to the Custom Reporting Tool on Machine B, all we need to do is to install a product called Open Intelligence Interface on machine B. It generally is bundled under the Client_Ancillary folder of the BI EE software bundle. What this product basically does is it installs a ODBC driver for Oracle BI Server. Once you have this installed this product you can create a ODBC DSN using the Oracle BI Server ODBC driver and can connect to any BI Server. So, while creating the DSN just use the Oracle BI Server odbc driver as shown below.



Now you can use this DSN within your reporting tool and can start reporting using BI Server metadata. Though this is pretty straight forward i thought i would blog about this since it is a very handy utility

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