CODEWITHSUNDEEP
pythonpandasolappython.net
marc nicole
marc nicole

Reputation: 83

Convert Pythonnet result set into a pandas dataframe

I am using Pythonnet to query a cube and return the results in a System.Data 's DataSet. Now I want to convert DataSet into a Pandas' dataframe.

Full code :

clr.AddReference("r"C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\11.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.AdomdClient.dll"")
clr.AddReference ("System.Data")
from Microsoft.AnalysisServices.AdomdClient import AdomdConnection , AdomdDataAdapter
from System.Data import DataSet
conn = AdomdConnection("Data Source=tooldata.amr.xxx.com;Catalog=someDB;")
conn.Open()
cmd = conn.CreateCommand() 
cmd.CommandText = "your mdx query" 
adp = AdomdDataAdapter(cmd)
datasetParam =  DataSet()
adp.Fill(datasetParam)
#convert datasetParam into Pandas' dataframe?
conn.Close();

Is there a simple way to do that?

Upvotes: 0

Views: 228

Answers (1)

Peter Kazmir
Peter Kazmir

Reputation: 49

to get data from olap with pythonnet you can use mdx-to-pd

from mdx_to_pd import mdx_retriever

connection = "Data Source=https://biserver.company.com/database/;Catalog=Model;"
query = """
        SELECT NON EMPTY [Measures].[Order Count] ON COLUMNS,
        NON EMPTY ([Markets].[Country].[Country]) ON ROWS
        FROM [OLAP_CUBE]
        """
# returns pd.DataFrame()

df = mdx_retriever(query, connection)

Upvotes: 0

Related Questions