Data Analytics using PowerBI
The Reporting using OData page introduced you to OData feed that you can use to do your own data analytics and reporting.
Now we will look at some queries that you could do with PowerBI to get you jump started on your own data analytics development.
In the instructions below, replace {{gruntify-reporting-server}} with the reporting URL given on your Workspace settings page in the Admin Portal, accessible to staff with the Owner role. It will be something like https://reporting.gruntify.com/v4.3/odata/workspaces/workspacenamehere/.
Authentication Issues with PowerBI
Gruntify only allows
Requests
This section will cover the two main tables you may wish to query. The Request table contains the field entered data, but that very data gives it a complex data structure. Follow this guidance advice for help with accessing the Request table.
Form Query
The Forms set the type of Request, therefore it is a key entity when building your queries. Each form has a unique FormId, and each time you publish a form, the FormVersion changes. All requests (and assets) are marked with the combination of the FormId and the FormVersion.
If you are interested in the reporting on the general request fields, such as when they were done, who did how many, etc then you can query the whole request table without specifying the form.
However, if you wish to report on the user collected data then you must filter by the form. Every form, and potentially every version of a form, has different columns and the only way to get a valid list of columns is to filter by the form first.
To filter down to just one form (but all form versions) use the following OData syntax:
{{ Service Root }}/requests?$filter=Form/id eq {{FormId}}'
Once you have all the requests for one form, you can now expand on the FormData record. The FormData record contains the “fields” from the form.
Some OData feeds deliver a data in a flat structure similar, and you need to build across multiple tables to get anything usable. This is common with SQL based data sources. The Gruntify OData feed returns a more structured format, with smaller entities bundled into the table as records.
The FormData inside a Request is once such entity bundled within the larger entity. FormData records can be expanded over Requests without filtering by a Form first, but you will not know which columns apply to which Form without manual analysis. Hence, we recommend you do your Request queries Form by Form and then expand the FormData to give the columns across the different Form Versions. Should a new Version with new fields be created, the FormData will need to be “re-expanded” to determine the new set of data columns.
Expanding Without Form Filtering | Expanding with Form Filtering |
Columns are from all possible forms. | Columns are from a specified form. |
|
|
Filtering and Expanding in PowerBI
When you try to read the Requests table in PowerBI, a warning may occur that there were errors in the data. This is likely to be due to the record structure within the data. Expanding the record entries using the Query Editor, or ignoring the columns that contain the records, will reduce or eliminate these issues.
Once you have included the Request table in your work area, edit the query to expand out the records and access the data directly. For example, in a Device record you can access the operating system and version used, the type of device and the device id used. You may wish to generate a report based on mobile device versions; to check how many requests are being made on older mobile devices.
The Form record expands out into two fields – Id and Version. Selecting an Id here is equivalent to using a Form/id filter referred to previously.
If you are reporting on Requests grouped by Form, expand Form to Id and Version and keep all the Request records.
If you are reporting on Requests FormData, expand Form to Id and Version and then filter on one particular Form.Id as shown below. These Ids match the FormId column described previously under Form Query.
Selecting the Advanced Editor will show you the text version of the query PowerBI will execute. Expanding the Form record and then selecting a single Form.Id will result in a query similar to:
let
Source = OData.Feed("https://xxxxx/workspaces/workspacename", null, [Implementation="2.0"]),
Requests_table = Source{[Name="Requests",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Requests_table,{"Reference", "CreatedBy", "Form", "Status", "StatusReason", "Location", "Orientation", "GpsAccuracy", "Device", "Details", "Submitter", "FormData", "FeaturedImageFileId", "CaptureDateTime", "Media", "Regions", "RegionIds", "IsReinspection", "HasTrip", "LinkedRequests", "AuthorizedTeamIds", "Assets", "Job", "SourceSystem", "FeatureCollectionId", "Id", "WorkspaceId", "CreatedAt", "LastModifiedAt"}),
#"Expanded Form" = Table.ExpandRecordColumn(#"Removed Other Columns", "Form", {"Id", "Version"}, {"Form.Id", "Form.Version"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Form", each ([Form.Id] = "e3fc3a56-3bad-480c-811c-79781304a255"))
in
#"Filtered Rows"
With Form.Id and Form.Version available, it is tempting to build a relationship between the Request and the Form tables using the PowerBI automatic relationship feature. This will not work correctly as the two entities need to be joined over both the Id and the Version, and PowerBI attempts to join over one field only.
With one Form.Id selected, the FormData record can be expanded, and the columns listed will be those from that Form only.
Shown below are the fields from our example Landscaping form. This Form uses the default control names generated by Gruntify. If the form designer had taken the time to set proper control names, then it would be easier to work out the column meanings. We can at least see the type of controls embedded in the name. Segmented fields will have been a single choice field. Signature fields contain the URI of the signature image, so these will probably not be wanted for the report and may be deselected.
The query PowerBI that will execute will now be similar to:
let
Source = OData.Feed("https://xxxxx/workspaces/workspacename ", null, [Implementation="2.0"]),
Requests_table = Source{[Name="Requests",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Requests_table,{"Reference", "CreatedBy", "Form", "Status", "StatusReason", "Location", "Orientation", "GpsAccuracy", "Device", "Details", "Submitter", "FormData", "FeaturedImageFileId", "CaptureDateTime", "Media", "Regions", "RegionIds", "IsReinspection", "HasTrip", "LinkedRequests", "AuthorizedTeamIds", "Assets", "Job", "SourceSystem", "FeatureCollectionId", "Id", "WorkspaceId", "CreatedAt", "LastModifiedAt"}),
#"Expanded Form" = Table.ExpandRecordColumn(#"Removed Other Columns", "Form", {"Id", "Version"}, {"Form.Id", "Form.Version"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Form", each ([Form.Id] = "e3fc3a56-3bad-480c-811c-79781304a255")),
#"Expanded FormData" = Table.ExpandRecordColumn(#"Filtered Rows", "FormData", {"TextField_8ubq", "Segmented_hv5u", "Segmented_cp30", "Segmented_vwzh", "Segmented_u3pf", "Segmented_4pm0", "Segmented_8w6d", "Segmented_mmwm", "Repeater_1m3y"}, {"FormData.TextField_8ubq", "FormData.Segmented_hv5u", "FormData.Segmented_cp30", "FormData.Segmented_vwzh", "FormData.Segmented_u3pf", "FormData.Segmented_4pm0", "FormData.Segmented_8w6d", "FormData.Segmented_mmwm", "FormData.Repeater_1m3y"})
in
#"Expanded FormData"Request to User Relationship
The “Request is Created By” relationship can be modelled in PowerBI. Link Request.CreatedBy to Users.Id.
Note: There may be User records with Ids that do not exist in the Request Table.
Extracting Co-ordinates from Requests
All Requests have a location, which you may use in PowerBI to present your graphically.
To get the actual coordinates:
expand the Location record
extract the coordinates and turn them into a delimited string
split that string by the delimiter,
change the type of those fields to numbers,
and then (optionally) rename them to something meaningful
Example PowerBI Query Fragment:
#"Expanded Location" = Table.ExpandRecordColumn(#"Inserted Merged Column", "Location", {"Type", "Coordinates"}, {"Type", "Coordinates"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Location", {"Coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Coordinates", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Coordinates.1", "Coordinates.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Coordinates.1", type number}, {"Coordinates.2", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Coordinates.1", "Longitude"}, {"Coordinates.2", "Latitude"}})
Assets and Jobs
Assets and Jobs work in a similar manner to Requests. Assets are linked directly to Forms, while Jobs are linked to a Job Template, that is linked to Pre Work and Post Work forms. The Job fields Priority, Client Reference, and Description are in the Details object linked to the Job
Asset Geometry
Assets that are ‘LineStrings', ‘Polygons’ and ‘Multi-Geometries’ are stored under the 'Geometry’ heading in the Asset Table.
It recommended that these are processed separately from locations as these data points will require additional processing. All non-Point Geometries will be collections of points that will need to be processed differently depending on the type of Geometry and the number of points associated with the asset geometry.
Related articles