Post reply

PowerBI Parameterize Connection Variables in Power Query

  • July 2, 2017 at 8:42 pm

    #326977

    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 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-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!

  • July 3, 2017 at 8:34 am

    #1949163

    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