Scientific Data Server User Manual

Excel 2003 Pivot Tables - Connecting to the Data Cubes

Links to Other Manual Sections
  1. Overview of Server
  2. Data Dimensions
  3. Data Access Through the Web
  4. Data Access From Excel 2007


Using Microsoft Excel, the data can be accessed by connecting your local Excel installation to the data on the server. This interface makes use of Pivot Tables and Charts to display the data and allows a great deal of flexibility in defining what data you are viewing. Step-by-step instructions for accessing the data via Excel Pivot Tables can be found here. Before using the data, you might find it useful to understand a little of how we get, organize, and categorize the data on the server. The next section explains the data organization; please read it before using the data for serious science since all of the data selection interfaces are based on this organization.

Data Access From Excel 2003

1. Configuring Excel
2. Pivot Table Layout

Configuring Excel

In order to access the data cube from Excel, you will need Excel 2003 or Excel 2007. The instructions below are for Excel 2003. For information on connecting using Excel 2007, please go here.

If you are using Excel 2003 you will likely need to install some additional features. The typical install of Excel 2003 does not install some of the libraries needed to perform external data connections. So, the first thing you will need to do is to install these libraries. The easiest way to find out whether you have the necessary libraries installed is to try using them so the instructions for installing the necessary libraries are included below.

1. To begin, open a blank excel worksheet
2. Using the top menu choose Data -> "Pivot Table and Pivot chart report"
3. Choose External data source and PivotTable. Then click on the next button.
4. Click on the Get Data button

There is a fair chance that you will get a window at this point indicating that this requires an additional feature that is not currently installed in Office and asking whether you would like to install it. Click yes and then proceed with the installation. Installation will likely require your Office CD so you should have it handy.

5. Click on the "OLAP Cubes" tab and then choose "<New Data Source>" and click on OK.

6. In the next window, type in a name you want to use to refer to this data connection to the data cube and Select the Analysis Services 9.0 as the OLAP provider. Then click on "Connect..."

If Analysis Services 8.0 is the only provider you see then you need to add OLE DS for Analysis Services 9.0 to your instance of Excel using the directions that follow.

Go to the URL below and download and install Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider Follow the link under "Microsoft Core XML Services (MSXML) 6.0" to the download site and download the file labeled “msxml6.msi”. Then go back to and download " SQLServer2005_ASOLEDB9.msi” under the heading "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider" and install the two files in the order listed above (msxml6.msi first). Once you have done this, you should have analysis server 9.0 connectivity available.

IF IT STILL DOES NOT WORK - Go to the start menu, click on Run and put the following command into the box.

regsvr32 "c:/program files/common files/system/ole db/msolap90.dll". This program puts dll files in the Windows registry.

7. In the next window choose "Analysis server", enter the name of the server "", and enter the access username and password. Then click "Next". (If your machine is part of a domain, you may need to specify the username as BWC\username.

8. If all goes well and your local Excel is able to connect over the Internet to the server then the next window will show you a list of available cubes. Choose the cube you wish to connect to (below I choose LatestORNLDaily) and then click "Finish".

9. This will return you to the "Create New Data Source" window. Check the box in front of "Save my user ID and password . . ." and click yes on the warning window that pops up. Then click "OK".

10. In the next window choose your new data source and click "OK".

11. The window you had clicked data in (step 4) will come back up. Now choose "Next".

12. The window that comes up allows you to choose where to place the pivot table on the spreadsheet and to choose how you want to layout the pivot table. If you want the pivot table to appear on a new worksheet in this excel document than choose "New worksheet". If you want it to appear in the current worksheet, specify what cell should be the upper left corner of the pivot table $A$3 (upper left corner) is the default. You now have two options for laying out the pivot table. These are explained in the 12a and 12b instructions below. Each of the two layout methods have their pros and cons. I personally prefer the method 12b.

12a. The first method of laying out the pivot table is to choose what fields should be columns and rows and how the table should look before creating the pivot table. To use this method, choose "Layout.." in the window above. This will bring up the layout wizard.

In order to layout the pivot table, you need to drag the boxes on the right hand side to the image of a pivot table on the left. For instance, suppose you want the sites to be columns. Then drag the "Site" box to the "COLUMN" section of the pivot table. If you want the years going down in rows, then drag "Year" to "ROW". The page filters go in the "PAGE" section. The value that you want to see e.g. "Average" should be dragged into the "DATA" section. (Further explanation of the meanings of the various boxes and layout areas in the pivot table is provided in instruction 13 of this manual. Below is a completed example layout.

Once you are done specifying how you want the pivot table to look, then click on "OK". It may take a short while for the pivot table to appear since the pivot table will be built on the server to your specifications and then displayed in your excel worksheet.

12b. The second method of laying out a pivot table is to bring up an empty pivot table and drag the fields to the row, column, page, and data area. To do this, in the window you had up in instruction 12 choose "Finish" (instead of "Layout" which you chose for 12a). This will bring up an empy pivot table.

To lay out the pivot table, drag items from the "PivotTable Field List" on the right into the desired location on the pivot table (page fields, columns, rows, or data) . Each time you drag an item into an area, you may see a short delay each time you drag a field onto the pivot table because the server will build a list of the items in the field and display them. There are several tricks to speeding the overall layout process up. For instance, each time you drag a field into the pivot table, you can click on the arrow to the right of the field name on the pivot table and then only select the desired items. Select the "Data" item last since once you choose the data item, the server will begin sending all the values for the pivot table every time you make a change in the layout. Below is a partially layed out pivot table.

13. Pivot tables are incredibly powerful for browsing the data but it is helpful to understand how the data is organized to use the page filters, columns, and rows correctly. For most of the data subes you will encounter, there are multiple datasets, offsets, exdatumtypes, and repeats. If you do not specify these dimensions explicitly you will get whatever is the defined default behaviour for the field. This might be to display all items in the dimension or only one. Page filters are items that you want to be applied as filters to the data. For example, if you want to see data from only a single site in the table, then put "Site" in the page filters and choose only the one site. It is good practice to specify all dimensions on the pivot table. So, dimensions that you are not laying out in the columns and rows should all appear in the page filters. The only exceptions are the alternate time dimensions and the various was of specifying sites.

The time dimensions can be a bit confusing at first. There are many to allow for flexibility. I will try to give you a basic idea of how to interpret the times. The time field such as "Year to Day" and "Year to Month" will provide you a hierarchical display of the time dimension. For example, when "Year to Day" first displays, it will show all years there is data for. If you want to drill down and see individual days in a particular year, then open the selection menu in the arrow on the right of the field and click again on that year. A second check mark should appear in the box. If you return to the pivot table you will see that the years are all still shown but that the year with the double check mark is now broken down into days. Time fields such as "Year, Month of Year, Day of Year, and Hour of Day" are flat dimensions. For instance, if you use "Month" then you get the months 1-12. If you are displaying averages in the data then you will get the average for that month across all the years that there is data.

The data field are the items that have the icons with "01" in them. These are typically things like count, average, and value. The count will tell you how many data items are available for that intersection of the pivot table as currently configured. The average will show you an average of all those values. The value will show you the sum of these values. So for instance, if you are looking at precipitation values you will want to use value if you want cumulative precipitation. It is useful to look at count regularly to make sure that you have the data you expected.

The pivot table by default will not store your data connection information. In order to save the connection information right click in the pivot table and select "Table options" from the menu that comes up.

As in the example above, check the "Save password" and uncheck both "Grand totals...". This will make sure that when you save the excel spreadsheet and then move it to another computer or reopen it, that it will reconnect to the data source. It will also get rid of the grand total column and row. By default, pivot tables also provide subtotals for each field in the pivot table. To turn these off, right click on the field (e.g. datumtype) in the pivot table and choose "Field settings" from the menu. In the window that pops up choose "None" and the subtotals will disappear.

14. Pivot charts is an alternate way to look at the data in a pivot table and choose pivot table fields. To bring up a pivot chart, select the "chart wizard" icon on the pivot table tool bar. It is the second icon from the left.

This will bring up a chart of the data currently displayed in your pivot table on a new worksheet. The default chart type is a bar chart.

If you want to change the chart type, then right click on the chart and choose "Chart type" from the menu.

You will likely want a line chart. The pivot chart can be manipulated in exactly the same way as the pivot table in terms of the fields. You can rearrange what fields are displayed and what items within the fields the same way you did in the pivot table. Keep in mind that the pivot table and pivot chart change together so manipulations in one also change the other. You can flip back and forth between the table and chart simply by changing work sheets.

At this point you should be off and running.

Next Section: Accessing Data Using Excel 2007

Contact: BWC Support
Credits:The research and development of the Scientific Data Server is funded by the Microsoft Corporation.