TechMobius

Data warehouse using Azure Cloud Implementation

The Business Need

Based out of US, a manufacturing engineering product entity which deals with selling of engineering materials for manufacturing sector had urgent requirement to build a data warehouse system to track the inventory, sales, order, forecasting details of the products across different categories. The workflow solution thus developed would enable the data analyst to get the daily track of order, sales, fore casting details and get the insight out of that using BI reports.

Challenges

The major challenge was to fully automate large-scale sales, order and inventory tracking of the products across various industries. Delta processing should be done on daily basis, data validation, any post processing need to be done post that. We have to deliver very high quality of data from the different API / ERP enterprise sources and so building up the validation and data quality layer was very challenging in the automation process.

TechMobius

The Mobius Solution

TechMobius

Top Challengers/ How Can We help/ Why us?

Mobius developed a complete automated pipelines setup in Azure cloud to process historical data as one time and then to do the delta processing on daily basis using Python, PySpark, Azure data factory, Azure Data Lake, Azure Synapse, Azure SQL and Power BI. Data will be pulled from ERP, CRM, Operational and Third party server. Data will be extracted from different listed sources using APIs / flat files from ERP/CRM system SharePoint services. Extracted output will be converted into flat files and placed in the Azure blob. We build Azure data factory pipelines to load the flat files from the blob to Azure SQL database. Using store procedures in Azure SQL, delta processing, data validation will be completed. 

The unstructured/semi structured data (JSONs) from APIs will be stored in Azure data lake which then can be consumed by Azure Synapse Analytics as and when required. Flat files data will be loaded to the Azure SQL data warehouse from there data marts will be developed for the reporting needs. BI reports were developed using Power BI on different dimensions of Sales order, Inventory and forecasting information across business verticals providing very helpful business insights. 

Data ware house output also been shared with AI/ML bots as input from which further analytics carried out on client end. Based on the internal and quality audit review comments, we will add the automated rules for ensuring data quality before loading into data ware house system.  

Highlights

Results

The solution provided a single robust system which can be highly scalable as required in the future. Also single point for getting all the data sets for BI reports and AI/ML processing data sets achieved and thereby improving the operational efficiency in the client end. Increased data quality activities before ingestion into data ware house system made the data readily consumed for analytics purpose. 

Using scalable services in Azure infra and go with pay as you use 

Azure SQL can be scaled up/down as required which helps the cost management 

Data storage in blob can be archived and can be stored for any number of years as per client requirement. No separate archive mechanism needed in this case 

Highly scalable model developed 

Improved operational efficiency and ready to analytics data delivered.

Related Case Studies

Price Monitoring Tool

Price Monitoring Tool

Price Monitoring Tool The Business Need A leading Australian marketplace organization wished to monitor

Reporting Tool Solution

Reporting Tool Solution

Reporting Tool Solution The Business Need A renowned software service provider to the petroleum

Smart Product Classification

Smart Product Classification

Smart Product Classification The Business Need A leading US-based online marketplace organization wished to