by David Crowther
Question:
Can you link MapThat Layers to Power BI for reporting?
Answer:
Yes, … and below are some hints and tips on how to get started!
Once you have installed it, open Power BI.
Choose the Get Data option.
In this example as our MapThat data is in a SQL Database we will choose SQL Server database.
Enter the host and database name for the MapThat SQL database.
Note – here we have chosen a direct query. This will allow us to refresh the charts and tables as the data changes in the MapThat database.
Enter the Users login credentials.
Note - here we have chosen a Database user, as our login utilises a SQL Database user not windows authentication.
Once connected to the database, in the Connector choose the Table or View that displays your data in MapThat.
We will report against our Grounds Maintenance layer, so choose gm_polys.
Choose Load and the Power BI window opens.
The Table is added on the right-hand side in the Data Pane.
Firstly, we will visualise the Grounds Maintenance (GM) data using a Stacked Bar Chart from the list.
Choose the Stacked Bar Chart option and then draw a box in the Power BI window to place the item.
To add the data to the chart, drag the field names from the GM table into the X-axis and Y-axis fields in the Visualization pane. In this example we will view the SUM of the size_area values grouped by GM Type.
Once the Chart has been added, we can now customise the chart as needed, e.g. rename the X and Y axis titles, add a Legend, and change the Title.
Use the Visualization pane to then change the bar colours, the axis units, add values to bars, add move and update the legend etc….
The Power BI report will automatically update as your data changes. For example, if a GIS user creates a new Grounds Maintenance plot e.g. adds a grasscrete polygon via QGIS.
… back in Power BI if we then choose the Refresh button, the Power BI Chart(s) will auto update.
Note – how the grasscrete bar has now grown based on the new size_area for that GM Type.
Using the Visualisation pane, we could change the Stacked Bar Chart to be a Pie Chart…
Or an Area Chart…
Or even a Tree Map…
.. finally, if you select an existing chart and copy it you can then paste it multiple times and edit their style, so you can create a Dashboard with multiple charts. The one below, shows
- the GM Area by Type as a Tree Map
- with a count per GM Type as a table below
- a summary list to the right
- a pie chart showing the GM ownership
- and finally, a SUM of the total GM Area in Sq. Metres.
…. Enjoy linking your MapThat Layers to Power BI and see what types of reports you can create.