Skip to main content

Data Export REST API

A customer account is required to read the description of all the REST services provided with the ActivePivot application.

DataExport

DataExport service allows you to download the result of MDX queries as a stream or directly export them to files in the server or in the cloud. The exports can be customized.

The minimal configuration for an export requires an MDX query and a format but many options are available.

Formats

All export orders should precise a format for the output. By default, the available formats are:

  • arrow : Arrow format (using the format version 1.0.0) that conserves the type of the values exported (for more details on Arrow here)
  • csvTabular : CSV format with one level per column and one measure per column
  • csvPivotTable : CSV format to reproduce the format of the MDX query. Can only handle MDX query with columns and/or rows (only 2 axis)

When using the csv formats you can add a custom charset or/and a custom separator.

Difference between the two csv formats

The csvPivotTable will not change the structure of the MDX result. The csvTabular will change the structure of the MDX result to have one level per column and one measure per column. The difference is only visible if the query puts several measures or levels in the same column.

Let's take this query as an example:

"SELECT [People].[People].[traderId].Members ON ROWS,
Crossjoin([Currency].[Currency].[currency].Members, {[Measures].[PNL]}) ON COLUMNS
FROM [myCube]"

The query result will have two axis: the ROWS with the traderId level, and the COLUMNS with the currency level members and the measure PNL. The csvPivotTable format will return:

People,EUR>PNL,USD>PNL
2,1.1,1.2
3,-1.1,-0.2
4,1.4,1.5

But the csvTabular format will put the currency and the PNL measure on different columns and return:

[Currency].[Currency].[currency],[People].[People].[traderId],PNL
EUR,2,1.1
USD,2,1.2
EUR,3,-1.1
USD,3,-0.2
EUR,4,1.4
USD,4,1.5

Customization

The data export service is made to allow customization. You can easily add measure formatters or even add a whole new export format.

Configure the export directory

Use the activeviam.dataexportservice.rootpath property to specify a root folder for the exported files.

Add a New Measure Formatter

To add a new measure formatter you will need to implement a IMeasureFormatter and give it a plugin key to use it in the export orders. A measure formatter can transform the headers and/or the value of a measure.

Cloud exports

To export your query results to a cloud storage, override ActivePivotRestServicesConfig#dataExportService to create a CloudDataExportService.