IT Connect

Information technology tools and resources at the UW

Connect to and Query EDW Data

The Enterprise Data Warehouse (EDW) contains a wealth of information. Sometimes what you seek is available among the scores of pre-built reports and analytics. But when it’s not, you can query the data directly.

Get connected and get going.

Examples of query tools

1. Understand the Data

If you’re a query-writer, you know one of the biggest challenges to success is understanding the meaning of the data and how to apply the business rules to get the correct information. These resources can help you get started.

 

2. Open Your Query Tool With Your UW NetID Credentials

In order to query EDW data, you need to first connect to it using your UW NetID credentials. The process to connect to EDW data can vary depending on:

  • The computer’s operating system
  • The type and version of the query tool
  • The network domain in which the computer resides

The following instructions cover the most common connection protocols.  If you are unable to connect after following the steps below, please contact help@uw.edu.

Open your query tool using your UW NetID Credentials

There are a few basic steps required to connect to EDW data, independent of your selected tool, operating system, or network domain. Other steps may be required after the basic steps, depending on the tool you are using. Before getting started, make sure you have  access to the data. Then:

1. Determine What Data You Need

Before connecting to data you need to know what data you need. EDW data is stored on two servers, one for SQL databases and one for cube databases. The Servers, Data Sources and Descriptions page provides more detail about the available EDW data.

2. Open the Tool Using UW NetID Credentials

You must use your UW NetID credentials to connect to EDW data, as described in About Data SecurityIf you log into your computer in a native UW NetID environment, you can skip ahead to the Establish a Connection to EDW Data 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 UW NetID credentials, so your query tool can can pass your UW NetID to the EDW. 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 Nebula computers.  If your computer is not in the Nebula domain, check with your department’s IT staff to modify these for your computing environment.

There are two recommended ways to issue the RunAs command:

Create and issue the ‘RunAs’ command through the Run dialog box

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

Create the RunAs command specific to you and the program you will be running.

  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 that so you have the included spaces.
  2. Create the second part of the command by finding the path for the program you want to open by:
    • 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
    • Skip to Step 4
      shortcut 1     Inaccurate Excel address
  3. If the contents of Target resemble the 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
  4. Bring up the Run dialog box by pressing the Windows and R keys together
  5. 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
  6. When prompted, enter your UWNetID password
Script the ‘RunAs’ command in a batch file

Another way to use the RunAs command is through a batch file, 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

3. Connect Your Query Tool to the EDW Data

Now that you’ve opened your query tool using your UW NetID credentials, the next step is to connect that tool to EDW data. Select your tool to learn how.

Tableau

Before starting this step, make sure you’ve opened your query tool using UW NetID credentials, as described above. Now you’re ready to connect to EDW data and encrypt your data connection to secure the data.

Tableau Software

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.

  1. Open Tableau Desktop, and 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 data server. Tableau’s Connecting to Databases video shows you how to select tables and manage joins.
    tableau connection

Microsoft SQL Server Management Studio (SSMS)

Note: These instructions are for SSMS 2012

  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, Data Sources and Descriptions page 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. Each view begins with sec. followed by the original table name. The data visible in the views has been customized for each user’s role permissions, as described on the Security page.
    database connection

Microsoft Access
Microsoft Excel

To connect Excel to data cubes:

  • Read the “Connection Steps – Written” section of the  Connect to Cubes instructions.
  • Watch the 1:30 video on connecting to the Research Cube, in the Do-It-Yourself Deep Dive Analysis section of our on-demand videos.

Microsoft Visual Studio/Business Intelligence Development Studio (BIDS)

Note: These instructions are for BIDS 2008

  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

Troubleshooting Tips

Depending on your operating system, query tool, and computer domain, you may find yourself wrangling a few moving parts to getting connected to the EDW. If you’re stuck, check out these troubleshooting tips. If you still need help, please write to help@uw.edu

EDW Connection Troubleshooting

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

  1. Verify that you have been granted access to EDW data by checking your access here: Check My Authorizations.
  2. Be sure to connect using the fully qualified address of the server to which you are connecting. See a list of all EDW servers.
    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. (Geek 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 CA Certificate. (The user’s error message may refer to an SSL Security error). See https://www.washington.edu/itconnect/security/ca/

4. Build your query

No matter which query tool you use, you will be querying secured views rather than tables. Learn where to find the views and how to refer to them in your query.

Query Syntax

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.

EDW Data is Housed in Secured Views

Notice that there are several databases listed on the server. The databases in the red box are available for querying – the rest are for system management. The Servers and Data Sources page 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 Enterprise Data Warehouse (EDW), you are querying the DAC views rather than tables, as these views secure the data per the rules applied by the UW Data Custodians.
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 …