Since last week’s blog post about dynamic M parameters generated so much interest, this week I thought I’d give you another example of something cool you can do with them when you’re using Azure Data Explorer (ADX) as a DirectQuery source in Power BI: geospatial analysis.
Let’s say you work for a chain of supermarkets and want to use Power BI see what other competing stores are close to one of your stores. First of all you need some data, in in this case my data source was this list of British supermarkets and their locations , published by the nice people at Geolytix, and called Retail Points. Once again I loaded the csv file into ADX and was able to write a KQL query using the geo_point_in_circle() function to filter the list of supermarkets down to those within a specified distance of a given supermarket. For example, here’s a simple KQL query that finds all the supermarkets within a 5000m radius of the location of my local Marks and Spencer store in Amersham:
let centrestore = retailpoints | where store_name == "M&S Amersham SF" | project long_wgs, lat_wgs; let centrelat = toscalar( centrestore | summarize max(lat_wgs)); let centrelong = toscalar ( centrestore | summarize max(long_wgs)); retailpoints | where geo_point_in_circle(long_wgs, lat_wgs, centrelong, centrelat, 5000)
[Apologies if this isn’t the greatest KQL query – I’m still learning]
I was then able to create two M parameters called selectedstore and selectedradius and bind them to two other tables in my dataset using dynamic M parameters so that the end user could use slicers to select:
- The store they want to use as the central point of their analysis
- The size in metres of the radius of the circle around the central point within which other stores should be displayed
These M parameters could then be injected into the KQL query of a DirectQuery table using the following M query:
let
Source = AzureDataExplorer.Contents(
"https://mycluster.northeurope.kusto.windows.net",
"retailpoints",
Text.Format(
"declare query_parameters(filterstore:string = ""#[storeMparametername]"", centreradius:int = #[radiusMparametername]);#(lf)let centrestore = #(lf)retailpoints#(lf)| where store_name == filterstore#(lf)| project long_wgs, lat_wgs;#(lf)let centrelat = toscalar( centrestore | summarize max(lat_wgs));#(lf)let centrelong = toscalar ( centrestore | summarize max(long_wgs));#(lf)retailpoints#(lf)| where geo_point_in_circle(long_wgs, lat_wgs, centrelong, centrelat, centreradius)",
[
storeMparametername = selectedstore,
radiusMparametername = selectedradius
]
),
[
MaxRows = null,
MaxSize = null,
NoTruncate = null,
AdditionalSetStatements = null
]
)
in
Source
There are two important things to notice here:
- I declared KQL query parameters to hold the selected store name and selected radius at the start of the KQL query. As the documentation notes, this is important to guard against KQL injection attacks.
- I used the M Text.Format function to inject the M parameter values into the KQL query parameter declarations, which was easier and cleaner than generating the KQL query string by concatenating text.
And here’s the report I built, for example showing how you can select an Aldi store in Aberdeen and display all stores within a 1km, 3km and 7km radius of that store:
Not the most sophisticated geospatial report in the world, I admit, and I know very few people are using Azure Data Explorer as a data source for Power BI right now, but I do think it is a fun example of the kind analysis that dynamic M parameters now make possible.
