Clue has become the go-to Case Management tool for investigation professionals. We always liked that it’s dedicated to managing intelligence and investigations. It has a thought-out workflow that isn’t the ‘one size fits all’ of a standard Case Management system.

S-branch has always focussed on the data analysis piece of the puzzle. So we were delighted to see the addition of the Clue Analytics Connector.

---

What is it?

A ready-made connector that allows quick exports from Clue to Power Query for advanced reporting and data analysis. Power Query is built in both Excel and Power BI, both of which are available to any business with a Microsoft 365 account.

The connector allows easy access to pull data via the API from Clue into Power Query, without the need for expertise in APIs, JSON, or programming.

---

What can it do for me?

A lot! If we’re talking Excel, it can be used to pull out a Registers data, or a Register’s linked data for external analysis. It can be great for performing additional analysis, calculations or even duplicate detection.

When used with PowerBI, the data within Clue can be used to produce powerful dashboards. Using Clue data in this way allows Analysts to visualise and share insights from the Clue with other interested parties. It also allows drill-down analysis of a Register and its linked Registers.

What’s more, Clue provides ready-made templates that utilise Power Query to export data for use in Power BI and Excel.

The connector allows easy access to pull data via the API from Clue into Power Query, without the need for expertise in APIs, JSON, or programming.

---

Tell me more about the Excel Templates!

Well, there is a standard empty template that comes with all the Power Query functions you need to pull data out of a desired Register. There is also a Duplicate Checker that will work with any Clue implementation. This will pull data out from POLE-style registers (People, Objects, Locations and Events) and look for duplicates between the data using fuzzy matching.

Finally, there is the DVLA checker, which goes through the Vehicle Register picking out Vehicle Registration Numbers and returning the DVLA information about that vehicle. We see this as an excellent example of how the Clue Analytics Connector can be used to collaborate Clue data with other sources (Companies House comes to mind).

New templates are also being added all the time, which is great to see.

---

Okay, what about the Power BI Templates?

For S-branch, this is where it gets exciting. We’ve always been fans of Power BI. Again, there is a standard empty template, which allows data from multiple Registers to be extracted and then linked together using the Power BI model view. As for pre-configured templates, there is a great user management template, which allows administrators to easily see when a user last logged in, what teams they belong to and even the last record they added/updated. This isn’t a full audit log but it’s great for giving management an idea of how users are using the system.

There are also some great Register templates. The Incident template not only gives great insight into the Incident itself, but it also reports on how many days pass until a Task, Event or Investigation is created from the Incident. Great for management reporting.

---

In Conclusion

The Clue Analytics Connector is a great addition to Clue and a real asset for teams who want to get the most out of their Clue Implementation. There is a growing community using the Connector and a great knowledge base that is constantly evolving. The flexible nature of using Power Query means that feedback can be considered quickly. It also gives customers a great opportunity to be part of the design piece, as feedback on these templates can be channeled to the product eventually.

If you think this would be a useful addition to your Clue implementation or you are looking at purchasing Clue, please contact the Clue team who will be able to help you. Likewise, if you are looking into Data Analysis tools in general, or would like Consultancy on either Clue or PowerBI, please contact S-branch here.

PowerBI and Clue – Take your analysis to a new level

We are huge fans of PowerBI here at S-branch. If we ever need to get a ‘lie of the land’ with a new set of data, then it’s our go-to tool. Likewise, we’ve utilised data held within analysis databases using PowerBI before, an example can be found here.

One tool that we were keen to try PowerBI on was Clue. Clue is an investigation case management and intelligence platform that enables organisations to build intelligence and quickly manage and progress investigations. We instantly liked Clue. It’s refreshing to see technology that is designed specifically for Investigations. Too often we see people managing investigations using tools not fit for the job. This leads to workarounds and compromises – Not with Clue!

Being a modern application, Clue has its own API (Application Programming Interface). It is with this API that we interrogated the data within Clue using PowerBI.

Clue’s API

Clue’s API documentation can be found on their support site. We found it to be concise and helpful. We were quickly making API calls using the Postman API Platform to test our requests. There are security measures in place that need to be set up before you start using the API, again this was all documented on the support site.

Making Clue data available using Power BI

Clue has a great article about Power BI on their support site, with examples using Power Query. Utilising Power Query meant we could perform additional basic transformations (such as splitting out date/time fields) on the data too.

The Dashboards

On our test Clue site, we only have a limited amount of data. For this reason, we chose to focus on the Intelligence register.

In the dashboard above, we bring together temporal (time), network and statistical analysis in one place. We can drill down into the data using any one of these analysis types, using interactive visualisations.

• The bottom left shows statistics on the intelligence grading (3x5x2) in accordance with the National Intelligence Model
• The date of the Intelligence can be interrogated either using a slider or by clicking on a specific time period (in this case a month)
• The network visualisation can be used to spot commonalities between the intelligence records. The green dots show people that have more than one intelligence record linked to them. This method of visualisation proved quite performant, we tested it against some of our other datasets with 10’s of thousands of records and the results were impressive
• A filter on the network visualisation allows us the ability to focus on the elements that are important to us
• The righthand side analyses the linked Outcomes, Decisions, Tasks and Events linked to the intelligence.

It’s important to mention at this point that Clue does have its own dashboard and network analysis capability. PowerBI builds on this ability by having all this information in one place with an interactive dashboard.

Clue is used to tackle threats including counter economic crime, cybercrime, organised crime, corruption, counter-terrorism, environmental crime, human trafficking, counter extremism, drug crime, sexual and violent crime, and hate crime.

If any of the above could benefit you or you’re looking into data analysis tools in general; please feel free to contact us. For more information on Clue, please visit their website

Who doesn’t love a good dashboard? What better way to gain a high-level understanding of your data than to see it come alive in bright colours before your eyes? They’re great for presentations but can also give valuable insights into where the peaks and troths in your data lie. From an analytical perspective, it can give you a starting point for your analysis; which with large datasets is very useful.

Dashboards need data. i2 users will store most of their data in iBase; which is very sensible. They do this because items can be quickly retrieved using the intuitive search tools, while the details and hidden connections are easily drilled down upon using i2 Analyst’s Notebook.

It is not always possible, or indeed sensible to place your entire iBase database onto an Analyst’s Notebook chart.

So i2 users have the data, they also have the ability to drill down into the detail. What they sometimes lack is the high-level statistics that a dashboard can provide.

Analyst’s Notebook – Bar Charts and Histograms

One way of doing this is to use the Bar Charts and Histograms functionality within Analyst’s Notebook. This functionality has proven itself very popular as it provides interactive statistical information. The only limitation to this functionality is: it only works with data that is on an Analyst’s Notebook Chart. Statistical information works best with lots of data and it is not always possible, or indeed sensible to place your entire iBase database onto an Analyst’s Notebook chart.

PowerBI

Power BI is a dashboard and reporting analytics tools that can connect to existing data sources and deliver insights that might otherwise have been missed. It allows its users to produce beautiful, interactive reports and then publish them for your organization to consume.

The example below shows national crime information. The data (although fake) is being held in an iBase database. The dashboard shows a location heat map (top left), number of crimes by type (top right), crimes by ward (bottom left) and crimes by date (bottom right).

PowerBI is interactive, meaning a user can drill down into a specific area of the data. In the example below a user has concentrated on pedal cycle crime.

From here the user can glean insights into this crime type. They can see the areas of the country that have the most pedal cycle crime. The time of the year which it most occurs (July to October) and which wards are most affected (Thames Valley Police and Cambridgeshire).

This can assist with strategic planning. If tasked with reducing pedal bike crime, a team could concentrate on university towns (Thames Valley and Cambridgeshire both hold large universities) between the date ranges of July to October.

What’s the catch?

In order to use iBase data with PowerBI you need a good understanding of your iBase security set-up and the underlying table structure; both of which S-branch can help you with. Regarding costs, the desktop version of PowerBI is free; yes free. This allows users to produce dashboards locally. Most organisations will choose a paid for package where they publish dashboards other colleagues within their organisation. To view a published dashboard all that is needed is a web browser.

For more information on PowerBI, visit the website here. For information on how to integrate it with your iBase implementation (new or old) please contact us