Case Study

Client Background Client is an Indian e-commerce company that connects Indian manufacturers or suppliers with buyers and provides B2C, B2B and C2C sales services through its web portal. Business Objective Client has deployed an on-premise Oracle RDBMS infrastructure to store its data in order of 500 GB at present which grows 20GB on a monthly basis. The current Oracle RDBMS infrastructure is used to manipulate data through Stored Procedures, extract the data manually through a WEBERP system and use that extracted data to create the business reports manually in MS Excel. The reports were based on monthly data with limited view into daily trends. The client wanted to revamp the data processing and reporting process so that reports can be generated daily or on demand. Solution Our data engineering team understood the client’s current data setup & future technology roadmap. It was decided to go with AWS Redshift for data warehousing needs. Additionally, a data pipeline & reporting solution was also proposed to be built using serverless components of AWS, namely, AWS Glue & AWS QuickSight. We were provided with a set of 50 business views (reports), covering 90% of business queries. It was also desired for data warehousing design to support any future adhoc information request. Following development steps were followed We built an intermediate staging layer to capture data from the input source as it is with limited transformation. This meant we would have entire historical data in one place that could be referred to later if needed. This data layer was provided by S3. Created a logical view of the data warehouse model to capture all facts and dimensions provided in business views. Logical model once approved was converted into a physical AWS Redshift model with appropriate partition and sort keys. Although data warehouse is supposed to be “write once, read many”; it was requested to allow the client team to make infrequent updates for records that are prone to be modified. Initial data for tables (multiple files for each table) was provided in a pre-agreed S3 location in csv files. Each row in the csv file had a logical operator indicating record was meant for Insertion, Updating or Deletion. Tables further had hierarchical relationships and dependencies between themselves which meant that any update or deletion logic in ETL jobs had to handle complex sequential atomic update scenarios. Glue jobs were created to move data from S3 to the staging layer (AWS Redshift tables acting as temporary storage for daily processing). At this stage, only columns needed for business reports were moved with changes in data types for some columns. Post having data in staging tables in AWS Redshift, AWS Redshift queries (as a part of cron job deployed on EC2 instance) were used to move data into warehouse tables in AWS Redshift itself. At this stage, aggregations and selections were performed. Job performance metrics and associated logs were stored in a metadata table and notifications were sent via email to the developer team on completion or failure of any step of the data pipeline. Outcome We went live with the data warehouse in 6 months with 3 years of historical data. Incremental processes were set up to ingest data on an ongoing basis with an automated mechanism for handling any failures.

Client Background Client is an Indonesia-based mobile telecommunications services operator. The operator’s coverage includes Java, Bali, and Lombok as well as the principal cities in and around Sumatra, Kalimantan and Sulawesi. Client offers data communication, broadband Internet, mobile communication and 3G services over GSM 900 and GSM 1800 networks.   Current Scenario & Business Need Currently, client has an on premise Teradata Warehouse infrastructure to store their multi Terabytes of data which is increasing in volume day by day. The current Teradata infrastructure has already been exhausted by as much as 70% which prompted the client to look for alternative solution apart from scaling the Teradata warehouse vertically. The maintenance of on-premise infrastructure at such a scale is also a concern for client which made the client look for a cloud based data warehousing solution. The existing client setup used Ab Initio for ETL requirements which increased the cost of operations. The client wanted a low cost ETL solution for the cloud infrastructure. Client had an existing PowerBI licence which the client wanted to utilize to generate reports to data-driven business decisions. Solution Our team met business stakeholders to understand their current setup, overall business requirements and propose a solution. We decided to develop a scalable Data Warehouse, ETL and reporting solution for the client using AWS Cloud Services. We used AWS Redshift to create a Data Warehouse AWS Glue for the ETL process Existing PowerBI for reporting purposes Solution workflow Data Extraction & Storage: We extracted the relevant data from Teradata warehouse and stored it into a Virtual Machine in CSV, Avro and Parquet formats to test the compatibility of different data storage formats with the ETL process – AWS Glue. The files were uploaded to S3 from the virtual machine via a secure channel to make sure that the data is not compromised during transit. Data Preparation: Our next step was to prepare the data, i.e., perform the logical operations on the data so that it can be stored in the data warehouse as well as could be used for reporting purposes with least/no manipulation. We wrote ETL scripts using pyspark and scheduled those scripts to be executed at some specific time using AWS Glue. Data Processing: AWS Glue processed the data from S3, made necessary transformations and stored the resultant data into AWS Redshift. We wrote more data manipulation queries in SQL to further process the data stored in AWS Redshift and prepare it for reporting. Data Visualisation: We used PowerBI to present the reporting data. We installed powerBI desktop on an EC2 instance so that the data can be accessed from AWS Redshift with minimal network latency and reports can be generated in seconds. Result Migration from on premise tera data environment to AWS cloud resulted in significant gains to the customer with reduced infrastructure and storage cost, lower maintenance costs, improved reliability and much needed agility for further analytics roadmap.

Client Background Client is a prominent Indian digital engagement solutions company providing brands with multi-channel digital solutions to connect with their customers 24X7. Its analytics enabled cross-channel targeting platform enables brands to deliver personalized engagement to their customers. Its digital channels have a reach of 500 million plus end customer base powering 200 billion plus interactions on monthly basis. Business Objective Majority of customer interaction powered by client’s digital platform comprises of unstructured data. These interactions alone generate nearly 5 TB of data per month and contain a wealth of information about consumer lifestyle and their preferences. This information once discovered could truly be an asset to the marketing teams as they look drive increased customer engagement and transactions across brands. The client had no in-house capability to store and mine such datasets at scale. It was therefore desired to create a technology infrastructure that can scale with data and allow mining of unstructured data to discover customer preferences & lifestyle information. Such a platform needed to leverage text mining & natural language processing capabilities to understand unstructured texts and use machine learning algorithms to predict customer preferences wherever missing. Client didn’t have any in-house team with data science & big data skills or any prior experience in with such technologies. Valiance was chosen as a technology partner for this initiative to enable discovery of best possible solution and implement it successfully. Solution Our team of business analysts, data scientists & data engineers worked closely with client’s business and product team for the first three weeks to arrive at a right methodological approach comprising text mining framework, machine learning approach and technology platform that would achieve our business objectives. A POC was done initially to validate the approach for accuracy & completeness of results. After a month we had narrowed down to: Hadoop (Open source distribution) with HDFS for storage and Map-reduce for text mining in batch. Spark for training machine learning algorithms. HBase as storage for structured customer data. Text mining & machine learning approach to discover & predict customer attributes. In next 5 months our team performed following activities: Data engineering team created Hadoop based infrastructure to process TB’s of unstructured data. Data science team created text mining rules and NLP algorithms to discover customer attributes including but not limited to age, gender, spending pattern, number of credit cards, number of kids, travel frequency, mutual fund info. We were able to do limited discovery through this due to sparse nature of data. However, it gave us sufficient samples to create training sets for training purpose. Business analytics & data science team also recommended additional customer attributes that could be useful from a marketing standpoint along with acting as a useful predictor in the identification of missing ones. Data science team trained ML algorithms to predict missing customer attributes using discovered attributes. Different algorithms were experimented to arrive at winner algorithms. Results of text mining & machine learning predictions were shared with client’s team regularly to take their feedback in improvement. At the end of 5 months, we were able to arrive at the first version of results with robust & accurate customer data. ~450 customer attributes thus discovered were exposed to marketing team through open API’s. Post initial deployment we are constantly engaged with the client to improve the accuracy of ML algorithms and refine text mining rules. Outcome Enabled discovery of customer attributes through mining of unstructured data as against no information before. This resulted in intelligent marketing campaigns with higher ROI compared to baseline. 10% increase in customer engagement from digital interactions in 2 months of testing with different marketing campaigns.