So in my previous post, I explained how functions have changed and how you can convert existing queries to a function. In this post I'll continue with my original issue of using multiple queries but using parameters to connect to one SQL server/db.

With the June Power BI desktop update, we can now specify parameters to connect to data sources and there are a couple of ways we can do this...

Creating a Parameter - Through the Query Editor

To use parameters on creation of a data source, you need to have already created a parameter prior to selecting one.

1. Load the query editor.



2. Click Manage Parameters.



3. Create two new parameters for the SQL Server and Database and add the default values in and click OK.



4. Verify you can see the parameters in the left hand queries pane.







Creating a Parameter - On Getting Data

If your query already contains parameters, you can select or create additional parameters through the below:

1. Get data. 


2. Select your data source (I used SQL Server for this example). You'll notice that there is a couple of new drop down boxes...



3. You can select the "New Parameter" option to create (step 3 from previous section) or select "Parameter" and specify an existing one.



When done, you can follow the rest of the wizard and connect to your data source. So what about updating existing queries...?


Updating Existing Queries

In my Part 1 I talked about using a dynamic XML table as a source for my parameters. To update an existing query to use the new parameter method, we need to edit the existing reference variable below.



In the above, you can see we have our variables which currently point to my XML table. To change this to the new parameters all we need to do is the below:



This tells the query that the SCCMServer and SCCMDatabase variables are the relevant parameters and the source then uses these to connect.

If you don't have a preset variable specified like mine, to amend an existing query, all you need to do it add variables first

Let
<VariableName0> = #"<Parameter Name0>",
<VariableName1> = #"<Parameter Name1>",
Source = sql.database(VariableName0, VariableName1, [Query="


With the SQL parameters now set and tested, all I needed to do was then go through the rest of the queries and do the above then all good again!








I created a post a while back on invoking functions to specify parameters for data queries (Link Here). This was happily working using a single function to update multiple queries... until what seems to be the May/June Update. This introduced inline functions and parameters to the BI desktop, which caused me a bit of pain on some of my reports as it switched the way functions were used.


The Problem

So in previous versions when invoking a function, I could use the below code to create a dynamic  XML table, allowing me to use this table as a parameter reference in my other SQL queries.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) =>
let
    Source = Xml.Tables("<Source><Server>" & SCCMSQLServer & "</Server><Database>" & SCCMDatabase & "</Database></Source>"),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"Database", type text}})
in
    #"Changed Type"
in
    SQLSource



In the above example, this would have invoked the configuration function and allowed me to reference a field by using the below:

SCCMServer = Configuration{0}[Server],
SCCMDatabase = Configuration{0}[Database],

Since the Power BI desktop update this has changed as it no longer updates its original table but creates a new executed query called "Invoked Function#".

When attempting to create a function off my original query via the right click options,  I'd also get the below.



The Quick Fix

To quickly fix the issue, I initially wrapped the function around one of the SQL queries, added the variables in and then invoked it.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) =>
let  
     
    SCCMServer = SCCMSQLServer,
    SCCMDatabase = SCCMDatabase,

<Rest of Code>
in
    SQLSource

This returned my data but again...in a new query. This wouldn't have matted so much if I was using one query but I had multiple referencing my original table.


The Solution

To properly resolve this issue, we can utilise the parameter feature to create the references required for executing the SQL queries. In part 2, I'll go through how we use parameters in the Power BI desktop and how we update existing queries to variables instead.


It's been a busy couple of months for me on the Power BI front. Following the success of my Power BI SCCM Dashboard (see previous post here), I'm pleased to announce version 1.4! This version is a little different though as its the first version with the PowerON brand and its packed with improvements for the community to use.

It's been a natural progression with the dashboard and with the great things we're doing at PowerON on the BI front, we felt it was the right time to improve the dashboard and showcase some of the innovate things we've implemented in other system center reports.

With this now coming under the PowerON brand, we're also adding the option to contact PowerON for more advanced consultancy, customisation and enablement support contracts of the dashboard.

Hopefully you'll enjoy the update and we'll be continuing to add improvements over the coming months :)


Version 1.4 Release Notes

- Complete report redesign
- Functions for invoking removed following June Power BI update
- Parameters added for specifying ConfigMgr Server/DB
- Optional paramters added for limiting collections
- Optional parameter added for including device name in report
- Query improvements / buug fixes



Client Overview

This view contains a basic overview of client information such as, EndPoint Compliance, Client Activity and Health.




Workstation / Server Breakdown

This contains a breakdown of your site workstations/Servers including manufacturer, operating systems, architecture and even chassis type info! The Chassis Type information goes off to a technet page so you may need internet access when initially loading your data.





Workstation Admin

This contains a breakdown of your site workstations admin info additional to the workstation breakdown.



Client Health Summary

Contains summary information on your clients health and activity status.



Update Compliance

This view contains a breakdown for update compliance, showing you missing updates by bulletin ID.



EndPoint Compliance

This view contains summary information on client EndPoint compliance and protection status.


Site Admin

The site admin view queries the configmgr dogs technet post for the latest SCCM release. It also queries the Microsoft support life cycle site and checks for expired or expiring operating systems currently deployed which you will need to address.



Managed Mobile Devices

This view contains summary information on Managed Mobile Devices in ConfigMgr. It allows you to view things like Make and OS version of devices connected.



DB Fragmentation 

Added DB Fragmentation Report into the dashboard. SQL query from Kent Agerlund's NIC 4th Edition.






Installation

You can download the dashboard from the Technet Gallery


Ensure you have at least read access to the SCCM SQL Database and you can access the Internet from the machine running Power BI.

Download the lastest Power BI desktop here.

Once installed Open Power BI.

1. Go to File > Options and Settings > Options.

2. Under Privacy enable Fast Combine to file privacy.

3. Under Security untick "Require user approval for native SQL database queries".


4.  Select Edit Queries to load the Query Editor.


5. Supply parameter information in the Setup section



6. Select the "_Client Information" query under the ConfigMgr and supply any credentials required to access the database.


7. If prompted, Click OK on the encryption support message.


8. Close the Query Editor and load the data  


Publishing to the Web


The desktop version of Power BI is free to use and I used this without publishing the report up to the web just to produce a quick report. The data is on this report is not live as its only from the last time you load the data but you can easily reload the information on startup. 

For an even better experience, you can publish the file up to the web and create a dashboard.  though there can be cost implications but there is a free version.  Check on Power BI's website for further information on what best suits you.

                      

The great thing about publishing is you can utilise the ask a question feature which is awesome!


                              

Power Bi also comes with a mobile app which allows you to access the report from your mobile device anywhere too.



For advanced support, customisations or consultancy contact the PowerON team below:
Next PostNewer Posts Previous PostOlder Posts Home