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.

score 3 · Answer 1 · answered Apr 12 '18 at 10:29

3

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

answered Apr 12 '18 at 10:29

Kineolyan

723
8
24

Thanks you. I will try it. – Valee Apr 13 '18 at 03:29
Rest service is not enabled in my office. Is it possible to do it any other way. – Valee Apr 20 '18 at 11:01
I am not sure to understand what "Rest service is not enabled in my office" means. Does your ActivePivot application do not expose REST services or is it an office policy ? Anyway, there is not such as thing as a python connector like _mysql-connector-python_ would be to MySQL. If you have access to your ActivePivot application, you can code your own system, that would rely on the same service exporting data as the REST API. If not, I am afraid I don't have a solution for you. Reading your comment above, if you have a GUI that connects to ActivePivot, look how it does and try doing the same. – Kineolyan Apr 20 '18 at 12:37

score 2 · Answer 2 · edited May 26 '18 at 18:08

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.

Rest service is not enabled in my office. Is it possible to do it any other way by accessing the GUI application through my python with user name, password and mdx query to export CSV file. — Valee, Apr 20 '18 at 11:02

ActivePivot retrieve CSV output by sending MDX query through python?

2 Answers2