RETAIL EDW IMPLEMENTATION

ORGANIZATION

Our client is a leading retail chain specializing in men’s apparel, with headquarters in Texas. Operating over 1,700 stores across North America, the client has expanded its footprint through the acquisition of multiple brands in the U.S. and Canada.

CHALLENGE

• Long-term brand acquisitions led to various information challenges, including functional silos, data redundancy, multiple data marts, diverse reporting tools, the absence of a common ‘enterprise’ data model, integration and delivery issues, data quality concerns, and coordination challenges across multiple teams.
• The costs associated with maintaining multiple data marts and training various teams were rapidly escalating.
• The existing systems and analysis tools were limited in terms of volume, sophistication, and the rate at which they could handle data changes.
• A significant amount of data was replicated across multiple data marts, making it increasingly difficult to establish a single source of truth.

As the technical landscape encompassed multi-vendor database, ETL, and reporting platforms—such as Oracle, Informix, DB2, UniVerse, SQL Server, DataStage, MicroStrategy, Cognos, Hyperion, and various smaller niche toolsets—integrating data from acquired businesses into these heterogeneous platforms became a complex and uphill task. A comprehensive study involving various stakeholders revealed that consolidating all data marts into a single Integrated Data Warehouse (IDW) was essential.

SOLUTION

XTIVIA played an advisory role in technology selection, resulting in the selection of Teradata Data Warehouse Appliance – 2690. XTIVIA provided recommendations for IDW architecture and ELT solution architecture and was instrumental in migrating heterogeneous data marts into the Teradata IDW.

Phase 1 of the solution implementation involved developing the Teradata IDW by moving a major brand’s data to the target IDW. This phase was executed by a third party using their retail data model and Eclipse SQL Generator tool.

In Phase 2, XTIVIA played a significant role in migrating data marts from various acquired brands into the Teradata IDW. Our Data Warehouse architecture recommendation included a multi-stage ELT process. We developed a script generator tool to automatically generate TPT scripts for landing data from flat files into Teradata. DataStage jobs were created to handle RDBMS source data and load it into Teradata. Additionally, the third-party Eclipse SQL Generator tool was used to generate BTEQ scripts, incorporating transformation logic and data movement between staging areas.

A notable drawback of the SQL Generator tool was its limited access to the Teradata optimizer, which led to inefficient SQL generation. XTIVIA’s Teradata experts optimized the SQLs generated by the tool for optimal performance. Furthermore, XTIVIA’s Oracle and Teradata professionals converted Oracle backend code (procedures and functions) to Teradata.

Application DBA Activities

Our Teradata Application DBA continuously monitors workloads and performs system-level tuning to ensure optimal performance and efficient resource usage across the Teradata ecosystem. XTIVIA developed automated tools for tasks such as Multi-Value Compression, Skew Identification, Column Analysis, Database Object Comparison, Row Comparison, and Statistics Comparison.

We assist our clients in Teradata upgrade activities, ensuring smooth transitions and minimizing disruptions. Our DBA proactively monitors and recommends physical design changes using Teradata performance features wherever applicable.

RESULTS

• The single version of the truth
• Information Accuracy
• Analytics-Ready Data Warehouse Platform
• Reduced batch time
• Earlier availability of information
• Improvement in business continuity (ability to access critical reports in the event of production data warehouse failure)
• Significant cost savings (licensing cost elimination, Database Administration cost reduction, Reduced network resource usage, reduction in Data Warehouse and ETL operational costs)
• Enable faster integration of new brands and realize related benefits sooner
• Support for all brands and business units with a small and efficient team

KEY COMPONENTS

Data Engineering, Integrated Data Warehouse (IDW), data marts, heterogeneous platforms, multi-vendor Database, ETL, ELT, DataStage, BTEQ, TPT scripts, Teradata, Oracle, Informix, DB2, UniVerse, SQL Server, MicroStrategy, Cognos, Hyperion, Eclipse SQL Generator, Teradata Data Warehouse Appliance – 2690, Multi-Value Compression, Skew Identification, Statistics Comparison, SQL tuning, system-level tuning, physical design changes, Teradata performance features.

SOFTWARE
Teradata 14.10 Teradata TTU 14.10 Oracle 11g SUSE Linux Teradata Administration

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.