Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
Importing OBIEE Data into Microsoft Excel
Purpose
This tutorial covers steps to import data from Oracle Business Intelligence Enterprise Edition (OBIEE) subject areas into Microsoft Excel via native ODBC connectivity.
Time to Complete
Approximately 20 minutes.
Introduction
You can use Microsoft Query and ODBC to connect to the OBIEE Server and then import data from BI subject areas into Microsoft Excel. Microsoft Query enables you to use its QueryWizard to build queries that you can further fine tune to retrieve required data. In this tutorial you learn how to:
Set up an ODBC connection to the OBIEE Server
Create an external connection to the OBIEE Server from Excel
Build a query in Microsoft Query to retrieve data from the OBIEE ServerImport the query result into an Excel Worksheet
Prerequisites
Before starting this tutorial, you should:
Have access to or have installed OBI EE 11.1.1.7.0.
Have access to or have installed the OBIEE Sample Application.
To be able to connect to OBIEE Server from Excel, you need to create an ODBC connection:
1.Open ODBC Data Source Administrator and select the Drivers tab to check that the Oracle BI Server 1 driver is installed on your system.
2.Select the System DSN tab, click Add, and then select the Oracle BI Server 1 driver from the list.
第1页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
3.Click Finish.
The Oracle BI Server DSN Configuration Wizard is displayed.
4.Specify the name and optionally description for the DSN entry. For example, type airlines for Name and Oracle BI Server - Airlines Delay for Description.
5.Click Next.
6.Enter your login credentials for the Oracle BI Server and click Next.
第2页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
The wizard page with DSN defaults is displayed.
7.Select the Change the default subject area to option and select a subject area that you want to make as the default for the DSN entry. For example, select X - AirlinesDelay.
8.Click Finish.
The airlines DSN is added to the System DSN list.9.Click OK to close ODBC Data Source Administrator.
After you have created an ODBC DSN entry for the OBIEE Server, you can access the data source from Excel.
1.Open Excel, select the Data ribbon, expand From Other Sources, and select From Microsoft Query.
第3页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
The Choose Data Source dialog box is displayed.
2.Select the DSN entry created for connecting to OBIEE Server (for example, airlines) and click OK.
The Oracle BI Server Login dialog box is displayed.3.Enter your OBIEE Server login credentials and click OK.
The Microsoft Query warning is displayed.4.Click OK.
The Query Wizard - Choose Columns dialog box is displayed. Note that the Query Wizard may not display the available tables in which case you may need to changethe table options and refresh the list.5.Click Options.
第4页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
The Table Options dialog box is displayed.
6.Select Views and List Tables and Columns in alphabetical order option, leave the other options selected, and click OK.
The list of available tables and columns is populated from the OBIEE Server. Table names listed in the Query Wizard are derived from the Presentation Level
hierarchical objects representing each hierarchy level individually. For example, the Day table is listed as a separate table in the Query Wizard whereas it is a childobject of Date in the corresponding OBIEE subject area.
After you have gained access to the OBIEE Server data, you can select tables and columns for your query. For example, you want to build a query that returns the following report onairports with scheduled and cancelled flights:
第5页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
Selecting Tables and Columns in the Query Wizard
1.In the Query Wizard - Choose Columns page, in the Available tables and columns area, expand tables and select columns for your query.
2.Click Next.
The Query Wizard displays a warning message that it cannot join the tables.3.Click OK.
第6页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
The Query Wizard displays the results of the query.
4.Add a criteria to filter records with the number of cancelled flights greater than 500 by performing the following steps:
a.From the menu select Criteria, Add Criteria to open the Add Criteria dialog box.b.In the Field drop-down list, select Flight Facts.# of Cancelled Flights.c.In the Operator drop-down list, select is greater than.d.
In the Value text box, type 500.
e.Click Add.
第7页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
5.Select View > SQL from the menu to view the resulting SQL statement.
The SQL dialog box displays the SQL statement for the query. You can refine the query by editing the SQL text and then click OK to refresh the query result.
Optional. Overriding the Query From a Generated SQL Statement
You can also override the existing query from a SQL statement generated by OBIEE for your report.
1.Create and run a report in BI Answers and then open the session cursors in the Administration panel. In the Cursor Cache table, locate the SQL statement for the report, selectand copy the logical SQL statement into the clipboard.
第8页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
Note: You may want to edit the pasted SQL statement before submitting it to Microsoft Query (for example, remove the first column that returns zeros).3.Click OK.
Microsoft Query displays a warning message that it cannot represent the query graphically.4.Click OK.
Microsoft Query runs the query and displays the returned data.
第9页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
After you have built the query, you can import the query result into an Excel worksheet.
1.In Microsoft Query, select File > Return Data to Microsoft Office Excel.
You are returned to Excel and the Import Data dialog box is displayed.
2.Set destination options for the import. For example, accept the defaults and click OK.
第10页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
a.In the Design ribbon, select Refresh and then Connection Properties.
Note: You can access the connection properties item from the Data ribbon by selecting Connections and then clicking Properties for a specific connection.
b.In the Connection Properties dialog box, select the Usage tab, and configure refresh controls as desired. For example, you can set data to be refreshed at intervals andwhen the Excel worksheet is opened.
c.In the Connection Properties dialog box, select the Definition tab. Click the Edit Query button to open Microsoft Query to edit the query.
In this tutorial, you learned to:
第11页 共12页2014-2-14 12:02
Importing OBIEE Data into Microsoft Excelhttp://www.oracle.com/webfolder/technetwork/tutorials/ob...
Resources
All other tutorials related to Oracle Business IntelligenceOracle Business Intelligence documentation
Oracle Business Intelligence product page on OTNOracle Learning LibraryOracle University
第12页 共12页2014-2-14 12:02
