Setting Up Your Database


The Observations Data Model schema version 1.1.1.
The first step to data publication in the CUAHSI HIS is to format your data and metadata to comply with the Observations Data Model (ODM). The ODM is a relational database schema that has been developed specifically  to store hydrologic observations and sufficient ancillary information (metadata) about the data values to provide traceable heritage from raw measurements to usable information allowing them to be unambiguously interpreted and used. Click here for the ODM Specifications Document, which details every field and table in ODM.

Attaching a blank ODM instance to MS SQL Server
The Attach Databases window in SQL Server Management Studio.
The next step is attach your blank instance of ODM to MS SQL Server. It is best practice to place your database and log files in SQL Server's DATA folder. So, the first step is to cut/copy these two files, which are named OD.mdf and OD_log.ldf by default, from the directory where you downloaded them to the MS SQL DATA folder. Unless a different folder was designated during the installation of SQL Server, by default this folder should be located here: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

The DATA folder is the best place to put your database instance.
Next, open SQL Server Management Studio. You will be prompted for credentials to connect to your server. After authenticating, expand the Databases folder in the Object Explorer on the left side of the program. Right click on the Databases folder and select Attach...This will open the Attach Databases window. Click the Add... button. You will then need to navigate to the DATA folder to identify they database to attach. Select the database (it will be a .mdf file) and click OK. Once back in the Attach Databases window, the name of the database can then be changed by altering its name under the "Attach As" field.

Configuring user access and authentication

 There are two levels of permission needed in order to enable data loading and data discovery for your database.  First, a login with read and write privileges must be used to use ODM data loaders. Second, a login with read only privileges must be used to properly setup a WaterOneFlow web service, which will allow your data to be shared in HIS.

You may need to create a new login (or two). Be sure to designate them as SQL Server Authentication.

If you already have logins available, you do not need to create new ones. But, if you do need to create a new login, expand the Security folder in the Object Explorer, right click the Logins folder, and select "New Login...".

Use the Database User - New window to designate users and permission levels for your database.

Expand your database to expose its folders. Then, expand the Security folder and the Users folder located inside the Security folder. This will show all of the users that have permission to access this database. To add a User, right click on the Users folder and select "New User...". To adjust permissions of a user that already has access to the database, right click on that user and select Properties.
You will need (1) user that has read and write permission so that you can load data using one of the ODM Data Loaders and (1) user that has read permission for your data to be accessible for download.

Use ODM Data Loader to populate database

 Now that you have your database setup, you will want to begin to populate it. There are two software applications that have been developed for this purpose:
ODM Data Loader - The ODM Data Loader is used for static data sets.


ODM Streaming Data Loader - The ODM Streaming Data Loader is for streaming data straight from sensors to your ODM database.



Next, create and configure your web service so that you can share your data over the internet.



Subscribe to: Posts (Atom)