📋 Case study — Manufacturing
When data helps teams make decisions every day
How a multi-site industrial company brought its disparate systems together in a sovereign, on-premises self-service BI platform that its business teams use.
📊 Project overview
| Sector | manufacturing / logistics |
| Deployment | on premises |
| Sites covered | 4 (1 production site + 3 warehouses) |
| Sources integrated | ERP · MES · PLM · WMS |
| Stack | SQL Server · SSAS · Power BI |
| External cloud | ✓ none |
01 - The context
An industrial group with fragmented data
Our client operates a central production site and three satellite warehouses. Its business data flows through ERP, MES, PLM and WMS systems, each supporting a critical part of the value chain.
There was no consolidated view. Teams made decisions from Excel exports and recalculated KPIs manually, sometimes using different methods.
Management wanted business teams to explore data on their own without going through IT for every query, while retaining full control over data security.
Meanwhile, IT was swamped with requests for BI reports, dashboards and datasets and could not meet users’ needs within reasonable time frames.
“The point was to get data into the hands of the people who make decisions, at the moment they need it.”
— Datavizin, during project scoping
Problem
Data silos
Four source systems that did not communicate with each other and no unified view.
Problem
Reliance on IT
Every query went through the IT department.
Constraint
100% on premises
Security policy prohibited the use of an external cloud.
Constraint
Multiple sites
One production site and three satellite warehouses.
02 - Technical architecture
A fully on-premises Microsoft platform
The client’s security policy governed the technology choices. The platform uses an established Microsoft ecosystem, with access managed centrally through the existing Active Directory.
Storage & ETL
SQL Server + SSIS
A central database and ETL flows for all business data sources.
OLAP modelling
SSAS Multidimensional and Tabular
Multidimensional cubes and tabular models for different analytics users.
Reporting
Power BI Report Server
An in-house self-service reporting portal with no cloud dependency.
Security
Active Directory
Fine-grained permissions by group, with no additional accounts to create.
Project management and version control — Jira and Confluence for tracking and documentation, and GitHub for version control of SQL, SSIS, MDX and configuration artefacts.
03 - The data model
Four layers for reliable data end to end
The data architecture draws on Kimball models and the medallion architecture, to which we added a fourth layer designed for self-service access.
This separation lets power users access a stable, controlled layer without touching the production tables of business applications.
Bronze - Raw
1:1 replication of source data
Raw data from ERP, MES, PLM and WMS, with no transformations and maximum traceability.
Silver - Cleaned
Standardised data
Formats are standardised, duplicates removed, and business rules applied. Data is reconciled across sources and prepared for aggregation.
Gold - Certified
Kimball dimensional models
Fact and dimension tables that underpin the SSAS cubes and official KPIs.
Data mart - Self-service
BI reporting layer
Selected tables, SQL views and SSAS cubes that users can explore independently without being given access to sensitive data.
Data sources
ERP, MES, PLM and WMS across four sites, using different formats and protocols.
Reporting access
Documented SQL tables and views plus pre-aggregated SSAS cubes. Direct connection from Power BI Desktop or Excel.
Governance and security
Granular access via Active Directory. Sensitive data is excluded from the self-service layer.
04 - Custom engineering
Solutions built for this environment
On top of the standard architecture, we built several components specifically for the client’s requirements and constraints.
SharePoint connector for SQL Server
Automated scripts synchronise internal SharePoint lists, including master data, business parameters and tracking data, directly with SQL Server. This keeps SharePoint and the data warehouse in sync without manual intervention or version discrepancies.
In-house load balancer for Power BI Report Server
A system built in-house distributes and schedules Power BI report refreshes. It prevents load spikes, keeps data up to date and maximises availability without third-party components or extra licences.
Workflow orchestrator (central catalogue)
A single catalogue lets operators manage, sequence and monitor all SSIS packages from one interface. It handles dependencies between flows and retries automatically after a failure, simplifying daily operations.
Integrated monitoring for jobs, cubes and reports
Real-time dashboards cover the full BI pipeline: SQL Agent job status, SSIS processing, SSAS cube refreshes and Power BI report refreshes. Power BI and Excel users can access the reports through a dedicated portal.
05 - The results
Measurable independence for business teams
Since launch, business teams have explored data independently on a stable, secure platform managed entirely in-house.
Self-service in practice
Power users build and publish their own Power BI reports without contacting IT.
Simpler governance
Access is managed through the existing Active Directory, with no additional accounts to maintain.
SSAS cube performance
Pre-aggregated cubes respond almost instantly on large datasets.
Full data sovereignty
No cloud dependency. All data stays within the client’s infrastructure.
Proactive monitoring
Data flow issues are detected in real time before users are affected.
Up-to-date documentation
Each report and cube is documented in Confluence, so new users can find what they need independently.
Get started with self-service BI
Take your data strategy further with self-service BI.
Become a genuinely data-driven business by developing your own data management and analytics platform.
Datavizin supports you with Microsoft BI infrastructure hosted in a sovereign cloud.