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.
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.
The secured views are denoted by the “sec” schema prefix, as shown in 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)
Converting from DAC views to sec views
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).
- 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)
This document provides report conversion
instructions. It takes under 5 minutes to convert each report.