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

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.

   

3. Click Finish.

   

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.

   

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 - Airlines Delay.

   

8. Click Finish.

   

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.

   

2. Select the DSN entry created for connecting to OBIEE Server (for example, airlines) and click OK.

   

3. Enter your OBIEE Server login credentials and click OK.

   

4. Click OK.

   

5. Click Options.

   

6. Select Views and List Tables and Columns in alphabetical order option, leave the other options selected, and click OK.

   

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

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.

   

3. Click OK.

   

4. Add a criteria to filter records with the number of cancelled flights greater than 500 by performing the following steps:

   1. From the menu select Criteria, Add Criteria to open the Add Criteria dialog box.
   2. In the Field drop-down list, select Flight Facts.# of Cancelled Flights.
   3. In the Operator drop-down list, select is greater than.
   4. In the Value text box, type 500.

   

   5. Click Add.

   

5. Select View > SQL from the menu to view the resulting SQL statement.

   

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, select and copy the logical SQL statement into the clipboard.

   

2. In Microsoft Query in the SQL dialog box, paste the SQL statement from the clipboard.

   

3. Click OK.

   

4. Click OK.

   

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.

   

2. Set destination options for the import. For example, accept the defaults and click OK.

   

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

   1. In the Design ribbon, select Refresh and then Connection Properties.

      

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

      

   3. 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:

• 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

• All other tutorials related to Oracle Business Intelligence
• Oracle Business Intelligence documentation
• Oracle Business Intelligence product page on OTN
• Oracle Learning Library
• Oracle University