Extract the executed statement to a file

[ExGnyaana]

How do we export the executed MDX statement to a file?

[nurzen]

the content of your query is in the rows of the essbase object, you iterate over it to get it line by line. You can also use essbase.tdf() function to get the results.

Here is the sample Code

mdx_query="""
SELECT
{Profit, [Margin %]}
ON COLUMNS,
NON EMPTY [Product].Children
ON ROWS
FROM Sample.Basic
WHERE [Actual]
"""

esb.bMdxQuery = True
esb.do(mdx_query)
for row in esb.rows():    
    print(row)

then i get these results

[b'(Colas)', -100881760, -100875136]
[b'(Root Beer)', -100881568, -100876320]
[b'(Cream Soda)', -100881664, -100875936]
[b'(Fruit Soda)', -100882336, -100874976]
[b'(Diet Drinks)', -100881520, -100876224]

[ExGnyaana]

Wow. This worked. But as expected MDX throws out a dirty format. Can you please show the "tdf" format? Or is there a way to nicely format it?

I tried this. But there is no data extracted in the file. Whereas i can see it in the console.

import csv
with open('output.csv', 'w', newline='') as csvfile:
    s = csv.writer(csvfile, delimiter=' ', quotechar='|', quoting=csv.QUOTE_MINIMAL)
    for row in esb.rows():
        s.writerow(row)
print("Completed")

[itsvasavya]

well if you just need export data from essbase you can trigger report script and iterate those dimensions or members which you need and export those data in CSV

In your report script { SUPEMPTYROWS } { SUPMISSINGROWS } { SUPZEROROWS }

Below is sample py script, you can tweak it based on your needs,

import sys, os, re, fileinput from multiprocessing import Pool, Array import Essbase

Extracts_location="where you want to export"

def main():

esb = Essbase.Essbase()
esb.connect(esb_usr, esb_pwd, esb_svr)
        rep="export.rep"
        rep_file = open("%s/%s" % (Extracts_location,rep)).read()
        mxl = "export database 'APP'.'DB' using report_file '%s' to data_file '%s'" % (rep_file,"%s.txt" & filename) # .csv or txt your prefernace 
        esb.execute(mxl)
esb.disconnect()

if name == 'main':

Hope it helps

[ExGnyaana]

@itsvasavya : Thanks for the snippet. I am currently running the same using batch script. I have the report script and Maxl. The batch script will trigger Maxl.

I am trying to validate two data source files where Essbase is one of them. The reason i am looking for Python solution is to have everything in one place. Extraction and comparison.

Dream is to connect and extract the essbase data to a DataFrame :)

[itsvasavya]

Then load the extracted data from essbase in to data frame and to compare

pd.concat([df1,df2]).drop_duplicates(keep=False)

[ExGnyaana]

Right!! But i dont want to use Report script, Maxl and Python. I would love to have all in one place so that it is easy to maintain. In your example, i need to have a report script (.rep) and Python. Is there a way to write report script in Python or write MDX in python and save the output to a csv file.

[itsvasavya]

then use import random function in python which will help to create a random number temp file which you can trigger based on my code above