SSAS MDX Query Within Python (adodbapi library) with dynamic Parameter '?'

3.5k views Asked by At

I am using the adodbapi python library to query an SSAS Cube. Essentially, my goal is to ask the user to first input the Account Id. Then, I would like that to be passed as a parameter dynamically in the WHERE section of the query with a ? and include an argument in the cur.execute() part of the code per how it is mentioned in the documentation I have linked below. cur.execute() is where I am getting the error, and I have also copy and pasted most of the error below.

I have narrowed down the issue to the fact that the parameter is not working. Dynamic parameters work well for me with PyODBC and pd.read_sql, but they don't seem to work with adodbapi, which I must use as it is the only library I have ever successfully queried an SSAS cube with from within python.

import adodbapi
import numpy as np
import pandas

account_id_input = input("Select Account ID: ").strip()

def get_df(data):
    ar = np.array(data.ado_results) # turn ado results into a numpy array
    df = pd.DataFrame(ar).transpose() # create a dataframe from the array
    return df


with adodbapi.connect('''Provider=MSOLAP;
Data Source=MyServerName;
Initial Catalog=MySSASModel;
User ID=MyUserLogin;
Password=MyPassword;
Persist Security Info=True;
Impersonation Level=Impersonate''', 7200) as con:
    with con.cursor() as cur:
        sql_str = r'''SELECT
NON EMPTY
{
[Measures].[Revenue]
}
ON COLUMNS,

NON EMPTY
[AccountTable].[Year-Month].[Year-Month]

ON ROWS
FROM [Model]
WHERE([AccountTable].[Account Number].[?])
'''
        args = [account_id_input]
        cur.execute(sql_str, args)
        data = cur.fetchall()
        df = get_df(data)
        df.columns = ['Year-Month','Revenue']
df

---------------------------------------------------------------------------
com_error                                 Traceback (most recent call last)
~\.conda\envs\ProgramData\lib\site-packages\adodbapi\adodbapi.py in _execute_command(self)
    681             else: #pywin32
--> 682                 recordset, count = self.cmd.Execute()
    683             # ----- ------------------------------- ---

~\.conda\envs\ProgramData\lib\site-packages\win32com\client\dynamic.py in Execute(self, RecordsAffected, Parameters, Options)

~\.conda\envs\ProgramData\lib\site-packages\win32com\client\dynamic.py in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    286         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
--> 287                 result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    288                 return self._get_good_object_(result, user, resultCLSID)

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for Analysis Services.', 'The following system error occurred:  The parameter is incorrect. ', None, 0, -2147467259), None)

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-51-87576a978d18> in <module>
     35 '''
     36         args = [account_id_input]
---> 37         cur.execute(sql_str, args)
     38 #         data = cur.fetchall()
     39 #         df = get_df(data)

~\.conda\envs\ProgramData\lib\site-packages\adodbapi\adodbapi.py in execute(self, operation, parameters)
    873         if verbose > 3:
    874             print('Params=', format_parameters(self.cmd.Parameters, True))
--> 875         self._execute_command()
    876 
    877     def executemany(self, operation, seq_of_parameters):

~\.conda\envs\ProgramData\lib\site-packages\adodbapi\adodbapi.py in _execute_command(self)
    688                                                             format_parameters(self.cmd.Parameters, True))
    689             klass = self.connection._suggest_error_class()
--> 690             self._raiseCursorError(klass, _message)
    691         try:
    692             self.rowcount = recordset.RecordCount

~\.conda\envs\ProgramData\lib\site-packages\adodbapi\adodbapi.py in _raiseCursorError(self, errorclass, errorvalue)
    561         if eh is None:
    562             eh = api.standardErrorHandler
--> 563         eh(self.connection, self, errorclass, errorvalue)
    564 
    565     def build_column_info(self, recordset):

~\.conda\envs\ProgramData\lib\site-packages\adodbapi\apibase.py in standardErrorHandler(connection, cursor, errorclass, errorvalue)
     55             cursor.messages.append(err)
     56         except: pass
---> 57     raise errorclass(errorvalue)
     58 
     59 # Note: _BaseException is defined differently between Python 2.x and 3.x

DatabaseError: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for Analysis Services.', 'The following system error occurred:  The parameter is incorrect. ', None, 0, -2147467259), None)
Command:

I have tried everything and am out of ideas. I was referencing this document: http://adodbapi.sourceforge.net/quick_reference.pdf and some some other stackoverflow posts that were similar: enter link description here , but I could not figure it out! My MDX is horrible, if anyone else has ideas on how I can rewrite the MDX query, so that it plays better with adodbapi in terms of dynamic parameters, if it is in fact an issue with the library.

0

There are 0 answers