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
.