Decision Support

Connect to Data

BI Portal imageBefore writing your own queries, check out the ever-growing inventory of available report and analytic tools at the Business Intelligence Portal. This intuitive site connects you to UW Enterprise Data Warehouse central reports and analytics. Star your favorites, see what's trending, get report recommendations, stay abreast of new releases, see the underlying SQL queries, peruse data definitions, read how others are using reports, add your feedback, and more. This guided tour shows you how.

Didn't find what you're looking for in available reports and analytics in the BI Portal? Read on to learn how to establish a database connection to Enterprise Data Warehouse (EDW) data and get started querying.

What Tools are Supported?

A number of query and report writing tools are currently supported:

The instructions for how to connect to data can vary depending on these factors:

  • The computer’s operating system
  • The version of the tool, e.g. Access 2010 vs Access 2013
  • The network domain in which the computer resides

There are many computer set up and environment variations across the University.  These instructions cover the most common connection protocols.  If you are unable to connect after following the steps relevant to your particular tool, network domain and operating system, please contact

Basic Steps to Connect to Data and Reports

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.

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 data and one for cube data.  The Servers, Data Sources and Descriptions page provides more detail about the available EDW data.

Server Name Contents

Note: some users may need a port number to connect, in which case use,1433

Human Resources (for ad hoc queries only)
Budget Index, Financial transactions, GL transactions, Degrees, Student Enrollment, Research Awards
Academic Data Store, Financial Data Store, Human Resources

Note: some users may need a port number to connect, in which case use,2383

Financial activity cubes, Research Administration Cube

2.  Open the Tool Using UW NetID Credentials

UW NetID credentials are necessary in order to connect to EDW data.  See About Data Security.

  • IMPORTANT! If you already log into your computer in a native UW NetID environment, you do not need to issue the RunAs command – simply open your programs as you normally would. You can proceed to Establish a Connection to EDW Data. 
  • 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 tool with your UW NetID credentials.  ‘RunAs’ allows you to open and operate programs using different credentials than you use to log in to your computer.

‘Runas‘ instructions 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:

Issue a ‘RunAs’ command through the Run dialog box

The ‘RunAs’ command has two basic components, as shown in the image:

  1. 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.
  2. 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 help

To issue the ‘RunAs’ command:

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

  1. "runas /netonly /user:netid\”yournetid” (removing the quotes and
    replacing yournetid with your UW NetID. NOTE: there is a space between runas, /netonly, and /user:netid
  2. Find the program 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 first image below, you now have
    the program path.  If the contents resemble the second image, skip to Step 3 below.
  • Copy the contents of the Target field on the Shortcut tab, keeping the double quotes
    in place.  Add this to the first part of the command, to get a full command that looks
    something like this:

    RunAs /netonly /user:netid\yournetid “c:\program files\microsoft office\office15\excel.exe”

  • Skip to Step 4

shortcut 1    Accurate Excel address

  1. If the contents of Target resemble the image above, you’ll 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 below 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
    ** SSMS - SQL Server Management Studio

  • Create the program path to get to the original executable file
  1. Bring up the Run dialog box by pressing the “Windows” and “R” keys simultaneously
  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 UWNetID password
explanation of runas command prompt

Script the 'RunAs' command in a batch file

Another way to invoke the RunAs command is through a batch file, also known as a .bat file. With a .bat file, all you need to do to open your tool is to double click the .bat file icon and supply your password.

To create a .bat file:

    1. Open Notepad
    2. Paste in the relevant command from above, making sure to change yournetid to your UW NetID
    3. Hit Enter, and type in the word “pause” without quotes
    4. Click on File > Save As
    5. Navigate to where you want to store the file
    6. Change Save as type to All Files
    7. Name the file with a .bat extension  (making sure it is not saved with a .txt extension). The example below is named excel2013.bat
    8. Repeat the batch file creation process for each tool that you use.

Establish a Connection to EDW Data

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.  

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 Some users may need a port number as well. See the Servers, Data Sources and Descriptions page for that information. Select the Options button.

SQL Server

  1. Select Encrypt connection to secure the data as it moves between the server and your computer. Select Connect.

Encrypt connection

  1. 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.
  2. 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.

ods connection

Microsoft Access

See Create an ODBC connection.

Microsoft Excel

See the Connection Guide to Financial Activity Cubes via Excel (PDF).

Tableau Desktop

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

  1. If you see, 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 Visual Studio/Business Intelligence Development Studio (BIDS)

Note: These instructions are for BIDS 2008. Updates for 2012 are coming soon.

  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

  1. 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

  1. In the Shared Data Source Properties window, enter the name of the database to which you are connecting, then click the Edit button.
  2. screen image

  3. 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

  1. 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

  1. 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

EDW Connection Troubleshooting

Having connection issues? Click here for troubleshooting steps.