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

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 UW 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 …