Software and data integration consulting: draw the data map before choosing a tool

An integration is an agreed rule about how a record gets from one system to another, who owns it, and which value wins when two systems disagree. The technical part, an API, an export file, a shared database, only carries that rule out.

The problem is old and has been measured. A NIST study put the cost of inadequate interoperability in the U.S. capital facilities industry at $15.8 billion for 2002, and called the figure likely conservative. A study by Autodesk and FMI found that 30% of respondents said more than half of their project data was bad.

Consulting here answers one question: which connections are worth building, in what order, and by which method?

A data map is a plain table that answers five questions for every system:

• What it holds: projects, clients, hours, costs, sheets, rooms or model elements.
• Who enters it: one person, a team, or another system.
• Which other systems need it, and which part of it each one needs.
• How it moves today: manual copying, an Excel export, email, or retyping from scratch.
• How often: live, daily, weekly, or only at the end of each stage.

The table usually gets filled in over a few sessions with the people who actually enter the data. The result rarely matches what management thinks the data flow looks like, because a lot of the moving happens in email and personal files that appear on no org chart.

Start with the transfers that happen most often, or cost the most when they go wrong. A timesheet total retyped into invoices every month is a better first connection than a yearly report, however awkward the yearly one is to put together.

The project number is the same in every system, until someone checks.

Data shared by every system, the project number, the client name, the stage code, the cost code, is reference data. Each item needs three decisions:

• A single source: the data is created and edited in one system only, and the others read it from there.
• A format: project numbers with or without a dash, stage codes as numbers or text, Jalali or Gregorian dates.
• A conflict rule: when two systems disagree, which value stands, and who resolves it.

Without those three decisions, every connection just moves the disagreements faster. A management dashboard is usually where they first show, because it puts numbers from several systems side by side.

The right method depends on how often data moves, how much of it there is, and what each program exposes:

• Scheduled file exchange: one system writes a CSV or Excel export, the other imports it at a set time. The simplest method, and for software without an API often the only one.
• Direct API connection: a program reads from one system's API and writes to the other. Fast and exact, but any change to either API stops it.
• An integration layer: every system connects to one shared layer that handles the conversion rules and logs errors. Once there are more than three or four systems, it's easier to maintain than pair-by-pair connections.
• A shared database: data from every system is collected in one place, and reports and dashboards read only from there.

If both programs already ship a connector to each other, use it. For a connection the vendor supports, you don't need us, or anyone like us. A custom build earns its cost when no connector exists, when the data has to be transformed on the way, or when one of the systems was built in-house.

Direction matters as much as method. A one-way transfer, from the timesheet system into accounting, has one source and one reader. A two-way sync, where both systems can edit the same record, needs a rule for every field about which side wins, and a way to detect when both sides changed it at once. Most offices need far fewer two-way connections than they ask for. Where one system can own the record and the other only reads it, build it one-way.

In most offices the Revit model is the largest source of data connected to nothing else. Room areas, material quantities, sheet lists and issue status are all in the model, and to reach a cost estimate or project control they usually get retyped.

Three things make that connection possible:

• The Revit API: a program that reads data straight from the model and, where needed, writes back into it.
• IFC: the open model exchange format maintained by buildingSMART, used to move models between different BIM programs.
• ISO 19650: the standard for managing information over the whole life of a built asset using BIM, which sets naming, status and approval routes for that information.

Any connection to a Revit model has to keep each element's stable identity, convert units correctly, and tell type parameters from instance parameters. Exporting Revit schedules to Excel covers those conditions in full for the Revit and Excel round trip.

Integration guides assume English and the Gregorian calendar. An office in Iran runs into problems those guides never mention:

• Persian and Arabic characters: Arabic ye and kaf in one system and Persian in another make the same client name two different records.
• Digits: Persian digits in one file and Latin in another make codes impossible to compare.
• Dates: Jalali dates in the accounting software, Gregorian in Revit or the scheduling tool, and a financial year that doesn't line up with the Gregorian one.
• Local software: many Iranian accounting and office programs have no public API, so file exchange is the only way in.

Normalising all of this belongs in each connection's conversion rules, written once. Preparing company data for AI covers the same normalisation from the angle of search and retrieval.

Sooner or later every connection meets data nobody wrote a rule for: a project with no stage code, a client that changed its name, a file saved halfway. The dangerous case is the connection that skips those records silently.

Four things keep that risk in check:

• A log of every transfer: which record, when, with what result.
• A reconciliation report: a periodic comparison of record counts and totals across the two systems.
• An alert: a message to the responsible person the moment an error happens.
• A named owner: someone in the office who looks at the error and fixes it.

No record should drop out of the process silently. Every item that didn't transfer has to be visible and traceable.

Consulting that ends in a list of recommended software hasn't done the main job. A usable result includes:

1. The data map, with every system and every current transfer.
2. The reference data table, with the source, format and conflict rule for each item.
3. The connections, ranked by the time each one saves or the error it removes.
4. The recommended method for each connection.
5. The first connection, built and measured, before anything is decided about the rest.

Software and data integration is part of ARTINEXT's office automation work, and it always starts from that data map.