Amazon Redshift data warehouse from on-premise
Manual & spreadsheet-based data processing
Monthly Report turnaround time reduced from weeks to mere minut
Sales Data Management and Reporting
Ecommerce
Our client is one of the largest B2B marketplaces in India. Different segments of buyers and suppliers starting from SMEs, MSMEs to large corporates and even individuals use the platform to facilitate business transactions. They were facing challenges in maintaining and governing this transactional data set in the use of multiple functions.
Excel-based operations of this data set were becoming challenging and not sustainable to the business growth. We helped them explore the technology offering best suited to their existing stack and automate certain leg-work. They chose Amazon S3 and Amazon Redshift solutions, while we implemented a centralized data repository, automated the data transformation steps and build data marts to cater to reporting needs of the specific line of businesses.
The client’s platform offered three types of listings - Free, Catalogue Services and Premium listings. For each type of listing and their services - a separate proforma is created. Based on these categories, separate teams and functions operating on the business development and client handling side.
Since the platform was too broad, several teams and individuals were interacting with business information. Such as, downloading the dumps from the ERP to spreadsheets to update the transactions. and further, once the modifications were made, all the information was merged back into a single excel file - the Master Data dump on which reporting was done.
Inconsistency and non-standard formatting challenges to the Master Dump
Prone to human errors which were difficult to track in a spreadsheet
Manual processes leading to productivity loss and inefficiencies
Inaccurate Sales data reporting due to the lack of a centralized repository
No real-time view into the business health, leading to last-minute hassles
The company was trying to modernize the existing setup and keep up with the technological advancements. Hence, the project objective was to set up practices and processes for better enterprise data management and business reporting to infuse agility in decision-making.
We conducted technology consulting sessions to demonstrate the ability of different cloud and BI platforms. Post which Amazon Redshift was chosen as the cloud platform for creating a centralized data repository and Microsoft Power BI for business reporting. Here are the key steps of the implementation:
Step 1: was importing the data from the source systems (flat files and Oracle ERP) with the help of Amazon S3. This step was crucial to automate the process and save the manual effort & minimize the human error probabilities.
Step 2: was the transformation of the data. Here an ETL layer was built with AWS glue where the data gets validated and properly formatted to adhere to the defined specification. The events triggers, creation of job and schedule for transformation and loading the data into Amazon Redshift is managed through this layer.
Step 3: the transformed data is then loaded into the Amazon Redshift cloud data warehouse in the form of facts and dimensions. It acts as a centralized repository now. This is further divided into data marts catering to specific BUs. The data is well-governed with logs getting created for every action and access rights are defined.
Step 4: the final and most important step for business and sales reporting is built on Microsoft Power BI. Basis the functional KPIs, data models were built and APIs were developed for these reports to result in accurate and live-to-data reporting. All the users get some pre-scheduled daily, weekly, and monthly interactive reports to take stock of their KPIs and numbers. They can further drill into it and slice and dice the information to create some custom reports.
The loss and duplicacy of data is completely removed with the adoption of the Amazon Cloud Platform and real-time reporting was enabled as well
The automated data processing and transformation with the AWS Glue has eliminated human errors and has led to improved productivity
Now, there is robust data management and governance practice in place with which every activity is accounted for and can be traced back
Reports delivered are dynamic and give a real-time snapshot, in addition to, allowing users to create custom reports and access it on-the-go.
The unstructured and semi-structured data was formatted and converted to a standardized format for a more comprehensive business reporting
