Uso di PowerShell per creare gruppi di calcolo in Power BI Desktop - Phil Seamark in DAX (2023)

Sfondo

IOrecentemente condiviso su Twitterun percorso di apprendimento di 7 moduli su MS Learn che insegna tutto ciò che devi sapere sui gruppi di calcolo in Power BI. Questo percorso di apprendimento è un corso eccellente e lo consiglio vivamente. Tuttavia, come parte di questo scambio, ho ricevuto una risposta da qualcuno che si lamentava del fatto che i gruppi di calcolo sono inutilizzabili nelle organizzazioni che non consentono applicazioni non Microsoft. Power BI Desktop attualmente non dispone di UX che consente di creare/gestire gruppi di calcolo in un modello Power BI, pertanto il metodo più comune oggi consiste nell'utilizzare strumenti di terze parti come l'editor tabulare.

Questo scambio non è la prima volta che sento questo feedback, quindi ho deciso di condividere una tecnica che mostra come tePotereutilizzare gli strumenti Microsoft. L'approccio usato in questo articolo usa PowerShell ma può essere rapidamente tradotto in VS Code o altri ambienti di scripting.

La tecnica

Per utilizzare PowerShell per creare/gestire gruppi di calcolo, è necessario quanto segue:

• Un file DLL archiviato localmente nel computer a cui fare riferimento per il modello a oggetti tabulare
• Report di Power BI aperto in Power BI Desktop.
• Script di PowerShell

Individua il file DLL

Il primo elemento è una DLL archiviata nel computer che deve essere usata da PowerShell per lavorare con Power BI Desktop. Questo file è creato e di proprietà di Microsoft ed è progettato per consentire agli strumenti di funzionare con i modelli Power BI. Il nome del file DLLMicrosoft.AnalysisServices.Tabular.dllpotrebbe già essere sul tuo locale se lo haiSSMS installato.

Se non hai SSMS installato sul tuo computer locale o qualsiasi altro strumento Microsoft che installa il fileMicrosoft.AnalysisServices.Tabular.dllfile da qualche parte, puoi prenderlo daNugetcercando Microsoft.AnalysisServices escaricando l'ultima versionedelMicrosoft.AnalysisServices.retail.amd64pacchetto. Il pacchetto dovrebbe avere una dimensione di circa 5 Mb e scaricherà un file con estensione .nupkg. Rinomina il file scaricato in un'estensione del nome file .Zip in modo che possa essere aperto utilizzando il tuo strumento zip preferito. ILMicrosoft.AnalysisServices.Tabular.dllpuò essere trovato nella sottocartella \lib\net45, dove può essere copiato in una posizione da qualche parte sul tuo computer locale. Prendi nota della posizione utilizzata come destinazione del file in modo da poter aggiornare lo script di PowerShell.

Apri il modello di Power BI

Affinché PowerShell si connetta a Power BI Desktop, è necessario comprendere quale porta Power BI Desktop ha aperto per le chiamate API usate nel nostro script PowerShell. Power BI Desktop usa una porta diversa ogni volta che viene aperto, quindi questo valore non può essere codificato nello script di PowerShell. Lo script di PowerShell ha un paio di righe di codice per derivare questo valore dai processi del sistema operativo. Per semplicità, lo script di PowerShell è semplice e funziona solo finché è aperta una sola copia di Power BI Desktop.

Più istanze di Power BI Desktop confonderanno la versione dello script di PowerShell usato in questo blog. Lo script di PowerShell può essere modificato per supportare più istanze di Power BI Desktop, ma per ora ho deciso di lasciarlo fuori dall'ambito.

Lo script di PowerShell

Apri il seguente script inPower Shell ISE(ambiente di scripting integrato)

#################################################### ################################################ 1. Creare riferimento alla DLL di Analysis Services nel percorso SSMS############################################### #################################################### # # Approccio 1 # Usa la DLL nel GAC se hai già SSM o un altro strumento installato <# try { $Result = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.AnalysisServices.Tabular') } catch { $_.Exception.LoaderExceptions } #> # Approach 2 # Crea un riferimento direttamente a un file chiamato Microsoft.AnalysisServices.Tabular.Dll già installato da SSMS <# #$assemblypath = "c:\program files (x86)\microsoft sql server management studio 18\common7\ide\microsoft.analysisservices.Tabular.dll" try {add-type -path $assemblypath} catch { $_.exception.loaderexceptions } #> # Approach 3 # Scarica ed estrai la DLL da Nuget a da qualche parte in locale sulla tua macchina $assemblypath = "c:\temp\microsoft.analysisservices.Tabular.dll" try {add-type -path $assemblypath} catch { $_.exception.loaderexceptions }########## #################################################### ################################################################################################## #################################################### ######################################## $ProcessName = 'msmdsrv' try { $Process = Get-Process -Name $ProcessName -ea Stop -ErrorVariable 'ps_error' } catch { throw "Avviso: impossibile trovare alcun processo denominato: '$ProcessName'.`nAssicurarsi che Power BI sia aperto." } $Result = Get-NetTCPConnection -OwningProcess $Process.Id $Port = $Result | Select-Object -ExpandProperty LocalPort | Sort-Object -Unique $ConnectionStringPBIDesktop = "localhost:$Port"###################################### #################################################### ########## 3. Creare un oggetto AS Server e connettersi a Power BI ############################## #################################################### ############## $Server = [Microsoft.AnalysisServices.Tabular.Server]::new() $Server.Connect($ConnectionStringPBIDesktop) $Model = $Server.Databases[0] .Model <# foreach($table in $Model.Tables) { $table.Name } #>############################ #################################################### ################## 4. Rimuovi gruppo di calcolo esistente############################ #################################################### ################## $result = $Model.Tables.Remove($Model.Tables["Time Calc"])############ #################################################### #################################### 5. Crea gruppo di calcolo########## #################################################### #################################### $CalculationGroup = [Microsoft.AnalysisServices.Tabular.CalculationGroup]::new( ) $CalculationGroup.Description="Nessuno" $CalculationGroup.Precedence=10##################################### #################################################### ########## 6. Crea elemento di calcolo#################################### #################################################### ########## $CalculationItem = [Microsoft.AnalysisServices.Tabular.CalculationItem]::new() $CalculationItem.Description = "YTD" $CalculationItem.Name = "YTD" $CalculationItem.Expression = "CALCULATE (SELECTEDMEASURE(),DATESYTD(DimDate[FullDateAlternateKey]))" $CalculationItem.Ordinal = 0 $CalculationGroup.CalculationItems.Add($CalculationItem)#################### #################################################### ############################ 7. Crea elemento di calcolo 2################## #################################################### ############################ $CalculationItem = [Microsoft.AnalysisServices.Tabular.CalculationItem]::new() $CalculationItem.Description = "MTD " $CalculationItem.Name = "MTD" $CalculationItem.Expression = "CALCULATE(SELECTEDMEASURE(),DATESMTD(DimDate[FullDateAlternateKey]))" $CalculationItem.Ordinal = 0 $CalculationGroup.CalculationItems.Add($CalculationItem)#### #################################################### ############################################ 8. Crea tabella#### #################################################### ########################################## $Table = [Microsoft.AnalysisServices.Tabular. Tabella]::new() $Table.Name = "Time Calc" $Table.CalculationGroup = $CalculationGroup############################ #################################################### ################## 9. Crea colonna tabella############################ #################################################### ################## $TableColumn = [Microsoft.AnalysisServices.Tabular.DataColumn]::new() $TableColumn.Name = "Calcoli del tempo" $TableColumn.DataType = " Stringa" $result = $Table.Columns.Add($TableColumn)###################################### #################################################### ########## 10. Crea partizione###################################### #################################################### ######### $Partition = [Microsoft.AnalysisServices.Tabular.Partition]::new() $Partition.Name = "Partition For Time Calc" $Partition.Source =[Microsoft.AnalysisServices.Tabular.CalculationGroupSource ]::new() $result = $Table.Partitions.Add($Partition)################################## #################################################### ############## 11. Aggiungi tabella al modello################################ #################################################### ############## $result = $Model.Tables.Add($Table) ###################### #################################################### ########################## 12. Confermare tutte le modifiche al modello################## #################################################### ############################ $result = $Model.DiscourageImplicitMeasures=[bool] 1 $result = $Model.SaveChanges()### #################################################### ############################################################ #################################################### ##########################################

Lo script di PowerShell è suddiviso in diversi passaggi con blocchi di commenti che descrivono lo scopo di ciascuna sezione.

Le prime due sono le sezioni più critiche, progettate per connettere lo script a un'istanza di Power BI Desktop. Una volta connesso, il resto della logica dovrebbe essere abbastanza facile da seguire. Oggetti come CalculationGroups, CalculationItems, una tabella e una partizione devono essere nominati per adattarsi e aggiunti al modello (cosa che lo script fa per te).

Lo script può essere eseguito più volte senza interruzioni, consentendo di ripetere lo script apportando modifiche ai nomi degli oggetti, alle espressioni DAX e così via. Ogni volta che si esegue lo script in PowerShell, sarà necessario passare a Power BI Desktop e fare clic su un pulsante per aggiornare la struttura del modello per caricare le modifiche.

Commento alla sceneggiatura

Una spiegazione più dettagliata di ogni fase dello script è la seguente.

1. Creare un riferimento alla DLL di Analysis Services nel percorso SSMS
   
   • Ci sono tre approcci mostrati in questa sezione per fare riferimento aMicrosoft.AnalysisServices.Tabular.dll. Solo uno dei tre approcci dovrebbe essere decommentato qui.
   • La mia preferenza personale è Approach 1, che utilizza la notazione [System.Reflection.Assembly] per individuare il file DLL nella GAC ​​(cartella c:\windows\Microsoft.Net\assembly\GAC_MSIL). Ma questo si basa sul fatto che un'applicazione come SSMS installi la DLL nel GAC durante un processo di installazione.
   • L'approccio Nuget funziona abbastanza bene e non si basa sulla necessità di privilegi di amministratore per lavorare se non riesci a trovare ilMicrosoft.AnalysisServices.Tabular.dllfile ovunque sulla tua macchina.
   • Se hai una copia diMicrosoft.AnalysisServices.Tabular.dllsulla tua macchina, deve essere abbastanza recente da includere la conoscenza di cosa sono i gruppi di calcolo.
     
2. Ottieni il numero di porta di Power BI Desktop
   • Il codice in questo blocco tenta di identificare la porta di ascolto dell'istanza di Power BI Desktop. In questo modulo funziona solo se è aperta una sola copia di Power BI Desktop.
   • La logica cerca un processo denominato msmdsrv, che risulta essere il motore di Analysis Services avviato da Power BI Desktop. Una volta ottenuto l'ID processo, può cercare il numero di porta tramite il commandlet Get-NetTCPConnection.
   • La stringa di connessione finale sarà simile a "localhost:50555" e verrà utilizzata nel passaggio tre per connettersi.
     
3. Crea un oggetto AS Server e connettiti a Power BI
   • Questo blocco di codice tenta di connettersi all'istanza di Power BI Desktop. Se la connessione riesce, il resto è facile.
   • Una sezione commentata del codice PowerShell mostra come è possibile eseguire l'iterazione di ogni tabella nel modello e visualizzare un elenco di nomi di tabella sullo schermo.
     
4. Rimuovi gruppo di calcolo esistente
   • La singola riga in questo blocco rimuove qualsiasi gruppo di calcolo esistente nel modello chiamato "Time Calc".
   • Non importa se lo esegui per la prima volta e non esiste un gruppo di calcolo con quel nome.
     
5. Crea gruppo di calcolo
   • Qui usiamo la sintassi PowerShell [object]::new() per costruire un nuovo oggetto Calculation Group.
   • In questo esempio vengono impostate solo due proprietà, ma è possibile impostare proprietà aggiuntive in base alle esigenze.
   • Questo codice non aggiunge ancora il gruppo di calcolo al modello. Quel lavoro è completato più avanti nella sceneggiatura.
     
6. Crea elemento di calcolo
   • Qui viene creato un nuovo elemento di calcolo utilizzando il metodo [object]::new().
   • Una volta create, le proprietà possono essere impostate, come Nome ed Espressione.
   • L'espressione deve essere una sintassi DAX valida.
   • L'ultima riga in questo blocco aggiunge il CalculationItem appena creato all'oggetto CalculationGroup creato nel passaggio 5.
     
7. Crea elemento di calcolo 2
   • Questo passaggio mostra come aggiungere altri elementi di calcolo utilizzando un approccio simile al passaggio 6
   • Assicurati di scegliere un valore diverso per la proprietà Name.
     
8. Crea tabella
   • Prima di aggiungere un gruppo di calcolo al nostro modello, dobbiamo creare un nuovo oggetto tabella per ospitare il gruppo di calcolo.
   • Il valore utilizzato per la proprietà Name qui deve corrispondere allo stesso valore utilizzato in "rimuovi gruppo di calcolo esistente" nel passaggio 4.
   • L'ultima riga di codice in questo blocco è dove il nostro gruppo di calcolo creato nel passaggio 5 viene aggiunto al modello.
     
9. Crea colonna della tabella
   • L'oggetto Table creato nel passaggio 8 utilizzato per ospitare il nostro gruppo di calcolo deve avere una colonna.
   • Un nuovo oggetto colonna viene creato qui e aggiunto all'oggetto tabella creato nel passaggio 8
   • Questo blocco non deve essere modificato.
     
10. Crea partizione
    • Anche l'oggetto Table creato nel passaggio 8 deve avere una partizione, quindi questo codice esegue la quantità minima richiesta per creare la partizione necessaria richiesta.
    • L'ultima riga di codice aggiunge la partizione alla tabella.
    • Questo blocco non deve essere modificato.
      
11. Aggiungi tabella al modello
    • La singola riga di codice qui aggiunge l'oggetto Table creato nel passaggio 8 al modello.
    • Questo passaggio aggiunge anche Table Column, Partition, CalculationGroup e qualsiasi CalcuationItems al modello.
    • Questo blocco non deve essere modificato.
      
12. Eseguire il commit di tutte le modifiche al modello
    • Questo blocco infine esegue il commit di tutte le modifiche al modello.
    • Una finestra di dialogo in Power BI Desktop mostrerà che sono state apportate modifiche e richiederà di fare clic su un pulsante per aggiornare la struttura del modello (non equivale a un aggiornamento dei dati!)

E questo è tutto. Si spera che lo schema sia abbastanza facile da seguire e, una volta impostato, non dovrebbe essere necessario molto più tempo per creare un gruppo di calcolo rispetto all'utilizzo di uno strumento di terze parti.

Riepilogo

Si spera che questo modello sia utile se non è possibile installare/utilizzare strumenti non Microsoft come l'editor tabulare per lavorare con i gruppi di calcolo. La tecnica non si limita solo all'aggiunta di gruppi di calcolo a Power BI Desktop e può essere utilizzata per eseguire tutti i tipi di attività tramite il modello a oggetti tabulare, come la creazione di misure, la documentazione di modelli, l'esecuzione di query sui dati e così via.

PowerShell è uno strumento eccellente per automatizzare le attività e funziona bene se il modello viene ospitato in un'area di lavoro Power BI Premium.

La tecnica può essere migliorata in molti modi diversi. Lo script può essere eseguito come strumento esterno da Power BI Desktop. L'esecuzione come strumento esterno significa che il passaggio 2 potrebbe essere semplificato.

Grazie a Paul su Twitter per avermi dato l'idea e a Kasper perfornendo l'esempio c#Ho eseguito il porting su PowerShell per questo articolo.