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