# 10. Connection with data from the commercial management system

Giswater is prepared to simulate flow data in water projects (WS) taking data from the commercial management system. It is not an easy task. You will need specific tables similars to the ones on the following list (mandatory on bold):

***hydrometer***\
***hydrometer\_x\_data***\
\&#xNAN;*hydro\_cat\_catalog*\
\&#xNAN;*hydro\_cat\_category*\
***hydro\_val\_state***\
***hydro\_cat\_period***\
\&#xNAN;*hydro\_cat\_priority*

By using a script to connect to the CRM, these tables can be filled, either with real-time data, or with data obtained through a nightly data loading and updating process. 

Usually these tables are stored in an specific schema named *crm*.

Once the tables are filled in, its information **must be connected to Giswater data model** with their corresponding correlation tables:

***ext\_rtc\_hydrometer***\
***ext\_rtc\_hydrometer\_x\_data***\
***ext\_cat\_period***\
***rtc\_hydrometer***\
***rtc\_hydrometer\_x\_connec***

**WORKFLOW OF TABLES AND DATA**

* For each hydrometer, table **ext\_rtc\_hydrometer** need to be filled using some nightly process. After first load it only updates changes.
* For hydrometer consumptions, the table **ext\_rtc\_hydrometer\_x\_data** need to be filled using some nightly process each time you have new data from billing period (in combination with **ext\_cat\_period**).
* For each hydrometer, the relation with the Giswater hydrometer is: **rtc\_hydrometer.**<mark style="color:blue;">**hydrometer\_id**</mark>::text = **ext\_rtc\_hydrometer.**<mark style="color:blue;">**id**</mark>::text
* For each hydrometer, the relation with the Giswater connec is: **ext\_rtc\_hydrometer.**<mark style="color:blue;">**connec\_id**</mark>::text = **connec.**<mark style="color:blue;">**customer\_code**</mark>::text
* Finally it is mandatory to keep the table **ext\_cat\_period** updated and be sure that you fill two tables more with giswater ids (hydrometer\_id & connec\_id)
  * **rtc\_hydrometer** with <mark style="color:blue;">**hydrometer\_id**</mark>
  * **rtc\_hydrometer\_x\_connec** with <mark style="color:blue;">**hydrometer\_id**</mark> and <mark style="color:blue;">**connec\_id**</mark>