PowerShell Script to export Database Connection strings in XMLA

[vk_pbi]

Hi there,

I am working on a automation project, where i have a requirement to update the connection strings and credentials via powershell.

The process flow would likely to be 

>Connect SQL Server Analysis Services

>Select an Database

>Export the connection details into a xmla file.

Currently we are doing it manually via SSMS

> Opening SSMS --> Connect to SQL Server Analysis Services

>Select required Database

>Expand Connections folder --> Select and Right click a connection

>Right Click --> Script Connection as --> CREATE OR REPLACE To --> New Query Editor Window

This will generate a xmla scirpt of that connection. I want to achieve this process via powershell.

Thanks

[lbendlin]

How is this question related to Power BI?  I assume you are aware of the Power BI REST API?

[vk_pbi]

Its not directly related.. Yes I am aware of Power BI Rest Api, but it doesn't work with Onprem report server.

So I need to achieve this without using webservice

[lbendlin]

Run two DMV queries. 

select * from $SYSTEM.TMSCHEMA_PARTITIONS

select * from $SYSTEM.TMSCHEMA_EXPRESSIONS

[vk_pbi]

How can i run these queries ?

[lbendlin]

You can run them from any xmla client tool, including Powershell.

[lbendlin]

Invoke-ASCmd -Server "powerbi://api.powerbi.com/v1.0/myorg/<workspace>" -Database "<dataset>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\<user>\Downloads\dmv.xml

[vk_pbi]

Hi @lbendlin 

I tried your code, but it throws error like System Cube not available. I am accessing this command on Onprem Report server.

[lbendlin]

Not sure about the syntax there but it should work the same if you properly specify server and cube

[R1k91]

If powershell is not a requirement maybe you could think to use Tabular Editor and a combiantion of Advanced Scripting and Command Line interface.

I've not tested with your usecase but I did something similar in the past: https://medium.com/p/49e8f4a236f9

[vk_pbi]

Thanks for your suggestion, but currently I can't use Tabular Editor due to some limitations.