Dynamic Tabular Data Exchange Services

Last week we presented how we can export measure service data using TDX. To do so, we first defined the file definition in the workspace, and then defined the measure expression that we want to export using plugin logic. We showed that, although this allows for much of the definition of the delimited file to be set via URL parameters, we need to have previously defined the delimited file columns, since the delimited file needs to be defined statically.

It would be nice, however, if we could export any predicate or measure expression without having to make static configuration. Doing so would allow us to avoid to have to recompile and recreate workspaces for each new delimited file that we want to export and, thus, allow us to be more agile when developing.

To enable this, we have created the dynamic TDX. Adding this service to the workspace is quite simple:

service_by_prefix["/delim"] = s,
dynamic_delim_service(s) <- .

To use the dynamic TDX we simply pass a URL parameter file_binding with a FileBinding protobuf message. The file_binding protobuf message parameter is the equivalent of the file_binding predicate used to configure delimited files in the workspace. The FileBinding message is composed of the FileDefinition, which defines the headers that we want our file to have, and the list of predicates and how they bind into the file’s headers. The predicates can be any regular predicate, or a plugin predicate. As we know, if we want to export measure service data, we need to use the plugin logic with the appropriate measure expression.

The example below is be the protobuf message in JSON format that we would use to export a delimited file with the headers “SKU|WEEK|MONTH|STORE|SALES|RETURNS”. To create the delimited file we are joining 3 predicates: 2 of them generated by the measure expression, one for the sales metric and one for the returns metric and third predicate, “month_week”, that is a regular predicate,  so that we can get the month of each week. To retrieve this delimited file, we would request a GET to /delim?file_binding=… As can be seen, we don’t want to write the file_binding parameter by hand. Doing so would be error prone and exhausting. But this is nothing that a smart Javascript API wouldn’t solve 🙂 For more details about the FileBinding protobuf message and more examples of dynamic TDX, please refer to the reference guide.

{
  name: "sales-returns"
  file: {
    name: "sales-returns"
    delimiter: "|"
    column: {
      header: "SKU"
      format: {
        id: "ALPHA_NUMERIC"
      }
      required: true
      can_be_absent: false
    }
    column: {
      header: "WEEK"
      format: {
        id: "ALPHA_NUMERIC"
      }
      required: true
      can_be_absent: false
    }
    column: {
      header: "MONTH"
      format: {
        id: "ALPHA_NUMERIC"
      }
      required: true
      can_be_absent: false
    }
    column: {
      header: "STORE"
      format: {
        id: "ALPHA_NUMERIC"
      }
      required: true
      can_be_absent: false
    }
    column: {
      header: "SALES"
      format: {
        id: "FLOAT"
      }
      required: true
      can_be_absent: false
    }
    column: {
      header: "RETURNS"
      format: {
        id: "FLOAT"
      }
      required: true
      can_be_absent: false
    }
  }
  predicate: {
    predicate_name: "sales"
    column: {
      header: "SKU"
    }
    column: {
      header: "WEEK"
    }
    column: {
      header: "STORE"
    }
    column: {
      header: "SALES"
    }
    plugin_logic: {
      name: "sales"
      plugin_name: "measure"
      param: {
        key: "measure_str"
        value: "filter Sales by &lt;= max_sales : float"
      }
      param: {
        key: "max_sales"
        value: "100"
      }
    }
  }
  predicate: {
    predicate_name: "returns"
    column: {
      header: "SKU"
    }
    column: {
      header: "WEEK"
    }
    column: {
      header: "STORE"
    }
    column: {
      header: "RETURNS"
    }
    plugin_logic: {
      name: "returns"
      plugin_name: "measure"
      param: {
        key: "measure_str"
        value: "filter Returns by &lt;= max_returns : float"
      }
      param: {
        key: "max_returns"
        value: "200"
      }
    }
  }
  predicate: {
    predicate_name: "month_week"
    column: {
      header: "WEEK"
    }
    column: {
      header: "MONTH"
    }
  }
}
2 Comments
  1. Marouen Marzouki 7 years ago

    Are “required” and “can_be_absent” in the FileColumn message serving two different purposes?

Leave a reply

© Copyright 2020. Infor. All rights reserved.

Log in with your credentials

Forgot your details?