Decision Support

Updating your Data Connections to the EDW

On August 11, 2014, the Enterprise Data Warehouse (EDW) data and cubes moved onto more powerful hardware and updated software, and relocated to the NetID domain. As part of this migration, EDW users need to change their 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.

User type 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: some users may need a port number to connect, in which case 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: some users may need a port number to connect, in which case 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 .rdl 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

Changing Data Connections

There are many ways users can update their data connections to the EDW. Here are suggested edits you can use to update your data connections for Excel, Access, and Tableau.

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.

    screen image

  2. Select your connection and click Properties

    screen image

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

    screen image

  4. You can update the connection string here and then export it to overwrite your old connection file. Simply replace the old server name in the Connection String section, to edwpub.s.uw.edu, then click Export Connection File.

    screen image

  5. Note, in doing so, you are keeping the original file name, which may contain the old EDW server name, as pictured here. 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.

    screen image

  6. Alternatively, you can export the connection file to a new name. Once you do this you will have to point all your workbooks that use this data connection to the new file name. This may be the cleaner way, however, so that .odc file names do not potentially contain old server names.

    screen image

  7. If you decide to change the connection filename, your screen will resemble the following image

    screen image

Back to top

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.

    screen image

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

    screen image

  3. On the Definition tab, you will see the connection string and query. Remember the DSN name (highlighted) as it is needed for the next steps.

    screen image

  4. 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 data source you want to change, then select Configure.

    screen image

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


    screen image

  6. Once you change the server, all connections in Excel or Access using this DSN will be updated. No additional changes need to be made in other workbooks. The server Name and Description do not update automatically - you may want to change them if they contain the old server name. Note, if you change the name, you will need to repoint your Excel and Access data connections to this new name.

    screen image

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.

    screen image

  2. Select the most current driver.

    screen image

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

    screen image

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



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

    screen image

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

    screen image

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

    screen image

Back to top

Change a Data Source in Tableau Desktop

Tableau provides extensive documentation on changing data sources. Whereever you see a reference to a server, simply enter edwpub.s.uw.edu.

Questions? Send an email to help@uw.edu