Decision Support

Query Data

To query data in the Enterprise Data Warehouse (EDW), you (ad hoc query writers and report developers) need to understand the data structure and the query syntax involved. This page describes both.

Before starting, you must have created a connection to the data using UW NetID credentials.

Query the data using the secured views

Notice that there are several databases listed on the server to which you’ve connected.  The image below, taken from within SQL Server Management Studio, shows the various databases as of this writing on one of the EDW servers.
databases list on menu

Image 1

Some of the databases you see are for system management and are not available for querying.  Refer to the Servers and Data Sources page for a list of available databases.

The data in these databases are secured via views in the Data Access Control (DAC) database.  All EDW queries are written against DAC views rather than tables, as these views secure the data per the rules applied by the Data Custodians.

Query Syntax

The secured views are denoted by the “sec” schema prefix, as shown in Image 2
sec schema prefix illustration
Image 2

The naming convention is sec.TableName, where “sec.” represents the schema name and TableName represents the original table name.

Sample View Name:

sec.addresses (from the UWSDBDataStore database)

Sample Query:

SELECT *
FROM sec.addresses
WHERE …

Converting from DAC views to sec views

Background

The original EDW secured views were housed in a database called DAC.  All queries and reports using EDW data did so through the views in the DAC database.  In the summer of 2010 the technology changed to allow the views to live in each respective native database.  This provides two important benefits to users and the University:

  • Users benefit from far more intuitive view names that live in the native database
  • The University benefits by having finer grain control over reporting access to specific data

What does this mean to users?

Existing reports and queries will need to be converted from the DAC views to the native database views (also known as “sec” views).   

Converting Queries

  • Through a simple find and replace, users can update any references to the DAC view with references to the sec view.
  • An example of the old and new syntax is:

Old: UWSDBDataStore_dbo.vwAddresses (or DAC.UWSDBDataStore_dbo.vwAddresses)
New:  sec.addresses

Converting Reports

This document provides report conversion instructions.  It takes under 5 minutes to convert each report.