使用Python从PowerPivot模型中提取原始数据

Question

当我试图使用Python从PowerPivot模型中读取一些数据时，一个看似微不足道的任务变成了一个真正的噩梦。基本上，我想要做的就是通过以下方法之一连接到底层的PowerPivot（即MS Analysis Services）引擎，列出模型中包含的表，然后使用简单的DAX查询从每个表中提取原始数据（类似于EVALUATE（table_name））。但事实证明，这可能并不容易。

0. 一些背景信息

如您所见，我尝试了几种不同的方法。我将尽可能仔细地记录所有内容，以便那些不熟悉PowerPivot功能的人对我的要求有一个好的理解。

首先，一些关于编程访问Analysis Services引擎的背景知识（它说是2005 SQL Server，但所有内容应该仍然适用）：SQL Server数据挖掘可编程性和用于Analysis Services连接的数据提供程序。

我将在下面的示例中使用的示例Excel / PowerPivot文件可以在此处找到：Microsoft PowerPivot for Excel 2010和PowerPivot in Excel 2013 Samples。

另外，请注意我使用的是Excel 2010，因此我的一些代码是特定于版本的。例如，如果您使用Excel 2013，则wb.Connections [“PowerPivot Data”] .OLEDBConnection.ADOConnection应改为wb.Model.DataModelConnection.ModelConnection.ADOConnection。

我将在本问题中始终使用的连接字符串基于此处找到的信息：用C#连接到PowerPivot引擎。此外，某些方法显然需要在检索数据之前对PowerPivot模型进行某种初始化。请参见：这里：从VBA自动刷新PowerPivot操作。

最后，这里有几个链接，显示这应该是可以实现的（但请注意，这些链接主要是指C#，而不是Python）：

• 连接PowerPivot DataModel后，如何用数据填充数据集？
• 使用C＃连接PowerPivot
• 2013 C＃连接到PowerPivot DataModel
• 连接Tableau和PowerPivot。 这很简单。（表明外部应用程序实际上可以读取PowerPivot模型数据 - 请注意，Tableau插件安装Interop.ADODB.dll程序集，我猜这就是它用来访问PowerPivot数据的）

1.使用ADOMD

import clr
clr.AddReference("Microsoft.AnalysisServices.AdomdClient")
import Microsoft.AnalysisServices.AdomdClient as ADOMD
ConnString = "Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;
             Location=H:\\PowerPivotTutorialSample.xlsx;SQLQueryMode=DataKeys"

Connection = ADOMD.AdomdConnection(ConnString)
Connection.Open()

看起来问题在于 PowerPivot 模型尚未初始化：

AdomdConnectionException: A connection cannot be made. Ensure that the server is running.

2. 使用 AMO

import clr
clr.AddReference("Microsoft.AnalysisServices")
import Microsoft.AnalysisServices as AMO
ConnString = "Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;
             Location=H:\\PowerPivotTutorialSample.xlsx;SQLQueryMode=DataKeys"

Connection = AMO.Server()
Connection.Connect(ConnString)

同样的故事，“服务器未运行”：

ConnectionException: A connection cannot be made. Ensure that the server is running.

注意，AMO技术在技术上并不用于查询数据，但我将其包括为连接到PowerPivot模型的潜在方式之一。

3. 使用ADO.NET

import clr
clr.AddReference("System.Data")
import System.Data.OleDb as ADONET
ConnString = "Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;
             Location=H:\\PowerPivotTutorialSample.xlsx;SQLQueryMode=DataKeys"

Connection = ADONET.OleDbConnection()
Connection.ConnectionString = ConnString
Connection.Open()

这与使用Python或IronPython访问MSSQL的最简单方法是什么？类似。不幸的是，这也不起作用：

OleDbException: OLE DB error: OLE DB or ODBC error: The following system error occurred:
The requested name is valid, but no data of the requested type was found.

4. 使用adodbapi模块来使用ADO

import adodbapi
ConnString = "Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;
             Location=H:\\PowerPivotTutorialSample.xlsx;SQLQueryMode=DataKeys"

Connection = adodbapi.connect(ConnString)

与《Python和MS Access VBA之间OLEDB/ODBC相反的工作方式》类似，我遇到的错误是：

OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB
Provider for SQL Server 2012 Analysis Services.', u'OLE DB error: OLE DB or ODBC error: The
following system error occurred:  The requested name is valid, but no data of the requested
type was found...

这基本上与上面的ADO.NET问题相同。

5. 通过Excel/win32com模块使用ADO

from win32com.client import Dispatch
Xlfile = "H:\\PowerPivotTutorialSample.xlsx"
XlApp = Dispatch("Excel.Application")
Workbook = XlApp.Workbooks.Open(Xlfile)
Workbook.Connections["PowerPivot Data"].Refresh()
Connection = Workbook.Connections["PowerPivot Data"].OLEDBConnection.ADOConnection
Recordset = Dispatch('ADODB.Recordset')

Query = "EVALUATE(dbo_DimDate)" #sample DAX query
Recordset.Open(Query, Connection)

这个方法的想法来自于一个使用VBA的博客文章：Export a table or DAX query from Power Pivot to CSV using VBA。请注意，此方法使用显式的Refresh命令初始化模型（即“服务器”）。以下是错误消息：

com_error: (-2147352567, 'Exception occurred.', (0, u'ADODB.Recordset', u'Arguments are of
the wrong type, are out of acceptable range, or are in conflict with one another.',
u'C:\\Windows\\HELP\\ADO270.CHM', 1240641, -2146825287), None)

然而，看起来 ADO 连接已经建立:

• type(Connection) 返回 instance
• print(Connection) 返回 Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Subqueries=2;Optimize Response=3;Cell Error Mode=TextValue

问题似乎出在创建 ADODB.Recordset 对象上。

6. 通过 Excel/win32com 使用 ADO，直接使用 ADODB.Connection

from win32com.client import Dispatch
ConnString = "Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;
             Location=H:\\PowerPivotTutorialSample.xlsx;SQLQueryMode=DataKeys"

Connection = Dispatch('ADODB.Connection')
Connection.Open(ConnString)

类似于Python连接Access [重复]和在Win32平台上使用ADO查询Access数据库（Python示例）。不幸的是，Python输出的错误与以上两个示例相同：

com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB Provider for SQL
Server 2012 Analysis Services.', u'OLE DB error: OLE DB or ODBC error: The following system
error occurred:  The requested name is valid, but no data of the requested type was found.
..', None, 0, -2147467259), None)

7. 通过Excel/win32com使用ADO，直接使用ADODB.Connection并刷新模型

from win32com.client import Dispatch
Xlfile = "H:\\PowerPivotTutorialSample.xlsx"
XlApp = Dispatch("Excel.Application")
Workbook = XlApp.Workbooks.Open(Xlfile)
Workbook.Connections["PowerPivot Data"].Refresh()
ConnStringInternal = "Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=
                     Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX
                     Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member
                     Mode=Error;Optimize Response=3;Cell Error Mode=TextValue"

Connection = Dispatch('ADODB.Connection')
Connection.Open(ConnStringInternal)

我希望我可以初始化一个Excel实例，然后初始化PowerPivot模型，接着使用Excel内部连接字符串创建一个连接，以便使用嵌入式PowerPivot数据（类似于如何将powerpivot数据复制到excel工作簿中作为表？ - 请注意连接字符串与我在其他地方使用的不同）。不幸的是，这样做没有效果，我的猜测是Python在单独的实例中启动ADODB.Connection进程(当我执行最后三行时，如果没有先初始化Excel等，则会收到相同的错误消息)：

com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB Provider for SQL
Server 2012 Analysis Services.', u'Either the user, ****** (masked), does not have access
to the Microsoft_SQLServer_AnalysisServices database, or the database does not exist.',
None, 0, -2147467259), None)

- akavalar

---

我在一个行业工作，在这个行业中我们经常从第三方获取PowerPivot数据。他们并不是我们的客户，所以我们无法要求以更合适的格式提供原始数据等等。 - akavalar

明白了。DAX Studio能够针对打开的工作簿运行任意的DAX查询。您可以深入了解其内部或联系维护者Darren Gosbell，以获取更专业的帮助：https://daxstudio.codeplex.com/ - greggyb

感谢您的建议，@greggyb！我已经查看了DAX Studio，但由于PowerPivot / Vertipaq db引擎中没有行和行索引的概念，我认为没有办法将DAX查询限制为仅记录的子集（我已经做了大量研究，并且我认为这实际上值得一个全新的问题）。无论如何，通过使用VBA脚本将1m数据块输出到独立工作簿中（类似于此处建议的内容：https://dev59.com/BYLba4cB1Zd3GeqPj9Vz#33580647），可以绕过Excel的1m行限制。 - akavalar

@greggyb，谢谢你，但我不确定这个方法如何才能让我以编程方式循环遍历所有记录一次（因为我不能在每次循环中生成一个新的Rand列）。我可以在PowerPivot中手动创建一个计算列，保存修改后的文件，然后运行N个查询（无论是在DAX Studio还是在Excel本身中）。但这正是我想要以编程方式完成的：使用建议的AMO添加计算列，然后使用ADO/MD查询所有N个子集。我希望尽可能简化数据提取过程，以便我们的分析师（不精通计算机）可以使用它。 - akavalar

已经有一段时间了，但我刚刚意识到在使用PowerPivot模型时，基于行号进行筛选是可行的。当我查看SQL Server跟踪日志时，偶然发现了这一点。示例查询：SELECT * FROM [Model].[table] WHERE [Model].[table].[RowNumber]>=1000 AND [Model].[table].[RowNumber]<=2000。RowNumber似乎是一个隐藏的内部列。为了使其正常工作，必须将以下内容附加到连接字符串中：SQLQueryMode=DataKeys。这是我之前编写的用于提取PowerPivot数据的Excel“工具”：http://github.com/akavalar/ExtractPowerPivot - akavalar

Answer 1

哇，我终于成功地解决了这个问题——原来可以用Python访问Power Pivot数据！以下是我所做的简要回顾——您可以在此处找到更详细的描述：《花最少钱使用分析服务（SSAS）》(Analysis Services (SSAS) on a shoestring)。请注意：代码既没有被优化为效率，也没有被优化为优雅。

• 安装Microsoft Power BI Desktop（附带免费的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)

- akavalar

Answer 2

从PowerPivot中获取数据的问题在于，PowerPivot中的表格引擎在Excel内部运行，连接到该引擎的唯一方法是使您的代码也在Excel内部运行。(我怀疑它可能使用共享内存或其他传输方式，但绝对不是侦听TCP端口、命名管道或任何允许外部进程连接的东西)。

我们在Dax Studio中通过在Excel中运行C# VSTO Excel插件来实现这一点。然而，这只是为了测试分析查询而设计的，而不是用于批量数据提取。我们使用字符串变量将数据从插件传递到UI，因此整个数据集必须小于2GB，否则响应会被截断，并且您将看到一个“无法识别的响应”错误(数据被序列化为XMLA行集，这相当冗长，因此在提取几百MB的数据时可能会出现故障)。

如果您想构建一个脚本来自动提取模型中的所有原始数据，我认为您将无法使用Python，因为我不认为您可以在Excel内部运行Python解释器。我会考虑使用像这个VBA宏一样的vba宏：http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/ 您应该发现，您可以使用类似“SELECT * FROM $SYSTEM.DBSCHEMA_TABLES”的方式查询模型中的表格列表-然后您可以循环遍历每个表格并使用上面链接中代码的变体进行提取。

- Darren Gosbell

---

非常感谢您的输入，Darren！ - akavalar

Darren，你能否自动将数据子集分成块，然后将其传输到Dax Studio中，以避免处理大型PowerPivot模型时的2GB限制？比如每次提取100万行，最后再将所有内容组合在一起？ - akavalar

@akavalar - 可能有一种方法可以对数据进行分块处理，但这需要比我们目前拥有的XMLA协议更低级别的实现。而且，这对我们来说并不是一个真正的优先事项。如果有人需要详细的数据转储，最好从原始源系统获取，而不是从PowerPivot模型中提取。 - Darren Gosbell

Answer 3

我联系了Tom Gleeson（又名Gobán Saor），他很友善地允许我在这里发布他的电子邮件。其中有一些有趣的信息，希望其他人也能发现它们有用。 电子邮件＃1

当您提到Python时，是指作为独立exe运行Python.NET吗？ 如果是这种情况，您就无法使用Excel PP模型（Power BI桌面则不同）。 我已经成功地从VBA和Python.NET（通过AMO）访问了PP模型（2010+）， 使用类似于您在SO问题中的代码。不同之处在于（在VBA和.NET版本中）， 我的代码在Excel内部进程中运行，使用Excel的各种插件技术。（可能Tableau 也作为插件运行或嵌入Excel本身，从而实现类似的行为）。DAX Studio（一个有用的 C＃代码库，可以学习PP访问的操作方法）既可以作为Excel插件运行， 也可以作为独立的EXE运行，但只有作为插件时才能访问基于Excel的PP模型。

电子邮件＃2 您可能会发现使用Python.NET进行此操作有些具有挑战性。您需要使用C# / VB.NET Excel插件代码嵌入Python引擎。我以前使用过Excel-DNA（一个非常棒的开源项目），而不是微软官方开发此类.NET插件的高度繁琐的“官方”方法，但我尽可能地坚持使用VBA。

使用VBA，您将无法访问仅限于.NET的AMO（因此无法动态创建计算列），但通过将结果数据集加载到ADO记录集中，您应该能够输出到工作表或公司数据库/ MS Access或平面文件/ CSV等。

与1M工作表限制不同，对于平面文件或数据库输出，内存（RAM）将是限制因素，但是，假设您正在使用64位Excel并且具有足够的内存来保存压缩模型和最大模型表的工作区，在未压缩形式下的列格式（这将由DAX查询产生），乘以2ish（PP工作区内的一个实例，另一个在VBA的ADO工作区内），您应该没问题。

话虽如此，我从未尝试过提取非常大的数据集，并且使用模型作为数据集交换介质不是PP的“用例”之一；因此，非常大的表可能会遇到其他错误/约束！

- akavalar