ETL internal database

[DCIM_Support]

I'm scoping the work required to upload about 100-200 items and values from an external bms into dco using the etl internal database.  I've some questions, hope someone can help me.  Thank you so much.

1. After creating an ETL internal PostgreSQL database, I need to create items in the empty database.  Is there a configuration tool (with GUI) that I can use to easily add or to remove items to/from the database?  A tool that even an entry level service technician can add new items without knowing how to use ETL.
   
   

2. I want to know if I can update the values of the items that I've created directly on the PostgreSQL internal database?  For example:
   
   

   e.g. UPDATE CONFIGURATION_ITEM_MEASUREMENT SET cim_value= "25.0", state_flag = "new" WHERE ci_id = "temp1";

(CID:105466856)

[DCIM_Support · Accepted solution]

Perhaps pgadmin would help?  It's meant for DBAs but might be OK for entry level service technician with some guidance from you - it's point and shoot, tree style with a few helper features.

http://www.pgadmin.org/download/

Last time I tried I (for my Win 7 64 bit machine) I ended up downloading the entire PSQL software, the actual RDBMS I don't run on my laptop - I just use the pgadmin part to connect to ETL import/export

Good luck

Cheers

(CID:105466863)

[DCIM_Support]

Ed, appreciate your reply. Thanks. pgadmin is useful. By the way, any chance that you have a clue on my 2nd question?

(CID:105466868)

[DCIM_Support]

You can do such updates. You have to mark the corresponding row 'changed' for Operation to see that the values have been updated.

(CID:105466888)

[DCIM_Support]

Hi CP, PGAdmin would work as Ed Tarento suggested. You can also try https://www.dbvis.com/download/evaluate/ or any other similar tools. The updates you asked for should work as far as your query is fine. For production, would you develop some sort of scripts that automatically read values from BMS and updates the db? A manual process to update the db as needed works for testing and validation but is not ideal for customer production environment.

(CID:105467035)

[DCIM_Support]

Hi Ed, I used pgadmin, I'm able to import item_configuration records but not the related item_configuration_measurement.

(CID:105467943)

[DCIM_Support]

Ajay Nagar Can you assist ? I have limited time right now

(CID:105467998)

[DCIM_Support]

For the power measurements, please take a look here (if you haven't already): https://dcimsupport.apc.com/display/DCIMDEVELOPER/ETL+Integration+Examples

(CID:105468012)

[DCIM_Support]

Hi CP, please provide more details. Do you see any errors? You have to associate the imported configuration_items to DCO devices, save, and run the transformation again to be able to see measurements on power tab. Can you upload screenshot of ci_measurement table in pgAdmin?

(CID:105468052)

[DCIM_Support]

Hi CP

I now have a requirement to do the exact same thing as you have requested in this post

Did you get it working?  It would be good to share experience

Cheers

Ed

(CID:106891227)

[DCIM_Support]

Hi Ed, Unfortunately not. My user decided to use Pentaho script. I did the following and it didn't work. OnCreateAction=INSERT INTO configuration_item (ci_id, cit_id, ci_name, state_flag) VALUES ('{opc_item_id}', 0, '{point_description}', 'new') ON CONFLICT DO NOTHING;INSERT INTO configuration_item_measurement (cim_id, ci_id, cim_value, mu_id, mc_id, mt_id, state_flag) VALUES ('{opc_item_id}_measurement', '{opc_item_id}', '{source_value}', 0, 0, 0, 'new') ON CONFLICT DO NOTHING; OnUpdateAction=UPDATE configuration_item SET ci_name = '{point_description}', state_flag = 'changed' WHERE ci_id = '{opc_item_id}'; OnValueChange=UPDATE configuration_item_measurement SET cim_value = '{source_value}', state_flag = 'changed' WHERE ci_id = '{opc_item_id}'; I believe my user did what Ajay Nagar recommended below: You have to associate the imported configuration_items to DCO devices, save, and run the transformation again to be able to see measurements on power tab. If you managed to get it to work, please share. Thank you.

(CID:106891243)

[DCIM_Support]

You have to set the types of the measurements correctly. The 0, 0, 0 values for mu_id, mc_id and mt_id are not enough. Please check here: ETL Integration Examples, and let me know if you need more inputs.

(CID:106891365)

[DCIM_Support]

This question is closed for comments. You're welcome to start a new topic if you have further comments on this issue.