Decision Support

Here report authors and ad hoc query writers will learn how to connect to establish a database connection to Enterprise Data Warehouse (EDW) data.

• Basic Steps to Connect to Data and Reports
• Establish a Connection to EDW Data
• EDW Connection Troubleshooting

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

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.

• Obtain access to the data
• Once you have access to EDW data, you can run reports
• Determine what data you need
• Open the tool of choice using UW NetID credentials
  • If using MS Access, you will need to create an ODBC connection for your NetID access
  • If you need to save files from within the tool, you may need to allow access to local folders for your UW NetID

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 about the available EDW data.

Server Name	Contents
EDWSQLC1DB1	Human Resources (for ad hoc queries only)
EDWSQLC1DB2\SQL02	Budget Index, Financial transactions, GL transactions, Degrees, Student Enrollment, Research Awards
UCSSQLC1DB1\ODS	Academic Data Store, Financial Data Store, Human Resources
EDWSSAS1	Financial activity cubes, Research Administration Cube

Table 1.

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
• Script the 'RunAs' command and store in a batch file

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.

Figure 1.

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: the 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 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 office\office12\msaccess.exe”

• Skip to Step 4.

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



Program	File Name
Access	msaccess.exe
Excel	excel.exe
BIDS*	devenv.exe
SSMS** 2005	sqlwb.exe
SSMS** 2008	ssms.exe
* 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 simultaneously
5. To the right of Open: in the Run dialog box, paste the command for your query tool and click OK
6.  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

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

2. Set encryption to secure the data as it moves between the server and your computer.

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.

Microsoft Access

This page describes how to create an ODBC connection, and then how to link to tables once that connection has been made.

Microsoft Excel

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

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.

   

EDW Connection Troubleshooting

Having connection issues? Click here for troubleshooting steps.