Jumbabox

Earlier I posted about how to use an Excel Pivot Table with an Access database, today I thought I would go through how you can connect an Excel Pivot Table to an Oracle database.

This tutorial assumes you have an Oracle client installed on your PC already, this can also work with Oracle 10g XE if you download the client side for XE (click here to download the Oracle 10g XE client ~30mb).

Step 1 - The Data Sources (ODBC) Administrator

Click on Start > Settings > Control Panel

Once the control panel dialog box has opened, locate and double click on Administrative Tools.

Then click on Data Sources (ODBC)

You should now see the ODBC dialog box. Under the User DSN tab click on the Add… button on the right hand side of the ODBC Data Source Administrator dialog box.

Step 2 - Creating a new ODBC Connection

A Create New Data Source dialog box should pop up with a list of all available drivers on your computer.

Browse to the bottom of the list and you should find an Oracle driver, in this case I’m using the Oracle 9 driver (I have also tested this with Oracle 10g and 10g XE without any problems).

Once you have found your Oracle driver press the Finish button and another new dialog box will appear with the configuration settings for your driver Oracle ODBC Driver (seen below).

Step 3 - Configure your Oracle driver

Firstly, give your new data source a name, in this example I will be calling mine JUMBABOX_ORCL.

Then enter a description about your data source.

The next step is to choose your TNS Service Name, this should appear in the drop down box. If you can not see your TNS Service Name in the drop down box, type it in manually which is what I did. If nothing is in the drop down box, you can double check your TNS Service Name’s via the tnsnames.ora file (do a simple search in Windows to find it).

In this example my TNS Service Name is EARTH_ORCL_JB.

Next, put in your User ID, in this example it is EARTH_DBA and click the Test Connection button.

A new dialog box will pop up asking for a password, enter your password for this data source and click OK.

If successful you should see this message box.

If you get this error, you have no entry for your specified TNS Service Name in your tnsnames.ora file.

If you get this error, you have an entry in your tnsnames.ora file for your TNS Service Name however there are problems connecting to your data source.

After a successful test, click the OK button. You should be able to see your new ODBC Data Source connection under the USER DNS tab.

Step 4 - Setting up your Pivot Table

Open up a blank worksheet, and create a new Pivot Table.

The PivotTable and PivotChart wizard will pop up asking you where the data is that you would like to analyze, make sure you check the External data source option and click Next.

The PivotTable and PivotChart Wizard - Step 2 of 3 dialog box will pop up. As you can see No data fields have been retrieved, click on the Get Data… button to continue.

A dialog box will pop up asking which Data Source you would like to use, you should see the Data Source connection we created earlier in the list, click on it and click on the OK button.

It will ask you once again to confirm your User Id and Password, put them in and click the OK button.

The next dialog box will list all the available table names for you to base your Pivot Table on (a lot of these are system tables), locate the table or tables which you want to build your Pivot Table on.

Click on the table and then click the > arrow button, this will move across all the fields associated with that table, you can choose to only move over certain columns by selecting each column name and clicking the > arrow button as well. Click the Next button to move on.

I ignored the next two steps (as the Pivot Table can do these) however these steps will allow you to:

1. Filter your data before it enters the Pivot Table (good for large data sets and improving performance)
2. Sort your data before it enters the Pivot Table

Here are screen shots of the steps:

Filter Data

Sort Data

Click the Next > button twice to get through these steps.

The last part of the wizard will ask you to either:

• Return Data to Microsoft Office Excel - This will close down the Query Wizard and resume finishing the Pivot Table setup.
• View data or edit query in Microsoft Query - This will open up Microsoft Query to edit your query further (editing the native SQL statement). I would suggest this if you are basing a Pivot Table on multiple tables.
• Create an OLAP Cube from this query - You can use this option to create an OLAP cube from the query.

I selected the Return Data to Microsoft Office Excel then clicked the Finish button, as I wanted to start using my Pivot Table straight away.

Step 4 - Setting up the Pivot Table

After you click the Finish button, the original dialog box will let you know that Data Fields have been retrieved, this is letting us know that all the data has been retrieved for the Pivot Table successfully. Click the Next > button to continue.

The next step is part of the standard Pivot Table wizard. It will ask you where you would like to put your Pivot Table. Select to put it in an existing worksheet starting at a certain cell or in a new sheet. Click on the Finish button to continue.

Done! Now you have a Pivot Table which is based on a table or tables in Oracle.

Something to Remember

If the data in the Oracle database changes or is refreshed, to reflect the same changes in your Pivot Table you must refresh your Pivot Table. This can be done by activating the PivotTable toolbar (right click in the tool bar section at the top of Excel and select Pivot Table) and click on the ! (Refresh) button.

Your Pivot Table should now be refreshed.

If you have any questions or problems please comment.

Enjoy!