This company is to remain confidential to satisfy current contracts and non-disclosure agreements. Their primary focus is on footwear, which is marketed towards people intending outdoor and casual use. The company also sells apparel such as clothes, watches, glasses and leather goods. They are a Fortune 500 company founded in 1918, and in 2007 were named to the top 100 best places to work by CNN Money.
The client recently switched email service providers and was faced with the task of managing master data from twenty different data feeds, all from separate systems, that totaled approximately 40M+ primary objective was to build a Consumer Data Warehouse that will store multiple data sources with a scalable architecture that provides a “single-view” of their customers. The data warehouse to be structured as a distribution hub, which serves as the data source of record (DSOR) for the Exact Target integration and augment enterprise-wide reporting capabilities.
During the initial discovery process, the client realized that they did not have the available resources in-house to accomplish the desired objectives. The client acknowledged they had the proper software, hardware and support staff, but was lacking in data management expertise.
In an effort to control costs and obtain necessary resources, the client engaged DatumLabs, for design/development only, and agreed to the following solution. Solution Enables users to configure incoming files, load into staging database and then distribute into the data warehouse. Nightly and monthly batch processes control the import/load into the databases, as well as the extraction of files for Exact Target integration. A mix of transactional databases, data warehouses and data marts were created to provide a complete solution. Total development time for the solution was six months.
Solution functionality included the following
- Automated file system search process that logged files received and sent alerts regarding files expected but not received.
- Smart load process that built all necessary objects needed for import off of configurable properties within the operational data store (ODS) database.
- Modular ETL process that allows scalability by offering a ‘plug-in’ structured approach for file specific transformation rules.
- Global centralized-action-logging (CAL). Custom database action logging plug-in that captures user-defined events, system level errors and processing metrics.
- Custom cleansing/standardization routine that is property driven and configurable for multiple data sources. NCOA, CASS and fuzzy-logic matching were all out of scope to control costs.
- Automated daily data extracts and delivery for integration.
The solution incorporated the following.
- Tables/Views/Indexed Views
- Stored Procedures/Functions/User-defined Types
- SSIS Packages
- Job Agents
- Custom CLR Assemblies/REGEX
The overall benefit of the solution was it provided a very flexible system that was repeatable, scalable and
enabled the client to quickly on-board new clients and/or additional files per clients to the system.
- Decreased time by 50% to incorporate file specific ETL procedures per client
- Provided “single-view” of customer while maintaining security and data integrity
- Increased mail campaign creation efficiency by 300%
- Modular design supports system growth
Products and services used
- MS Server 2012
- MS SQL Server 2012
- Exact Target