Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.