3 回答

• 3

  瞧，我终于设法解决了这个问题 - 事实证明使用Python访问Power Pivot数据确实是可能的！下面是我所做的简短回顾 - 你可以在这里找到更详细的描述：Analysis Services (SSAS) on a shoestring . 注意：代码已经过优化，无论是效率还是优雅 .

  • 安装Microsoft Power BI桌面（附带免费的Analysis Services服务器，因此不需要昂贵的SQL Server许可证 - 但是，如果您拥有适当的许可证，相同的方法显然也可以使用） .

  • 首先创建msmdsrv.ini设置文件，然后从ABF文件（使用AMO.NET）恢复数据库，然后使用ADOMD.NET提取数据，启动AS引擎 .

  这是说明AS引擎AMO.NET部分的Python代码：

  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)
  

  回复于 2024-05-02T21:00:21+08:00
• 8

  从PowerPivot获取数据的问题在于，PowerPivot中的表格引擎在Excel内部运行，并且连接到该引擎的方式也是让您的代码在Excel中运行 . （我怀疑它可能使用共享内存或其他一些传输，但它肯定不会侦听TCP端口或命名管道或类似的东西，这将允许外部进程连接）

  我们通过在Excel中运行C＃VSTO Excel加载项在Dax Studio中执行此操作 . 但是，它仅用于测试分析查询，而不是用于批量数据提取 . 我们使用字符串变量编组来自UI的加载项的数据，因此整个数据集必须小于2Gb或响应被截断，您将看到“无法识别的响应”错误（数据被序列化为XMLA行集）这是非常冗长的，所以当只提取几百Mb的数据时可能会看到它中断）

  如果你想构建一个脚本来自动从模型中提取所有原始数据，我不相信你可以在Excel中运行进程内运行的python解释器 . 我会看看像这样的一个vba宏http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/

  您应该会发现可以使用类似“SELECT * FROM $ SYSTEM.DBSCHEMA_TABLES”的内容查询模型以获取表的列表 - 然后您可以遍历每个表并使用上述链接中的代码变体进行提取 .

  回复于 2024-05-02T21:00:21+08:00
• 1

  我联系了Tom Gleeson（又名GobánSaor），他很友善地让我在这里发布他的电子邮件 . 其中有一些有趣的小块，所以希望其他人也会觉得它们很有用 .

  Email #1

  当你说Python时，你的意思是将Python.NET作为一个独立的exe运行？如果是这种情况，那么您对Excel PP模型（尽管Power BI桌面不同的故事）也不满意 . 我已经使用与您的SO问题类似的代码，从VBA和Python.NET（通过AMO）成功访问了PP模型（2010） . 不同之处（在VBA和.NET版本中）是我的代码使用Excel的各种插件技术在Excel中运行 . （可能Tableau也作为加载项运行，或者在其自身内部嵌入了Excel，从而实现了类似的行为） . DAX Studio（一个有用的C＃代码库，用于学习PP访问的方法）既可作为Excel加载项运行，也可作为独立的EXE运行，但仅作为加载项可以访问基于Excel的PP模型 .

  Email #2

  您可能会发现使用Python.NET的过程具有一定的挑战性 . 您需要使用C＃/ VB.NET Excel加载项代码嵌入Python引擎 . 我曾经使用Excel-DNA（一个梦幻般的开源项目）而不是MS过去开发此类.NET插件的高度繁琐的“官方”方法，但我主要坚持使用VBA . 使用VBA，您将无法访问仅支持.NET的AMO（因此无法动态创建计算列），但通过将生成的数据集加载到ADO记录集中，您应该能够输出到工作表或者企业数据库/ MS访问或平面文件/ CSV等 . 与1M工作表限制不同，对于平面文件或数据库输出内存（RAM）将是限制因素，但是，假设您使用的是64位Excel并且有足够的内存来保存压缩模型和工作空间，用于以非压缩形式存储最大的模型表（即基于行而不是基于列的格式，这将来自DAX查询），乘以2ish（一个实例）在PP工作区内，另一个在VBA的ADO工作区内）你应该没问题 . 话虽如此，我从未尝试过提取非常大的数据集，并且使用模型作为数据集交换介质并不是PP的“用例”之一;所以，非常大的表可能会遇到其他一些bug /约束！

  回复于 2024-05-02T21:00:21+08:00