OpenMAINT 2. Export/Insert Data & Multiple Update procedures & links to 3DPDFs, Plans and Maps

OpenMAINT putting data into system

Export/Import

Manually putting in data line by line  using “cards” can be tedious, especially when data spreads across 3 or more tabs.

So openMAINT has a couple of ways to bulk input/update.

They are in the Administration Tab under “Utiulities” (at bottom) in LH side bar.

You can select a class, I will use the example of “Rooms” and export it.

Make sure you change separator from “;”( semicolon)  to “,” (comma) for the CSV (comma separated file) output.

Hit export and open in Excel.

You can see that all of the data in the other tabs are also in columns in the header, so you can fill out details all on one row which is nice.

The presets for export are different for import.

One nice thing with the import process is that it highlights faults in data to be pushed in, so a simple validation method.

We will do a save-as on this file (exported file is read only) and import it straight back into openMAINT.

As you can see there are red text items for Complex/Building/Floor & Unit. This is because these are filled in with “Code” “Space”  “Description” and the Import only needs “Code” in these columns.

It also adds a lot more to the original description of the rooms, Adding the “Codes” for “Complex”-“Building”-“Floor”- “Room Number” to the Original named description (not reflected in the above example as I have Exported/Imported these items a couple of times.

So for importing back in you have to clean up the RELATION Items , ie the “Complex”-“Building”-“Floor” and “Unit” to only show their “CODES” then the data will import.

I am still trying to figure this out as in some cases it overwrites whats there, in others it creates a complete 2nd set & you have to go through, line by line and delete the repeated items. An INTERESTING aspect of this is, if the original has the relations attached, you will not be able to delete, (Unless you break all the relations) and you’ll only be able to delete the ones where the relations are not attached (usually the updated ones that you were trying to push back in to update).

So, for data that is already in there, this does not seem an elegant way of updating data, but, the Export/Import templates are ideal for pushing in NEW rows of data. I found this was great for putting more/New  windows into openMAINT.

In the Export CSV file above note the 4 red horizontal arrows at the bottom, this is repeat data that is sitting in the system, that I have had to go into openMAINT and delete line by line which is very tedious.

An update to Export/import 19/11/2017

If you use the export/import process, exporting information means information is already in the database, openMAINT will not overwrite this with updated information, but rather append a new row with the extra information that you have added , so you have the 2 rows of data, one old and one new. If you do this a number of times then you will have a lot of redundant data which can possibly bloat the database and slow it down.

My current thinking on this is that, in the course of compiling your asset register you will most probably go through several iterations of updating the data, thus creating a lot of partial data sitting in the database. It would be sensible to then do a large export of the current dataset and then delete the database and then import all the new data. This way you have cleaned a lot of the database out to make it run faster.

A possible alternative is to run some SQL queries that will delete older data from certain database tables. You will need to find linkages to see which tables are associated with that data as they often link across a few tables. I would do a backup before you try this as I have naffed up a few things deleting the wrong stuff and broken databases in the past.

The issue is, if you have 5 rows of the same data, but only the latest is active ( I think this is how openMAINT classifies which row is current and which are old) then any search may need to look at all 5 rows to see which is the line of data it needs. Not a problem with 5 rows, but if it is 50,000 instead of 10,000 this could add a noticeable delay to searches. openMAINT over browser is slow enough, adding extra dead weight to the search is not to be encouraged.

On that, I do find openMAINT slow, and I am running with 12GB of RAM on the VPS, so the leaner it is, the quicker it will be.

Multiple Updates

These seem to be a selection set , that when picked, you can update a field/or fields in the selected items.

I have been looking at how I can use openMAINT to link in with my other visual works, such as Maps, 3DPDF’s , Floor Plans & Room Data Sheets.

I have the basic openMAINT setup running, with the Database, Tomcat & shark running. I am having issues with Alfresco DMS and hope to have some of the BIM stuff up and running in the future.

I wan to explore incremental stages of the tool. So I would like to have access to my other information by linking it to OpenMAINT.

My method of doing this is:

• At “Complex” Level, show where the site is on a MAP. Using the Burnham Camp Example this can have links to a lot of other Data and may also reflect external services such as drainage, sewer, potable water & power services around the site with a transparent layer overlaid on google maps.
• At ” Building” level, a link to a 3D PDF that can be downloaded. This gives the overview of the building and its geometry and levels.
• At “Floor” level to show links to floor plans. Or to 360 photos plan with links to 360 panorama views of each room (great for condition and items in room).  This then shows an indication of Units and room locations, plus also doors/window locations
• At “Room” level either floor plans or Room Data Sheets.

(note- look at comments in conclusion at bottom of blg)

So, once you have your documents set up on web site or intranet system, you can just use a link to connect to those items.

Once you have the URL’s, it is a matter of putting them into openMAINT. For this, where there are a number of row items with the same link (eg Rooms and using floor plans) you can use the Multiple Update method.

So in openMAINT under  the Data Management Module (DMM) go to class tab in LH side bar, select, say Room under the asset category and this then shows you the properties and attribute tabs. Select Attributes and add a new attribute (column) where you can store the link to the plans.

After creating the new attribute, you need to go to DMM setup tab at bottom and clear cache, 

then go to Administration Module & refresh browser, then the new column will appear.

So , after creating new column we now want to fill it.

Go to Administration Module (AM) and click on utility tab in LH sidebar. Select Inventory Item/Site/Room in list and select rooms that you want to update with the same information. In this case we are putting link to Plan of Building pdf so that graphical information can be accessed for the room.

Conclusion.

• Export/Import is a bit delicate but good for bulk upload of new data .
• Multiple Update good for updating same data to lots of elements of same types. Say for inspection, the date needs to be updated, this would be a good method to use.
• There still needs to be a few other methods to use that I haven’t found yet & it may be going into the back door of the database and using SQL scripts to upload/Delete data, but this would have to be done with caution ass to ensure RELATIONS are not destroyed.
• URL links cannot be activated on the main table, you have to Modify the Card of the item that you want and copy the url to your web browser. Not elegant but usable.  Showing it in the main area shows that there is more data about the element which is useful.
• URL update. Look at Page 23 of the CMDBuild Administrator Manual (HERE) for data types. I initially used” String” for URL’s but now see that”Text” is better as it allows HTML. So when you go to modify card, you just right click on link to be taken to the page in the web browser. One less step.