Before 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 email@example.com.
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
available EDW data.
Note: some users may need a port number to connect, in which case use edwpub.s.uw.edu,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
Note: some users may need a port number to connect, in which case use cubes.uw.edu,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:
- 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.
- 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.
To issue the ‘RunAs’ command:
Create the RunAs command line specific to you and the program you will be running.
- "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
- 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
- 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)
|SQL Data Tools*
* Formerly known as BIDS - Business Intelligence Development Studio
** SSMS - SQL Server Management Studio
- Create the program path to get to the original executable file
- Bring up the Run dialog box by pressing the “Windows” and “R” keys simultaneously
- To the right of Open: in the Run dialog box, paste the command for your query tool and click OK
- When prompted, enter your UWNetID password
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:
- Open Notepad
- Paste in the relevant command from above, making sure to change yournetid to your UW NetID
- Hit Enter, and type in the word “pause” without quotes
- Click on File > Save As
- Navigate to where you want to store the file
- Change Save as type to All Files
- Name the file with a .bat extension (making sure it is not saved with a .txt extension). The example below is named excel2013.bat
- 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
- When opening SSMS, you will be prompted for a server name. Enter edwpub.s.uw.edu. Some users may need a port number as well. See the Servers,
Data Sources and Descriptions page for that information. Select the Options button.
- Select Encrypt connection to secure the data as it moves between the server and your computer. Select Connect.
- 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.
- 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.
See Create an ODBC connection.
See the Connection Guide to Financial Activity Cubes via Excel (PDF).
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.
- Open Tableau Desktop, and select Data > Connect to data...
- From the 'On a server' section, select Microsoft SQL Server
- 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.
Microsoft Visual Studio/Business Intelligence Development Studio (BIDS)
Note: These instructions are for BIDS 2008. Updates for 2012 are coming soon.
- Open an existing project, or start a new project by clicking on File > New > Project
- Select the kind of project you want – this example selects a Report Server Project – and click OK
- 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.
- In the Shared Data Source Properties window, enter the name of the database to which you are connecting, then click the Edit button.
- 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.
- Note the Share Data Source Properties window now shows the Connection string information populated. Click OK to finish creating the data source.
- The new data source now appears in the Solution Explorer. You can begin writing queries and reports against this data source.
EDW Connection Troubleshooting
Having connection issues? Click here for troubleshooting