Set Up SQL Server Database

Before starting AMI configuration, you will need to run several scripts that generate tables as well as configure existing tables to support AMI events. Follow the steps on this page to run these scripts.

AMI requires read-committed snapshot isolation on the database that will contain the AMI tables. The SQL commands below require a user with dba permissions with exclusive access to the database. In the examples below, replace <database name> with the name of the database that will hold the AMI tables.

ALTER DATABASE <database name>
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <database name>
SET READ_COMMITTED_SNAPSHOT ON

By default, the scripts are installed in the following location: C:\Program Files (x86)\Miner and Miner\Responder\Developer Resources\Implementation Scripts\MS-SQL\Addons

These scripts must be run in the order listed.

Ensure that you are the only database connection (i.e., no other services running against the database, open applications connected to the database, etc.)

Script	Description
SmartGridAMI_01_ConfigureServicePoints.bat	Configures the ServicePoint (or equivalent) feature class for use with AMI objects.
SmartGridAMI_02_CreateStateStore.sql	Script requires modification. Creates the schema for AMI state store.
SmartGridAMI_03_RegisterAMIEvents.bat	Configures the necessary ArcSDE components necessary to support AMI events.
SmartGridAMI_04_CreateConfigStore.sql	Creates the Configuration table for centralized configuration.
SmartGridAMI_05_LoadConfigStore.sql	Loads the initial values for centralized configuration of the Responder AMI integration.
SmartGridAMI_05a_LoadEventMapping2010.sql	Run this script only if you are implementing Responder AMI 10.0.2, which leverages the event types defined in the CIM 61968-9 standard tested in the January, 2010. Do not run this script if you have run or plan to run other scripts that load event types (for example, SmartGridAMI_05a_LoadEventMapping201111.sql).
SmartGridAMI_05a_LoadEventMapping201111.sql	Run this script only if you are implementing Responder AMI 10.0.3 or greater, which leverages the event types defined in the CIM 61968-9 2nd Ed. standard tested in November, 2011. Do not run this script if you have run or plan to run other scripts that load event types (for example, SmartGridAMI_05a_LoadEventMapping201110.sql).
SmartGridAMI_06_LoadConfigRole.sql	Creates the Smartgrid Administrator role and assigns it to the administrator user.
SmartGridAMI_07_LoadStateStore.sql	Script requires modification. Loads the AMI state store from the Responder RX_CUSTOMERS table and the ServicePoint_ST feature class.
SmartGridAMI_08_RefreshStateStore.sql	Refreshes the state store based on changes that may have occurred in the ServicePointFeature class or the RX_CUSTOMERS table.

Use the steps outlined below to run the scripts listed above. Use an application such as SQL Plus to execute these scripts.

Before you begin the set-up process using the scripts described on this page, it is important to first make a backup of the database.

Configure Service Points

This script configures the ServicePoint feature class. The steps below will walk through how to modify the script to point to the feature class that represents service points in your geodatabase.

This script will break any existing spatial views based on the ServicePoint feature class. These should be dropped before starting and created after AMI configuration is complete.

This script has a few pre-requisites:

ArcSDE 10 must be installed on the client machine to run the ArcSDE commands.
You must have SQL Server 2008 or higher installed.
The index on the OBJECTID field of the ServicePoint layer must be clustered for both the base and adds tables.
The owner of your ServicePoint (or equivalent) feature class must have the Create View database privilege.
Any existing spatial views against the ServicePoint feature class must be dropped before proceeding. These must be re-created after AMI configuration is complete.

Follow the steps below to execute the script:

Double-click the SmartGridAMI_01_ConfigureServicePoints.bat script to execute it.
You will be prompted to indicate whether your system meets the pre-requisites listed above. Press y on the keyboard to continue or n to stop the script. Press Enter.
Next you will be prompted to review the information you'll need to proceed. If you have the data required, press y on the keyboard to continue or n to stop the script. Press Enter.
- Name of the feature class that stores service points
- Port number or direct connect string for the geodatabase
- ArcSDE server name (no instance attached to it)
- Database owner of the ServicePoint feature class: user name and password (case-sensitive)
- Database owner of the AMI tables
- Name of the existing multi-versioned view against the ServicePoint feature class (if it exists)
Next you'll be prompted to enter each piece of information listed above. Press Enter on the keyboard after each.
After entering all pieces of information, you will be prompted to confirm that the information is correct. Press y on the keyboard to continue or n to stop the script. Press Enter.
Next, the script will migrate the geometry type of the feature class you entered (e.g., ServicePoint). When the migration is complete, double-check to ensure the migration was successful. Open ArcCatalog and view the Properties window for the ServicePoint (or equivalent) feature class.
Select the Fields tab and scroll to the SHAPE field. The Data Type should be set to Geometry.

The script will prompt you to indicate whether the Shape field on the ServicePoint feature class was successfully set to SQL geometry. Press y on the keyboard to continue or n to stop the script. Press Enter.
Next, the script will create a multi-versioned view on your ServicePoint (or equivalent) feature class if one doesn't already exist. The first will ask whether a multi-versioned view already exists. Press y on the keyboard if the new already exists. Press n if it does not. Press Enter.

Do I have a multi-versioned view?

In ArcCatalog, look for an object named similarly to your ServicePoint feature class. This object will likely have a suffix of _MV. This object will not reside in the dataset next to the ServicePoint feature class, but rather in the root of the geodatabase.

In the example below, the multi-versioned view is named ELECDIST.SERVICEPOINT_MV. The feature class used to create this view is called ELECDIST.SERVICEPOINT.

If you don't see a view named like this in ArcCatalog, you may need to query the database. Below is a sample query. Replace the values in blue with values specific to your database.

select imv_view_name

from database.schema.SDE_table_registry

where table_name='SERVICEPOINT'

and owner='TableOwner'

If you responded no (n) to indicate that a multi-versioned view does not exist, the script will attempt to create this one for you. Once it has finished, the script will prompt you to indicate whether the view was created successfully. Press y if it did or n if it did not. Press Enter. Skip to step 13.
If you responded yes (y) to indicate that a multi-versioned view does exist, then you will be prompted to enter the name of that view. Press Enter.
The script will grant the appropriate permissions to this multi-versioned view. When it has finished, it will prompt you to indicate whether the permissions were granted successfully. Press y if they were or n if they were not. Press Enter.
Next, the script will retrieve parameters for the Create State Store and Load State Store scripts. Press any key to continue the script.
The script will provide additional instructions. Press any key to continue.
The script will provide a sample list of information. This is just a sample. Press any key to continue.
Next the script will present the actual list of information that you will need. Make note of the following for future steps:
- SRID value: Update the RegisterAMIEvents script with this value in later steps.
- Layer Envelope values: Update the CreateStateStore script with this value in later steps. Make note of all four values: minx, miny, maxx, maxy.
Press any key to exit.

Create State Store

This script creates the AMI tables in the geodatabase. The following pre-requisites exist:

Run this script as the user to own the AMI tables (e.g., smartgrid).
The AMI table owner must have the following system privileges to the AMI tables:
- Create table
- Create view
- Create procedure

Before executing this script, you'll need the following information.

Layer Envelope values (from previous script)
Database and owner of the ServicePoint feature class
Database and owner of the RX_CUSTOMERS table
Multi-versioned view name

To edit and then execute this script, follow the steps outlined below.

Right-click SmartGridAMI_02_CreateStateStore.sql and select Edit to open this file for editing.
Look for the following:

use powerstream

Modify "powerstream" to reflect the name of your database.
Look for the following:

/***************SET THESE VARIABLES!!***************************/
-- Set these to the same values as your servicepoint feature class
SET @MINX = 582314.87000
SET @MINY = 4845356.71806
SET @MAXX = 646752.15600
SET @MAXY = 4961888.36000
-- Set the DB and SCHEMA name for your servicepoint feature class
SET @SERVICEPOINT_DB = 'POWERSTREAM'
SET @SERVICEPOINT_OWNER = 'PSGIS'
-- Set the DB and SCHEMA name for your rx_customers table
SET @RX_CUSTOMERS_DB = 'RESPONDER'
SET @RX_CUSTOMERS_OWNER = 'RESPONDER'
-- Set the name of your multiversioned view against the servicepoint feature class.
SET @MULTIVERSIONED_VIEW_NAME='SERVICEPOINTS_MV'

In the above section, you'll need to indicate the following:
- MINX, MINY, MAXX, MAXY: These are the values displayed in the Layer Envelope field in the first script.
- SERVICEPOINT_DB: Database in which the ServicePoint feature class resides.
- SERVICEPOINT_OWNER: This is the user that owns the ServicePoint feature class.
- RX_CUSTOMERS_DB: Database in which the RX_CUSTOMERS table resides.
- RX_CUSTOMERS_OWNER: This is the user that owns the RX_CUSTOMERS table. In most instances this will be the Responder user.
- MULTIVERSIONED_VIEW_NAME: This is the name of the multi-versioned view associated with the ServicePoint feature class.
Save and close SmartGridAMI_02_CreateStateStore.sql.
Login as the owner of the AMI tables (e.g., smartgrid) and execute SmartGridAMI_02_CreateStateStore.sql. You can execute the script using applications such as SQL Server Client or SQL Server Management Studio.

Register AMI Events

This script registers the AMI_Events database view as a layer with ArcSDE. It also grants View permissions to the owner of the AMI objects so that it can be viewed in ArcMap. This script requires some modification. The script will prompt you to provide the following information:

Name of the feature class that stores service points
Port number or direct connect string for the geodatabase
ArcSDE server name
Database owner of the ServicePoint feature class: user name and password
Database owner of the AMI tables: user name and password

Follow the steps below to execute the script:

Double-click SmartGridAMI_03_RegisterAMIEvents.bat to launch it.
You will be prompted to review the script's pre-requisites. If you have successfully run the first two scripts, you have met the pre-requisites. Enter y to contine or n to exit the script. Press Enter on the keyboard.
Next, the script prompts you to review the information you will be prompted to provide. This information is listed just above these steps. If you have all the required information, enter y to continue or n to exit the script. Press Enter on the keyboard.
The script will now prompt you for each piece of information. Enter the value required and press Enter on the keyboard for each.
After entering the required information, you will be prompted to review your entries. If they are accurate, enter y. If you wish to make changes, enter n. Press Enter on the keyboard to continue.
Next, the script will inform you that it is about to provide you with the SRID and the Layer Envelope values needed for future steps. Press any key to continue.
The script provides you with a sample layer property output where you can see the information you'll need. This is just a sample. Press any key to continue.
Next, the script displays the layer properties from which you can note the following values:
- SRID
- Layer Envelope minx
- Layer Envelope maxx
- Layer Envelope miny
- Layer Envelope maxy
The script will prompt you to indicate whether the layer properties were displayed successfully. Enter y if the properties were displayed. Enter n if they were not. Press Enter on the keyboard to continue.
Enter the SRID value and press Enter.
Next, you'll be prompted to enter the Layer Envelope min/max x/y values. Enter each value and press Enter to continue after each.
After entering the required information, you will be prompted to review your entries. If they are accurate, enter y. If you wish to make changes, enter n. Press Enter on the keyboard to continue.
Verify that the layer was successfully registered. Press y if it was; Press n if errors occurred; you will have the opportunity to re-enter values. Press Enter.
Press any key to exit.

Create Configuration Store

This script creates a centralized configuration schema for the Responder AMI integration. Login as owner of the Responder tables (e.g., responder) and run SmartGridAMI_04_CreateConfigStore.sql in an application such as SQL Server Client or SQL Server Management Studio.

Load Configuration Store

This script loads the initial values into the centralized configuration (Configuration Manager) of the Responder AMI integration. The settings this script loads into the Configuration Manager may not be accurate for your system. However, you will modify these initial values in the Configuration Manager in a future step.

Execute the script as the owner of the Responder tables (e.g., responder). You can use an application such as SQL Server Client or SQL Server Management Studio.

Load Configuration Role

This script creates a Responder security role for the administrator user required by the Responder AMI integration. Login as the owner of the Responder tables (e.g., Responder). Run SmartGridAMI_06_LoadConfigRole.sql in an application such as SQL Server Client or SQL Server Management Studio.

Load State Store

Values in the Meter field of the RX_CUSTOMERS table must be unique. Before executing this next script, ensure that there ar no duplicate values in this field. Before executing SmartGridAMI_07_LoadStateStore.sql, you'll need the following information.

Owner of the ServicePoint feature class
Owner of the RX_CUSTOMERS table
SRID value
Spatial index value

Follow the steps below to edit the SmartGridAMI_07_LoadStateSTore.sql script before executing it.

Open SmartGridAMI_07_LoadStateStore.sql and edit the following values:

/***************SET THESE VARIABLES!!***************************/
-- Set these to the same values as your servicepoint feature class
SET @MINX = 580644.09381
SET @MINY = 4845356.71906
SET @MAXX = 646752.15600
SET @MAXY = 4961888.36000
-- Set the DB and SCHEMA name for your servicepoint feature class
SET @SERVICEPOINT_DB = 'POWERSTREAM'
SET @SERVICEPOINT_OWNER = 'PSGIS'
-- Set the DB and SCHEMA name for your rx_customers table
SET @RX_CUSTOMERS_DB = 'RESPONDER'
SET @RX_CUSTOMERS_OWNER = 'RESPONDER'
-- Set the name of your multiversioned view against the servicepoint feature class.
SET @MULTIVERSIONED_VIEW_NAME='SERVICEPOINTS_MV'

In the above section, you'll need to indicate the following:
- MINX, MINY, MAXX, MAXY: These are the values displayed in the Layer Envelope field in the first script.
- SERVICEPOINT_DB: Database in which the ServicePoint feature class resides.
- SERVICEPOINT_OWNER: This is the user that owns the ServicePoint feature class.
- RX_CUSTOMERS_DB: Database in which the RX_CUSTOMERS table resides.
- RX_CUSTOMERS_OWNER: This is the user that owns the RX_CUSTOMERS table. In most instances this will be the Responder user.
- MULTIVERSIONED_VIEW_NAME: This is the name of the multi-versioned view associated with the ServicePoint feature class.
Save and close SmartGridAMI_07_LoadStateStore.sql.
Login as the owner of the AMI tables (e.g., smartgrid) and execute the script in an application such as SQL Server Client or SQL Server Management Studio.

Refresh State Store

This script refreshes the AMI state store table based on changes that may have occurred in the ServicePoint feature class or the RX_CUSTOMERS table. Login as owner of the AMI tables (e.g., smartgrid) and run SmartGridAMI_08_RefreshStateStore.sql in an application such as SQL Server Client or SQL Server Management Studio.

Default Database

Set the default database for the SmartGrid user to the database that contains the AMI State Store. Likewise, set the default database for the Responder user to the database containing Responder tables.