Tag Archives: data warehousing

Data Warehouses, OLAP & the Fraud Examiner

banner

SewingSince two of the topics to be discussed in our April 2014 Introduction to Fraud Examination seminar in Richmond next week will be client data warehousing and online analytical processing (OLAP), I thought I’d write a short post briefly introducing both concepts as practice tools for fraud examiners.

A fraud examiner laying out an investigation might well ask, “What’s a data warehouse and how’s can it be of use to me in building the case at hand?”  The short answer is that a client’s data warehouse represents formatted, managed client data stored outside of the client’s operational information systems.  The originating idea behind the warehouse concept is that data stored to answer all sorts of analytical questions about the business can be accessed most effectively by users by separating that data from the enterprise’s operational systems.  What’s the point of separating operational data from analytical data?  Most audit practitioners today can remember that not too long ago our clients archived inactive data onto tapes and ran any analytical reports they chose to run against those tapes, primarily to lessen the day to day system performance impact on their important operational systems.

In today’s far more complex data handing environment, the reason for the separation is that there’s just far more data of  different types to be analyzed, all available at once and requiring processing at different  frequencies and levels,  for a seemingly ever expanding roster of purposes. The last decade has demonstrated that the data warehouse concept operates most successfully for those organizations which can combine data from multiple business processes such as marketing, sales and production systems into an easily updated and maintained location (such as the cloud), accessible by all authorized user stakeholders, both internal and external to the organization. Source applications feed the warehouse incrementally and map the transfer trace allowing fraud examiners and other control assurance professionals to perform transaction cross-referencing and data filtering; the fraud examiner profiling the data flows related to a fraud scenario can generate case related queries  for a given week, month, quarter or a year and (this is most important) compare financial transaction data flows based the on-going historical status (old and updated) of the same and related applications.

A important point for fraud examiners to be aware of is that, often, access to just low end data analysis tools such as simple query capabilities may be all that’s required to assist in the construction of  even relatively complex fraud cases.  For examiners who choose to broaden their practice capabilities to handle even more complex investigations, access to powerful, multi-dimensional tools is now, increasingly, available.  One of these tools is on line analytical processing (OLAP) based on the concept of the relational database embodied by many, if not most, information systems database applications today.

Think of a cloud based data ware house over-laid by a complex, very large spreadsheet (the OLAP application) allowing the examiner to perform queries, searches, pivots, calculations and a vast array of other types of data manipulation over multiple dimensional pages.  Imagine being able to flip a complex database on its side and examine all of the data from that different perspective or being able to highlight an individual data element and then drilling down to examine and trace the basic foundational data that went into creating that item of interest.

Today’s increasingly cloud based OLAP systems support multi-dimensional conceptual (what-if) views of the underlying data allowing for calculations and modeling to be applied across multiple dimensions, through hierarchies, and across database elements.  Amazingly, advanced tools are presently available to allow OLAP-based analysis across eight to ten different dimensions.  Of special interest to investigators is the OLAP’s ability to perform detailed analysis of trends and fluctuations in transactional data while laying bare the supporting information rolling up to the trend or fluctuation.

Fraud examiners and other assurance professionals need to be generally aware that there are various software products on the market today that can be used to perform OLAP functions.  As we’ll cover in the seminar, client organizations usually implement OLAP in a multi-user client/server mode with the aim of offering authorized users rapid responses to queries regardless of the size and complexity of the internal or cloud based underlying warehouse.  OLAP can held fraud examiners and other users summarize targeted client information for investigation through comparative, personalized viewing as well as through analysis of historical and projected data in various what-if data model scenarios.