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:

Setting Up ODBC Connection to OBIEE Server

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.

    alt description here
  2. Select the System DSN tab, click Add, and then select the Oracle BI Server 1 driver from the list.

    alt description here
  3. Click Finish.

    Oracle BI Server DSN Configuration Wizard

    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.

    OBI Server DSN Configuration Wizard - DSN Name and Description
  5. Click Next.

    OBI Server DSN Configuration Wizard - DSN Log In information
  6. Enter your login credentials for the Oracle BI Server and click Next.

    OBI Server DSN Configuration Wizard - DSN Defaults

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

    OBI Server DSN Configuration Wizard - DSN Defaults
  8. Click Finish.

    ODBC Data Source Administrator - Systm DSN tab

    The airlines DSN is added to the System DSN list.

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

  1. Open Excel, select the Data ribbon, expand From Other Sources, and select From Microsoft Query.

    Excel

    The Choose Data Source dialog box is displayed.

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

    Oracle BI Server Login dialog box

    The Oracle BI Server Login dialog box is displayed.

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

    Microsoft Query exception

    The Microsoft Query warning is displayed.

  4. Click OK.

    Microsoft Query selection

    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.

  5. Click Options.

    Table Options

    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.

    Table Selection

    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.

    Subject area objects

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:

Flight Delays Report Results

The report was based on the following selection of columns and filters:

Flight Delays Report Selected Columns

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.
    Selected columns
  2. Click Next.

    MS Query Warning message

    The Query Wizard displays a warning message that it cannot join the tables.

  3. Click OK.

    MS Query Results

    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:

    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. MS Query Add Criteria
    6. Click Add.
    7. MS Query Filtered Results
  5. Select View > SQL from the menu to view the resulting SQL statement.

    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, select and copy the logical SQL statement into the clipboard.
    SQL Statement
  2. In Microsoft Query in the SQL dialog box, paste the SQL statement from the clipboard.

    alt description here

    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.

    Excel Connection Properties

    Microsoft Query displays a warning message that it cannot represent the query graphically.

  4. Click OK.

    Report results

    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.

  1. In Microsoft Query, select File > Return Data to Microsoft Office Excel.
    alt description here

    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.

    alt description here

    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.

  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.

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

    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.

      Connection Properties - Refresh Settings
    3. In the Connection Properties dialog box, select the Definition tab. Click the Edit Query button to open Microsoft Query to edit the query.

      Connection Properties - Refresh Settings

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.