This article describes a mechanism to create reports in excel using the relations established in Oracle BI Server. We can leverage BI server and the relations established in it to create quick reports in excel. The last paragraph of the article talks about using the iqy file to get data with OBIEE's formatting.
I am using ODBC driver to connect to the BI server. Presentation server also uses ODBC to connect to BI Server.
Let’s see how it’s done
1. Open excel and go to Data tab. Click on ‘From Other Sources’ button. It’s the 4th one in the ribbon. Select ‘From Data Connection Wizard’ from the list
2. Select ODBC DSN, select your ODBC connection and then put your repository credentials. I am using AnalyticsWeb data source to connect to paint repository
3. I got the following message after this :(
4. Click on OK. Data connection wizard will pop up. Select your data source and click on Next
5. Bingo! I could see the 2 subject areas in the drop down. Uncheck ‘Connect to specific database’ checkbox(Checkbox is right under the dropdown). Click Next, select the file name to store your credentials and then click Finish
6. Select your data source and enter your credentials again. Data source will be listed under ‘Machine Data Source’ tab
7. You will get a list of all the tables listed in your presentation layer
8. Select a table and click ok. Select your preferred report view
9. Got the following message.
10. Clicking on ok will take you to the prompt shown in step 6. Select your DSN and enter your repository credentials again and taste the sweet result :)
I must point out that excel has ways to refresh data at a set frequency. It can create a number of beautiful charts, graphs etc. If you are good at writing scripts in excel, then you can work wonders with this technique.
If you want to get an OBIEE answer with OBIEE's formatting in Excel then you can generate the .iqy file for it. Option to generate the iqy file is under the advanced tab in an OBIEE answer.
.iqy file opens in excel and downloads the answer with OBIEE's formatting in excel, after connecting to the presentation server.
The mechanism listed here connects to BI server.
So the deal is, if you want data with OBIEE's formatting then use iqy and if you just want to leverage the joins in the repository then use this mechanism
5 comments:
Hi there,
When I am trying this mechanism, I am getting an architecture mismatch error on the data sources screen of excel. I have windows 64 bit.
Hi,
I did it this windows 7 which is 64 bit.
Follow the exact steps mentioned in this post. You will get some unfavorable messages and I have pointed those out.
Make sure that you have access to the BI server from your machine
This mechanism does not involve a lot of technical stuff. It just connects to BI server and the rest is done in BI server.
So your 1st check should be that your BI server should be up and running. Start your presentation services and see if you can run your reports.
If the above check is successful then run the following command
telnet <BI_Server_ip_address> <Port_on_which_BI_server_is_running_default_9703>
The above command will tell you whether you are able to reach your BI server from the machine which has MS Excel on it.
If this works then you are just missing one of the steps mentioned in my post.
Let me know if you still find some trouble
PS: I hope you are doing this on OBIEE 10G. I believe that fair amount of changes will be required to make this work on OBIEE 11G. I haven't tried this out on 11G
Regards,
Vishal
Hi Vishal,
I tried to connect within this technology:
- OBIEE server 11.1.1.6 on Linux x64
- Windows 7 x64
- OBIEE Client tools 11.1.1.6 32-bit
- MS Office Professional Plus 2010 32-bit
When I try to connect via Excel I am stuck at step #2. I choose ODBC DSN and then a particular ODBC DSN, but no dialog window appears to input credentials. Double click nor Next button simply doesn't work.
I tried to choose Other/Advanced instead of ODBC DSN and then Microsoft OLE DB Provider for ODBC drivers. Then I am able to choose a particular ODBC DSN, put down credentials and test connection. It says connection is working. And can even choose a catalog from the presentation layer of a repository. But next OK leads to the failure: Unable to retrieve the list of tables. Then it brings me to the previous screen with credentials.
I checked the Oracle support knowledge base and didn't find anything related.
Michal
Hi Vishal,
Right now we are doing something similar but with 2 main differences:
1. We are doing it on OBIEE 11G (11.1.1.5).
2. We are creating the connection from excel but using VBA (Visual Basic).
Until now we are having excellent results (We are creating reports and consulting info from BI Server from excel). The only thing that we want to change is the creation of DSN. Because we have installed the admin tool, we can make use of the drivers that come with it to create the DSN in the ODBC administration tool of windows. But our final goal is to be able to create the DSN without having installed the admin tool. Our question for you is ¿ Is there some way to get the drivers or an installer of those ?
Thank you so much for your help. :)
Hi Jamie,
I wish, I could help. I haven't started working on 11G yet so cannot really present a solution for you. I wonder is there is some kind of Advanced installation of the Admin tool in OBIEE 11G which lets us selectively install the components of 11G client. Atleast this kind of thing is there in the 11G database server.
Since the obiee 11g client is the only source of getting this driver so I believe we cannot have a solution unless the developers of OBIEE want us to have one
I wish I could help more
Regards,
Vishal
Post a Comment