ActivePivot retrieve CSV output by sending MDX query through python?

Question

How can I retrieve CSV output file by sending MDX query to ActivePivot by using python? Instead of XMLA or Web Services.

Answer 1

there is a POST endpoint to get CSV content from an MDX query, available since ActivePivot 5.4.

By calling http://<host>/<app>/pivot/rest/v3/cube/export/mdx/download with the following JSON payload:

{
    "jsonMdxQuery":
    {
        "mdx" : "<your MDX query>",
        "context" : {}
    },
    "separator" : ";"
}

you will receive the content of the answer as CSV with fields separated by ;.

However, note that the form of your MDX will impact the form of the CSV. For good results, I suggest you MDX queries in the form of:

SELECT 
    // Measures as columns
    {[Measures].[contributors.COUNT], ...} ON COLUMNS 
    // Members on rows
    [Currency].[Currency].[Currency].Members ON ROWS
    FROM [cube]

It will generate a CSV as below:

[Measures].[Measures];[Currency].[Currency].[Currency];VALUE
contributors.COUNT;EUR;170
pnl.SUM;EUR;-8413.812452550741
...

Cheers