How to configure the export of a report for integration with a BI system?

If a form report is to be consumed by a Business Intelligence system, we recommend the following configuration.

Export command

Use the command:

export_doctype_report_native

You can see how to configure its execution from How to schedule automatic tasks?

Command configuration parameters

The export command should be configured in differential or delta mode. That is, the command should export only new or updated information. This is important because misuse of this command can lead to performance problems.

--name

In the "name" parameter you must indicate the internal name of the form for which you want to export the data.

--max 10000

The "max" parameter receives an integer and indicates the maximum number of rows that the report will contain. 10,000 is a reasonable number of rows, but you must take into account the number of documents of that form that are generated/modified per day approximately. Records are included in the report based on the last modification date.

--days_ago 2

The "days_ago" parameter expects an integer and refers to the number of days to be included in the report, counting from the current date. 2 or 3 days is a reasonable setting, but it also depends on how often you need the updated information in the BI. Perhaps you only need the updated information on a monthly basis, in which case, you can indicate 30 days.

In short, your command should look like:

export_doctype_report_native --name mi_formulario --max 10000 --days_ago 3

Frequency of execution

It will depend on when the updated information is needed, for example, on a daily basis. We recommend that the command be executed during non-working hours or when the system has less workload.

BI configuration

With the command configured as we have shown, the report will be generated only with the new records or those that have been modified. Therefore, you must configure the BI to take this circumstance into account.

It is also important to note that the first time you configure the BI, you will need to do an export of all records.

In your BI system, you will need to update the data based on a key, which will be the UUID of the documents.

In Zoho Analytics for example, this would be the configuration.