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
|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||/|
|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||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
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. Note: this example uses the old server name ucssqlc1db1.admin.washington.edu. Your server name may differ.
- Click the Definition tab to see where your workbook is getting the data.
- Update the old server name in the Connection String section, to edwpub.s.uw.edu, then click Export Connection File.
- 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.
- 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.
- If you decide to change the connection file name, the file and string should look as follows:
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.
- 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.
- 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.
- Here, you can update the server to the new server name: edwpub.s.uw.edu
- 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.
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.
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.