programing

Python을 사용하여 PowerPivot 모델에서 원시 데이터 추출

lovejava 2023. 6. 8. 19:05

Python을 사용하여 PowerPivot 모델에서 원시 데이터 추출

Python을 사용하여 PowerPivot 모델의 일부 데이터를 읽어야 할 때 사소한 작업처럼 보였던 것이 실제 악몽으로 변했습니다.저는 지난 며칠 동안 이 문제에 대해 매우 잘 조사했다고 생각하지만, 지금은 벽에 부딪혔고 Python/SSAS/ADO 커뮤니티에서 도움을 주시면 감사하겠습니다.

: PowerPivot 모기델데프원저장이시로에방래다밍그식니액것입으제세아어이디이는로본스)를 사용하여 각합니다. 아래 나열된 방법 중 하나를 통해 기본 PowerPivot(즉, MS Analysis Services) 엔진에 연결하고 모델에 포함된 표를 나열한 다음 간단한 DAX 쿼리를 사용하여 각 테이블에서 원시 데이터를 추출하는 것입니다.EVALUATE (table_name)쉬엄쉬엄, 그렇죠?글쎄, 아닐 수도 있어요.

일부 배경 정보

보시다시피, 저는 몇 가지 다른 접근법을 시도했습니다.PowerPivot 기능을 처음 접하는 분들이 제가 무엇을 하고 싶은지 잘 알 수 있도록 가능한 한 신중하게 모든 것을 문서화하도록 노력하겠습니다.

먼저 Analysis Services 엔진에 대한 프로그래밍 방식 액세스에 대한 몇 가지 배경(2005 SQL Server로 표시되지만 모두 적용 가능해야 함): Analysis Services 연결에 사용되는 SQL Server 데이터 마이닝 프로그램 및 데이터 공급자.

아래 예제에서 사용할 Excel/PowerPivot 파일 샘플은 Microsoft PowerPivot for Excel 2010과 PowerPivot in Excel 2013 샘플입니다.

또한 Excel 2010을 사용하고 있으므로 일부 코드는 버전별로 다릅니다.예.wb.Connections["PowerPivot Data"].OLEDBConnection.ADOConnection야 .wb.Model.DataModelConnection.ModelConnection.ADOConnectionExcel 2013을 사용하는 경우.

이 질문에서 사용할 연결 문자열은 여기에 있는 정보(C#을 사용하여 PowerPivot 엔진에 연결)를 기반으로 합니다.또한 일부 방법은 데이터를 검색하기 전에 PowerPivot 모델을 초기화해야 합니다.다음을 참조:VBA에서 PowerPivot 새로 고침 작업을 자동화하는 중입니다.

마지막으로, 이를 달성할 수 있어야 한다는 것을 보여주는 몇 가지 링크가 있습니다(그러나 이러한 링크는 주로 Python이 아닌 C#을 참조합니다).

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.

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 모델에 연결할 수 있는 잠재적인 방법 중 하나로 포함했습니다.

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()

이것은 파이썬 또는 아이언피톤으로 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.

dodbapi 모듈을 통한 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과 동일한 문제입니다.

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: VBA를 사용하여 Power Pivot에서 CSV로 테이블 또는 DAX 쿼리를 내보내는 블로그 게시물에서 비롯되었습니다.이 접근 방식은 모델(예: "서버")을 초기화하는 명시적인 새로 고침 명령을 사용합니다.다음은 오류 메시지입니다.

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의 창설에 있는 것 같습니다.레코드 집합 개체입니다.

Excel/win32com을 통한 ADO 사용, ADODB 직접 사용.연결

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)

Win32 플랫폼(Python recipe)에서 ADO를 사용한 Python에서 액세스할 수 있는 Connection to Access 및 Query 액세스와 유사합니다.안타깝게도 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)

Excel/win32com을 통한 ADO 사용, ADODB 직접 사용.연결 및 모델 새로 고침

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 모델을 초기화하고 내장된 PowerPivot 데이터에 Excel이 사용하는 내부 연결 문자열을 사용하여 연결을 만들 수 있기를 희망했습니다(PowerPivot 데이터를 Excel 워크북에 표로 복사하는 방법과 유사).연결 문자열이 다른 곳에서 사용한 것과 다릅니다.불행히도, 이것은 작동하지 않고 제 추측으로는 파이썬이 ADODB를 시작하는 것 같습니다.별도의 인스턴스에서 연결 프로세스(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)

마침내 문제를 해결했습니다. 파이썬을 사용하여 Power Pivot 데이터에 액세스하는 것이 실제로 가능하다는 것이 밝혀졌습니다!다음은 제가 수행한 작업에 대한 간략한 요약입니다. 보다 자세한 설명은 여기에서 확인할 수 있습니다. 바로 분석 서비스(SSAS)입니다.참고: 코드는 효율성이나 우아함을 위해 최적화되지 않았습니다.

  • Microsoft Power BI Desktop 설치(무료 Analysis Services 서버와 함께 제공되므로 고가의 SQL Server 라이센스가 필요하지 않습니다. 하지만 적절한 라이센스가 있는 경우에도 동일한 접근 방식이 적용됩니다.)
  • 먼저 msmdsrv.ini 설정 파일을 생성하여 AS 엔진을 시작한 다음 AMO.NET을 사용하여 ABF 파일에서 데이터베이스를 복원한 다음 ADOMD.NET을 사용하여 데이터를 추출합니다.

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)

PowerPivot에서 데이터를 가져올 때의 문제는 PowerPivot의 표 형식 엔진이 Excel 내부에서 진행 중이며 해당 엔진에 연결하는 유일한 방법은 코드를 Excel 내부에서도 실행하는 것입니다.(공유 메모리나 다른 전송을 사용할 수도 있지만, TCP 포트나 명명된 파이프 등에서 외부 프로세스를 연결할 수 있는 수신 대기 기능이 전혀 없습니다.)

Dax Studio에서는 Excel에서 C# VSTO Excel 애드인을 실행하여 이 작업을 수행합니다.그러나 이는 대량 데이터 추출이 아닌 분석 쿼리 테스트에만 사용할 수 있도록 설계되었습니다.문자열 변수를 사용하여 추가 기능에서 UI로 데이터를 마샬링하므로 전체 데이터 세트가 2Gb 미만이어야 합니다. 그렇지 않으면 응답이 잘리고 "인식할 수 없는 응답" 오류가 표시됩니다(데이터가 XMLA 행 집합으로 직렬화되어 있어 수백Mb의 데이터만 추출할 때 데이터가 손상될 수 있음).

만약 당신이 모델에서 모든 원시 데이터를 자동으로 추출하는 스크립트를 만들고 싶다면, 나는 당신이 엑셀에서 파이썬 인터프리터를 실행할 수 있다고 생각하지 않기 때문에 파이썬으로 그것을 할 수 없을 것이라고 생각합니다.저는 이와 같은 vba 매크로를 사용할 것입니다. http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/

모델에 "SELECT * FROM $SYSTEM.DBSCHEMA_TABLES"와 같은 테이블 목록을 쿼리할 수 있습니다. 그러면 각 테이블을 루프하여 위 링크의 코드 변형으로 추출할 수 있습니다.

저는 톰 글리슨(일명 고반 사오르)과 연락을 취했는데, 그는 제가 그의 이메일을 여기에 올릴 수 있도록 친절했습니다.그 안에는 흥미로운 너겟들이 몇 개 들어 있어서, 다른 사람들도 그것들이 유용하다는 것을 알게 되기를 바랍니다.

이메일 #1

Python이라고 하면 Python.NET을 독립 실행형 exe로 실행하는 것을 의미합니까?그렇다면 Excel PP 모델은 운이 좋지 않습니다(그러나 Power BI 데스크톱의 경우는 다릅니다).저는 VBA와 Python 모두에서 PP 모델(2010+)에 성공적으로 액세스했습니다.SO 질문과 유사한 코드를 사용하는 NET(AMO 경유).차이는 (VBA 및 둘 다에서)입니다.NET version)은 엑셀의 다양한 애드인 기술을 사용하여 엑셀 내에서 내 코드가 진행 중인 것입니다.(Tableau도 애드인으로 실행 중이거나 Excel을 내장하여 유사한 동작을 지원하고 있습니다.)DAX Studio(PP 액세스 방법을 배우는 데 유용한 C# 코드 기반)는 Excel 추가 기능 및 독립 실행형 EXE로 실행되지만, 추가 기능으로만 Excel 기반 PP 모델에 액세스할 수 있습니다.

이메일 #2

이를 위해 Python.NET을 사용하는 프로세스는 다소 어려울 수 있습니다.C#/VB.NET Excel 추가 코드를 사용하여 Python 엔진을 내장해야 합니다.저는 그런 개발을 위해 MS의 매우 번거로운 "공식적인" 방법 대신 Excel-DNA(환상적인 오픈 소스 프로젝트)를 사용했습니다.NET은 과거에 중독되었지만, 저는 가능한 한 주로 VBA를 고수합니다.

VBA를 사용하면 에 액세스할 수 없습니다.NET 전용 AMO(따라서 계산된 열을 즉시 생성할 수 없음)이지만 결과 데이터 세트를 ADO 레코드 세트에 로드하면 워크시트나 회사 데이터베이스/MS 액세스 또는 플랫 파일/CSV 등에 출력할 수 있습니다.

1M 워크시트 제한과 달리 플랫 파일 또는 데이터베이스 출력 메모리(RAM)가 제한 요인이 되지만 64비트 Excel을 사용하고 있고 압축된 모델과 가장 큰 모델 테이블의 작업 공간(즉, 압축되지 않은 형태)을 보관할 수 있는 충분한 메모리가 있다고 가정할 경우.DAX 쿼리에서 생성되는 열 기반 형식이 아닌 행 기반 형식으로, PP 작업 공간 내의 한 인스턴스에 2ish를 곱하면 됩니다(VBA의 ADO 작업 공간 내의 다른 인스턴스).

그렇긴 하지만, 저는 매우 큰 데이터 세트를 추출하려고 시도한 적이 없으며 모델을 데이터 세트 교환 매체로 사용하는 것은 PP의 "사용 사례" 중 하나가 아닙니다. 따라서 매우 큰 테이블은 다른 버그/제약 조건에 부딪힐 수 있습니다!

언급URL : https://stackoverflow.com/questions/34846090/extracting-raw-data-from-a-powerpivot-model-using-python

'programing' 카테고리의 다른 글

행렬도:lib getylim 값  (0) 2023.06.08
wordpress 포스트 유효성 검사 추가  (0) 2023.06.08
iOS: 텍스트 길이에 따른 UIButton 크기 조정  (0) 2023.06.08
Capybara를 사용하여 드롭다운에서 옵션을 선택하는 방법  (0) 2023.06.03
여러 캐치 블록을 사용한 VB.NET 캐치 시도  (0) 2023.06.03

'programing'의 다른글

  • 현재글Python을 사용하여 PowerPivot 모델에서 원시 데이터 추출

관련글

댓글

티스토리툴바