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 edw-help@uw.edu.

• When does EDW data load?
• Do I have access to the EDW?
• What servers and databases are available in the EDW?
• How do I open my query tool using UW NetID credentials?
  • Windows
  • MacOS
• How do I connect to the EDW using:
  • Microsoft SQL Server Management Studio (SSMS)
  • Excel
    • Excel (MacOS)
  • Tableau
  • ODBC Connection and/or Microsoft Access
  • Microsoft Visual Studio/Business Intelligence Development Studio (BIDS)
  • How should I structure my query to work with secured (.sec) views?
  • What are some basic troubleshooting steps?

When does EDW data load?

Trying to find out when Production or Test EDW data loads? If so, this has been moved to the EDW Data Load Status page.

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 “View My Authorizations“
    3. Login using your UW NetID and password
    4. ASTRA will display a list of authorizations:

• • 4. 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. Note: you may have been granted access to more than one role.
    5. 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?

Each Database has 2 Environments available as follows:

Environment	SQL Server	Notes
Production (PROD)	edwpub.s.uw.edu	Each databases is updated per a unique schedule listed on Data Load Status
TEST	edwtestpub3.s.uw.edu	Updated as needed and by request via edw-help@uw.edu Latest updates listed on Data Load Status Changes added without notice

All EDW Databases, including current state are as follows. Some databases have customer resources available that you can access by clicking on the link:

Database	Description	State
CoreData	A database that includes a cross-section of source-similar reference data from multiple domains, which is designated by the UW Data Governance Steering Committee as essential to UW core business. This data is managed to guarantee uniformity and usage in support of UW functions.	Current
EDWPresentation	the presentation layer includes integrated financial, research, and academic data designed for easy queries and analysis, with certified definitions and documented business rules. The presentation layer is optimized for commonly requested queries and analyses of institutional data. Predefined reports, dashboards, and cubes based on EDWPresentation data are available in the BI Portal.	Current
EFECSDataStore	This database is a copy of some Management Accounting & Analysis (MAA)-related tables from the eFECS EffortReport database.	Scheduled to be frozen 10/13/2023 with the retirement of FAS and introduction of Workday.
IBS	The Institutional Base Salary (IBS) database is the operational database for School of Medicine salary data downloaded from ADP.	Current
UWODS	The UWODS is a database which reflects the Foundation Data Model (FDM), the technical framework that organizes HR/Payroll and Finance data in UW’s implementation of Workday. This database supports reporting for the following: Workday Foundation Data Model (FDM) and Financial Reference Data HR/Payroll Data	Current
UWSDBDataStore	A relational database that provides data from the central mainframe Student system which includes student demographic information, course schedule and student enrollment information, student degrees, student majors, registration and transcript information, and many other topics and tables in the area of academics.	Current
WDFinDataMart	A Finance Data Mart introduced as part of UW Finance Transformation (UWFT) and provides the following data from Workday: Accounting & Operational Journal Transactions Grant & Financial Budgets Current Budget Balances Payroll Journal Line Detail	Current
AnalyticInteg (AIDB)	A sandbox environment that was created to give data users across campus the ability to work with and explore PNBDB data structures in a SQL context. AIDB was built in collaboration with the Office of Planning and Budgeting (OPB) to serve as a proof of concept for consolidating academic and finance data.	Academic data current Finance data frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance. (See UWODS and  WDFinDataMart for current data).
FinancialSumMart	A relational database that is organized to meet various administrative needs including annual financial auditing, UW organizational financial reporting, and budgeting analysis. The data covers the university business domains of budget, expenditures, revenue, gifts and endowments, as well as some grant information.	Frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance. (See UWODS and  WDFinDataMart for current data)
FRDataMart	Archive (from the ODS) of one year payroll check register detail, and gross pay control totals used for verification.	Frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance
GLDataMart	A General Ledger Data Mart. This database contains GL transactions including payroll cash detail and use tax detail for the current biennium and prior three biennia.	Frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance. (See UWODS and  WDFinDataMart for current data).
HumanResources	An integration database for HR/Payroll data	Frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance (See UWODS and WDFinDataMart for current data).
ODS	A relational database based on data that was originally captured in the central mainframe financial system (FAS) and the Human Resources/Payroll system (Workday). This database includes financial transactions, a variety of paid and unpaid employment information, academic relationships, details about positions at the UW, and many other topics and tables in the area of Finance and HR/Payroll.	Frozen as of 7/27/2023 with the retirement of FAS and introduction of Workday Finance. (See UWODS and WDFinDataMart for current data).
RAD	Relational database that provides Research Administrative data related to research activities.	Frozen as of 7/6/2023 with the retirement of FAS and introduction of Workday Finance. (See UWODS and WDFinDataMart for current post-Award data. Contact grantrp@uw.edu for pre-Award data.

 

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

First, if you’re trying to connect to the EDW remotely (i.e. from an off-campus computer), you must log on to the UW network using the Husky OnNet VPN software. Note: In general, UW Medicine locations are considered “off-campus” and also require Husky OnNet.

Second, regardless of what query tool you use with the EDW, you must connect with your UW NetID credentials. If you log into your computer using your UW NetID credentials in a native UW environment, you can skip this section. If you do not log into your computer in a native UW NetID environment, you will need to use a special command to open your query tool with your NetID credentials.

Windows Instructions

The “RunAs” command allows you to open and operate programs using different credentials than the credentials you use to log in to your computer. Instructions to create your “RunAs” command vary depending on the operating system, the program you want to use, and the program’s path. These examples work for standard Managed Workstation Service 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:yournetid@netid.washington.edu – 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.

Example:

RunAs /netonly /user:yournetid@netid.washington.edu “C:\Your\Program\File\Location.exe”

• • 1. The first part of the RunAs command is as follows (replacing yournetid with your UW NetID):
       RunAs /netonly /user:yournetid@netid.washington.edu
       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 to run:
       • Click 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:yournetid@netid.washington.edu “c:\program files\microsoft office\office15\excel.exe”

         

    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
       
    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 that we created above:
       1. RunAs /netonly /user:yournetid@netid.washington.edu “C:\Your\Program\File\Location.exe”
    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 Excel.bat
    7. Repeat the batch file creation process for each tool you use

MacOS Instructions

In order to connect to the EDW using MacOS, you will need to update (or create) your Kerberos configuration file (krb5.conf). The krb5.conf file contains Kerberos client configuration information that will be necessary to connect to the EDW. This file should be located in your /etc/ directory.

Step 1: Navigate to the /etc/ directory

Navigate to the /etc/ directory on your MacOS machine. Please note that this directory is hidden by default. To navigate to this directory, open Finder and press Command + Shift + G. In the popup, enter the path to the folder we want to access: /etc/.

Step 2: Create your krb5.conf file

Now that you are in the /etc/ directory, you will need to create your Kerberos configuration file (krb5.conf). Using your preferred text editor, create a new file titled krb5.conf in the /etc/ directory. Note that you should only need to do this step once. This file consists of information that tells your OS what Kerberos realm it needs to be using. We suggest copying and pasting the below example into your new file:

[libdefaults]
 default_realm = NETID.WASHINGTON.EDU
 
[realms]
NETID.WASHINGTON.EDU = {
  admin_server = _kerberos._tcp.netid.washington.edu
  kdc = ezra.netid.washington.edu
  kdc = ahsoka.netid.washington.edu
  kdc = leia.netid.washington.edu
  kdc = kanan.netid.washington.edu
  kdc = obiwan.netid.washington.edu
  default_domain = NETID.WASHINGTON.EDU
}

[domain_realm]
  netid.washington.edu = NETID.WASHINGTON.EDU
  .netid.washington.edu = NETID.WASHINGTON.EDU
  .s.uw.edu = NETID.WASHINGTON.EDU

Step 3: Run the kinit command

In order to run your preferred EDW querying program as your UW NetID, you will need to run the kinit command. Open a terminal window, and run the following (adjusting yournetid to be your UW NetID):

kinit yournetid@NETID.WASHINGTON.EDU

Enter your UW NetID password when prompted.

Step 4: Open your Querying Program

Once you have completed creating your krb5.conf file and you have run the kinit command, you will now be able to use Kerberos authentication for the querying program of your choice. See the Excel (MacOS) section for an example of this.

 

How do I connect to the EDW using SSMS?

Note: The screenshots below are taken from SSMS 2012; the instructions can be applied to later versions of SSMS as well.

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. Note: When connecting from an off-campus computer, you may not see your NETID displayed in the User name section. If you know that you’re running SSMS with your UW NetID credentials, please continue onto the next steps.
       
    2. Select Encrypt Connection to secure the data as it moves between the server and your computer. Select Connect.
       
    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.
       

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:
         • yournetid@netid.washington.edu
         • yourpassword

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

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

• • 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 Tableau?

Note: These instructions are for Tableau Desktop for Windows. If you are using Tableau Desktop for the Mac, you can connect if you are running Windows on your Mac. For more information on connecting to Tableau with a Mac, please visit our Tableau-specific FAQ!

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

• • 1. Make sure you’re connected to the VPN and open Tableau Desktop.
    2. From the ‘To a Server’ section, select Microsoft SQL Server
       
    3. On the Microsoft SQL Server window, enter the following, and then click Sign In:
       • Server: edwpub.s.uw.edu.
       • Database: Optional – Choose a specific database that you want to connect to. For example, WDFinDataMart.
       • Authentication: Use Windows Authentication (preferred)
         
    4. You are now connected to the EDW server. Tableau’s Connecting to Databases video shows you how to select tables and manage joins.
       
    5. It’s very important that you only try to query EDW secured views. Secured views are prepended with “sec.” in front of the object name. You will not be able to connect to non-secured tables.

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

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
       
    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.
       
    4. In the Shared Data Source Properties window, enter the name of the database to which you are connecting, then click the Edit button.
       
    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.

       
       

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

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

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.

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 here: Do 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. If you are connecting remotely (off-campus), check to make sure you are connected to the VPN using Husky OnNet.
    4. If you used a RunAs command to open your query tool and connect to the EDW, modify the command to remove /netonly from the string. You may need to cycle through the steps above again.
    5. 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.