[OBIEE 10g] Creating reports in Excel using OBI server

 

Creating reports in Excel using OBI server

This 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.

   1.   Open excel and go to Data tab. Click on ‘From Other Sources’ button. It’s the 4^th 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 

   2.   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.