The aim of the project
Our client makes daily business decisions based on Power BI reports that require a real-time, reliable data source. The goal was to build a transaction-based synchronization system using SQL Server installed on their own Windows-based VPS to continuously update data and serve Power BI queries.
Infrastructure development
1. VPS environment
- Operating system: Windows Server 2022
- Resources: 4 vCPU, 16 GB RAM, SSD storage
- Security: firewall configuration, IP-based access, SSL encryption
2.) SQL Server installation
Version: Microsoft SQL Server 2022 Standard
Configuration:
- Dedicated database for the client
- Optimised indexing and partitioning
- Automatic backup and monitoring
Transaction-based synchronisation
1) Source system integration
We retrieve transaction data (e.g. sales, inventory movements) from the accounting company's SQL system via API.
2.) ETL process
Technology: SQL Server Integration Services (SSIS)
Process:
- Get data from API
- Validation and transformation
- Transaction-level insertion into SQL database
- Logging and error handling
3.) Synchronisation logic
Each record has a LastModified timestamp field.
The SSIS package updates only those records that have been modified.
Power BI integration
1.) Data connection
Power BI connects to the SQL Server via a DirectQuery connection.
Queries are optimised so as not to overload the VPS.
2.) Report structure
Main reports:
- Sales dashboard
- Stock rotation statement
- Transaction log analysis
Update frequency: automatic data update every 15 minutes)
Results
Frequency of data updates: 15min
Power BI load time: <5 seconds
Data quality error rate: <1%
Customer satisfaction: Featured on
Lessons and suggestions
Transaction-based synchronisation significantly reduces data duplication and errors.
Having your own VPS gives you flexibility.
Using Power BI DirectQuery is fast, but only works effectively with a well-optimized SQL structure.