Harnessing Business Intelligence to enhance the River Ribble’s environment
Ribble Rivers Trust is a registered charity whose principle objective is the conservation of the River Ribble and its surrounding flora and wildlife.
The Trust’s work is catchment-area based (i.e., it looks after the wider area, rather than just the main water course) and extends over an area of 900 square kilometres.
Safeguarding the natural environment is an incredible responsibility that relies on collaboration, awareness and scientific study. The Department for Environment Food & Rural Affairs (Defra) offer ‘guidance’ for this and has a great many frameworks and policies upon which environmental bodies can base their work.
River Trusts with far reaching conservational and ecological remits really have their work cut out. With limited resources and valuable work to do, they need to ensure that everyone involved (directly or indirectly) is able to work toward the same goals, i.e., ‘improving the catchment for wildlife and people’.
Ribble Rivers Trust collects many different types of data (e.g., pollution levels, species recorded, etc.) about the different geographical areas which make up the catchment area of the River and its tributaries. This data is can be used in many ways.
As part of its work through the Catchment Based Approach (CaBA) the trusts partners identified data and evidence sharing as one of the top priorities for increasing delivery of environmental improvements. With support from the Heritage Lottery Fund, the Trust wanted to publish its data for use by other environment agencies, volunteer groups, clubs, water companies and volunteers of the Trust.
The data needed to be accessible, up to date, relevant and useful for each party. The information needed to be available through a publicly accessible website and take the form of an interactive (dynamic) dashboard.
Critically, the solution to the Trust’s needs couldn’t accommodate change to how ‘raw’ information is collected. The reason being that many individuals, volunteers, groups and third party agencies contribute to data collection efforts. This information is captured in different ways, using different data sets. It would be too disruptive (and, frankly, unachievable) to change the working methods for data collection. To ensure that gathered intelligence was interpreted effectively and utilised by the various audiences the Trust wanted to:
- View information: Create a centralised, mobile device-friendly view of its information
- Aggregate data: Summarise and view information from across all files into one dashboard, with the ability to show aggregated data from different subsets of areas within the catchment
- Reduce effort: Minimise changes to working practices and to the formats of data files
- Real-time information: Ensure changes to the data held in spreadsheets were reflected in the dashboard
- Contextual dashboards: Illustrate different dashboards by navigating the catchment area via an interactive map
- Visibility: Surface the dashboard on their public WordPress website.
- Add depth: Show pictures from the Trust’s Flickr account, embedded and filtered to the map area
Business Agility were engaged to help design and deliver a Business Intelligence solution to realise all of the above.
The SharePoint experts initially produced a demo, illustrating the capabilities of Excel Power Pivot Data Models and the reporting options. It embedded an Excel dashboard, charts and Power View maps into its SharePoint Online Office 365 site for the demo, using a SharePoint capability that allows Slicers to control the data displayed.
- Stored the data repositories (in this case approx. 20 large Excel Spreadsheets) on the Trust’s SharePoint Online platform enabling secure, version-controlled, direct editing
- Offered a suite of Power Queries which extracted and transformed all data, populating it into one Power Pivot Data Model in a master Excel file located on the SharePoint site
- Created a dashboard in the master Excel file using Cube formulas to extract the correct values from the Data Model, aggregated to the correct level
- Embedded the dashboard in the Trust’s public website, using a URL parameter to control which data is displayed in the dashboard
Business agility we great at harnessing our business intelligence and enhancing our environment
The dashboard, viewable here (http://ribblelifetogether.org/catchment-map/), summarises information based on the map’s current external map bounds. All the values and lists are live real-time feeds from the Excel data model hosted on the Trust’s SharePoint site.
The major accomplishment of this project was to take existing data (from multiple spreadsheets, collected from multiple different data sets and sources), creating a suitable, attractive dashboard to view the information and then funnelling all data, updates, etc. through SharePoint and then surfacing that with the dashboard in a public anonymous access website. To show different data from the different data sources, Business Agility had to devise a unique mechanism to allow the different dashboard/views to be shown.
This was a rewarding project. The biggest benefit has been empowering people to use and act on this data. The foundation of environmental work should be co-operation.
People need information, guidelines and support. This initiative from Ribble Rivers Trust is going to be of great benefit to the communities within the catchment and the environment itself. It’s a project Business Agility has been proud to have been a part of.