Scientific Data Server Quick Start Manual

This Guide is for users with Windows XP, Vista and Excel 2003

(for the Excel 2007 quick start guide go here)


Add Needed Packages

If you have never used Excel with external pivot tables, you will need to install 2 packages. These are Microsoft Core XML Services (MSXML) 6.0 and SQLServer2005_ASOLEDB9.msi. You may also need your MS Office Install CD to install these.


Connecting and Configuring an Excel 2003 Pivot Table

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


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

7. In the next window choose "Analysis server", enter the name of the server "bwc.berkeley.edu", 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. In t he next window that comes up choose "Finish".

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

 

13. The time field such as "Year to Day" and "Year to Month" give a hierarchical display of the time dimension. It will initially display years. If you want to drill down and see individual days in a particular year, then click the plus symbol next to the year. 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 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 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.

Check the "Save password" . 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.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".

14. Pivot charts are a graph form of the pivot table. 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 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.

 


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