Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vk_pbi
Resolver II
Resolver II

PowerShell Script to export Database Connection strings in XMLA

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

10 REPLIES 10
lbendlin
Super User
Super User

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

R1k91
Continued Contributor
Continued Contributor

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors