The client was initially using an Informatica based legacy business intelligence and data transformation solution. The old solution was not very efficient in handling terabytes of data generated by multiple streams of data generation channels. The current solution was also comparatively expensive to scale up. The new solution should address all these problems. However, the first challenge would be data migration without compromising or stopping the data stream so that no real-time data is lost.

Our first priority was to configure and setup the Cloudera Hadoop solution and setup the new data warehouse based on researched requirements. We then integrated all multiple types of data streams to the new solution making sure that no real-time data like clickstream, purchases, etc. was lost. In the next step, we used Scoop scripts to migrate all legacy data from existing Oracle data warehouse to the new Hadoop Distributed File System based data warehouse clusters. We had built custom Scoop tool scripts to automate one-time historical load transfer of 15+ terabytes of complexly structured data to different HDFS clusters.

Efficient storage of big data is an important challenge. However extracting meaningful insights from the stored data was also of utmost importance. The client was saving multiple types of data like user information, product inventory, product delivery, logistics, employees’ details, end-to-end accounting data, etc. Filtering, transforming, and then uploading data to data warehouse in a timely manner from this large amount of data was a challenge in itself. In addition, it was also business critical to do automated ETL transformation on some specific types of data to accelerate report generation process.

Hadoop data warehouses are very efficient in storing a large amount of information. However, it comes with an added advantage i.e Apache Hive, a data warehouse infrastructure built on top of Hadoop that aids in data summarization, query, and big data analysis. We used Apache Hive over Hadoop warehouse to accelerate data query. We created custom User Defined Functions (UDFs) to handle business-specific use cases and accelerating specific data queries. Hive was complemented by Apache Pig Scripts, custom execution scripts that helped in automatic transformation and loading of incoming data to facilitate various business reports.

Data analysis at the backend would be useless without an effective solution for data representation. The client was using an outdated version for Tableau and their team was well versed with its usage. They needed the new solution to integrate seamlessly with the Tableau solution. They also wanted us to update Tableau, and create new dashboards and reports over the tool.

The first step was to update the Tableau solution to latest editions without losing the already built dashboards and reports structure. Once that was completed, we integrated new Hadoop-based data analytics solution with Tableau through Tableau APIs. The integration is done at the virtualization layer allowed real-time generation of reports and summaries of processed Hadoop data. Next, we created new dashboards and reports to showcase all new types of raw and processed data provided by the new Hadoop solution. We also modernized all the reports and dashboards making them more visually appealing and user intuitive.