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:
- Microsoft SQL Server Management Studio
- Microsoft Access
- Microsoft Excel
- Microsoft Visual Studio, aka Business Intelligence Development Studio (BIDS)
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 2003 vs Access 2007
- The network domain in which the computer resides
There are many variations in computer set up and environment 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 firstname.lastname@example.org.
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 several
servers. You can make connections to 1, 2, or all 3 servers. The Servers, Data Sources and Descriptions page provides more detail
available EDW data.
||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
||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. For more details about why that is, 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 stop reading now and proceed to the instructions
relevant to your tool.
- 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 Figure 1 below:
- 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.
- 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: the 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 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, 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
- If the contents of Target resemble the image on the right 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)
|* BIDS – Business Intelligence Development Studio, aka Visual 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 msaccess.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 2008
- When opening SSMS, you will be prompted for a server name. See the Servers,
Data Sources and Descriptions page for a
list of servers. See the image below for an example of the fully qualified server name.
- Set encryption to secure the data as it moves between the server and your computer.
- 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.
This page describes how to create an ODBC connection, and then how to link to tables once that connection has been made.
See the Connection Guide to Financial Activity Cubes via Excel (PDF).
Microsoft Visual Studio/Business Intelligence Development Studio (BIDS)
Note: These instructions are for BIDS 2008
- 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