{"id":1313,"date":"2023-01-05T09:38:47","date_gmt":"2023-01-05T07:38:47","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1313"},"modified":"2023-01-05T09:38:47","modified_gmt":"2023-01-05T07:38:47","slug":"process-event-hub-data-with-stream-analysis-and-push-them-into-a-dimensional-model","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1313","title":{"rendered":"Process Event Hub data with Stream Analysis and push them into a dimensional model"},"content":{"rendered":"\n<p>In my scenario, I get 1000 JSON files per day, which must be analyzed with Power BI. So, I will get approx. 380 million rows per year. The system has an initial load of circa. 500 Mio. rows. So, I think a data model is mandatory to perform analysis and a live watch of the data. To do this, I need some dimensions which must be connected to the fact data.<\/p>\n\n\n\n<p>I push the data via a C# script, at this point, many thanks to a very good colleague \u2013 Stephan Tomke \u2013 for providing me this awesome program, to a Azure Event Hub and staging the data inside a SQL database. In this table, I don\u2019t have aby relationships to the dimensions. To perform this, it is not a option to do this with SSIS, ADF or some other ETL tools, because the business needs real time data.<\/p>\n\n\n\n<p>To do a real time processing, I decided to add a table trigger inside the SQL database to get the needed identifiers from the dimensions:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>alter TRIGGER TR_Audit_Employees\r\nON dbo.eh\r\nFOR INSERT\r\nAS\r\nINSERT INTO dbo.eh2\r\n(\r\n    Id,\r\n    TotalNumber,\r\n    SuccessfulUploads,\r\n    FailedUploads,\r\n    ProcessDate,\r\n    UploadTypeId,\r\n    UploadStatus,\r\n    ElapsedTime,\r\n    UploadSize,\r\n    ErrorCode,\r\n    ErrorMessage\r\n)\r\nselect Id,\r\n       TotalNumber,\r\n       SuccessfulUploads,\r\n       FailedUploads,\r\n       ProcessDate,\r\n       (\r\n           select id from dbo.uploadtype x where d.uploadtype = x.uploadtype\r\n       ),\r\n       d.UploadStatus,\r\n       ElapsedTime,\r\n       UploadSize,\r\n       ErrorCode,\r\n       ErrorMessage\r\nFROM inserted D\r\n\r\n\r\n<\/code><\/pre>\n\n\n\n<p>Now, I can connect to the tables and built up an efficient model:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/01\/image-6.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"471\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/01\/image-6.png\" alt=\"\" class=\"wp-image-1314\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/01\/image-6.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/01\/image-6-300x150.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/01\/image-6-768x383.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>If you add an automatic page refresh or using a change detection measure, you can provide a near real time analysis.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my scenario, I get 1000 JSON files per day, which must be analyzed with Power BI. So, I will get approx. 380 million rows per year. The system has an initial load of circa. 500 Mio. rows. So, I &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1313\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1313"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1313"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1313\/revisions"}],"predecessor-version":[{"id":1315,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1313\/revisions\/1315"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}