RETAIL EDW IMPLEMENTATION
ORGANIZATIONOur client is one of the most recognized retail chains specializing in Men’s apparel. Headquartered in Texas, our client operates 1,700+ stores in North America. The retail chain acquired many brands as part of expansion in US and Canada.
CHALLENGEMultiple brand acquisitions in the long term led to information challenges (functional silos, data redundancy, multiple data marts, multiple reporting tools, no common “enterprise” data model, integration and delivery issues, data quality issues, and coordination issues with multiple teams). Costs of maintaining multiple data marts and costs of training multiple teams were growing out of control. The systems and analysis tools were limited in volume, sophistication and rate of change of data they can handle. Significant amount of data was replicated across multiple data marts. Getting a single source of truth for data was becoming more and more challenging as the technical landscape involved multi-vendor Database, ETL and Reporting platforms – Oracle, Informix, DB2, UniVerse, InfoBright, SQL Server, DataStage, MicroStrategy, Cognos, Hyperion and several smaller niche toolsets. Integrating ongoing acquired business’ data into heterogeneous platforms was a challenging and uphill task. A study was carried out with input from various stakeholders and the key outcome from this study was to consolidate all the data marts into one single Integrated Data Warehouse (IDW).
SOLUTIONXTIVIA played an advisory role in technology selection. The outcome of the Data Warehouse platform evaluation exercise was the selection of Teradata Data Warehouse Appliance – 2690. XTIVIA provided IDW architecture recommendation, ELT solution architecture recommendation and involved in migrating heterogeneous data marts into the Teradata IDW. Phase 1 of the solution implementation involved the development of Teradata IDW moving a major brand’s data over to target IDW which was performed by a third party using their retail data model and Eclipse SQL Generator tool. XTIVIA played a major role in phase 2 moving data marts related to different acquired brands into Teradata IDW. Our Data Warehouse architecture recommendation involved multi-stage ELT process. We developed script generator tool that would automatically generate TPT scripts for landing data from flat files into Teradata. DataStage jobs were developed to land RDBMS source data into Teradata. Third party Eclipse SQL Generator tool was used to create BTEQ scripts with transformation logic and data movement between staging areas. A major drawback of the SQL Generator tool was that it lacked access to Teradata optimizer due to which it would not generate efficient SQLs. XTIVIA’s Teradata professionals tuned the tool generated SQLs for optimal performance. XTIVIA’s Oracle and Teradata experts converted Oracle database backend code (procedures and functions) to Teradata. Application DBA Activities Our Teradata Application DBA continuously monitors workload and performs system level tuning ensuring optimum performance and resource usage on Teradata ecosystem. XTIVIA developed automated tools to identify candidates for Multi-Value Compression, Skew Identification, Column Analysis, Database Object Comparison, Row Comparison, and Statistics Comparison. We assist our client in Teradata upgrade activities. Our DBA proactively monitors and recommends physical design changes using Teradata performance features where applicable.
- Single version of 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
- One small and efficient team supporting all brands and business units