Skip to content

DWH Bouwsoft Configuration

Configuration of Data to be downloaded: config.bouwsoft_Tables

This table contains the configuration of each table to be downloaded.

dwh-bouwsoft-configuration-config-table.png

  1. ID: The pipeline downloads tables according to the ID. Make sure that the Projects table is always first.

  2. Endpoint: the service endpoint, as shown in Postman or in <Swagger UI>

  3. TableName: the name of the table where the data is sinked

  4. Type: full, incremental or per_project

    1. full: the table is dropped and the full table is downloaded
    2. incremental: This only works for endpoints that deliver a ts_lastupdateattribute. The records that have been updated in the last IncrementalRedownloadDays are deleted. Then newer records are downloaded. NOTE: this may not proper handle deleted records. If you see that deleted records are staying in the table, you can do the following:
      1. You can increase IncrementalRedownloadDays
      2. You can switch to full download
      3. You can switch to a hybrid, e.g. downloading incrementally during the week, and downloading a full download on the weekend (by setting drop_table parameter in the pipeline to true.)
    3. per_project: This allows to download data for which the endpoint requires a mandatory project parameter, such as Overview and ProjectPhases. In the filter, you can add a placeholder $project_id$ that will be replaced by the project id.
      1. Example: for ProjectPhases: Filter: project_id eq $project_id$
      2. Example: for Overview/all: AdditionalParameter: source=22&source_id=$project_id$

  5. PerProjectDays: this is valid for Type = per_project. If set to 1, then all projects are downloaded every day. If set to 2, then the oldest half of unchanged projects are downloaded on a single day. As a result, all projects are re-downloaded every 2 days.

  6. IncrementalRedownloadDays: if set to 30, then all records having been changed in the last 30 days (according to ts_lastupdate)are being deleted and thus re-downloaded.

  7. Filter: a filter to be applied to the call to the endpoint.

    1. NOTE: some endpoints contain defaults if no filter is provided. For example if you call Projects without filter, you get fewer records than if you call Projects?filter=id ne 0
    2. NOTE: you will have to add the $project_id$ placeholder for Type = per_project

  8. Select: a selection of colums/attributes. This follows the bouwsoft API standard, according to which the columns need to be separated with a pipe.

    1. For example: project_id|time_total|wagecost|totaldistance_chargetoclient|totaldistance_chargetoclient_backandforth|date

  9. AdditionalParameters: any additional parameters you would like to pass on. This can for example be useful for downloading Overviews, e.g. Overview/all?source=22&source_id=13

    1. Example: source=22&source_id=13
    2. NOTE: you can combine multiple parameters just as you would in the URL, using &
    3. NOTE: the $project_id$ placeholder is respected for per_project downloads

  10. TypeOverwrites: This allows you to configure the SQL types used when a new table is created by the AF. You can provide a json configuration that maps attribute names to SqlAlchemy types.

    1. Example
      {   "yourreference": "UnicodeText",     
    "countrycode": "NVARCHAR(2)",   
    "units": "DECIMAL"
 }

  11. Active: whether the download is active or not.

Configuration of Triggers

The triggers are defined in ADF.

Pipeline: bouwsoft_pipeline

Parameters:

dwh-bouwsoft-configuration-parameters.png

  1. drop_table: you can set this to true, false, or default

    1. true: the table will be dropped before starting the download. This is useful for a full load. In a nutshell, this "overwrites" the incremental type from config.bouwsoft_Tables.
    2. false: the table will never be dropped. This can in theory be useful for full tables too. It will keep each version of the record, as we add a run_id and run_timestamp column to the downloaded tables.
    3. default: this will drop the table or not, based on the mode (i.e. based on the configuration in config.bouwsoft_Tables.Type)
      1. incremental: false
      2. full: true
      3. per_project: false

  2. filter: an SQL filter clause that allows running the pipeline for a set of tables. This is in relation to config.bouwsoft_Tables. For example:

    1. WHERE Type = 'incremental'
    2. WHERE Id IN (1, 3, 5)

  3. table_name: an additional SQL filter, so that it's easy to run the pipeline manually for a single table