Ответ 1

И вот, мне наконец удалось решить проблему - оказалось, что доступ к данным Power Pivot с помощью Python действительно возможен! Ниже приведено краткое описание того, что я сделал - вы можете найти более подробное описание здесь: службы Analysis Services (SSAS) на небольшой основе. Примечание: код не был оптимизирован ни для эффективности, ни для элегантности.

• Установите Microsoft Power BI Desktop (поставляется с бесплатным сервером служб Analysis Services, поэтому нет необходимости в дорогостоящей лицензии на SQL Server - однако тот же подход, очевидно, также работает, если у вас есть соответствующая лицензия).
• Запустите механизм AS, сначала создав файл настроек msmdsrv.ini, затем восстановите базу данных из файла ABF (используя AMO.NET), а затем извлеките данные с помощью ADOMD.NET.

Вот код Python, который иллюстрирует движок 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 = '''<ConfigurationSettings>
       <DataDir>{0}</DataDir>
       <TempDir>{0}</TempDir>
       <LogDir>{0}</LogDir>
       <BackupDir>{0}</BackupDir>
       <DeploymentMode>2</DeploymentMode>
       <RecoveryModel>1</RecoveryModel>
       <DisklessModeRequested>0</DisklessModeRequested>
       <CleanDataFolderOnStartup>1</CleanDataFolderOnStartup>
       <AutoSetDefaultInitialCatalog>1</AutoSetDefaultInitialCatalog>
       <Network>
          <Requests>
             <EnableBinaryXML>1</EnableBinaryXML>
             <EnableCompression>1</EnableCompression>
          </Requests>
          <Responses>
             <EnableBinaryXML>1</EnableBinaryXML>
             <EnableCompression>1</EnableCompression>
             <CompressionLevel>9</CompressionLevel>
          </Responses>
          <ListenOnlyOnLocalConnections>1</ListenOnlyOnLocalConnections>
       </Network>
       <Port>{1}</Port>
       <PrivateProcess>{2}</PrivateProcess>
       <InstanceVisible>0</InstanceVisible>
       <Language>1033</Language>
       <Debug>
          <CallStackInError>0</CallStackInError>
       </Debug>
       <Log>
          <Exception>
             <CrashReportsFolder>{0}</CrashReportsFolder>
          </Exception>
          <FlightRecorder>
             <Enabled>0</Enabled>
          </FlightRecorder>
       </Log>
       <AllowedBrowsingFolders>{0}</AllowedBrowsingFolders>
       <ResourceGovernance>
          <GovernIMBIScheduler>0</GovernIMBIScheduler>
       </ResourceGovernance>
       <Feature>
          <ManagedCodeEnabled>1</ManagedCodeEnabled>
       </Feature>
       <VertiPaq>
          <EnableDisklessTMImageSave>0</EnableDisklessTMImageSave>
          <EnableProcessingSimplifiedLocks>1</EnableProcessingSimplifiedLocks>
       </VertiPaq>
    </ConfigurationSettings>'''

    #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

И часть извлечения данных:

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

Сырые данные затем извлекаются с помощью чего-то вроде этого:

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)

Ответ 2

Проблема с получением данных из PowerPivot заключается в том, что табличный движок в PowerPivot работает внутри процесса в Excel, и единственный способ подключиться к этому движку - заставить ваш код работать внутри Excel тоже. (Я подозреваю, что он может использовать разделяемую память или какой-либо другой транспорт, но он определенно не прослушивает порт TCP или именованный канал или что-либо подобное, что позволит подключиться к внешнему процессу)

Мы делаем это в Dax Studio, запустив надстройку С# VSTO Excel в Excel. Однако это было предназначено только для тестирования аналитических запросов, а не для массового извлечения данных. Мы перенаправляем данные из надстройки в пользовательский интерфейс с использованием строковой переменной, поэтому весь набор данных должен быть меньше 2 ГБ или ответ усекается, и вы увидите ошибку "неузнаваемого ответа" (данные сериализуются в набор строк XMLA который довольно многословный, поэтому может показаться, что он прерывается, когда извлекается только несколько сотен МБ данных)

Если вы хотите создать сценарий для автоматизации извлечения всех необработанных данных из модели, я не думаю, что вы сможете сделать это с Python, поскольку я не верю, что вы можете заставить интерпретатор python работать внутри процесса в Excel. Я бы посмотрел на использование макроса vba, подобного этому http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/

Вы должны найти, что вы можете запросить модель для списка таблиц с чем-то вроде "SELECT * FROM $ SYSTEM.DBSCHEMA_TABLES" - вы могли бы затем перебирать каждую таблицу и извлекать с изменением кода в приведенной выше ссылке.

Ответ 3

Я связался с Томом Глисоном (он же Гобан Саор), который был достаточно любезен, чтобы позволить мне опубликовать его электронные письма здесь. В них есть некоторые интересные самородки, поэтому, надеюсь, другие также найдут их полезными.

Электронная почта # 1

Когда вы говорите Python, вы имеете в виду запуск Python.NET в качестве автономного exe? Если это так, вам не повезло с моделями Excel PP (разная история для рабочего стола Power BI). Ive получил доступ к моделям PP (2010+) успешно как из VBA, так и из Python.NET (через AMO), используя аналогичный код в соответствии с вашим вопросом SO. Разница заключается в том, что (как в версии VBA, так и в.NET) заключается в том, что мой код работает в процессе Excel с использованием дополнительных технологий надстройки Excels. (Вероятная таблица также работает как надстройка или встроенный Excel внутри себя, что позволяет подобное поведение). DAX Studio (полезная база кода С# для ознакомления с правилами доступа PP) работает как надстройка Excel, так и как автономная EXE, но только в качестве надстройки она может обращаться к моделям PP на основе Excel.

Электронная почта # 2

Вы можете найти процесс использования Python.NET для этого несколько сложного. Вам понадобится встроить Python-движок с использованием кода надстройки С#/VB.NET Excel. Ive использовал Excel-DNA (фантастический проект с открытым исходным кодом), а не MS, весьма громоздкий "официальный" метод для разработки таких добавлений.NET в прошлом, но я в основном придерживаюсь VBA, где это вообще возможно.

Используя VBA, вы не сможете получить доступ к AMO.NET-only (так что не сможете создавать расчетные столбцы "на лету"), но, загрузив полученный набор данных в набор записей ADO, вы сможете выводить на рабочий лист ИЛИ корпоративному -database/MS Access ИЛИ в файл /CSV и т.д.

В отличие от предела лимита 1M, для плоского файла или базы данных выходная память (ОЗУ) будет лимитирующим фактором, но, предполагая, что вы используете 64-битный Excel и имеете достаточно памяти для хранения сжатой модели и рабочей области для самой большой таблицы моделей в не-уплотненной форме (т.е. формат на основе строки, а не на основе столбцов, который является результатом запроса DAX), умноженный на 2ish (один экземпляр внутри рабочего пространства PP, другой в рабочем пространстве ADOB), вы должны быть в порядке.

Сказав это, Ive никогда не пытались извлечь очень большой набор данных, а использование моделей в качестве среды обмена данными не является одним из "вариантов использования" ПП; поэтому очень большие таблицы могут поразить некоторые другие ошибки/ограничения!

Ответ 4

Я заранее благодарен, если возможно, чтобы обсудить ваше очень полезное решение.

[email protected]