PowerBI Parameterize Connection Variables in Power Query
-
I'm trying to get used to Power BI Desktop, and seem to need a little boost either getting started, or understanding the limitations.
II want to build a dashboard, and what better way to get started than have a dashboard bubble up info I am familiar with, right?
But Right away, since I manage multiple servers, I need to be able to change the server name from a drop down menu, and have the queries refresh.
If I could get just one dang query to do that, i could run with the ball from there.
I found an article that kind of shows how to get a connection to use a variable, but it's not working for me so far. I get confused about why i HAVE to use excel, instead of a query or static list, and the half dozen iterations I've tried never worked. I get killed in DAX/BI syntax.I want a query available that shows me all my servers from central Management Server, easy enough right?
SELECT DISTINCT name,server_name FROM msdb.[dbo].[sysmanagement_shared_registered_servers]So i can get that into a PowerBI Desktop data, but i'm not seeing how to get soemthing to assign to a variable, so that i could then use that variable to modify a connection string.
so say i wanted to just run the most basic query, something like this:SELECT
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('Edition') AS Edition,
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_kb/1024 As MemoryinMb,
s.virtual_machine_type_desc,
CASE WHEN s.virtual_machine_type > 0 THEN 'Virtual' ELSE '' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info sif i got that to work, i could then use the same logic on other queries, and have dozens of other objects in a dashboard that pulls various details a DBA would care about.
there is a brief article on Power BI Tutorial: How to Parameterize Connection Variables in Power Query at
https://businessintelligist.com/2015/05/12/power-bi-tutorial-how-to-parameterize-connection-variables-in-power-query/, but i need a better leg up, than that two year old article.
Has anyone ever done what I'm asking? can you get a drop down selection to assign a value to a variable, so that connection strings would refresh?Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Seems like you'll have to use that Excel method. Perhaps you can make the leftmost cell in the "parameter table" a drop down with all your servers as drop-down values?
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply