Mapping and Standardizing Data

Provisioning a Collection

Create a Collection to begin the process of mapping your asset data to your Collection. This is the step necessary to have your data in the standardized relational model and for creating any natural language or JSON output.

To create a Collection:

Navigate to the Asset details page.
Select an Asset by selecting the link to a previously created Asset. The Asset Overview page is displayed.
Select the Create Collection button.
Provide the following details
- Display Name: Optionally provide a display name for your Collection. This is used as a user friendly option to better identify the Collection.
- Schema prefix: This is the schema prefix that is used in the database to reference you Collection.
- Model: This is the industry data model and version you want provisioned with your Collection.
Select Submit.

Now you will have two schemas created in the Data Store. One schema will be your map schema which is where you will configure your database views that map your raw asset data to the standardized model. The other schema is the model view. Once the data has been mapped in the map view, it can be replicated to the standardized model view.

Set Up Mapping

After your Assets and Collections are configured, you are ready to map the asset data to the model and replicate it to your collection schema.

To create a mapping:

Create the mapping as described below.

Connect to your Asset using a database querying tool such as SQL developer using your Asset credentials you created.
Navigate to the section labeled Other users and expand the list.
Locate the schema labeled with the schema prefix you used when you created the Collection. Here are two schemas, one with a suffix of _map and one with the suffix _model.
Expand the _map view and then expan the views in the _map view. Here is a list of data model views to map your data model to.
Grant access rights to the mapping view by executing a grant statement such as the example below:
```
 GRANT SELECT ANY TABLE ON SCHEMA UTILITYCO_ASSET TO UTILITYCO_MAP;
```
Map an asset view to the standardized model.

Create the mapping from the customer database service point tables to the standardized model. Execute a statement such as the following example to create this mapping:

     CREATE VIEW 
     "UTILITYCO_MAP"."ELECTRIC_SERVICE_POINT" 
     ("UTILITY_SERVICE_POINT_ID", 
     "UTILITY_LOCATION_ID", 
     "ACTUAL_START_DATE_TIME", 
     "ACTUAL_END_DATE_TIME")
        
     AS 
     SELECT SP.SP_ID AS UTILITY_SERVICE_POINT_ID,
         SP.PREM_ID AS UTILITY_LOCATION_ID,
         CAST(SP.INSTALL_DT AS TIMESTAMP WITH TIME ZONE) AS ACTUAL_START_DATE_TIME,
         CAST(SP.ABOLISH_DT AS TIMESTAMP WITH TIME ZONE) AS ACTUAL_END_DATE_TIME
     FROM            "UTILITYCO_ASSET".CI_SP        SP
     INNER JOIN      "UTILITYCO_ASSET".CI_SP_TYPE   SP_TYPE
     ON SP.SP_TYPE_CD = SP_TYPE.SP_TYPE_CD
     WHERE
     SP_TYPE.SVC_TYPE_CD = 'E';

To validate your mapping:

Query your newly created view with a statement such as the following example:
```
 SELECT * FROM UTILITYCO_MAP.ELECTRIC_SERVICE_POINT;
```
If you see data, you have successfully mapped part of the model. You can create views for the rest of the model if you wish to map them, or move on to the validations.

To sync to your Collection:

After validating that your mapping views are set up, you can replicate to your Collection. Run the replication process by executing the command such as the following example as a script:
```
 SET SERVEROUTPUT ON
 
 EXEC UTILITYCO_MODEL.DE_COLLECTION.SYNC_COLLECTION;
```

Validating Data Quality

Within each Collection, a Model schema with data validation tables are available. There are two validation tables to check within each model schema.

Schema	View Name	Description
Collection Model	DE_MAPPING_ERRORS_DETAIL	Error view that lists out row level information.
Collection Model	DE_MAPPING_ERRORS_SUMMARY	Error summary view that lists out the tables that are mapped improperly.