Have you ever thought of wanting to export measure service data using the tabular data exchange services (TDX)?
If so, you might know that, until now, those two powerful services did not play nice together. It was quite difficult, if not impossible, to configure a delimited file to export data from a measure service’s measure expression. The main reason for such difficulty is that delimited files needs to be declared with the names of the materialized predicates that are to be exported, but since the measure service generates logic on demand, we can’t declare a file binding in advance.
The following example illustrates the case.
// file with headers SKU|STORE|WEEK|SALES
file_definition_by_name["sales-file"] = fd,
file_definition(fd) {
file_delimiter[] = "|",
column_headers[] = "SKU, STORE, WEEK, SALES",
column_formats[] = "alphanum, alphanum, alphanum, float"
}.
// how sales predicate binds to file headers
file_binding_by_name["sales-file-binding"] = fb,
file_binding(fb) {
file_binding_definition_name[] = "sales-file",
predicate_binding_by_name["sales"] =
predicate_binding(_) {
predicate_binding_columns[] = "SKU, STORE, WEEK, SALES"
}
}.
|
This example configures a delimited file with the headers SKU|STORE|WEEK|SALES. The file_binding below the file definition specifies that we want to export the "sales" predicate, and how its columns bind to the delimited file’s headers. As the example shows, we have to specify the name of the predicate that contains our measure service sales data in the predicate_binding_by_name predicate. This doesn’t work well with the measure service, since it generates measure predicates on the fly, on each request to the measure service, so it is not possible to statically declare file bindings, since we don’t know what the measure service’s predicate names are in advance.
To solve this problem, we’ve created a means to configure the TDX to trigger the measure service on demand. Now, on a request to a delimited file, TDX it will ask the measure service to generate the predicates that are to be exported. The measure service will then generate and install code, pulse any predicates to trigger materialization, and then return the name of the generated predicate to TDX. TDX, in turn, will then complete the configuration of the file binding with the generated predicate name.
That probably sounds complicated. But using it is very simple. All we need to do is to bind a measure expression, which describes the measure we want to export, to a file binding, as shown below:
plugin_logic_by_name["sales"] = spec,
plugin_logic(spec) {
executor[] = "measure",
plugin_predicate(),
param("measure_json", "...")
} <- .
file_binding_by_name["sales-file-binding"] = fb,
file_binding(fb) {
file_binding_definition_name[] = "sales-file",
predicate_binding_by_plugin_predicate["sales"] =
predicate_binding(_) {
predicate_binding_columns[] = "SKU, STORE, WEEK, SALES"
}
}.
|
The first clause declares a “plugin predicate” with the name “sales”. This plugin predicate is configured with the "measure_json" parameter, which should be a string with a measure expression in JSON format. The second clause binds the delimited file to the “sales” plugin predicate, via the predicate_binding_by_plugin_predicate predicate.
Measure Service Parameters
As shown above, we configure the measure to be exported using the plugin_logic:param predicate. The main parameter that the measure service expects is that which defines the measure that we want to export. The measure service accepts one of three different parameters to specify this measure:
- measure_json: a JSON formatted Protobuf measure expression message;
- measure_expr: a binary encoded Protobuf measure expression message;
- measure_str: a string that specifies measure expressions according to a new syntax, which is yet to be released (stay tuned!).
Filtering
Naturally, we can specify measure expressions that declare filters. To pass values to the filter, we use the param predicate, as shown below.
plugin_logic_by_name["sales"] = spec,
plugin_logic(spec) {
executor[] = "measure",
plugin_predicate(),
param("measure_str", "filter Sales by <= max_sales : float"),
param("max_sales", "100")
} <- .
|
In this example, we are exporting sales that is filtered by a max_sales parameter. In this example, we are setting the value of such parameter statically. Assuming that this delimited file is being serviced at /sales, then a get to /sales will return a delimited file with sales filtered by max_sales=100. For this example, it is not possible to change the value of the filter.
To allow a user to change the filter value, we use the allow_override predicate, as shown below.
plugin_logic_by_name["sales"] = spec,
plugin_logic(spec) {
executor[] = "measure",
plugin_predicate(),
param("measure_str", "filter Sales by <= max_sales : float"),
param("max_sales", "100"),
allow_override("max_sales")
} <- .
|
For this example, we can call a GET to /sales?max_sales=200 to get sales data filtered by max_sales=200.
Joining multiple predicates
Just as with regular delimited file, we can also specify file bindings that will join measure predicates with other measure predicates or any other predicate. The example below, for instance, exports a delimited file with the headers SKU, STORE, WEEK, SALES, RETURNS joins a sales and a returns predicate.
file_definition_by_name["sales-returns"] = fd,
file_definition(fd) {
file_delimiter[] = "|",
column_headers[] = "SKU,STORE,WEEK,SALES,RETURNS",
column_formats[] = "alphanum,alphanum,alphanum,float,float"
}.
file_binding_by_name["sales-returns"] = fb,
file_binding(fb) {
file_binding_definition_name[] = "sales-returns",
predicate_binding_by_plugin_predicate_index[0, "sales"] =
predicate_binding(_) {
predicate_binding_columns[] = "SKU,STORE,WEEK,SALES"
},
predicate_binding_by_plugin_predicate_index[1, "returns"] =
predicate_binding(_) {
predicate_binding_columns[] = "SKU,STORE,WEEK,RETURNS"
}
}.
plugin_logic_by_name["sales"] = spec,
plugin_logic(spec) {
plugin_predicate(),
executor[] = "measure",
param("measure_str", "Sales")
}.
plugin_logic_by_name["returns"] = spec,
plugin_logic(spec) {
plugin_predicate(),
executor[] = "measure",
param("measure_str", "Returns")
}.
|
Dynamic vs. static delimited files
In the previous examples, most of the measure configuration was static. We allowed only values for filters to be specified dynamically (on request). The use of "*", however, allows us to leave more of the measure configuration to be done via request parameters, thus, allowing us to create more dynamic services.
We could, for example, allow the user to set the the measure expression as a request parameter using allow_override("measure_str"), or allow_override("measure_*"), or even allow_override("*"). The only caveat here is that the measure we specify must generate a measure predicate that matches the delimited file’s predicate_binding_columns = "SKU, STORE, WEEK, SALES".
plugin_logic_by_name["sales"] = spec,
plugin_logic(spec) {
executor[] = "measure",
plugin_predicate(),
allow_override("*")
} <- .
GET /sales?measure_str=filter Sales by <= max_sales : float&max_sales=200
|
Not dynamic enough?
Although the above example shows how we can completely specify measures and filters on request, the caveat we have mentioned means that the delimited file needs to be configured statically. In our next post, however, we will reveal how it is possible to be completely specify the file binding and the measure expression via request parameters. This means that that it possible to export a delimited file to export any predicate, without having to statically configure any file binding. This means no more compiling and rebuilding workspaces just to export another delimited file. Stay tuned!
