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.
||Connecting SQL Server Management Studio (SSMS), Excel, Access, Tableau, Business Intelligence Development Studio (BIDS), etc to EDW data
Note: some users may need a port number to connect, in which case use edwpub.s.uw.edu,1433
||Connecting Excel, BIDS, Tableau, etc to a cube
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
Creating an ODBC connection to report metadata database
Tableau visualization developers
||Publish .rdl files to the transitional server
Connect to SMAT to edit security settings on data
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.
- Open an existing workbook, and select the Data tab, then the Connections icon.
- Select your connection and click Properties
- Click the Definition tab to see where your workbook is getting the data.
- 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.
- 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.
- 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.
- If you decide to change the connection filename, your screen will resemble the following 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.
- Open the Excel workbook you use to query external data. Go to the Data tab, then select the Connections icon.
- This will show you the databases to which you can connect your workbook. Select a connection from the list and then choose Properties.
- 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.
- 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.
- Here, you can update the server to the new server name: edwpub.s.uw.edu
- 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.
If your DSN uses an older SQL driver, you may want to recreate it.
- Remove the old DSN entry, then select the Add button.
- Select the most current driver.
- 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
- Leave this set to the default unless you know you are using a service account.
- Change the default database to your choice. READONLY is generally the best setting for Excel. Access sometimes requires READ-WRITE.
- Select Use strong encryption for data, and then Finish
- 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.
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 email@example.com