For a global marketing conglomerate, designed and developed a data integration system for loading both reference and transactional data into multiple systems. The need for these systems, and this data grew out of SOX remediation activities. The prior environment was a manual, ad-hoc, roll-your-own process that resulting in different versions of the truth being loaded in disparate systems. The new solution was a service oriented architecture (SOA) using BizTalk for extraction and transformation, dot net web services for authentication and schema validation, and SQL Server stored procedures for data load that is compliant with application specific business rules. Architecturally, the system had central logging and exception handling, a single control center, high performance, high availability, and a high degree of flexibility in how the data gets loaded into each target system.
For a global marketing conglomerate, as part of an overall financial restatement project, developed back end SOA services for migration of detailed restatement data to the corporate consolidation system (Hyperion Enterprise). Timing of the restatement forced the development team to generate multiple restatement scenarios, thereby producing multiple overlapping export files.
For a global marketing conglomerate, implemented a multidimensional data mart for Sarbanes Oxley data. Management of SOX compliance was difficult with over 500 companies, of all sizes, under one corporate umbrella. A system was deployed last year to manage the SOX process including local and corporate approvals. However, analysis of this data was limited to extracts formatted in various spreadsheets. Given the natural dimensional aspects of this data, an OLAP system was prototyped. The initial incarnation was built in Microsoft SQL Server 2000 with Analysis Services using a “familiar” Excel front end (via pivot tables). The initial deployment resulted in issues with regards to OLAP reporting of textual metrics (e.g., sample item descriptions). Next steps include an investigation of SQL Server 2005, which is currently too “new” for production deployment.
For a global pharmaceutical company, developed their year two program for Sarbanes Oxley for internal testing of IT SOX controls. Work included refining IT controls and test procedures, and the linking of those IT controls with their risk factors, objectives, and test steps using the COSO framework. After the test programs were developed, responsibilities shifted to program management of the company’s internal IT SOX testing program. All completed test programs and supporting evidence were assembled in a consistent manor as to make it easy to re-perform any given test. Also, acted as primary liaison between the company and their external SOX Auditors. A result SOX testing were a handful of opportunities for operational improvement. The company passed their audit with no major setbacks.