This video explains how you could design and implement a SAP HANA solution that identifies potential crime suspects from cell company records and police reports.
It covers the end-to-end process from concept to reality including data discovery, table design, ETL via FTP, calculation views and integration to BOBJ explorer information spaces.
Three data tables are created in SAP HANA and then data is loaded from CSV files via FTP transfer into the SAP Developer Center environment and import into HANA.
Crime data is available for NSW from BOSCAR over 15 years in a spreadsheet summarised by month. As part of the challenge an Excel macro was developed to convert a subset of the summary data into 6 million individual records with randomised dates.
Tower locations are contained in approval records published by ACMA. These could only be accessed in pages of 50 at a time, so a PHP screen scraper script had to be developed and deployed using ScraperWiki to provide infrastructure for execution and database.
Cell phone connection data to tower sites was not publicly available so another Excel macro was used to randomly generate connections for a subset of towers and dates.
The tables are joined by date and location using a calculation view, and a projection is added to filter the time dimension.
Finally, BOBJ explorer information spaces are created to provide views into raw crime statistics, raw cell tower location data, and potential suspects. Potential suspects are simply the subscribers who were present at the most crime scenes. This can be interactively analysed by type of crime, area, etc.
Thanks for considering my entry.
Presentation – prezi.com/tdr2bfsm98a8/detective-hana/
BOSCAR Crime Statistics - bocsar.nsw.gov.au/lawlink/bocsar/ll_bocsar.nsf/pages/bocsar_research
ACMA RF Site Database – web.acma.gov.au/pls/radcom/register_search.main_page
ScraperWiki - scraperwiki.com/scrapers/acma/