A dedicated SQL environment receives changes from the source system every 15 minutes and serves Power BI business reports. A coordinated workflow, from data ingestion to queries, supports day-to-day decisions.
Contents
The task: up-to-date data for business decisions
The client uses Power BI reports for day-to-day decisions. It needed a data source that regularly receives changes from the source system and supports sales, inventory and transaction reports.
BitNext built an SQL environment on a dedicated Windows-based VPS. Data ingestion focuses on changed records, avoiding the need to process the entire dataset again each time.
The data flow in three steps
- We retrieve the changes. New and modified transactions arrive from the source system through an API connection.
- We check and load the data. The data processing workflow validates and transforms the records before writing them to the target database. It logs events and errors.
- Power BI queries the SQL database. Reports use the prepared database, with queries tuned to the VPS's capacity.
We track changes using each record's last modification timestamp. In this project, data is refreshed every 15 minutes, so this schedule also determines how current the reports are.
Which reports does the system support?
- Sales overview: following sales data in one place.
- Inventory turnover report: reviewing stock movements and turnover.
- Transaction log analysis: finding and examining individual business events.
Power BI's DirectQuery connection queries the SQL database directly. Fast responses therefore required tuning the database structure and queries as well as the reports themselves.
A manageable, protected environment
The system runs in an isolated Windows Server environment with Microsoft SQL Server. Access is protected with firewall rules, IP-based restrictions and encrypted connections.
The solution includes automatic backups and system monitoring. Data processing logs help identify failures during ingestion or refresh operations.
What does this mean for the client?
The reports use a dedicated data source that is refreshed regularly. Sales, inventory and transactions can be analysed from the same SQL environment.
Targeted processing of changed data reduces repeated data transfers, while logging makes refresh operations easier to follow. A dedicated VPS also allows the environment to be tailored to the client's needs.
Technical details
| Component | Configuration |
|---|---|
| Virtual server | Windows Server 2022, 4 vCPUs, 16 GB of memory and SSD storage. |
| Database | Microsoft SQL Server 2022 Standard with a dedicated database, indexing and partitioning. |
| Data ingestion | API-based ingestion and SSIS processing; changes tracked using the last modification timestamp. |
| Refresh | Data refreshed every 15 minutes, with logging and error checks. |
| Reporting connection | Power BI DirectQuery, with queries tuned to the server. |
| Operations | Automatic backups, system monitoring and controlled access. |
Need a regularly refreshed data source for your Power BI reports? Tell us where the data comes from, and we will help build the supporting environment.