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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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