IT Connect

Information technology tools and resources at the UW

Update Your EDW Data Connections

On August 11, 2014, the Enterprise Data Warehouse (EDW) data and cubes moved onto more powerful hardware and updated software. Data and cubes were also relocated to the NetID domain. Use the information and instructions below to re-point your EDW data connections to the new locations.

Server Name Crosswalk

Use this table to find the new server name that corresponds to the old name in your existing data connections.

EDW User Data used for Old server New server
EDW queryer Connecting SQL Server Management Studio (SSMS), Excel, Access, Tableau, Business Intelligence Development Studio (BIDS), etc to EDW data ucssqlc1db1.admin.washington.edu\ods edwpub.s.uw.edu

Note: If a port number is required, use: edwpub.s.uw.edu,1433

edwsqlc1db2.admin.washington.edu\sql02
edwsqlc1db1.admin.washington.edu
Cube users Connecting Excel, BIDS, Tableau, etc to a cube edwssas1.admin.washington.edu cubes.uw.edu

Note: If a port number is required, use: cubes.uw.edu,2383

Reporting Services report developers Publishing .rdl files to the development server ottawa.admin.washington.edu\BIDev edwdevbi1.s.uw.edu/Reports/
Creating an ODBC connection to report metadata database edwtest1.admin.washington.edu\Pres edwprdetlc1db2.s.uw.edu\SQL02
Tableau visualization developers Publish Tableau workbook files to the transitional server edwtab1.cac.washington.edu No change
Data Custodians Connect to SMAT to edit security settings on data https://ucs.admin.washington.edu/SMAT No change

Data Connection Update Instructions

Excel ODC Connection

Change an ODC-based Excel Workbook Connection

Excel commonly uses ODC connections to connect to a table or to a view directly.

  1. Open an existing workbook, and select the Data tab, then the Connections icon.

Connection Icon

  1. Select your connection and click PropertiesNote: this example uses the old server name ucssqlc1db1.admin.washington.edu. Your server name may differ.

Workbook connections window

  1. Click the Definition tab to see where your workbook is getting the data.

Connection properties window

  1. Update the old server name in the Connection String section, to edwpub.s.uw.edu, then click Export Connection File.

Connection properties window

  1. If you choose to keep the original file name, it will include the old server name. The benefit of this option is all other Excel workbooks that use this connection file will automatically connect to the new EDW server. The downside is that the connection file name is potentially confusing.

File save window

  1. Alternatively, you can export the connection file to a new name. Once you do this you will have to point all your workbooks using this data connection to the new file name. This is the cleaner way because .odc file names will not contain old server names.

File save window

  1. If you decide to change the connection file name, the file and string should look as follows:

Connection properties window

Excel or Access ODBC DSN Connection

Change an ODBC DSN-based Excel Workbook Connection

Note – for Excel connections, start at Step 1. To change an ODBC DSN connection for Access, you can skip to Step 4.

  1. Open the Excel workbook you use to query external data. Go to the Data tab, then select the Connections icon.

Connection Icon

  1. This will show you the databases to which you can connect your workbook. Select a connection from the list and then choose Properties.

Workbook connections window

  1. Click the Definition tab, then you will see the Connection string query. Remember the DSN name (highlighted in yellow) as it is needed for the next steps.

Connection properties window

  1. Open Control Panel and ODBC connections to find the DSN: Control Panel > Administrative Tools > Data Sources (ODBC). On the User DSN tab, select the Name of the User Data Source you want to change, then select Configure.

Connection properties window

  1. Here, you can update the server to the new server name: edwpub.s.uw.edu

File save window

  1. If you keep the same DSN name, all connections in Excel or Access using this DSN will be automatically updated. However, if the DSN name includes the original server name, as in the example above, it is recommended that you update the name to the current server or something more generic. Note, if you change the name, you will need to repoint your Excel and Access data connections to this new name.

File save window

If your DSN uses an older SQL driver, you may want to recreate it.

  1. Remove the old DSN entry, then select the Add button.

ODBC Data Source administrator window

  1. Select the most current driver.

Create new data source window

  1. Fill in the name field with the old DSN name or a new one. Keep in mind that if you use the old name, all your workbooks will use this connection and won’t require updating. If you choose a new name, all your workbooks that used the old DSN name will need to be updated. In this example the Name is server-agnostic. Fill a description and the server name of the new server: edwpub.s.uw.edu

Create new data source window

  1. Leave this set to the default unless you know you are using a service account.

Create new data source window

  1. Change the default database to your choice. READONLY is generally the best setting for Excel. Access sometimes requires READ-WRITE.

Create new data source window

  1. Select Use strong encryption for data, and then Finish

Create new data source window

  1. If you did not change the DSN name, remember to click Refresh All from the Data tab in your workbooks – otherwise, you will be looking at old data. If you did change the DSN name, you will need to repoint your connections to the new name.

Refresh all icon

Tableau Data Source Connection

Change a Data Source in Tableau Desktop

Tableau provides extensive documentation on changing data sources. Any references to an old server should be updated to edwpub.s.uw.edu.