jasonwjones / essbasepy Goto Github PK

Hi, I'd like to make use of tdf() but am unsure of how to do so. Does anyone have sample code I could take a look at? Trying to find the easiest way to convert a result set from an mdx query into a dataframe, and this seems like a good route, if I can understand it well enough to use it!

Update, figured it out.

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

Hi,
I'm wondering if there is any way to pass encrypted credentials to the Essbase server when using the connect command. When using the essbase client it is possible to connect to essbase using encrypted credentials and a private key that can be generated by the MaxL client itself. I would assume the functionality is available via the dll, since the client is using it as well.

Is this possible already or is it an enhancement?

Thanks

Looking at some other issues, the ESSBASEPATH should be something like 'c:\Oracle...\Essbase.... .
I have no Essbase data in this directory, Is this something i need to download separately?

Usually I connect to the Essbase cube via the Smartview excel plugin, so i have a 'C:\Hyperion\SmartView' and 'C:\Oracle\SmartView' directory,

Any help would be greatly appreciated.

I am running the mdxtest.py example and the code is failing on line 2. The below code snippet is the error that is being thrown. Can't seem to find that particular dll on my computer either (thoughts on where I can download it?). I am up for any questions/suggestions: spent a ton of time testing various configurations... to no avail. This "issue" isn't so much a problem with the essbasepy code as it is with getting it to run on my computer. Thanks in advance.

Running Python 2.7.5

__maxldll = find_library('essmaxlu') if __maxldll: print ("Using Maxl DLL in {DLLpath}".format(DLLpath = __maxldll)) self.maxl = cdll.LoadLibrary(__maxldll) else: print ("maxl DLL not found") raise SystemExit

Hi there,
I can't get a MDX query to return the resultset

import Essbase
esb = Essbase.Essbase()

esb.connect('xxx','xxx','xxx')

stmts = [
    "alter session set dml_output alias off",
    "alter session set dml_output metadata_only on",
    "SELECT NON EMPTY CrossJoin(Descendants([ALL ENTITIES],10,LEAVES),Descendants([BUSINESS LINE],10,LEAVES)) ON ROWS, {[YTD]} ON COLUMNS FROM [FCRSSG].[FCRS] WHERE {([A2_01],[ACT],[PARTNER ENTITY],[FY16],[M5],[PRODUCT],[CUSTOMER],[COST CENTER],[ACCOUNT NATURE],[Adjusted])}"
]

for stmt in stmts:
    esb.execute(stmt)

esb.disconnect()

It returns

ERROR - 1241202 - The most recent MaxL statement did not produce any output

Although the same statement inside MaxL works fine.

Does essbasepy support MDX queries? If it does, am I missing something?

Thanks,

Regis

Hi,
When I do an execute() of a mdx statement it is printing the results out:

Axis-1 (Qtr1) (Qtr2) (Qtr3) (Qtr4)

(Clown Noses) 6608 7167 9579 3152
(Floppy Shoes) 89 99 23 84

However if I change to using the esb.do(" ...mdx...") and then print the rows using:
rows = list(esb.rows())
for row in rows:
print (row)

Then the output does not have the Axis-1. It looks like this:

[b'(Clown Noses)', 6608, 7167, 9579, 3152]
[b'(Floppy Shoes)', 89, 99, 23, 84]

Is the Axis-1 supposed to be in the result list?
My Essbase.py has MAXL_MDXCELLSTRSZ = 1024 + 3

Thanks much,
flummoxify

Hi All,

I am trying run the Essbasepy build and automated tests on RHEL 7.3 VM ppc64le.
I followed the steps mentioned in the 'General Installation' section of the readme and was able to build Essbasepy using the command 'python setup.py install'
Output seen:

bash-4.2# python setup.py install
running install
running build
running build_py
running install_lib
running install_egg_info
Removing /usr/lib/python2.7/site-packages/Essbase-11.1.2012.0514-py2.7.egg-info
Writing /usr/lib/python2.7/site-packages/Essbase-11.1.2012.0514-py2.7.egg-info```

But I was unable to run the tests for the package.
As mentioned in the readme, Example Scripts provided are MaxlExec.py, DisplayPrivilegeAll.py and mdxtest.py.
All the scripts have executable rights, but fail to execute

Errors seen in MaxlExec.py
```bash-4.2# python MaxlExec.py <user> <pwd> localhost ls
maxl DLL not found
Exception AttributeError: "class Essbase has no attribute 'isInitialized'" in <bound method Essbase.__del__ of <Essbase.Essbase instance at 0x3fff92bfaab8>> ignored

Errors seen in DisplayPrivilegeAll.py

maxl DLL not found```

Errors in mdxtest.py
```bash-4.2# python mdxtest.py
maxl DLL not found
Exception AttributeError: "class Essbase has no attribute 'isInitialized'" in <bound method Essbase.__del__ of <Essbase.Essbase instance at 0x3fff95dbaab8>> ignored```

'Installation Sequence for 11.1.2.3' section mentioned to add the Essbase DLL folder in your PATH. 
Can anyone help with how can I get the DLL installed in my container so that I can add it to the PATH. 

Thanks,
Archa

Hello,

I am unable to execute below command post importing Essbase module. Any help would be great. Thank you!

Hi there,
MDX query executed through essbasepy returns encoded data:

Python 3.5.2 (v3.5.2:4def2a2901a5, Jun 25 2016, 22:18:55) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import Essbase
>>> esb = Essbase.Essbase()
Using Maxl DLL in D:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0\bin\essmaxlu.dll
>>> esb.connect('xxxxx','xxxxx','xxxxxx')
>>> esb.bMdxQuery = True
>>> esb.execute('select non empty Descendants([Market],10,LEAVES) on ROWS, [Year].Children on COLUMNS from Demo.Basic where {([Product],[Actual],[Sales])}')
Wed Mar 15 17:39:46 2017
MAXL> select non empty Descendants([Market],10,LEAVES) on ROWS, [Year].Children on COLUMNS from Demo.Basic where {([Product],[Actual],[Sales])};

Axis-1
------  -
(New_York)      52568384        52568400        52568416        52568368
(Boston)        52568352        52568560        52568576        52568592
(Chicago)       52568608        52568624        52568640        52568656
(San_Francisco) 52568672        52568688        52568704        52568720
(Seattle)       52568736        52568752        52568768        52568784
(Denver)        52568800        52568816        52568832        52568848
(Los_Angeles)   52568864        52568880        52576480        52576496
(Dallas)        52576512        52576528        52576544        52576560
(Houston)       52576576        52576592        52576608        52576624
(Phoenix)       52576640        52576656        52576672        52576688


 OK/INFO - 1241150 - MDX Query execution completed.

0
>>>

The same query run in MaxL client returns the correct numbers.

Am I missing something? Do I need to set something up to fetch the float values?

Thanks,

Regis

Hi Jason ,

While working in windows maxl output works well but whereas in linux output skips for for few statements for some reason. Can you help to check that.

Howdy all. I am trying to connect using python 3 & essbase.py 11.1.2012.51
Code:
import Essbase
esb = Essbase.Essbase()
esb.connect(user='XXX',password='XXX',host='XXX')
Error:

---

OSError Traceback (most recent call last)
in ()
----> 1 esb.connect(user='XXX',password='XXX',host='XXX')

C:\Program Files\Anaconda3\lib\site-packages\Essbase.py in connect(self, user, password, host)
197 self.sid = c_ushort(0)
198 self.ssnInit = maxl_ssninit_t()
--> 199 self.sts = self.maxl.MaxLSessionCreate(c_char_p(host.encode('utf-8')), >c_char_p(user.encode('utf-8')), c_char_p(password.encode('utf-8')), byref(self.ssnInit), byref(self.sid))
200 self.user = user
201 self.numFlds = 0

OSError: exception: access violation reading 0x000000000000000C

I have tested essmsh and able to connect via that method, so my path variables should be correct. Any pointers where I need to look to debug?
Thanks.

Hello everyone.

This is one row (tuple?) returned by my esb.execute(statement):

(January, BE08008309-North, Central and East HQ Mtmt Adj., 2014, ICP, US Dollar, TOPC1, TOPC2, TOPC3, ACTUAL, ACCUMDEP - Accumulated Depreciation) 4321.878

Can I somehow change the member delimiter to a pipe "|" instead of a comma "," ?
In other words, is it possible to alter my MDX query/Essbase.py to separate members with different separator than comma or optionally enclose them within double quotes ?

The problem I'm facing is that some of my members contain commas in their names, e.g. "BE08008309-North, Central and East HQ Mtmt Adj." in my example above includes a comma.

I would like to have my returned row read like this instead:

(January | BE08008309-North, Central and East HQ Mtmt Adj.| 2014| ICP| US Dollar| TOPC1| TOPC2| TOPC3| ACTUAL| ACCUMDEP - Accumulated Depreciation) 4321.878
or
("January", "BE08008309-North, Central and East HQ Mtmt Adj.", "2014", "ICP", "US Dollar", "TOPC1", "TOPC2", "TOPC3", "ACTUAL", "ACCUMDEP - Accumulated Depreciation") 4321.878

Thank you.
Marcel

Hi there,
I amended execute and msgs functions to output errors and warnings to stderr pipe. It's easier to isolate exceptions when running a script that generates a lot of output.

msgno, level, msg = self.pop_msg()
stderr = False

while msgno:
    # Decode message level
    if level == MAXL_MSGLVL_SUCCESS:
        msglvl = "OK/INFO"
    elif level == MAXL_MSGLVL_WARNING:
        msglvl = "WARNING"
        stderr = True
    elif level == MAXL_MSGLVL_ERROR:
        msglvl = "ERROR"
        stderr = True
    elif level == MAXL_MSGLVL_FATAL:
        msglvl = "FATAL"
        stderr = True
    else:
        msglvl = str(level)
    if stderr:
        print ("%8s - %7d - %s." % (msglvl, msgno, msg.decode()), file=sys.stderr)
    else:
        print ("%8s - %7d - %s." % (msglvl, msgno, msg.decode()))
    msgno, level, msg = self.pop_msg()

print ('')

"""------------------------------- execute -------------------------------

Execute a MaxL statement and print resulting output.
"""
def execute(self, stmt, output=sys.stdout, timefmt=None):
# format MaxL statement for output
stmt = stmt.replace("\t", "")
out = stmt.split("\n")
for i in range(1, len(out)):
out[i] = "%4d> %s" % (i, out[i])
if timefmt:
timestamp = time.strftime(timefmt)
else:
timestamp = time.asctime()

print (timestamp)
print ("MAXL> %s;\n" % "\n".join(out))

# execute MaxL statement
sts = self.do(stmt)

# exit if the execution failed
if sts:
    # dump status messages
    self.msgs(output)

    print ("Execution of [%s] failed with status %d" % (stmt, sts), file=sys.stderr)
elif self.numFlds:
    print (self.tdf())

# dump status messages
self.msgs(output)

return sts

Thanks,

Regis