Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
How is this question related to Power BI? I assume you are aware of the Power BI REST API?
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
Run two DMV queries.
select * from $SYSTEM.TMSCHEMA_PARTITIONS
select * from $SYSTEM.TMSCHEMA_EXPRESSIONS
How can i run these queries ?
You can run them from any xmla client tool, including Powershell.
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
Hi @lbendlin
I tried your code, but it throws error like System Cube not available. I am accessing this command on Onprem Report server.
Not sure about the syntax there but it should work the same if you properly specify server and cube
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
Thanks for your suggestion, but currently I can't use Tabular Editor due to some limitations.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
50 | |
31 | |
26 | |
26 | |
25 |
User | Count |
---|---|
60 | |
49 | |
29 | |
24 | |
23 |