ETL CONFIG

Prerequisite: Being familiar with how DATA_SOURCES are configured is preferable before reading the following

In the etl_config file, we can declare and describe our reports/dashboards structure.

Deprecation warning

This documentation on the use of reports, dashboards and the augment.py file refers to deprecated features. It is there for legacy purpose but you shouldn’t need it in new small apps (please use the home studio interface). As a result, reports, dashboards and augment.py interfaces are disabled by default on all new small apps and existing apps that do not already use the feature.

Reports (deprecated)

Let’s start with reports.

A report block reads like this:

REPORTS: [
  query: #(1)
    domain: 'reports' #(2)
    defaut: 1 # See data example below: corresponds to the name of the 'default' column of the 'reports' domain
  template: 'report' #(3)
]
  • (1) - The query that is used to declare reports. It must return 1 row for every report.

  • (2) - Name of the domain declared in the DATA_SOURCES block or that was computed in the augment.py part (ETL)

      • The id for the cson template associated with the report.

    Notes:

  • REPORTS is an array that can contain multiple elements, each with a different report template. The syntax below should be used:

REPORTS: [
  query:
    domain: 'reports'
    type: 'marketing'
  template: 'report_marketing'
,
  query:
    domain: 'reports'
    type: 'sales'
  template: 'report_sales'
]

Using the REPORTS block, we’ve just declared our reports. Now we need to associate the dashboards.

Dashboards (deprecated)

DASHBOARDS: [
  query: #(1)
    domain: 'reports' #(2)
  report: 'report_col' #(3)
  template: 'dashboard' #(4)
]
  • (1) - The query that is used to declare the dashboards. It must return 1 row per dashboard.
  • (2) - Name of the domain declared in the DATA_SOURCES block (or computed in the augment.py part - deprecated) (ETL)
      • The column(s) used to join reports and dashboards. See below.
      • The id of the cson template used for the dashboard.

Warning “report_id” is a reserved word. This means it can’t be used as a column name in your data source.

By default the number of dashboard is limited to 30000. But you can overwrite this setting by modifying the default value, for example 50000 dashboards.

MAX_JOIN_REPORTS_DASHBOARDS: 50000

This parameter should be placed at the same level as the DASHBOARD block (but not inside ;) ).

Join Reports and Dashboards

To join reports and dashboards, two method exist.

Option 1: report

You need a dashboard column that match the entityName of the reports and gives a unique id to identify report.

report is the name of the dashboard column used to join. The column should match the entityName

Example

DASHBOARDS: [
  query: #(1)
    domain: 'reports' #(2)
  report: 'report_col' #(3)
  template: 'dashboard' #(4)
]

For domain report:

| report_col   | dashboard_id   |   default   |manager_name |report_group |
|--------------|----------------|-------------|-------------|-------------|
| sales        | France         |     1       |   Jean      |   group_1   |
| sales        | Italy          |     0       |   Vito      |   group_2   |
| sales        | Germany        |     0       |   Hans      |   group_2   |
| marketing    | France         |     1       |   Jacques   |   group_2   |

We will have:

  • 2 reports (“sales” and “marketing”)
  • 3 dashboards for the “sales” report, and 1 for the “marketing” report

Option 2: joinReportDashboard

This option allows you to join a report and a dashboard by using several columns as key.

joinReportDashboard is a list of pair of dashboardColumn and reportColumn.

Example

DASHBOARDS: [
  query: #(1)
    domain: 'reports' #(2)
  joinReportDashboard:[
      dashboardColumn: entity_id' #(3)
      reportColumn: 'entityName'
  ,
      dashboardColumn: 'entity_type' #(3)
      reportColumn: 'entityType'
  ]
  template: 'dashboard' #(4)
]

With domain report:

| entity_type | entity_id   |   default   |
|-------------|-------------|-------------|
| sales       | France      |     1       |
| sales       | Italy       |     0       |
| sales       | Germany     |     0       |
| marketing   | France      |     0       |

We will have:

  • 4 reports with 1 dashboard each
  • “sales”/”France”
  • “sales”/”Italy”
  • “sales”/”Germany”
  • “marketing”/”France”