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

You can use the ActivePivot webservices or RESTful services then you write a python client and fire your MDX query:

With webservices: http://host:port/webapp/webservices

Look for IQueriesService the method executeMDX should help

or

with RESTful services: http://host:port/webapp/pivot/rest/v3/cube/query?_wadl

Look for

<resource path="mdx">
<method name="POST">
<request>
<representation mediaType="application/json"/>
</request>
<response>
<representation mediaType="application/json"/>
</response>
</method>
</resource>

You'll get the query result, loop over the retrieved records and build your own csv.

Another option (still with RESTful services) is to use the following endpoint http://host:port/webapp/pivot/rest/v3/cube/export?_wadl that allows you to export a query result in CSV directly.

Answer 2

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