{"id":78,"date":"2024-08-08T06:12:03","date_gmt":"2024-08-08T06:12:03","guid":{"rendered":"https:\/\/dbserv.animarmedia.com\/?page_id=78"},"modified":"2024-08-26T08:54:36","modified_gmt":"2024-08-26T08:54:36","slug":"project-dwh-is-finally-completed","status":"publish","type":"page","link":"https:\/\/dbserv.animarmedia.com\/?page_id=78","title":{"rendered":"Project DWH is finally completed!"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text admin_label=”Text” _builder_version=”4.27.0″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n

\n

Task\"\"<\/span><\/h2>\n
Multiple operators receive calls and each call creates a record in the database (SQL Server). Our task was to combine and unify those databases and tables into one single unique DWH (Data Warehouse) system which later on the client should be able to run various reports using the report tool provided. The reports are running on Power BI.<\/span><\/div>\n
<\/div>\n
\n

Business Challenge<\/span><\/h2>\n
\n
The problem we faced was that databases did not have unique primary key or referral constraints. Thus many records could potentially be doubled or tripled, or records in the referral tables did not have the key record in.<\/span><\/div>\n<\/div>\n
\n
For example one of the customers changed their name. He was renamed in the dimension tables but all records with the old name was left the same in the transactional tables \u2013 (tables where actual data is)! Thus – lost records. Nightmare!<\/span><\/div>\n<\/div>\n
\n
Also there were quite a few problems with current documentation being very outdated and we had to make some assumptions.<\/span><\/div>\n<\/div>\n
\n
However we have managed to get through all of this finally!<\/span><\/div>\n<\/div>\n

Successful Solution<\/span><\/h2>\n
\n
Our pipeline is PROD -> STAGING ->DWH with transformation procedures and procedures to catch up exceptions mentioned above.<\/span><\/div>\n<\/div>\n
\n
Our first stage was TEST-> STAGING ->DWH with transformation procedures and procedures to catch up exceptions mentioned above.<\/span><\/div>\n<\/div>\n
\n
Another challenge we came up with is to refresh TEST env with PROD data, giving the fact there was not much storage space for the TEST. We came up with compression of the old data deletion, partitioning and index rebuilds techniques.<\/span><\/div>\n<\/div>\n
\n
But the refresh data was still one of our biggest issues.<\/span><\/div>\n<\/div>\n
\n
All in all Project took 6 months from the start , first 3 months went on discovery, how the current process works with limited or old documentation in place, setting up TEST environment with limited storage capacity and automate data refresh from PROD to TEST.<\/span><\/div>\n<\/div>\n

Benefits<\/span><\/h2>\n
\n
There is 790 371 876 rows replicated and transformed to DWH and 530 Gb data as per initial DWH set up and now it is receiving 1 million rows on a daily basis.<\/span><\/div>\n<\/div>\n
\n
The client is using Power BI and quite happy ( I hope) with our skills and effort we have made.<\/span><\/div>\n<\/div>\n<\/div>\n<\/div>\n

[\/et_pb_text][et_pb_button button_url=”https:\/\/dbserv.animarmedia.com\/?page_id=76″ button_text=”Read other case studies” button_alignment=”center” module_class=”customorange” _builder_version=”4.27.0″ _module_preset=”default” button_use_icon=”off” box_shadow_style=”none” global_colors_info=”{}”][\/et_pb_button][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

Task Multiple operators receive calls and each call creates a record in the database (SQL Server). Our task was to combine and unify those databases and tables into one single unique DWH (Data Warehouse) system which later on the client should be able to run various reports using the report tool provided. The reports are […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"

\r\n

Task\"\"<\/span><\/h2>\r\n
Multiple operators receive calls and each call creates a record in the database (SQL Server). Our task was to combine and unify those databases and tables into one single unique DWH (Data Warehouse) system which later on the client should be able to run various reports using the report tool provided. The reports are running on Power BI.<\/span><\/div>\r\n
<\/div>\r\n
\r\n

Business Challenge<\/span><\/h2>\r\n
\r\n
The problem we faced was that databases did not have unique primary key or referral constraints. Thus many records could potentially be doubled or tripled, or records in the referral tables did not have the key record in.<\/span><\/div>\r\n<\/div>\r\n
\r\n
For example one of the customers changed their name. He was renamed in the dimension tables but all records with the old name was left the same in the transactional tables \u2013 (tables where actual data is)! Thus - lost records. Nightmare!<\/span><\/div>\r\n<\/div>\r\n
\r\n
Also there were quite a few problems with current documentation being very outdated and we had to make some assumptions.<\/span><\/div>\r\n<\/div>\r\n
\r\n
However we have managed to get through all of this finally!<\/span><\/div>\r\n<\/div>\r\n

Successful Solution<\/span><\/h2>\r\n
\r\n
Our pipeline is PROD -> STAGING ->DWH with transformation procedures and procedures to catch up exceptions mentioned above.<\/span><\/div>\r\n<\/div>\r\n
\r\n
Our first stage was TEST-> STAGING ->DWH with transformation procedures and procedures to catch up exceptions mentioned above.<\/span><\/div>\r\n<\/div>\r\n
\r\n
Another challenge we came up with is to refresh TEST env with PROD data, giving the fact there was not much storage space for the TEST. We came up with compression of the old data deletion, partitioning and index rebuilds techniques.<\/span><\/div>\r\n<\/div>\r\n
\r\n
But the refresh data was still one of our biggest issues.<\/span><\/div>\r\n<\/div>\r\n
\r\n
All in all Project took 6 months from the start , first 3 months went on discovery, how the current process works with limited or old documentation in place, setting up TEST environment with limited storage capacity and automate data refresh from PROD to TEST.<\/span><\/div>\r\n<\/div>\r\n

Benefits<\/span><\/h2>\r\n
\r\n
There is 790 371 876 rows replicated and transformed to DWH and 530 Gb data as per initial DWH set up and now it is receiving 1 million rows on a daily basis.<\/span><\/div>\r\n<\/div>\r\n
\r\n
The client is using Power BI and quite happy ( I hope) with our skills and effort we have made.<\/span><\/div>\r\n
<\/div>\r\n<\/div>\r\n<\/div>\r\n
[button link=\"https:\/\/dbserv.animarmedia.com\/?page_id=76\" type=\"big\" color=\"orange\"] Read other case studies[\/button]<\/div>\r\n<\/div>\r\n
\r\n\r\n[popup_trigger id=\"19\" tag=\"button\" classes=\"contactform\"]Send a request[\/popup_trigger]\r\n\r\n<\/div>","_et_gb_content_width":"","footnotes":""},"class_list":["post-78","page","type-page","status-publish","hentry"],"yoast_head":"\nProject DWH is finally completed! - Database Management & DevOps Management Company | DB Serv<\/title>\n<meta name=\"robots\" content=\"noindex, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Project DWH is finally completed! - Database Management & DevOps Management Company | DB Serv\" \/>\n<meta property=\"og:description\" content=\"Task Multiple operators receive calls and each call creates a record in the database (SQL Server). Our task was to combine and unify those databases and tables into one single unique DWH (Data Warehouse) system which later on the client should be able to run various reports using the report tool provided. The reports are […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbserv.animarmedia.com\/?page_id=78\" \/>\n<meta property=\"og:site_name\" content=\"Database Management & DevOps Management Company | DB Serv\" \/>\n<meta property=\"article:modified_time\" content=\"2024-08-26T08:54:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9-300x169.webp\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78\",\"url\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78\",\"name\":\"Project DWH is finally completed! - Database Management & DevOps Management Company | DB Serv\",\"isPartOf\":{\"@id\":\"https:\/\/dbserv.animarmedia.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9-300x169.webp\",\"datePublished\":\"2024-08-08T06:12:03+00:00\",\"dateModified\":\"2024-08-26T08:54:36+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbserv.animarmedia.com\/?page_id=78\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage\",\"url\":\"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9.webp\",\"contentUrl\":\"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9.webp\",\"width\":1280,\"height\":720},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbserv.animarmedia.com\/?page_id=78#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbserv.animarmedia.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Project DWH is finally completed!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbserv.animarmedia.com\/#website\",\"url\":\"https:\/\/dbserv.animarmedia.com\/\",\"name\":\"Database Management & DevOps Management Company | DB Serv\",\"description\":\"Database Management Company offering expert solutions for seamless data handling. Elevate your performance with DevOps Management. Optimize, secure, and scale your DB with DBServ.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbserv.animarmedia.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Project DWH is finally completed! - Database Management & DevOps Management Company | DB Serv","robots":{"index":"noindex","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"og_locale":"en_US","og_type":"article","og_title":"Project DWH is finally completed! - Database Management & DevOps Management Company | DB Serv","og_description":"Task Multiple operators receive calls and each call creates a record in the database (SQL Server). Our task was to combine and unify those databases and tables into one single unique DWH (Data Warehouse) system which later on the client should be able to run various reports using the report tool provided. The reports are […]","og_url":"https:\/\/dbserv.animarmedia.com\/?page_id=78","og_site_name":"Database Management & DevOps Management Company | DB Serv","article_modified_time":"2024-08-26T08:54:36+00:00","og_image":[{"url":"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9-300x169.webp"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78","url":"https:\/\/dbserv.animarmedia.com\/?page_id=78","name":"Project DWH is finally completed! - Database Management & DevOps Management Company | DB Serv","isPartOf":{"@id":"https:\/\/dbserv.animarmedia.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage"},"image":{"@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage"},"thumbnailUrl":"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9-300x169.webp","datePublished":"2024-08-08T06:12:03+00:00","dateModified":"2024-08-26T08:54:36+00:00","breadcrumb":{"@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbserv.animarmedia.com\/?page_id=78"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78#primaryimage","url":"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9.webp","contentUrl":"https:\/\/dbserv.animarmedia.com\/wp-content\/uploads\/2024\/08\/622401058cc44a5667fa3e8a318d3dc9.webp","width":1280,"height":720},{"@type":"BreadcrumbList","@id":"https:\/\/dbserv.animarmedia.com\/?page_id=78#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbserv.animarmedia.com\/"},{"@type":"ListItem","position":2,"name":"Project DWH is finally completed!"}]},{"@type":"WebSite","@id":"https:\/\/dbserv.animarmedia.com\/#website","url":"https:\/\/dbserv.animarmedia.com\/","name":"Database Management & DevOps Management Company | DB Serv","description":"Database Management Company offering expert solutions for seamless data handling. Elevate your performance with DevOps Management. Optimize, secure, and scale your DB with DBServ.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbserv.animarmedia.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/pages\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=78"}],"version-history":[{"count":19,"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/pages\/78\/revisions"}],"predecessor-version":[{"id":586,"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=\/wp\/v2\/pages\/78\/revisions\/586"}],"wp:attachment":[{"href":"https:\/\/dbserv.animarmedia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}