Solución:
Y he aquí, finalmente logré resolver el problema: ¡resulta que acceder a los datos de Power Pivot usando Python es realmente posible! A continuación se muestra un breve resumen de lo que hice. Puede encontrar una descripción más detallada aquí: Analysis Services (SSAS) con un presupuesto reducido. Nota: el código no se ha optimizado ni por eficiencia ni por elegancia.
- Instale Microsoft Power BI Desktop (viene con un servidor de Analysis Services gratuito, por lo que no necesita una costosa licencia de SQL Server; sin embargo, el mismo enfoque obviamente también funciona si tiene una licencia adecuada).
- Encienda el motor AS creando primero el archivo de configuración msmdsrv.ini, luego restaure la base de datos desde el archivo ABF (usando AMO.NET), luego extraiga los datos usando ADOMD.NET.
Aquí está el código Python que ilustra las partes del motor AS + AMO.NET:
import psutil, subprocess, random, os, zipfile, shutil, clr, sys, pandas
def initialSetup(pathPowerBI):
sys.path.append(pathPowerBI)
#required Analysis Services assemblies
clr.AddReference("Microsoft.PowerBI.Amo.Core")
clr.AddReference("Microsoft.PowerBI.Amo")
clr.AddReference("Microsoft.PowerBI.AdomdClient")
global AMO, ADOMD
import Microsoft.AnalysisServices as AMO
import Microsoft.AnalysisServices.AdomdClient as ADOMD
def restorePowerPivot(excelName, pathTarget, port, pathPowerBI):
#create random folder
os.chdir(pathTarget)
folder = os.getcwd()+str(random.randrange(10**6, 10**7))
os.mkdir(folder)
#extract PowerPivot model (abf backup)
archive = zipfile.ZipFile(excelName)
for member in archive.namelist():
if ".data" in member:
filename = os.path.basename(member)
abfname = os.path.join(folder, filename) + ".abf"
source = archive.open(member)
target = file(os.path.join(folder, abfname), 'wb')
shutil.copyfileobj(source, target)
del target
archive.close()
#start the cmd.exe process to get its PID
listPIDpre = [proc for proc in psutil.process_iter()]
process = subprocess.Popen('cmd.exe /k', stdin=subprocess.PIPE)
listPIDpost = [proc for proc in psutil.process_iter()]
pid = [proc for proc in listPIDpost if proc not in listPIDpre if "cmd.exe" in str(proc)][0]
pid = str(pid).split("=")[1].split(",")[0]
#msmdsrv.ini
msmdsrvText = '''
0
0
0
0
2
1
0
1
1
1
1
1
1
9
1
1
2
0
1033
0
0
0
0
0
1
0
1
'''
#save ini file to disk, fill it with required parameters
msmdsrvini = open(folder+"\msmdsrv.ini", "w")
msmdsrvText = msmdsrvText.format(folder, port, pid) #0,1,2
msmdsrvini.write(msmdsrvText)
msmdsrvini.close()
#run AS engine inside the cmd.exe process
initString = ""0\msmdsrv.exe" -c -s "1""
initString = initString.format(pathPowerBI.replace("/","\"),folder)
process.stdin.write(initString + " n")
#connect to the AS instance from Python
AMOServer = AMO.Server()
AMOServer.Connect("localhost:0".format(port))
#restore database from PowerPivot abf backup, disconnect
AMORestoreInfo = AMO.RestoreInfo(os.path.join(folder, abfname))
AMOServer.Restore(AMORestoreInfo)
AMOServer.Disconnect()
return process
Y la parte de extracción de datos:
def runQuery(query, port, flag):
#ADOMD assembly
ADOMDConn = ADOMD.AdomdConnection("Data Source=localhost:0".format(port))
ADOMDConn.Open()
ADOMDCommand = ADOMDConn.CreateCommand()
ADOMDCommand.CommandText = query
#read data in via AdomdDataReader object
DataReader = ADOMDCommand.ExecuteReader()
#get metadata, number of columns
SchemaTable = DataReader.GetSchemaTable()
numCol = SchemaTable.Rows.Count #same as DataReader.FieldCount
#get column names
columnNames = []
for i in range(numCol):
columnNames.append(str(SchemaTable.Rows[i][0]))
#fill with data
data = []
while DataReader.Read()==True:
row = []
for j in range(numCol):
try:
row.append(DataReader[j].ToString())
except:
row.append(DataReader[j])
data.append(row)
df = pandas.DataFrame(data)
df.columns = columnNames
if flag==0:
DataReader.Close()
ADOMDConn.Close()
return df
else:
#metadata table
metadataColumnNames = []
for j in range(SchemaTable.Columns.Count):
metadataColumnNames.append(SchemaTable.Columns[j].ToString())
metadata = []
for i in range(numCol):
row = []
for j in range(SchemaTable.Columns.Count):
try:
row.append(SchemaTable.Rows[i][j].ToString())
except:
row.append(SchemaTable.Rows[i][j])
metadata.append(row)
metadf = pandas.DataFrame(metadata)
metadf.columns = metadataColumnNames
DataReader.Close()
ADOMDConn.Close()
return df, metadf
Luego, los datos sin procesar se extraen a través de algo como esto:
pathPowerBI = "C:/Program Files/Microsoft Power BI Desktop/bin"
initialSetup(pathPowerBI)
session = restorePowerPivot("D:/Downloads/PowerPivotTutorialSample.xlsx", "D:/", 60000, pathPowerBI)
df, metadf = runQuery("EVALUATE dbo_DimProduct", 60000, 1)
endSession(session)
El problema de obtener datos de PowerPivot es que el motor tabular en PowerPivot se ejecuta en proceso dentro de Excel y el solamente La forma de conectarse a ese motor es hacer que su código se ejecute también dentro de Excel. (Sospecho que puede usar memoria compartida o algún otro transporte, pero definitivamente no está escuchando en un puerto TCP o una tubería con nombre o algo por el estilo que permitiría que un proceso externo se conectara)
Hacemos esto en Dax Studio ejecutando un complemento de Excel C # VSTO en Excel. Sin embargo, eso solo fue diseñado para funcionar para probar consultas analíticas, no para realizar una extracción de datos masiva. Ordenamos los datos desde el complemento a la interfaz de usuario mediante un string variable, por lo que todo el conjunto de datos debe ser inferior a 2 Gb o la respuesta se trunca y verá un error de “respuesta irreconocible” (los datos se serializan en un conjunto de filas XMLA que es bastante detallado, por lo que es posible que se rompa cuando solo se extraen unos pocos cientos de Mb de datos)
Si desea crear un script para automatizar la extracción de todos los datos sin procesar de un modelo, no creo que pueda hacerlo con Python, ya que no creo que pueda hacer que el intérprete de Python se ejecute en proceso dentro de Excel. Miraría usando un vba macro como este http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/
Debería encontrar que puede consultar el modelo para obtener una lista de tablas con algo como “SELECT * FROM $ SYSTEM.DBSCHEMA_TABLES”; luego, podría recorrer cada tabla y extraer con una variación del código en el enlace anterior.
valoraciones y reseñas
Si para ti ha resultado de ayuda nuestro post, sería de mucha ayuda si lo compartes con más juniors de esta forma nos ayudas a dar difusión a este contenido.