Development of a National Adult Protective Services Data System: Namrs Pilot Final Report (volume 2). 6. Namrs Pilot Data Warehouse

09/25/2015

The NAMRS Pilot Data Warehouse provided a way for analysts to query the Case Component data collected from the states using statistical analysis tools, data visualization tools, or any other type of tool that can query a SQL database. The data warehouse contained only the Case Component data, and was connected to the Internet.

Function

There were several functional types of data that were stored in the data warehouse:

  • CaseDataSet--stored the various states/periods for the case data.

  • Investigation--stored the investigations.

  • Clients--stored the client data.

  • Maltreatments--stored the data about the maltreatments.

  • Perpetrators--stored the data about the perpetrators.

  • Relationships--stored the data about the client-perpetrator relationships.

Schemas

There was a single schema in the data warehouse:

  • Data Warehouse--The data warehouse schema contained all the objects that could be queried in the data warehouse.

Views

There were no views that could be accessed in the data warehouse. (There was a single view that could not be accessed through the data warehouse schema--it could be used for testing, but would probably be deleted.)

Stored Procedures

There were no stored procedures that could be accessed in the data warehouse.

Custom Functions

There were no user-defined functions that could be accessed in the data warehouse.

The database ran on Microsoft SQL Server 2014 (Web Edition), which was installed on a VM in the Microsoft Azure Cloud. Since this was a VM, normal maintenance had to be performed. In addition to manual service pack updates, there were two scheduled maintenance plans:

  • Nightly--The nightly maintenance plan ran at 1 a.m. every night and backed up all databases on the server (to Azure Storage), checked database integrity, reorganized indexes, and updated statistics.

  • Weekly--The weekly maintenance plan ran at 3 a.m. on Saturdays and rebuildt all indexes on all databases on the server.

Appendix C provides the ERD for the physical database structure. The ERD is provided as a PDF and can be modified using Adobe Acrobat software. Appendix D lists the definition of all the data warehouse tables and data elements.

Access

The NAMRS Pilot Data Warehouse was accessed by the following:

  • NAMRS Pilot Database--The database loaded data into the data warehouse.

  • Analysts--Analysts connected directly to this database to do queries.

The NAMRS Pilot Data Warehouse did not access any other components.

Security

There were two levels of security for the NAMRS Pilot Database.

Network Security

An Azure VM basically ran behind a firewall. One must expose "endpoints" which are essentially TCP ports. Each endpoint could be protected with an ACL which lets one limit access to the endpoint to one or more IP addresses (or address ranges that are specified as CIDR addresses).

The VM that ran this SQL Server only had the following endpoints exposed:

  • Powershell--This allowed powershell scripting against the VM.

  • Remote Desktop--This allowed a system administrator to get remote access to the server.

  • SQL Server--This allowed the SQL to be queried and managed.

The Powershell and Remote Desktop endpoints were open only to a single IP address--the WRMA office. These endpoints could not be accessed from any other IP address on the Internet other than the WRMA office. The SQL Server endpoint was wide open to the Internet. This was required so that the analysts could log in to query the database.

Database Security

Each person/component that accessed the database had database credentials. The only accounts available in SQL Server were for the system administrator and for the NAMRS Pilot database (so that it could load data). A role called data warehouse users was created in SQL server and in the database. This role had been denied access to most objects in the master database, denied access to all databases on the server (except the data warehouse) and it had been granted read-only access to the data warehouse schema.

New analysts could be given access to the data warehouse as needed. Analyst accounts were assigned to the data warehouse user role. This effectively allowed analysts to query the data in the data warehouse while denying access to any other database on the server.

View full report

Preview
Download

"NAMRSpilot-V2.pdf" (pdf, 1.83Mb)

Note: Documents in PDF format require the Adobe Acrobat Reader®. If you experience problems with PDF documents, please download the latest version of the Reader®