pyadomd

A pythonic approach to query SSAS data models.

Installation

console pip install pyadomd

Query SSAS Tabular model

```python from sys import path path.append('\Program Files\Microsoft.NET\ADOMD.NET\150')

from pyadomd import Pyadomd

conn_str = 'Provider=MSOLAP;Data Source=localhost;Catalog=AdventureWorks;' query = """EVALUATE Product"""

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

Integrates easily with pandas

```python

from pandas import DataFrame

with Pyadomd(conn_str) as conn: with conn.cursor().execute(query) as cur: df = DataFrame(cur.fetchone(), columns=[i.name for i in cur.description]) ```

FAQ

Q: I get the following exception? C# System.IO.FileNotFoundException: Unable to find assembly 'Microsoft.AnalysisServices.AdomdClient'. at Python.Runtime.CLRModule.AddReference(String name) A: This exception is most likely raised because you have'ent added the folder with the Microsoft.AnalysisServices.AdomdClient.dll to your path, before you import the pyadomd package.

Example: ```Python from sys import path

added to the path before importing the pyadomd package

path.append('\Program Files\Microsoft.NET\ADOMD.NET\150')

from pyadomd import Pyadomd ```

Q: When I try to connect to an Azure Analysis Service instance I get: Authentication faild: User ID and Password are required when user interface is not available?

A: This exception is most likely raised due to your "app" is not registered. Please follow the microsoft app registration documentation microsoft docs. The script: connect_to_ass.py (link to script) is a simple example on how to create a connection to a Azure Analysis Service. Please note that this is only an example, and not necessarily suitable for all apps.

Issues

unable to query using parameters

opened on 2023-01-13 13:40:02 by StefanStoian None

Unhandled exception when using Pyadomd as generator

opened on 2022-02-14 20:39:53 by nenkie

I was trying to improve runtime of application and yield chunks from pyadomd, transform data and write it immediately. Unfortunately faced unhandled exception:

File "C:\PythonEnv\CustomerProfiles2\lib\site-packages\pyadomd\pyadomd.py", line 94, in fetchmany l.append(next(self.fetchone())) File "C:\PythonEnv\CustomerProfiles2\lib\site-packages\pyadomd\pyadomd.py", line 81, in fetchone while(self._reader.Read()): Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException: The server sent an unrecognizable response. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.ReadEndElementS(XmlReader reader, String name, String ns) at Microsoft.AnalysisServices.AdomdClient.XmlaDataReader.InternalRead()

Logic to reproduce:

``` def get_data(): chunk_size = 10000 path.append('\Program Files\Microsoft.NET\ADOMD.NET\150') from pyadomd import Pyadomd

with Pyadomd(source) as conn: with conn.cursor().execute(self.resource) as cur: while True: rows = cur.fetchmany(chunk_size) row_count = len(rows)

          if not rows:
              break

          yield rows

def write_data(data): some_write_method()

def run(self): for chunk in get_data(): write_customers(customers) ```

All rows are written successfully (unless you catch locking conflict when Tabular model is refreshed), but at the end last iteration throws an exception.

Having spent some time on it, I got that issue comes from line 93-94 lines of pydomd.py when retrieving next item which is not existing. For example if you have 6 rows to extract and you put chunk_size 1 or 2 or 3 then no exception occurs, but if you put 4 or 5, then you got exception, since first chunk passes ok and then second chunk is not complete.

Python 3.9 and 3.10 support

opened on 2021-11-05 00:03:39 by matthew1davis

As mentioned in this Stackoverflow post it seems that this library currently does not support Python 3.9 (originally released October 2020) or Python 3.10 (original released last month October 2021). When trying to install this library in with a Python 3.9 version using pip, I face the following error:

``` C:\Python39\libs> pip install pyadomd Collecting pyadomd Using cached pyadomd-0.1.0.tar.gz (5.2 kB) Preparing metadata (setup.py) ... done Collecting pythonnet Using cached pythonnet-2.5.2.tar.gz (1.9 MB) Preparing metadata (setup.py) ... done Requirement already satisfied: pycparser in c:\python39\lib\site-packages (from pythonnet->pyadomd) (2.20) Using legacy 'setup.py install' for pyadomd, since package 'wheel' is not installed. Using legacy 'setup.py install' for pythonnet, since package 'wheel' is not installed. Installing collected packages: pythonnet, pyadomd Running setup.py install for pythonnet ... error ERROR: Command errored out with exit status 1: command: 'C:\Python39\python.exe' -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\Users\some_username\AppData\Local\Temp\2\pip-install-xseq24q1\pythonnet_bf3f0e493d964733872d8df8031a0b0a\setup.py'"'"'; file='"'"'C:\Users\some_username\AppData\Local\Temp\2\pip-install-xseq24q1\pythonnet_bf3f0e493d964733872d8df8031a0b0a\setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(file) if os.path.exists(file) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' install --record 'C:\Users\some_username\AppData\Local\Temp\2\pip-record-msa6cxi6\install-record.txt' --single-version-externally-managed --compile --install-headers 'C:\Python39\Include\pythonnet' cwd: C:\Users\some_username\AppData\Local\Temp\2\pip-install-xseq24q1\pythonnet_bf3f0e493d964733872d8df8031a0b0a\ Complete output (6 lines): usage: setup.py [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...] or: setup.py --help [cmd1 cmd2 ...] or: setup.py --help-commands or: setup.py cmd --help

error: option --single-version-externally-managed not recognized
----------------------------------------

ERROR: Command errored out with exit status 1: 'C:\Python39\python.exe' -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\Users\some_username\AppData\Local\Temp\2\pip-install-xseq24q1\pythonnet_bf3f0e493d964733872d8df8031a0b0a\setup.py'"'"'; file='"'"'C:\Users\some_username\AppData\Local\Temp\2\pip-install-xseq24q1\pythonnet_bf3f0e493d964733872d8df8031a0b0a\setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(file) if os.path.exists(file) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' install --record 'C:\Users\some_username\AppData\Local\Temp\2\pip-record-msa6cxi6\install-record.txt' --single-version-externally-managed --compile --install-headers 'C:\Python39\Include\pythonnet' Check the logs for full command output. ```

To resolve... I believe the setup.py file needs to be updated to include an explicit callout for Python 3.9?

using NetCore version

opened on 2021-01-24 08:42:40 by bowerth

Hello,

This is not an issue but rather a request: have you tested using the .NET Core ADOMD package? This would allow extending usage to platforms other than Windows.

Cheers

Releases

Release 7.0 2022-01-22 08:17:44

• pyadomd-0.1.1.tar.gz

Bug fix issue #14 merge PR #15

Fix missing .Net data type conversion.

Adds the following:

.Net data type -> Python data type

Single-> float
UInt16 -> int

Sixth release 2021-08-31 18:06:38

• pyadomd-0.1.0.tar.gz

Bug fix issue #8 merge PR #9

Fix missing .Net data type conversion.

Adds the following:

.Net data type -> Python data type - Guid -> str - UInt32 -> int - Int32 -> int - Int16 -> int

Fifth release 2021-08-14 14:59:09

• pyadomd-0.0.9.tar.gz

Bug fix: Issue #6 data types: - System.Boolean: False -> False of type bool - System.Int64: 0 -> 0 of type int - System.UInt64: 0 -> 0 of type int - System.Decimal: 0.0 -> 0.0 of type float - System.Double: 0.0 -> 0.0 of type float

Fourth release 2021-05-26 18:52:07

Bug fix #5 exception when converting type uint64

Third Release 2020-06-21 20:06:58

• pyadomd-0.0.7.tar.gz

Added support for python 3.8

Second release 2020-05-26 06:08:47

• pyadomd-0.0.6.tar.gz

Bug fix: System.Object Key error