I'm trying to get used to Power BI Desktop, and seem to need a little boost either getting started, or understanding the limitations.
I want to build a sample 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]
it could just as well be a select from a static list, ie SELECT'(local) AS name UNION ALL SELECT '(local)\SQLEXPRESS', but it needs to be a refreshible query to occasionally get the new server list.
So i can get that into a PowerBI Desktop data, but i'm not seeing how to get something 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 s
if 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-variable..., 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?
