Python - "KeyError : System.Object" - Pyadomd - Querying a SSAS Data Source

Working on a project where I am trying to query a SSAS data source we have at work through Python. The connection is presently within Excel files, but I am trying to reverse engineer the process with Python to automate part of the analysis I do on a day to day... I use the pyadomd library to connect to the data source, here`s my code:

clr.AddReference(r"C:\Program Files (x86)\Microsoft  Office\root\vfs\ProgramFilesX86\Microsoft.NET\ADOMD.NET\130\Microsoft.AnalysisServices.AdomdClient.dll")
clr.AddReference('Microsoft.AnalysisServices.AdomdClient')
from Microsoft.AnalysisServices.AdomdClient import AdomdConnection , AdomdDataAdapter
from sys import path
path.append('C:\Program Files (x86)\Microsoft Office\root\vfs\ProgramFilesX86\Microsoft.NET\ADOMD.NET\130\Microsoft.AnalysisServices.AdomdClient.dll')
import pyadomd
from pyadomd import Pyadomd
from pyadomd._type_code import adomd_type_map, convert

constr= "connection string"

with Pyadomd(constr) as conn:
    with conn.cursor().execute(query) as cur:
        print(cur.fetchall())

Which works (in part), seemingly I am able to connect to the SSAS data source. Say I do conn = Pyadomd(constr), it returns no error (no more as it did before). The issue is when I try to execute the query with the cursor it returns an error saying:

  File "C:\Users\User\Anaconda3\lib\site-packages\pyadomd\pyadomd.py", line 71, in execute
    adomd_type_map[self._reader.GetFieldType(i).ToString()].type_name

KeyError: 'System.Object'

By doing a bit of research, I found that KeyError meant that the code was trying to access a key within a dictionary in which that key isn't present. By digging through my variables and going through the code, I realized that the line:

 from pyadomd._type_code import adomd_type_map

Created this dictionary of keys:values: See dictionary here

Containing these keys: System.Boolean, System.DateTime, System.Decimal, System.Double, System.Int64, System.String. I figured that the "KeyError: System.Object" was referring to that dictionary. My issue is how can I import this System.Object key to that dictionary? From which library/module/IronPython Clr reference can I get it from?

What I tried:

clr.AddReference("System.Object")

Gave me error message saying "Unable to find assembly 'System.Object'. at Python.Runtime.CLRModule.AddReference(String name)"

I also tried:

from System import Object #no error but didn't work
from System import System.Object #error saying invalid syntax

I think it has to do with some clr.AddReference IronPython thing that I am missing, but I've been looking everywhere and can't find it.

Thanks!

Solution

Glad that the newer version solved the problem.

A few comments to the code snippet above. It can be done a bit more concise 😊 Pyadomd will import the necessary classes from the AdomdClient, which means that the following lines can be left out.

clr.AddReference(r"C:\Program Files (x86)\MicrosoftOffice\root\vfs\ProgramFilesX86\Microsoft.NET\ADOMD.NET\130\Microsoft.AnalysisServices.AdomdClient.dll")
clr.AddReference('Microsoft.AnalysisServices.AdomdClient')
from Microsoft.AnalysisServices.AdomdClient import AdomdConnection , AdomdDataAdapter

Your code will then look like this:

import pandas as pd
from sys import path
path.append(r'C:\Program Files (x86)\MicrosoftOffice\root\vfs\ProgramFilesX86\Microsoft.NET\ADOMD.NET\130')

from pyadomd import Pyadomd

constr= "constring"
query = "query"

with Pyadomd(constr) as con:
    with con.cursor().execute(query) as cur:
        DF = pd.DataFrame(cur.fetchone(), columns = [i.name for i in cur.description])

The most important thing is to add the AdomdClient.dll to your path before importing the pyadomd package.

Furthermore, the package is mainly meant to be used with CPython version 3.6 and 3.7.