Importing OBIEE Data into Microsoft Excel
Overview
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 Query Wizard 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 Server
- Import 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.
Setting Up ODBC Connection to OBIEE Server
To be able to connect to OBIEE Server from Excel, you need to create an ODBC connection:
-
Open ODBC Data Source Administrator and select the Drivers tab to check that the Oracle BI Server 1 driver is installed on your system.
-
Select the System DSN tab, click Add, and then select the Oracle BI Server 1 driver from the list.
-
Click Finish.
The Oracle BI Server DSN Configuration Wizard is displayed.
-
Specify the name and optionally description for the DSN entry. For example, type airlines for Name and Oracle BI Server - Airlines Delay for Description.
-
Click Next.
-
Enter your login credentials for the Oracle BI Server and click Next.
The wizard page with DSN defaults is displayed.
-
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 - Airlines Delay.
Click Finish.
The airlines DSN is added to the System DSN list.
-
Click OK to close ODBC Data Source Administrator.
Connecting to OBIEE Server from Excel
After you have created an ODBC DSN entry for the OBIEE Server, you can access the data source from Excel.
-
Open Excel, select the Data ribbon, expand From Other Sources, and select From Microsoft Query.
The Choose Data Source dialog box is displayed.
-
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.
-
Enter your OBIEE Server login credentials and click OK.
The Microsoft Query warning is displayed.
-
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 change the table options and refresh the list.
-
Click Options.
The Table Options dialog box is displayed.
-
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 child object of Date in the corresponding OBIEE subject area.
Building a Query
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 on airports with scheduled and cancelled flights:
The report was based on the following selection of columns and filters:
Selecting Tables and Columns in the Query Wizard
- In the Query Wizard - Choose Columns page, in the Available tables and columns area, expand tables and select columns for your query.
-
Click Next.
The Query Wizard displays a warning message that it cannot join the tables.
-
Click OK.
The Query Wizard displays the results of the query.
-
Add a criteria to filter records with the number of cancelled flights greater than 500 by performing the following steps:
- From the menu select Criteria, Add Criteria to open the Add Criteria dialog box.
- In the Field drop-down list, select Flight Facts.# of Cancelled Flights.
- In the Operator drop-down list, select is greater than.
- In the Value text box, type 500.
- Click Add.
-
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.
- 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, select and copy the logical SQL statement into the clipboard.
-
In Microsoft Query in the SQL dialog box, paste the SQL statement from the clipboard.
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).
-
Click OK.
Microsoft Query displays a warning message that it cannot represent the query graphically.
-
Click OK.
Microsoft Query runs the query and displays the returned data.
Importing Query Result into an Excel Worksheet
After you have built the query, you can import the query result into an Excel worksheet.
- In Microsoft Query, select File > Return Data to Microsoft Office Excel.
You are returned to Excel and the Import Data dialog box is displayed.
-
Set destination options for the import. For example, accept the defaults and click OK.
The query is being executed and the resulting data is returned to Excel. From this point, you can use all Excel analytic tools for further data analysis.
-
Optional. You can manage the established external connection to the OBIEE Server in Excel. For example, you can configure how the query result gets refreshed in your Excel worksheet or you can edit the query.
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.
-
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 and when the Excel worksheet is opened.
-
In the Connection Properties dialog box, select the Definition tab. Click the Edit Query button to open Microsoft Query to edit the query.
Summary
In this tutorial, you learned 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 Server
- Import the query result into an Excel Worksheet
Resources
To navigate this Oracle by Example tutorial, note the following:
- Topic List:
- Click a topic to navigate to that section.
- Expand All Topics:
- Click the button to show or hide the details for the sections. By default, all topics are collapsed.
- Hide All Images:
- Click the button to show or hide the screenshots. By default, all images are displayed.
- Print:
- Click the button to print the content. The content that is currently displayed or hidden is printed.
To navigate to a particular section in this tutorial, select the topic from the list.