{"id":11975,"date":"2018-11-09T18:01:34","date_gmt":"2018-11-09T17:01:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/"},"modified":"2018-11-09T18:01:34","modified_gmt":"2018-11-09T17:01:34","slug":"pass-summit-day-4","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/","title":{"rendered":"Pass summit &#8211; day 4"},"content":{"rendered":"<h2>Optimizing Multi-Billion Row Tables in Tabular in 2018<\/h2>\n<p>&nbsp;<\/p>\n<p>I wanted to attend the session moderated by Marco Russo to see his approach of optimizing performance in Tabular model<br \/>\nThe first thing to understand is how the data is stored and organized in a Tabular model. It is of course using the xVelocity in-memory capabilities with the Vertipac column storage layout engine. It organizes the data, compressing\u00a0 it by column,\u00a0 in combination with a dictionary encoding mechanism.<br \/>\nTherefore the most important aspects in a model to take care at are:<\/p>\n<ul>\n<li>Reducing the number of distinct values of columns<\/li>\n<li>Focus on columns with many distinct values<\/li>\n<\/ul>\n<p>What impacts the performance of data retrieval is also\u00a0 the relationships in the model that define the cardinality. The higher the cardinality is, the slower the query will be.<\/p>\n<p>In order to measure there criteria the following tools are available:<\/p>\n<ul>\n<li>Vertipac analyzer: it can help you to get insight on your tabular model, to get information on the number of items in your columns and their size.<\/li>\n<li>DAX studio: it is an open source that you can download visiting <a href=\"https:\/\/Daxstudio.org\">https:\/\/Daxstudio.org<\/a><\/li>\n<\/ul>\n<p>DAX Studio will help you to analyze how your queries are performing and you will be able to see how the performance is related to the number of cores, the size of the columns queried and as already said above the size of the cardinality involved.<\/p>\n<p>What is recommended for tabular model is to have the fastest CPU speed (&gt;= 3GHz) and CPU L2\/L3 large caches; large cache of the CPU is essential to avoid quickly performance problems, but having to many CPU can be counterproductive, having more sockets can negatively impact your performance. Fastest RAM is also recommended(&gt;=3200). You have to be aware of the latency of memory and storage access and see the huge impact it has on the performance.<\/p>\n<p>Marco delivers best practice using the Azure tiers available<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg\" alt=\"IMG_1081\" width=\"300\" height=\"225\" class=\"alignnone size-medium wp-image-29281\" \/><\/a><\/p>\n<p>The way you can influence having too high cardinality between fact and dimension is reduce the dimension with sub category but also to reduce you fact by splitting it and doing aggregates. You have to re-shape your data model.<\/p>\n<p>The great thting is that a new &#8220;Dual storage mode and aggregation option&#8221; is a new feature in Power BI Premium that enable to defined aggregation table of your big fact tables that do not fit in memory within Power BI.<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1082.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1082.jpg\" alt=\"IMG_1082\" width=\"300\" height=\"225\" class=\"alignnone size-medium wp-image-29282\" \/><\/a><\/p>\n<p>Again an interesting data analytic day&#8230;stay tuned..<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Optimizing Multi-Billion Row Tables in Tabular in 2018 &nbsp; I wanted to attend the session moderated by Marco Russo to see his approach of optimizing performance in Tabular model The first thing to understand is how the data is stored and organized in a Tabular model. It is of course using the xVelocity in-memory capabilities [&hellip;]<\/p>\n","protected":false},"author":38,"featured_media":11976,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1183,294,955,229,368],"tags":[644,1490,51],"type_dbi":[],"class_list":["post-11975","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-data","category-business-intelligence","category-cloud","category-database-administration-monitoring","category-development-performance","tag-performance-tuning","tag-power-bi","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Pass summit - day 4 - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pass summit - day 4\" \/>\n<meta property=\"og:description\" content=\"Optimizing Multi-Billion Row Tables in Tabular in 2018 &nbsp; I wanted to attend the session moderated by Marco Russo to see his approach of optimizing performance in Tabular model The first thing to understand is how the data is stored and organized in a Tabular model. It is of course using the xVelocity in-memory capabilities [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-09T17:01:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"980\" \/>\n\t<meta property=\"og:image:height\" content=\"735\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Christophe Cosme\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christophe Cosme\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/\"},\"author\":{\"name\":\"Christophe Cosme\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\"},\"headline\":\"Pass summit &#8211; day 4\",\"datePublished\":\"2018-11-09T17:01:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/\"},\"wordCount\":411,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/IMG_1081.jpg\",\"keywords\":[\"Performance Tuning\",\"Power BI\",\"SQL Server\"],\"articleSection\":[\"Big Data\",\"Business Intelligence\",\"Cloud\",\"Database Administration &amp; Monitoring\",\"Development &amp; Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/\",\"name\":\"Pass summit - day 4 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/IMG_1081.jpg\",\"datePublished\":\"2018-11-09T17:01:34+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/IMG_1081.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/IMG_1081.jpg\",\"width\":980,\"height\":735},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pass-summit-day-4\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pass summit &#8211; day 4\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/5cbf7c261d165a45977c85f535d6cb6a\",\"name\":\"Christophe Cosme\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"caption\":\"Christophe Cosme\"},\"description\":\"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/christophe-cosme\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Pass summit - day 4 - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/","og_locale":"en_US","og_type":"article","og_title":"Pass summit - day 4","og_description":"Optimizing Multi-Billion Row Tables in Tabular in 2018 &nbsp; I wanted to attend the session moderated by Marco Russo to see his approach of optimizing performance in Tabular model The first thing to understand is how the data is stored and organized in a Tabular model. It is of course using the xVelocity in-memory capabilities [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/","og_site_name":"dbi Blog","article_published_time":"2018-11-09T17:01:34+00:00","og_image":[{"width":980,"height":735,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg","type":"image\/jpeg"}],"author":"Christophe Cosme","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Christophe Cosme","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/"},"author":{"name":"Christophe Cosme","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"headline":"Pass summit &#8211; day 4","datePublished":"2018-11-09T17:01:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/"},"wordCount":411,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg","keywords":["Performance Tuning","Power BI","SQL Server"],"articleSection":["Big Data","Business Intelligence","Cloud","Database Administration &amp; Monitoring","Development &amp; Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/","url":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/","name":"Pass summit - day 4 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg","datePublished":"2018-11-09T17:01:34+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_1081.jpg","width":980,"height":735},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pass-summit-day-4\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Pass summit &#8211; day 4"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a","name":"Christophe Cosme","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","caption":"Christophe Cosme"},"description":"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.","url":"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11975"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11975\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/11976"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11975"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}