When we think about collecting and visualising data in a coherent manner, there are a few well-known tools out there to help do this, and one of those is Microsoft's PowerBI.
It can be used across multiple devices, ingest many different data types, and visualize that data in a lot of different ways depending on who the audience needs to be.
Gearset has some really powerful options available to retrieve key information on your Gearset usage and performance, such as the Reporting and Audit APIs. However, sometimes the raw data in JSON format won't be enough to help convey the messages you need to certain stakeholders or for auditing, and that's where a transformation and visualization tool can come in really useful.
This article uses the Windows PowerBI Desktop application to query, transform and visualize the data from Gearset's Reporting API to create simple graphs for key metrics.
It's very likely you'll want to tweak the data transformation and resulting graphs to your needs, so treat this as a starting point to evolve for your own use cases.
Important notes:
This document references the Reporting API V1 to quickly showcase how you can construct queries and transform the data returned - as per this advice, all queries going forward should utilise Reporting API V2 to ensure protection against timeouts and scalability moving forward.
Be aware of the various Gearset API limits in place when constructing and executing these queries, namely the '10 requests per hour per endpoint' to query the Reporting/Audit API.
Initial setup
Open up a new Report in PowerBI.
Click on '
Get data' in 'data' section of the top configuration panel and select 'Blank query'.
You'll now be taken to the '
Power Query Editor' and have opportunity to create multiple queries to build up this report. Click 'Advanced editor' in the 'query' section of top panel to allow us to customize the API call into Gearset and resulting data.Using this editor allows us to utilize the Power Query M Language to structure our request, transformation, and resulting data output, in the form of multiple queries.
Reporting API - 'Deployment Frequency Aggregate' - Query
The below screenshot and code showcase a query to the Deployment Frequency Aggregate endpoint for the last 30 days, where '
XXXXX' needs to be replaced with your Gearset API Token:
let
// Allow easy switching of the date dynamically to get the last 30 days
startDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -30), "yyyy-MM-dd"),
endDate = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),
urlWithoutDates = "https://api.gearset.com/public/reporting/deployment-frequency/aggregate" &
"?StartDate={StartDate}T00%3A00%3A00.0000000Z" &
"&EndDate={EndDate}T00%3A00%3A00.0000000Z" &
"&Interval=Daily&GroupBy=TotalDeploymentCount",
url = Text.Replace(Text.Replace(urlWithoutDates, "{StartDate}", startDate), "{EndDate}", endDate),
headers = [ #"Authorization" = "token 00199EA1EB70F4BA3F775B7587A70E6DF2259BE420B9CF5C84CC52FBF22F2AD557E"],
result = Json.Document(Web.Contents(url, [Headers = headers])),
Items = result[Items],
Items1 = Items{0},
#"Converted to Table" = Record.ToTable(Items1),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Value] <> "count")),
#"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Date", "Value"}, {"Date", "Value.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Value1",{{"Value.1", "Deployments"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type datetime}, {"Deployments", Int64.Type}})
in
#"Changed Type"
Once you hit
Doneand the query runs, you'll be presented with the following formatted table (the 'Applied steps' on the right panel correspond to our query above where the lines start with #, indicating we're performing some transformation). This ensures we've got data that can nicely fit into the in built table types.Exit the Query Editor panel to head back to the Report page, where the Data panel on the right hand side will showcase the Date/Deployments information we've just retrieved and transformed.
You now have the data available to Visualize as required using the various graphs/filtering capabilities of PowerBI as outlined here!
The example below shows a 'Stacked Column Chart' using the Month and Day attributes of the Date response, and Deployments associated with each one.
You can then modify this as required with the available data and filtering/visualization options to make it as needed, and refresh the data (in accordance with the API limits) for updated information.
Reporting API - 'Lead Time for Changes Aggregate' - Query
Another example may be that you're looking for a 6 month view of your Lead Time for Changes for a particular Pipeline, in aggregated form per week, specifically focusing on the 'Mean' lead time. The query and screenshots below show how to achieve this:
let
startDate = "2024-01-20",
endDate = "2024-07-20",
// Environment ID we care about tracking the failures on
envID = "79c7b264-5e32-4010-bea4-ac17d3a3124d",
urlWithoutDates = "https://api.gearset.com/public/reporting/lead-time/44910c8b-8da1-439f-aad9-47f97600ea9e/aggregate" &
"?StartDate={StartDate}T00%3A00%3A00.0000000Z" &
"&EndDate={EndDate}T00%3A00%3A00.0000000Z" &
"&Interval=Weekly",
url = Text.Replace(Text.Replace(urlWithoutDates, "{StartDate}", startDate), "{EndDate}", endDate),
headers = [ #"Authorization" = "token 00199EA1EB70F4BA3F775B7587A70E6DF2259BE420B9CF5C84CC52FBF22F2AD557E"],
result = Json.Document(Web.Contents(url, [Headers = headers])),
Environments = result[Environments],
Environments1 = Environments{0},
MeanTimeLeadTimeForChanges = Environments1[MeanTimeLeadTimeForChanges],
#"Converted to Table" = Table.FromList(MeanTimeLeadTimeForChanges, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Date", "Value"}, {"Column1.Date", "Column1.Value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1.Date", type datetime}, {"Column1.Value", type duration}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.Date", "Date"}, {"Column1.Value", "MeanLeadTime(Hours)"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Hours", each (Duration.Days([#"MeanLeadTime(Hours)"]))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Days", each (Duration.Days([#"MeanLeadTime(Hours)"]) / 24))
in
#"Added Custom1"
Once you hit
Doneand the query runs, you'll be presented with the following formatted table including the applied steps transforming the results. As above, this ensures we've got data that can nicely fit into the in built table types, and in this particular example we've done some processing to extract the 'Hours' and 'Days' separately from the returned format ofHH.HH:MM:SS.SSin case we want to use them more granularly in a chart.Exit the Query Editor panel to head back to the Report page, where you can see the. new query results in the 'Data' panel on the right, with selectable options for each column we transformed.
The example below shows a Line Chart to summarise the Month/Day over the 6 month period specified, and the Mean Lead Time (in Hours) to plot across the Y-axis.
We can see it really spiked in mid June so it gives me further information to go off and investigate about why this has occurred, which shows how powerful these visualizations can be!
Summary
As you can see, PowerBI has the capability to retrieve various data points from the Gearset APIs, and visualize them in different ways to satisfy your requirements for both performance-based tracking and auditing requirements.
While we haven't covered every possible endpoint and transformation/visualization opportunity in this document, it should serve as a foundation for you to build up the right set of dashboards to meet your needs as a team.