Designing a Data Warehouse for Cyber Crimes

Song, Il‐Yeol; Lee, Ki Jung; Hu, Xiaohua; Maguire, John David; Choi, Namyoun; Chen, Peter P.

doi:10.15394/jdfsl.2006.1007

Cited by 3 publications

(5 citation statements)

References 11 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Our final product of the DW manipulation was created using technologies such as Microsoft SQL Server Analysis Services and Visual Studio Intelligent Tools to create a Data Cube for query processing and OLAP analysis. Note, our DW design follows that of Kimball and Casetera (2004) approach, which has been widely accepted in industry, discussed in detail by Ariyachandra and Watson (2008), and further outlined as a four-step process by Song et al , (2009). Once we develop the OLAP cube, we present few results to compare with our objectives.…”

Section: Methodsmentioning

confidence: 99%

“…Recent advancement of DW and data mining tools are becoming popular in storing and analyzing large volumes of data for corporations and government agencies alike to help make strategic decisions. There has been an increasing demand of centralized systems to store criminal information so that users can retrieve the information as necessary (Hoogeveen and Meer, 1994; Chau et al , 2002; Chen et al , 2003a; Claburn, 2004; Kurlander, 2005; Bhaskar, 2006; Digital Evidence, 2006; Song et al , 2009; Shen et al , 2012). By making use of data warehouse technology, analysts could store and retrieve the “5 W’s of crime” – who, what, when, where and why (Song et al , 2009).…”

Section: Literature Reviewmentioning

confidence: 99%

“…There has been an increasing demand of centralized systems to store criminal information so that users can retrieve the information as necessary (Hoogeveen and Meer, 1994; Chau et al , 2002; Chen et al , 2003a; Claburn, 2004; Kurlander, 2005; Bhaskar, 2006; Digital Evidence, 2006; Song et al , 2009; Shen et al , 2012). By making use of data warehouse technology, analysts could store and retrieve the “5 W’s of crime” – who, what, when, where and why (Song et al , 2009). In relation to a project named COPLINK, Chen et al (2003a, 2003b) discussed data mining of criminal texts collected from the web by the Arizona Police department and used various data mining methods to find relationships between crimes and the possible criminal groups associated with these crimes.…”

Section: Literature Reviewmentioning

confidence: 99%

“…Chi and Wang (2012) proposed a data warehouse schema and OLAP technology to demonstrate the criminal activities of Wenzhou city in China. Similarly Song et al (2009) proposed how DW and OLAP technology can be used to understand the cybercrime patterns. New York Police Commissioner Raymond Kelly (2012) recently published a crime report for the year 2012 that included various criminal activities based on races including graphs; however, it does not include any DW or OLAP technology.…”

Section: Literature Reviewmentioning

confidence: 99%

“…Goeke and Faley (2007) extends this idea further, suggesting that the more flexible the data warehouse, the more useful said data warehouse will be to its end users. Data warehouses are designed to support decision-making by studying and analyzing complex sets of data (Song et al , 2009). A data model used for designing a data warehouse is typically called a dimensional model, which is composed of a fact table and a group of dimension tables surrounding the fact table (Kimball and Casetera, 2004).…”

Section: Why Data Warehousing?mentioning

confidence: 99%

See 4 more Smart Citations

Information gems from criminal mines

Rob

Srubar²

2016

View full text Add to dashboard Cite

Purpose The purpose of this study is to demonstrate how existing volumes of big city crime data could be converted to significantly useful information by law enforcement agencies using readily available data warehouse and OLAP technologies. During the post-9/11 era, criminal data collection by law enforcement agencies received significant attention across the world. Rapid advancement of technology helped collection and storage of these data in large volumes, but often do not get analyzed due to improper data format, lack of technological knowledge and time. Data warehousing (DW) and On-line Analytical Processing (OLAP) tools can be used to organize and present these data in a form strategically meaningful to the general public. In this study, the authors took a seven-month sample crime data from the City of Houston Police Department’s website, cleaned and organized them into a data warehouse with the hope of answering common questions related to crime statistics in a big city in the USA. Design/methodology/approach The raw data for the seven-month period was collected from the website in Microsoft Excel spreadsheet format for each month. The data were then cleaned, described, renamed, formatted and then imported into a compiled Access database along with the definition of Facts and Dimensions using a STAR Schema. Data were then transferred to the Microsoft SQL Server data warehouse. SQL Server Analysis Services and Visual Studio Business Intelligent Tool are used to create a Data Cube for OLAP analysis of the summarized data. Findings To prove the usefulness of the DW and OLAP cube, the authors have shown few sample queries displaying the number and the types of crimes as a function of time of the day, location, premises, etc. For example, the authors found that 98 crimes occurred on a major street in the city during the early working hours (7 am and 12 pm) when nobody virtually was at home, and among those crimes, roughly two-thirds of them are thefts. This summarized information is significantly useful to the general public and the law enforcement agencies. Research limitations/implications The authors’ research is limited to one city’s crime data, whose data set might be different from other cities. In addition to the volume of data and lack of descriptions, the major limitations encountered were the lack of major neighborhood names and their relation to streets. There are other government agencies that provide data to this effect, and a standard set of data would facilitate the process. The authors also looked at data for a nine-month period only. Analyzing data over many years will provide time-trend of crime statistics for a longer period of time. Practical implications Many federal, state and local law enforcement agencies are rapidly embracing technology to publish crime data through their websites. However, more attention will need to be paid to the quality and utility of this information to the general public. At the time, there exists no compiled source of crime data or its trend as a function of time, crime type, location and premises. There needs to be a coherent system that allows for an average citizen to obtain this information in a more consumable package. DW and OLAP tools can provide this information package. Social implications Having the crime data of a big city in a consumable form is immensely useful for all segments of the constituency that the government agencies serve and will become a service that these offices will be expected to deliver on demand. This information could also be useful in many instances for the decision makers, ranging from those seeking to start a business, to those seeking a place to live who may not necessarily know which neighborhoods or parts of the city are more prone to criminal activity than others. Originality/value While there have been few reports of possible use of DW and OALP technologies to study criminal data, the authors found that not many authors used actual crime data, the data sets and formats used in each case are different, results are not presented in most cases and the actual vendor technologies implemented can be different as well. In this paper, the authors present how DW and OLAP tools readily available in most enterprises can be used to analyze publicly available criminal datasets and convert them into meaningful information, which can be valuable not only to the law enforcement agencies but to the public at large.

show abstract

Section: Methodsmentioning

confidence: 99%