Councils play a pivotal role in managing and shaping our communities and one significant area of responsibility is overseeing planning development applications and monitoring property development within the council area.
In this post, I delve into some of the ways Power BI can be utilised by councils to visually represent the vast number of planning development applications they receive and process and to help build a picture of the progress of property development within their council area.
Welcome to the Iamdata Solutions Asset Management Newsletter - October 2024
Empowering Councils with Power BI Data Visualisations
Managing planning development applications efficiently is crucial for ensuring smooth and timely service delivery. Traditionally, Planning Officers within councils have relied a lot on manual processes. These manual processes often start with the time-consuming and tedious job of exporting the data from the corporate systems (e.g., Civica Authority, TechOne) into an Excel spreadsheets. Once the data has been exported from the systems into a spreadsheet, before any analysis and report can be done, the data cleansing and formatting has to be done. Probably the most important thing to remember is that as soon as data is extracted from the systems it is already out of date! As many of you who have been going through this process will agree, this method is time-consuming, tedious, prone to errors, and inconsistent.
Development Applications Managed through Power BI
For those of you that regularly read my posts, you will know that I'm a huge fan of Power BI. Power BI can definitely help you here in this scenario!
Power BI offers a robust solution that can streamline these processes by directly connecting to corporate systems, automating data extraction, and providing real-time, accurate reporting.
Connection to Corporate Systems
One of the key advantages of Power BI is its ability to directly connect to various corporate systems used by councils, such as Civica Authority, TechOne, and other specialised planning and development software. These systems store vast amounts of data related to development applications, including application status, timelines, compliance activities, and officer comments.
Here are some examples how, with Power BI, this data can be automatically retrieved without the need for manual extraction or manipulation.
1. Connecting to a Cloud-Based Application via API
When connecting Power BI to a cloud-based application, such as TechOne, you can do so via the application's REST API.
In this scenario, Power BI connects to the application’s API using a Web connector. This connection method involves entering the API endpoint URL (e.g., https://api.example.com/data) into Power BI. Depending on the API's security setup, you may need to provide authentication credentials, such as an API key or OAuth token.
Once authenticated, Power BI retrieves data, typically in JSON or XML format, which you can then transform and model within Power BI for analysis. Other types of custom connectors may connect you to the database where you can import specific tables, views or write custom SQL queries to fetch the data required.
In Power BI Desktop, you would start by selecting the Web connector, or search for the custom Connector, enter the API URL, and input any necessary authentication details. After connecting, you can transform the retrieved data as needed and load it into Power BI to create reports and visualisations.
2. Connecting to an In-House SQL Server
In a scenario where your organisation's Property and Rating system is stored and managed on an in-house SQL Server, Power BI can connect directly to this database.
This connection is straightforward, leveraging the SQL Server connector within Power BI. The process typically involves specifying the SQL Server’s name and database name, followed by choosing an authentication method, either Windows Authentication or SQL Server Authentication.
Once the connection is established, you can query the database to retrieve the necessary tables or views, or you can write custom SQL queries to fetch specific data.
In Power BI Desktop, you would select the SQL Server connector, enter the server and database details, choose your authentication method, and then select the data you want to import. The data is then loaded into Power BI, where you can begin creating your analyses.
3. Connecting via Microsoft Fabric (Data Warehouse/ Lakehouse)
When dealing with large-scale datasets stored in a data warehouse or lakehouse on Microsoft Fabric (formerly Azure Synapse or Azure Data Lake), Power BI offers robust integration capabilities.
In this scenario, Power BI connects to the data sources within Fabric using the Azure Synapse Analytics connector or the Azure Data Lake connector, depending on the specific storage architecture.
This setup allows you to either import the data into Power BI or use DirectQuery for real-time analysis.
To do this, open Power BI Desktop and use the Azure Synapse Analytics connector if you’re connecting to a data warehouse or the Azure Data Lake Storage connector for a lakehouse.
After entering the necessary Fabric workspace details and authenticating with Azure credentials, you can select the specific data source, such as a lakehouse or data warehouse, to retrieve the data. Once the data is loaded, you can leverage Power BI’s visualisation tools to generate insights from your large-scale datasets.
Benefits of Direct Integration
Real-Time Data Access
Power BI connects directly to live data sources, ensuring that the information presented in reports is always up-to-date. This eliminates the lag associated with manual data extraction and provides a real-time view of the development application process.
Automated Data Refresh
Once reports are set up in Power BI, they can be configured to refresh automatically at specified intervals or on demand. This automation saves time and ensures that Planning Officers are working with the latest data at all times.
Time Savings and Efficiency
Prior to using Power BI, I would spend significant time exporting data from our various corporate systems into Excel, cleaning and organising the data, and then creating the charts, graphs and reports that I needed. This process was not only labour intensive but also repetitive and tedious requiring the same steps to be performed each reporting period.
These steps are significantly streamlined with Power BI. For example:
Elimination of Manual Data Handling
Power BI automates the extraction, transformation, and loading (ETL) process, freeing up officers to focus on higher-value tasks, such as analysis and decision-making.
Reusable Reports
Reports in Power BI are built once and can be reused across reporting periods. The underlying data can be refreshed with a single click or automatically, without needing to recreate the report each time.
Custom Dashboards
Power BI allows for the creation of custom report pages that can be tailored to the specific needs of different stakeholders, from Planning Officers to senior management, to a cut down modified version to share with the residents on your council's webpage. These reports provide an at-a-glance view of key metrics, such as the number of applications received, processing times, and compliance rates, etc.
Reducing the Risk of Errors
Manual data handling is inherently risky, as it introduces multiple points where errors can occur, such as during data extraction, entry, or calculation. These errors can lead to inaccurate reporting, which in turn can affect decision-making and compliance with regulatory requirements.
Power BI mitigates these risks in several ways for example:
Data Integrity
By connecting directly to the source systems, Power BI ensures that the data used in reports is accurate and consistent with the original records. This direct connection reduces the risk of human error that is common in manual data handling.
Consistency
Power BI enforces consistency in data presentation by using standardised report templates. This ensures that the data is presented in the same format, regardless of who generates the report, reducing the variability that can lead to misinterpretation of the data.
Consistent and Familiar Reporting
Consistency in reporting is critical for ensuring that all stakeholders have a clear and uniform understanding of the data. With Power BI, reports are not only consistent but also visually appealing and easy to interpret.
Power BI Reporting Key Advantages
Standardised Reports
Power BI allows councils to develop standardised report templates that can be used across different reporting periods. This standardisation ensures that reports are always presented in a familiar format, making it easier for stakeholders to interpret the data.
Interactive Visualisations
Power BI offers interactive visualisations that allow users to drill down into the data, explore trends, and identify patterns. These visualisations are more engaging than traditional Excel charts and can provide deeper insights into the data.
Accessibility
Power BI reports can be accessed from any device with an internet connection, making it easy for Planning Officers and other stakeholders to view and interact with the data from anywhere.
Enhanced Stakeholder Engagement
Effective communication with stakeholders, including residents, businesses, and developers, is crucial for councils to build trust and foster transparency. Power BI offers visually captivating and interactive dashboards that can be shared with the public, providing a transparent view of property development within the council area. By sharing visual representations of data, councils can engage stakeholders in a more meaningful way, encouraging collaboration and generating valuable feedback.
Power BI for Development Applications
The adoption of Power BI in managing planning development applications offers significant benefits to councils. By directly connecting to corporate systems, automating data extraction and report generation, and providing consistent, error-free reporting, Power BI enhances the efficiency and accuracy of the planning process. Councils can save time, reduce the risk of errors, and present data in a format that is both familiar and consistent, ultimately leading to better decision-making and improved service delivery.
As more councils continue to embrace digital transformation, Power BI stands out as a powerful tool that can help you meet the challenges of managing planning development applications more effectively.
Interactive Demo Power BI Reports
I've built several Demo Power BI Reports you can have a play with on my website here: ➡️https://www.iamdata.solutions/interactive-power-bi-reports-for-you-to-explore
Please note, these reports have not been built for mobile viewing and will not be available on your mobile devices. To view, have a play and interact with these Power BI reports, you'll need to open this page in desktop.
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Hello, could you provide some information on the steps required to connect Power BI to Civica Authority ?