IT Connect
Information technology tools and resources at the UW

Help: Enterprise Data Warehouse

This page helps users resolve the most common questions about working with the Enterprise Data Warehouse (EDW). If your question isn’t answered here, please contact help@uw.edu.

When does EDW data load?

The data availability table shows when and how frequently data is loaded into the EDW. The table is organized by Subject Area and Data Component.



EDW Data Availability

Subject Area Data Component Load Freq Last Loaded
Academics Academic Data Store daily 11/18/19 3:40AM
Financial Budget Index daily 11/16/19 10:51PM
GL transactions daily 11/15/19 8:08PM
Financial transactions daily 11/15/19 8:14PM
Financial Activity cube daily 11/15/19 8:20PM
Human Resources - Legacy HEPPS Person, Employment, Financial (Projected Distributions) frozen as of 2017 10/16/17 2:06PM
Payment (Actual Distributions), Benefits, Faculty Bio frozen as of 2017 10/16/17 2:06PM
Check Register (Payment Verification) frozen as of 2017 06/20/17 12:35AM
Human Resources - Workday Daily load from Workday into HumanResources daily 11/18/19 4:39AM
Daily load from Workday into IBS daily 11/18/19 4:39AM
Daily load from Workday into ODS daily 11/18/19 4:43AM
Paycalc load from Workday into HumanResources pay calc 11/06/19 10:03PM
Paycalc load from Workday into ODS pay calc 11/06/19 10:03PM
Institutional Base Salary IBS Projected/Pre-Award daily 11/17/19 12:13AM
IBS UW Actual & Projected frozen as of 2017 06/20/17 5:11AM
IBS Actual monthly (day 5) 11/17/19 12:44AM
Master Data Organization daily 11/16/19 10:51PM
Research Proposal, Award, and Expenditure data daily 11/18/19 1:14AM
ResearchAdminData Cube daily 11/17/19 1:33AM


Do I have access to the EDW?

Not sure if you have access to EDW data and tools? Or want to find out what kind of access you have? Any UW employee can use ASTRA to view their EDW access.

  1. Navigate to ASTRA
  2. Click on “Show Me My Authorizations
  3. Login using your UW NetID and password
  4. ASTRA will display a list of authorizations:

Check ASTRA authorization

  1. Look under the Application column – you have access to EDW data, reports, cubes, and visualizations if you see EDW listed. The data you can access is governed by the role to which you have been assigned. In the example above, the user has been assigned to the EDW Administrator role.  Noteyou may have been granted access to more than one role.
  2. If you need to request new or different access to EDW, visit the Request Access page

For more information on EDW security, visit the Data Security page.

What servers and databases are available in the EDW?

Server Database Description

edwpub.s.uw.edu

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

EDWPresentation Integrated financial, research, and academic data designed for easy queries and analysis, with certified definitions and documented business rules.
FinancialSumMart Daily accumulation of FAS transaction data, budget index, etc.
FRDataMart Archive (from the ODS) of one year payroll check register detail, and gross pay control totals used for verification.
GLDataMart General ledger data mart. GL transactions including payroll cash detail and use tax detail for the current biennium and prior three biennia.
EFECSDataStore Daily snapshot of data generated from online faculty effort certification.
ODS Daily snapshot of human resources and finance operational data.
RAD Daily snapshot of Research Administrative data related to research activities.
UWSDBDataStore Daily snapshot of UWSDB including additional lookup tables.

How do I open my query tool using UW NetID Credentials?

Regardless of what query tool you use with the EDW, you must connect with your UW NetID credentials. If you log into your computer in a native UW NetID environment, your query tools automatically use NetID credentials, and you can skip this section. If you do not log into your computer in a native UW NetID environment, you will need to use the “RunAs” command to open your query tool with your NetID credentials. The “RunAs” command allows you to open and operate programs using different credentials than you use to log in to your computer.

Instructions to create your “RunAs” command vary depending on the operating system, the tool, and the path to the tool program. These examples work for standard Managed Workstation Services computers.  If your computer is not managed by MWS, check with your department’s IT staff to modify these instructions for your computing environment.

In order to open your query tool using UW NetID credentials via RunAs, you’ll follow two steps:

  1. Build the RunAs command
  2. Execute the RunAs command

Step 1: Build the RunAs command

In this step, you’ll build a RunAs command specific to you and the query tool you will be running.

The “RunAs” command has two basic parts, as shown below:

  • First part: RunAs /netonly /user:netid\yournetid – this tells your computer to open the tool using your UW NetID credentials, where you replace yournetid with your UW NetID. The /netonly option of the command maintains your access to your local and network drives and printers.
  • Second part: Program path – in other words, the location on your hard drive where the tool program is stored. This can vary based on operating system and tool version.

run as explanation

  1. The first part of the RunAs command is RunAs /netonly /user:netid\”yournetid” (removing the quotes and replacing yournetid with your UW NetID). NOTE: we suggest you copy and paste this string so you have the included spaces.
  2. Create the second part of the command by finding the path for the program you want:
    • Click the Start > All Programs, and navigate to the program you want to open
    • Right click on the program icon and choose Properties
    • If the contents of the Target field resemble the image on the left below, you now have the program path. If the contents resemble the image on the right, skip to Step 3 below.
    • Copy the contents of the Target field on the Shortcut tab, including the double quotes. Add this to the first part of the command, to get a full command that looks similar to this:
      RunAs /netonly /user:netid\yournetid “c:\program files\microsoft office\office15\excel.exe”

      shortcut 1     Inaccurate Excel address
  3. (Optional) If the contents of the Target field resemble the “Unusable Target” image on the right above, you will need to create the target by hand by finding the path of the program’s .exe file.
    • Search your computer for the appropriate file name (see the table for examples)
      Program File Name
      Access msaccess.exe
      Excel excel.exe
      Tableau tableau.exe
      SQL Data Tools* devenv.exe
      SSMS** 2012 ssms.exe
      * Formerly known as BIDS – Business Intelligence Development Studio, aka Visual Studio
      **SSMS – SQL Server Management Studio
    • Create the program path to get to the original executable file

Step 2: Execute the RunAs command

Now that you’ve built your RunAs command, there are two recommended ways to execute it:

  1. Issue the “RunAs” command through the Run dialog box
  2. Script the “RunAs” command in a batch file

Option 1: Issue the “RunAs” command through the Run dialog box

  1. Bring up the Run dialog box by pressing the Windows and R keys together
  2. To the right of Open: in the Run dialog box, paste the command for your query tool and click OK
    explanation of runas command prompt
  3. When prompted, enter your UW NetID password

Option 2: Script the “RunAs” command in a batch file

Another way to use the RunAs command is through a batch file with the extension .bat.  You can double click the .bat file icon and supply your password to open the tool you would like to use.

To create a .bat file:

  1. Open Notepad
  2. Paste in the command from above (make sure to change yournetid to your own UW NetID)
  3. Hit Enter, and type in the word pause without quotes
  4. Click File > Save As and save the batch file
  5. Change Save as type to All Files
  6. Name the file with a .bat extension.  Do not save the file with a .txt extension. The example below is named msaccess.bat
  7. Repeat the batch file creation process for each tool you use

How do I connect to the EDW using SSMS?

Note: These instructions are for SSMS 2012

First, ensure you’re running SSMS with your UW NetID credentials. Then:

  1. When opening SSMS, you will be prompted for a server name. Enter edwpub.s.uw.edu. Some users may also need a port number. See the Servers and Databases section for that information. Select the Options button.
    SQL Server
  2. Select Encrypt Connection to secure the data as it moves between the server and your computer. Select Connect.
    Encrypt connection
  3. That server now appears in the Object Explorer pane. If the Object Explorer pane is not visible on your screen, click View > Object Explorer from the menu.
  4. Navigate to the database you’re interested in and open the Views folder. Here you will find the secured views available for querying.
    database connection

How do I connect to the EDW using Excel?

First, ensure you’re running Excel with your UW NetID credentials. Then:

  1. Open a new blank workbook in Excel
  2. Under the Data tab, select From Other Sources, then From SQL Server
  3. In the Data Connection Wizard window,
    • Server name : edwpub.s.uw.edu
    • Log on credentials
      • Use Windows Authentication if you followed the above instructions to run Excel with your UW NetID credentials
    • You may need to select the Use the following User Name and Password option when you connect and enter your own NetID and password as shown below:
      • netid\yourUWnetid
      • yourpassword

How do I connect to the EDW using Tableau?

Note: These instructions are for Tableau Desktop for Windows, Version 8.2. If you are using Tableau Desktop for the Mac, you can connect if you are running Windows on your Mac.

First, ensure you’re running Tableau Desktop with your UW NetID credentials. Then:

  1. Select Data > Connect to data
  2. From the ‘On a server’ section, select Microsoft SQL Server
  3. If you see edwpub.s.uw.edu as pictured below, you are now connected to the EDW server. Tableau’s Connecting to Databases video shows you how to select tables and manage joins.
    tableau connection

How do I connect to the EDW using ODBC and/or MS Access?

The steps below describe how to create an ODBC connection using Microsoft Access. First, ensure you’re running Access with your UW NetID credentials. Then follow these steps:

  1. Prepare Microsoft Access
  2. Create a new data source
  3. Choose tables

Step 1: Prepare Microsoft Access

  1. From MS Access, open the database in which you want to work – either a new database or an existing one.
  2. On the External Data tab, choose ‘ODBC Database’
    ODBC Database
  3. Select Link to the data source by creating a linked table.
    Link to the data
  4. Select the ‘Machine Data Source‘ tab.
  5. If the data source you want is in the list, select it and skip down to Step 3: Choose tables. Otherwise, press ‘New’ and continue.
    Machine data source
  6. This warning is for information only. Press ‘OK‘ to proceed if you encounter it.
    warning

Step 2: Create a new data source

  1. Select ‘User Data Source (Applies to this machine only)’, then select Next.
    DataConnection005.jpg
  2. You have to make a choice here. For the best experience with EDW data, we recommend Option 1.
    1. Option 1 (recommended)- Choose “SQL Server Native Client xx.x”, where xx.x is the version number. This driver provides improved functionality (learn more), but requires you to install additional software (download site) in order to take advantage of that functionality. For example, the generic driver (Option 2) does not support Date. The SQL Native Client translates this format in Microsoft Access as a date, while the generic driver translates dates into strings.
      SQL Server by Version
    2. Option 2 – Choose “SQL Server” from the list of drivers. A known issue with this option is that the driver will translates dates into strings.
      SQL Server
  3. Verify your selections and select Finish if satisfied.
    DataConnection007.jpg
  4. Choose a name and description that describes the data source you want to connect to. This example connects to the database called ODS. Select Next.
    create new data source
  5. Choose ‘With Integrated Windows authentication’, then select Next
    DataConnection009.jpg
  6. Change the default database to ODS for this example, then select Next.
    DataConnection010.jpg
  7. Choose options as noted below, then select Finish.
    DataConnection011.jpg
  8. To make sure the connection is working, click ‘Test Data Source’.
    DataConnection012.jpg
  9. This is what you should see.
  10. Select OK until you see the Link Table dialog box, then skip to Step 3: Choose tables. If you’re using MS Access 2016, continue.
  11. Select the ODBC Database icon again
    ODBC Database
  12. Select Link to the data source by creating a linked table, then select OK
    Link to the data
  13. Select the Machine Data Source tab, then EDW Published ODS, then OK
    Data Source window

Step 3: Choose tables

The list of secured views available from the selected data source are listed on the Link Tables dialog box. Select the view you need and then select OK.

  1. ScreenCapture002.jpg
  2. MS Access prompts for unique key identification.
    unique key identification

The tables you linked to will now be available in the Tables list in MS Access. You are ready to build queries in MS Access.

How do I connect to the EDW using Excel on Mac OS?

You can now connect your Mac directly to the EDW using integrated security and Excel 2016.

  1. Open Excel and click the “Data” tab at the top.
  2. Choose “New Database Query” and select “SQL Server ODBC”
  3. A connection wizard window will open. In the Authentication section you’ll type:
    1. Server: type your server connection “edwpub.s.uw.edu”
    2. Database: This field is optional
    3. Method: Choose “Kerberos”
    4. Kerberos ID: Choose “Create ID”
    5. User Name: [yournetid]@netid.washington.edu
    6. Password: [your netid password]
    7. Select “Connect”
  4. Now you’re connected to the EDW and can query the databases.

How do I connect to the EDW using Visual Studio?

Note: These instructions are for BIDS 2008

First, ensure you’re running Visual Studio with your UW NetID credentials. Then:

  1. Open an existing project, or start a new project by clicking on File > New > Project
  2. Select the kind of project you want – this example selects a Report Server Project – and click OK
    New project screen shot
  3. In the Solution Explorer pane, right click on Shared Data Source and select Add New Data Source. If the Solution Explorer pane is not visible, click View > Solution Explorer from the menu.
    solution explorer
  4. In the Shared Data Source Properties window, enter the name of the database to which you are connecting, then click the Edit button.
    screen image
  5. In the Connection Properties dialog box:
    • Enter the server name.
    • Under Select or enter a database name, use the drop down box to select your database.
    • Click the Test Connection button to make sure the connection is working, then click OK twice to return to the Share Data Source Properties window.
    • Finally, click the Advanced button to turn Encryption on, then click OK to return to the Connection Properties window.

    connection properties
    advanced properties

  6. Note the Share Data Source Properties window now shows the Connection string information populated. Click OK to finish creating the data source.
    General properties screen shot
  7. The new data source now appears in the Solution Explorer. You can begin writing queries and reports against this data source.
    Shared Data Sources screen shot

How should I structure my query to work with secured (.sec) views?

EDW data is structured in “secured views” which secure the data per the rules applied by the UW Data Custodians. Once you have created a connection to the data using your UW NetID credentials, you will need to know where the data is housed and the specific query syntax needed to access the data.

In the following screenshot, notice that there are several databases listed on the EDW server. The databases in the red box are available for querying; the rest are for system management. The Servers and Databases section provides a complete list of available databases.

databases list on menu

The data in these databases are secured via views in the Data Access Control (DAC) database. When you query the EDW, you must query the DAC views rather than tables.

sec schema prefix illustration

The secured views are denoted by the “sec” schema prefix. The naming convention is sec.TableName, where sec. represents the schema name and TableName represents the original table name.

Sample SQL Query

In the image above, sec.addresses is the view name, and is used in a SQL query as follows:

SELECT *
FROM sec.addresses
WHERE …

What are some basic troubleshooting steps?

If you are having trouble connecting to EDW data, follow the steps below.

  1. Verify that you have been granted access to EDW data hereDo I have access to the EDW?
  2. Be sure to connect using the fully qualified address of the server to which you are connecting. See a list of EDW servers and databases.
    Example: The main data server address is edwpub.s.uw.edu. If you cannot connect to that address, use the server’s port number with the name: edwpub.s.uw.edu,1433 
    * Note: port numbers are not the preferred method of connection. If the port changes, users’ connections break. (Technical details: port numbers have been needed by some users to connect to named instances residing on clustered servers.)
  3. Modify the RunAs command to remove /netonly from the string. You may need to cycle through the steps above again.
  4. Check that your computer is trusted by the UW Services CA Root Certificate. (The user’s error message may refer to an SSL Security error). See more about the CA Root Certificate.

 

Last reviewed September 20, 2019