Home - POSOCO/wrldc_warehouse_data_migration_python GitHub Wiki

Welcome to the wrldc_warehouse_data_migration_python wiki!

All the element types, station types, configuration options etc., are stored in _MASTER tables

Issues found in master database

Owner name duplication found in OWNER table with ids 18, 20 of Owner RIPL. Owner 20 can be safely deleted.
The states 'ISGS', 'Other Region' have Region Id 0, but Region with region Id 0 is not present.
Koradi - 400KV associate_substation with id 84 has major substation Id of 72 which is not present. We cant remove this substation also since 3 Ac Transmission lines refer to this substation.
ac trans line with id 208 not present but referred in ckt row with webuat id of 343, 345, 346, 347. All these are of substation Koradi - 400KV referred above
It is found that bus name is being duplicated in BUS table using the query

select min(bus_name), min(id), min(bus_name), count(bus_name) from bus group by BUS_NAME having count(bus_name)>1

It is found that bus number and substationId combination is not unique for 51 rows in the BUS table using the query

SELECT MIN(ID), MIN(BUS_NAME), MIN(BUS_NUMBER), MIN(VOLTAGE), MIN(FK_SUBSTATION_ID), COUNT(NUM_SS) FROM 
(SELECT ID, BUS_NAME, BUS_NUMBER, VOLTAGE, FK_SUBSTATION_ID, CONCAT(BUS_NUMBER, FK_SUBSTATION_ID) AS NUM_SS FROM BUS WHERE 1=1 AND BUS_NAME IS NOT NULL AND ID IS NOT NULL AND BUS_NUMBER IS NOT NULL AND VOLTAGE IS NOT NULL AND FK_SUBSTATION_ID IS NOT NULL) 
group by num_ss HAVING COUNT(NUM_SS)>1

Upon examining it is found that the transfer bus and bus are stored this way. The only distinction was the name of the bus. The type column of the bus element needs to be used for this purpose

It is found that BusReactor names are not unique. This can be checked with

select min(REACTOR_NAME), min(id), min(REACTOR_NAME), count(REACTOR_NAME) from BUS_REACTOR group by REACTOR_NAME having count(REACTOR_NAME)>1

It is found that the combination of STATION_ID, BAY_TYPE_ID, SOURCE_ENTITY_TYPE, SOURCE_ID, DESTINATION_ENTITY_TYPE, DESTINATION_ID is not unique in BAT table. This can be verified by the following query

SELECT MIN(ID), MIN(BAY_NAME), NUM_SS, COUNT(NUM_SS), LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) FROM 
(SELECT ID, BAY_NAME, (STATION_ID || BAY_TYPE_ID || SOURCE_ENTITY_TYPE || SOURCE_ID || DESTINATION_ENTITY_TYPE || DESTINATION_ID) AS NUM_SS FROM BAY WHERE 1=1 AND BAY_NUMBER IS NOT NULL AND ID IS NOT NULL) 
group by NUM_SS HAVING COUNT(NUM_SS)>1

It is found that the combination of STATION_ID, BAY_NUMBER is not unique in BAT table. This can be verified by the following query

SELECT MIN(ID), MIN(BAY_NAME), NUM_SS, COUNT(NUM_SS), LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) FROM 
(SELECT ID, BAY_NAME, (STATION_ID || BAY_NUMBER) AS NUM_SS FROM BAY WHERE 1=1 AND BAY_NUMBER IS NOT NULL AND ID IS NOT NULL) 
group by NUM_SS HAVING COUNT(NUM_SS)>1

It is found that id is not unique in BAY table. This can be verified by the following query

SELECT MIN(ID), MIN(BAY_NAME), COUNT(ID) FROM bay group by id HAVING COUNT(id)>1