{"id":1851,"date":"2026-03-09T16:59:00","date_gmt":"2026-03-09T16:59:00","guid":{"rendered":"https:\/\/bitnext.pixdev.hu\/?p=1851"},"modified":"2026-03-10T10:39:12","modified_gmt":"2026-03-10T10:39:12","slug":"transaction-based-data-synchronization-between-sql-servers-to-power-bi-server","status":"publish","type":"post","link":"https:\/\/bitnext.pixdev.hu\/en_gb\/tranzakcioalapu-adat-szinkronizacio-sql-szerverek-kozott-power-bi-kiszolgalasara\/","title":{"rendered":"Transaction-based data synchronization between SQL Servers to serve Power BI"},"content":{"rendered":"<h3>The aim of the project<\/h3>\n<p>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.<\/p>\n<h3>Infrastructure development<\/h3>\n<p><strong>1. VPS environment<\/strong><\/p>\n<ul>\n<li>Operating system: Windows Server 2022<\/li>\n<li>Resources: 4 vCPU, 16 GB RAM, SSD storage<\/li>\n<li>Security: firewall configuration, IP-based access, SSL encryption<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>2.) SQL Server installation<\/strong><\/p>\n<p>Version: Microsoft SQL Server 2022 Standard<\/p>\n<p>Configuration:<\/p>\n<ul>\n<li>Dedicated database for the client<\/li>\n<li>Optimised indexing and partitioning<\/li>\n<li>Automatic backup and monitoring<\/li>\n<\/ul>\n<h3><span class=\"notion-enable-hover\" data-token-index=\"0\">Transaction-based synchronisation<\/span><\/h3>\n<p><strong>1) Source system integration<\/strong><\/p>\n<p>We retrieve transaction data (e.g. sales, inventory movements) from the accounting company's SQL system via API.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>2.) ETL process<\/strong><\/p>\n<p>Technology: SQL Server Integration Services (SSIS)<\/p>\n<p>Process:<\/p>\n<ul>\n<li>Get data from API<\/li>\n<li>Validation and transformation<\/li>\n<li>Transaction-level insertion into SQL database<\/li>\n<li>Logging and error handling<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>3.) Synchronisation logic<\/strong><\/p>\n<p>Each record has a LastModified timestamp field.<\/p>\n<p>The SSIS package updates only those records that have been modified.<\/p>\n<h3>Power BI integration<\/h3>\n<p><strong>1.) Data connection<\/strong><\/p>\n<p>Power BI connects to the SQL Server via a DirectQuery connection.<\/p>\n<p>Queries are optimised so as not to overload the VPS.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>2.) Report structure<\/strong><\/p>\n<p>Main reports:<\/p>\n<ul>\n<li>Sales dashboard<\/li>\n<li>Stock rotation statement<\/li>\n<li>Transaction log analysis<\/li>\n<\/ul>\n<p>Update frequency: automatic data update every 15 minutes)<\/p>\n<h3>Results<\/h3>\n<p>Frequency of data updates: <strong>15min<\/strong><\/p>\n<p>Power BI load time: <strong>&lt;5 seconds<\/strong><\/p>\n<p>Data quality error rate: <strong>&lt;1%<\/strong><\/p>\n<p>Customer satisfaction: <strong>Featured on<\/strong><\/p>\n<h3>Lessons and suggestions<\/h3>\n<p>Transaction-based synchronisation significantly reduces data duplication and errors.<\/p>\n<p>Having your own VPS gives you flexibility.<\/p>\n<p>Using Power BI DirectQuery is fast, but only works effectively with a well-optimized SQL structure.<\/p>","protected":false},"excerpt":{"rendered":"<p>Our client makes business decisions on a daily basis based on Power BI reports that require a real-time, reliable source of data. 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.<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[],"class_list":["post-1851","post","type-post","status-publish","format-standard","hentry","category-esettanulmany"],"_links":{"self":[{"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/posts\/1851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/comments?post=1851"}],"version-history":[{"count":7,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/posts\/1851\/revisions"}],"predecessor-version":[{"id":1977,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/posts\/1851\/revisions\/1977"}],"wp:attachment":[{"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/media?parent=1851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/categories?post=1851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitnext.pixdev.hu\/en_gb\/wp-json\/wp\/v2\/tags?post=1851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}