Production and Distribution
Problem Statement
Established conglomerate with mature business lines and technological platform standardized across its different companies and divisions. Conglomerate implemented SAP R3 in 2018 to manage all companies and product lines. Although data input into the system and system reports were in order, company management had trouble managing an increasing number of reports independently made by different companies and divisions on a vast number of product lines and SKUs. Shareholders and upper management made it a priority to integrate the data so they could easily analyze interactive reports to improve operations, management, and data audit.
Objectives
- Analyze business objectives, current reports, and available data to propose course of action. Identify and map all target KPIs from their OLTP databases.
- Design on-premises data warehouse and implement all ETL packages to extract, transform and load all data automatically.
- Put in place code version controls and database management alerts.
- Perform data validation and cleaning while documenting adjustments needed from the company’s staff. Design and implement an analytical database to feed Power BI reports from the data warehouse.
- Create detailed permissions and profiles in the analytical database to maintain data security and governance.
- Design 7 high level automated interactive visualizations in Power BI as preliminary phase for three divisions: inventory planning, finance, and sales.
- Design Power BI reports with app-like menu to run on tablets for all sales team based on role level security access to allow for product management, pricing, order status, client management and overall business intelligence on their specific roles and responsibilities.
- Deep dive and drill-through reports to analyze and expand phase 1 high level reports.
- Design and implement platform to request, manage and monitor all user’s roles and accesses.
Corporate culture based on data and results
Data mapping and report design was an interactive and collaborative team effort between the Client and Posibilia. Through the course of the two phases which lasted 8 months all together the work was split per division. Every week, 2-3 work sessions were held with the division implementation phase.
Our work methodology became a seamless induction for the Clients' management staff SO they could fully understand the capability and usage of the tools and effectively meet their needs for interactive, integrated, and automated reports.
Data warehouse
Posibilia implemented a data warehouse on premises using SSIS for the ETL process and SSAS to configure the analytical database used via live connection with Power BI.
Posibilia implemented code source control and database management alerts.
Posibilia provided Client with its data dictionary and data warehouse architecture documentation.
In the data warehouse, many transformations, categorizations, and groupings were added per Client's requirement to meet visualization needs without major ERP adjustments.
Visualizations
Posibilia developed over 20 interactive reports which led to significant operations and administration changes and improvements including:
- increase purchasing efficiencies,
- address aging products by lots,
- manage clients' sales, seasonality, payment patterns and profitability,
- manage strategies, sales staff efforts and
- analyze financial statements in the context of its key influencers from other divisions,
- increases operation flows and turnover times.
Client has about 100 licenses currently configured with different permissions and access to the datasets and reports.
During the visualization process all data sources underwent significant cleaning and iterations that lead to tighter company control and complete review of physical product warehouses and data input processes.
