Power BI Desktop – eksport modelu do csv przy pomocy Powershell

Prowadząc różnego rodzaju szkolenia i treningi często słyszę pytanie o to czy jest możliwość eksportu danych z Power BI Desktop do Excela lub plików csv. Dotyczy to zarówno danych zaimportowanych z różnych źródeł jak i tych wprowadzonych bezpośrednio poprzez “Enter Data”. Jak dobrze wiemy natywnie coś takiego możemy osiągnąć poprzez zbudowanie tabeli na warstwie wizualizacji i i wyeksportować używając dostępnych przy tym visualu opcji:

Funkcjonalność ta jest jednak mało wygodna bo musimy przeklikać się przez wszystkie tabele, a do tego posiada limity w postaci 30 tysięcy możliwych do wyeksportowania wierszy do csv i 150 tysięcy przy eksporcie do Excela:

Możemy to niejako obejść poprzez użycie Powershell. Jak to wszystko zrobić? Postaram się to zaprezentować w niniejszym artykule, serdecznie zapraszam.

Nie owijając w bawełnę przełączmy się do Power BI Desktop do którego pobrałem dane pochodzące z AdventureWorksDW. Cały model przedstawia się w następujący sposób:

Samego procesu pobierania nie będę opisywał bo jest on na tyle prosty, że z całą pewnością czytelnicy niniejszego artykułu poradzą sobie z tym zadaniem sami. Po pobraniu danych nie pozostaje nam nic innego jak spróbować podłączyć się do lokalnej instancji Analysis Services, która jest częścią Power BI Desktop i na której znajduje się baza danych będąca naszym modelem danych. Na ten moment wiemy, że instancja ta jest zlokalizowana na naszym lokalnym komputerze (localhost). Jedyne co musimy zrobić to odnaleźć port na którym ona działa. Możemy tego dokonać na kilka różnych sposobów my jednak zostaniemy wewnątrz Powershell i spróbujemy się tam dostać właśnie w ten sposób.

To czego szukamy do proces o nazwie msmdsrv, a jesteśmy w stanie go odnaleźć używając komendy Get-Process tak jak zostało to przedstawione poniżej. Całość zapiszemy w zmiennej o nazwie $Process aby w dalszej części budowanego skryptu móc się do tego odwołać ( pamiętajmy, że podczas wykonywania/budowania przedstawianych komend Power BI Desktop musi być włączony):

$Process=Get-Process|Where-Object -Property Name -EQ "msmdsrv"

 

Mając już zlokalizowany proces możemy spróbować odnaleźć Port, a zrobimy to używając Get-NetTCPConnection:

$LocalPort = Get-NetTCPConnection | Where-Object {($_.OwningProcess -EQ $Process.Id)} |Select-Object -Property LocalPort -First 1

Komenda ta zwraca oczywiście mnóstwo użytecznych informacji jednakże nas interesuje w tym przypadku jedynie właściwość LocalPort. Wcześniej przefiltrowaliśmy rezultat w taki sposób aby pokazywać jedynie te połączenia, których właścicielem jest znaleziony wcześniej proces zapisany w zmiennej $Process. Dodałem również filtrację -First 1 po to aby nieco uprościć i podłączyć się do pierwszego znalezionego portu (tak więc powinniśmy mieć otwartą tylko jedną instancję PBI Desktop).

Mając już wszystkie niezbędne informacje możemy przejść do zbudowania łańcucha połączeniowego (Connection String). Definicja ta jest dosyć prosta i przedstawia się w następujący sposób:

$asServerAddress="localhost:"+$localPort.LocalPort

$connectionString = “Provider=MSOLAP;Data Source="+$asServerAddress

W dalszej części tworzymy obiekt typu Tabular.Server i wywołujemy metodę Connect aby nawiązać połączenie. Jeśli wszystko przebiegło poprawnie powinniśmy być w stanie wylistować  jedyną dostępną bazę danych o identyfikatorze 0 i zapisać w zmiennej o nazwie $db:

$as = New-Object Microsoft.AnalysisServices.Tabular.Server;
$as.Connect($asServerAddress);

$db= $as.Databases[0]

Mając już gotowe połączenie do bazy możemy przejrzeć każdą tabelę, a zrobimy to przy pomocy pętli foreach. Dodatkowo musimy obsłużyć dwa typy tabel które są obsługiwane wewnętrznie przez Power BI Desktop czyli LocalDate oraz Template, które po prostu będziemy pomijać w naszej analizie:

foreach($t in $db.Model.Tables) {

if ($t.Name -like "LocalDate*" -or $t.Name -like "*Template*") 
{
}
else
{

}
}

Musimy teraz w jakiś sposób odpytać interesujące nas tabele czyli technicznie rzecz biorąc uzupełnić w powyższym zapisie sekcję else. W pierwszej części zdefiniujemy sobie zapytanie, które będziemy wykonywać dla każdej z tabel. Będzie to nic innego jak EVALUATE tabela czyli pobranie wszystkich wierszy z tabeli – zapytanie to będzie przechowywane w zmiennej $query aby móc go wywołać w odpowiednim momencie.  Dalej mamy tworzenie nazwy pliku która będzie miała następujący schemat: NazwaTabeli_NazwaServera_DataCzasEksportu.csv – kod powershell przeznaczony do tego celu przedstawia się w następujący sposób:

   $query = "EVALUATE '"+ $t.Name +"'" 
    $GetDate = Get-Date -Format "yyyyMMddmmhhss".ToString()
    $filename = $t.Name+"_"+ $as.ConnectionString+"_"+$GetDate+".csv"
    $filename=$filename.Replace(":","")

Zbliżamy się powoli do końca.  Tworzymy sobie nowe połączenie jako obiekt OleDBConnection, a następnie przy jego pomocy komendę której tekst zdefiniowaliśmy wcześniej jako EVALUATE. Po zdefiniowaniu wszystkich potrzebnych obiektów komenda ta jest wywoływana i otrzymany rezultat uzupełnia obiekt typu DataSet jest przy pomocy obiektu OleDBDataAdapter i jego metody Fill. Dla przejrzystości dodałem również komunikat do wyświetlenia o treści “Number of rows in Tabela”:

    $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
    $dataset = New-Object -TypeName System.Data.DataSet

   "Number of rows in "+$t.Name+": "
    $adapter.Fill($dataset)

Ostatnim elementem, o który musimy zadbać jest eksport do pliku csv co możemy bardzo prosto osiągnąć  komendą export-csv. Na koniec zamykamy połączenie:

$dataset.Tables[0] | export-csv $filename -notypeinformation -Delimiter ","
$connection.Close()

Całość kodu przedstawia się w następujący sposób:

$Process=Get-Process|Where-Object -Property Name -EQ "msmdsrv"

$LocalPort = Get-NetTCPConnection | Where-Object {($_.OwningProcess -EQ $Process.Id)} |Select-Object -Property LocalPort -First 1  #-and ($_.LocalAddress -EQ "127.0.0.1")} 


$asServerAddress="localhost:"+$localPort.LocalPort

$connectionString = "Provider=MSOLAP;Data Source="+$asServerAddress
    


$as = New-Object Microsoft.AnalysisServices.Tabular.Server;
$as.Connect($asServerAddress);

$db= $as.Databases[0]

foreach($t in $db.Model.Tables) {

if ($t.Name -like "LocalDate*" -or $t.Name -like "*Template*") 
{
}
else
{
    $query = "EVALUATE '"+ $t.Name +"'" 

    
    $GetDate = Get-Date -Format "yyyyMMddmmhhss".ToString()
    $filename = $t.Name+"_"+ $as.ConnectionString+"_"+$GetDate+".csv"
    
    $filename=$filename.Replace(":","")

    $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
  
    $command.CommandText = $query
    
  
    $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
    $dataset = New-Object -TypeName System.Data.DataSet

    "Number of rows in "+$t.Name+": "
    $adapter.Fill($dataset)

    $dataset.Tables[0] | export-csv $filename -notypeinformation -Delimiter ","
      
    $connection.Close()

}
}

Efekt działania stworzonego przez nas skryptu jest taki, że w oknie otrzymamy liczbę wyeksportowanych wierszy:

Następstwem tego działania są również pliki CSV umieszczone w bieżącym folderze. Powinniśmy zobaczyć tyle plików ile mieliśmy tabel w modelu do którego się podłączyliśmy:

Wygląda całkiem dobrze prawda? Oczywiście powstałe pliki są pełnoprawnymi plikami CSV, które standardowo można otworzyć/zaimportować do Excela czy też gdziekolwiek indziej. Jedyna rzecz na jaką warto zwrócić uwagę to nagłówki, które mają DAXową formę tabela[kolumna] ale chyba nie jest to większy problem:

Mam nadzieję, że zaprezentowany przeze mnie sposób na eksport danych z Power BI Desktop Wam się spodobał. Oczywiście w analogiczny sposób można wyciągnąć dane z bazy Analysis Services. Jedyne co będzie się różnić to wyszukiwanie portu na którym działa instancja bo to akurat ta sekcja nie jest potrzebna. Na ten moment to było na tyle – dzięki za poświęcony czas.