{"id":33419,"date":"2021-01-21T10:26:45","date_gmt":"2021-01-21T10:26:45","guid":{"rendered":"https:\/\/www.testpreptraining.com\/tutorial\/?page_id=33419"},"modified":"2021-01-21T10:27:25","modified_gmt":"2021-01-21T10:27:25","slug":"overview-of-dimension-granularity","status":"publish","type":"page","link":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/","title":{"rendered":"Overview of Dimension Granularity"},"content":{"rendered":"\n<p>Since users will require to dimension fact data at different dimension granularity or specificity for different purposes. Moreover, in these scenarios, users will require a time dimension with a different grain or level of detail for each of these different fact tables. Also, you could define a new database dimension as a time dimension with this different grain, there is an easier way with Analysis Services. In general, in analysis services, when a dimension is used within a measure group, the grain of the data within that dimension is based on the key attribute of the dimension.<\/p>\n\n\n\n<p>In order to specify a grain for a cube dimension other than the default grain, follow the below steps<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Firstly, you must modify the granularity attribute for a cube dimension as used within a particular measure group on the\u00a0<strong>Dimension Usage<\/strong>\u00a0tab of Cube Designer. <\/li><li>Secondly, when you change the grain of a dimension within a specific measure group to an attribute other than the key attribute for that dimension, then you must guarantee that all other attributes in the measure group are directly or indirectly related to new granularity attribute. <\/li><li>In the next step, you must specify attribute relationships between all other attributes and the attribute that is specified as the granularity attribute in the measure group. <\/li><li>Now, you should define additional attribute relationships rather than move attribute relationships. <\/li><li>Further, the attribute that is specified as the granularity attribute effectively becomes the key attribute within the measure group for the remaining attributes in the dimension. <\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"adding-tables-and-defining-the-sales-quotas-measure-group\"><strong>How to add tables and defining the Sales Quotas Measure Group?<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>Firstly, switch to the\u00a0<strong>Adventure Works DW 2012<\/strong>\u00a0data source view.<\/li><li>Then, right-click anywhere in the\u00a0<strong>Diagram Organizer<\/strong>\u00a0pane, click\u00a0<strong>New Diagram<\/strong>, and then name the diagram\u00a0<strong>Sales Quotas<\/strong>.<\/li><li>Thirdly, grag the\u00a0<strong>Employee<\/strong>,\u00a0<strong>Sales Territory<\/strong>, and\u00a0<strong>Date<\/strong>\u00a0tables from the\u00a0<strong>Tables<\/strong>\u00a0pane to the\u00a0<strong>Diagram<\/strong>\u00a0pane.<\/li><li>Then, add the\u00a0<strong>FactSalesQuota<\/strong>\u00a0table to the\u00a0<strong>Diagram<\/strong>\u00a0pane by right-clicking anywhere in the\u00a0<strong>Diagram<\/strong>\u00a0pane <\/li><li>Next, select\u00a0Add\/Remove Tables. Notice that the\u00a0<strong>SalesTerritory<\/strong>\u00a0table is linked to the\u00a0<strong>FactSalesQuota<\/strong>\u00a0table through the\u00a0<strong>Employee<\/strong>\u00a0table.<\/li><li>Review the columns in the\u00a0<strong>FactSalesQuota<\/strong>\u00a0table and then explore the data in this table.Notice that the grain of the data within this table is the calendar quarter, which is the lowest level of detail in the FactSalesQuota table.<\/li><li>Now, in Data Source View Designer, change the\u00a0<strong>FriendlyName<\/strong>\u00a0property of the\u00a0<strong>FactSalesQuota<\/strong>\u00a0table to\u00a0<strong>SalesQuotas<\/strong>.<\/li><li>Further, switch to the Analysis Services Tutorial cube, and then click the\u00a0<strong>Cube Structure<\/strong>\u00a0tab.<\/li><li>Then, right-click anywhere in the\u00a0<strong>Measures<\/strong>\u00a0pane, click\u00a0<strong>New Measure Group<\/strong>, click\u00a0<strong>SalesQuotas<\/strong>\u00a0in the\u00a0<strong>New Measure Group<\/strong>\u00a0dialog box, and then click\u00a0<strong>OK<\/strong>.The\u00a0<strong>Sales Quotas<\/strong>\u00a0measure group appears in the\u00a0<strong>Measures<\/strong>\u00a0pane. <\/li><li>Also, expand the\u00a0<strong>Sales Quotas<\/strong>\u00a0measure group.<\/li><li>Here, in the\u00a0<strong>Measures<\/strong>\u00a0pane, select\u00a0<strong>Sales Amount Quota<\/strong>, and then set the value for the\u00a0<strong>FormatString<\/strong>\u00a0property to\u00a0<strong>Currency<\/strong>\u00a0in the Properties window.<\/li><li>Select the\u00a0<strong>Sales Quotas Count<\/strong>\u00a0measure, and then type\u00a0<strong>#,#<\/strong>\u00a0as the value for the\u00a0<strong>FormatString<\/strong>\u00a0property in the Properties window.<\/li><li>Delete the\u00a0<strong>Calendar Quarter<\/strong>\u00a0measure from the\u00a0<strong>Sales Quotas<\/strong>\u00a0measure group. Analysis Services detected the column that underlies the Calendar Quarter measure as a column that contains measures. However, this column and the CalendarYear column contain the values that you will use to link the Sales Quotas measure group to the Date dimension later in this topic.<\/li><li>In the\u00a0Measures\u00a0pane, right-click the\u00a0Sales Quotas\u00a0measure group, and then click\u00a0New Measure. The\u00a0New Measure\u00a0dialog box opens, containing the available source columns for a measure with a usage type of\u00a0Sum.<\/li><li>In the\u00a0New Measure\u00a0dialog box, select\u00a0Distinct count\u00a0in the\u00a0Usage\u00a0list, verify that\u00a0SalesQuotas\u00a0is selected in the\u00a0Source table\u00a0list, select\u00a0EmployeeKey\u00a0in the\u00a0Source column\u00a0list, and then click\u00a0OK. Notice that the measure is created in a new measure group named\u00a0<strong>Sales Quotas 1<\/strong>. Distinct count measures in SQL Server are created in their own measure groups to maximize processing performance.<\/li><li>Lastly, change the value for the\u00a0<strong>Name<\/strong>\u00a0property for the\u00a0<strong>Employee Key Distinct Count<\/strong>\u00a0measure to\u00a0<strong>Sales Person Count<\/strong>, and then type\u00a0<strong>#,#<\/strong>\u00a0as the value for the\u00a0<strong>FormatString<\/strong>\u00a0property.<\/li><\/ul>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter\"><a href=\"https:\/\/www.testpreptraining.ai\/analyzing-data-with-microsoft-power-bi-da-100-free-test\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" src=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test.png\" alt=\"Microsoft Exam DA-100 Free Practice Test\"\/><\/a><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Since users will require to dimension fact data at different dimension granularity or specificity for different purposes. Moreover, in these scenarios, users will require a time dimension with a different grain or level of detail for each of these different fact tables. Also, you could define a new database dimension as a time dimension with&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"footnotes":""},"categories":[174],"tags":[1830,3955,3954],"class_list":["post-33419","page","type-page","status-publish","hentry","category-microsoft-azure","tag-da-100-study-guide","tag-microsoft-exam-da-100-exam-questions","tag-microsoft-exam-da-100-free-practice-test"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Overview of Dimension Granularity - Testprep Training Tutorials<\/title>\n<meta name=\"description\" content=\"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!\" \/>\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.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Overview of Dimension Granularity - Testprep Training Tutorials\" \/>\n<meta property=\"og:description\" content=\"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/\" \/>\n<meta property=\"og:site_name\" content=\"Testprep Training Tutorials\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-21T10:27:25+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test.png\" \/>\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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/\",\"name\":\"Overview of Dimension Granularity - Testprep Training Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\"},\"datePublished\":\"2021-01-21T10:26:45+00:00\",\"dateModified\":\"2021-01-21T10:27:25+00:00\",\"description\":\"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Overview of Dimension Granularity\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"name\":\"Testprep Training Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\",\"name\":\"Testprep Training\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"contentUrl\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"width\":583,\"height\":153,\"caption\":\"Testprep Training\"},\"image\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Overview of Dimension Granularity - Testprep Training Tutorials","description":"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!","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.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/","og_locale":"en_US","og_type":"article","og_title":"Overview of Dimension Granularity - Testprep Training Tutorials","og_description":"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!","og_url":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/","og_site_name":"Testprep Training Tutorials","article_modified_time":"2021-01-21T10:27:25+00:00","og_image":[{"url":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/","url":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/","name":"Overview of Dimension Granularity - Testprep Training Tutorials","isPartOf":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website"},"datePublished":"2021-01-21T10:26:45+00:00","dateModified":"2021-01-21T10:27:25+00:00","description":"Boost your chances to get ready to qualify Microsoft Exam DA-100 with latest practice test. Learn more about Dimension Granularity Now!","breadcrumb":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/overview-of-dimension-granularity\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.testpreptraining.ai\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Overview of Dimension Granularity"}]},{"@type":"WebSite","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","name":"Testprep Training Tutorials","description":"","publisher":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization","name":"Testprep Training","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","contentUrl":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","width":583,"height":153,"caption":"Testprep Training"},"image":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/33419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/comments?post=33419"}],"version-history":[{"count":5,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/33419\/revisions"}],"predecessor-version":[{"id":33428,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/33419\/revisions\/33428"}],"wp:attachment":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/media?parent=33419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/categories?post=33419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/tags?post=33419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}