{"id":14750,"date":"2016-02-23T12:41:20","date_gmt":"2016-02-23T12:41:20","guid":{"rendered":"https:\/\/www.whizlabs.com\/?p=14750"},"modified":"2020-08-31T12:22:13","modified_gmt":"2020-08-31T12:22:13","slug":"dimensional-modeling","status":"publish","type":"post","link":"https:\/\/www.whizlabs.com\/blog\/dimensional-modeling\/","title":{"rendered":"Dimensional modeling"},"content":{"rendered":"

After having seen a few blog posts on security topics, we will turn our attention to \u2018Data warehousing concepts\u2019 and more specifically the modeling techniques in this post. This post assumes knowledge of basic databases like tables and fields.<\/p>\n

We have already seen the meaning of a Data warehouse, the reasons for creating a data warehouse and the components of a Data warehouse in earlier posts.<\/p>\n

Kimball approach to designing Data warehouses:<\/h2>\n

Ralph Kimball preferred the bottom-up approach to designing data warehouses. Since the data warehouse is considered to be the union of all its data marts in the Kimball approach, it is said to be the bottom- up approach.<\/p>\n

The picture below illustrates this concept. The data from different source systems (legacy systems, ERP systems, CRM systems, flat files) is cleaned and loaded onto the different data marts. The data marts are in 3NF (third normal form) and the data warehouse is the union of all its data marts.<\/p>\n

\"kimball-vs-inmon4\"<\/a><\/p>\n

A few facts regarding the Kimball approach:<\/p>\n

    \n
  1. The data marts cater to different business processes(related to purchasing, sales or inventory)<\/li>\n
  2. The data marts themselves are in 3NF(third normal form)<\/li>\n
  3. The DW is in a \u2018star schema\u2019 and it is created using dimensional modeling technique.<\/li>\n
  4. \u2018Dimensional modeling\u2019 makes use of fact tables and dimensional tables<\/li>\n<\/ol>\n

    \n

    Primary key, Foreign key and more:<\/h2>\n

    Before we get deeper into the different facts of dimensional modeling, let us first see the definitions of primary key, foreign key and surrogate key. These concepts are the fundamental building blocks in the world of data bases, data warehousing and data analytics<\/a>.<\/p>\n

    Primary key:<\/p>\n

    A \u2018primary key\u2019 is a value that uniquely identifies the table. It is important to note that the primary key cannot take null values and there can be only one primary key in a table.<\/p>\n

    Foreign key:<\/p>\n

    A \u2018foreign key\u2019 is the \u2018primary key\u2019 in another table. Foreign keys can take null values.<\/p>\n

    For example, in the picture shown, the PK in the \u2018customer table\u2019 is \u2018CustID\u2019 and it is a FK in the \u2018Order table\u2019.<\/p>\n

    \"CustomerOrderTable\"<\/a><\/p>\n

    Surrogate keys:<\/p>\n

    Surrogate keys, on the other hand are an alternate to the primary keys and are naturally generated by system.\u00a0 They can take null values. Surrogate keys are also considered to be \u201cmeaningless \u00a0keys\u201d because they are generated by the system. According to Ralph Kimball, the joins in dimensional modeling should take place between surrogate keys.<\/p>\n

    Composite keys:<\/p>\n

    Concatenation of two attributes is said to be a \u2018composite key\u2019. For example, in the example shown, \u2018CustID\u2019 and \u2018CustPhone\u2019 can be considered to be a \u2018composite key\u2019.<\/p>\n

    Dimensional modeling concepts:<\/h2>\n

    The \u2018Dimensional modeling\u2019 approach is a contrast to the E-R modeling approach.\u00a0 The E-R modeling approach results in databases that cannot be queried. To overcome this, the dimensional modeling approach is adopted.<\/p>\n

    The DM modeling approach involves \u2018fact tables\u2019 and \u2018dimensional tables\u2019. These fact tables and dimensional tables are united through a \u2018star join\u2019.<\/p>\n

    \u2018 Fact tables\u2019 represent the \u201cfacts\u201d related to any event in any organization. They are numerical and additive.\u00a0 For example, in the picture shown,\u00a0 the \u2018Sales Fact table\u2019 shows the number of products purchased\u00a0 for a particular day.<\/p>\n

    The corresponding dimensional tables and their attributes are shown around the \u2018fact table\u2019 in a star join.<\/p>\n

    \"star\"<\/a><\/p>\n

    By means of analyzing the data in a data warehouse, smart business decisions can be taken.\u00a0 We will explore more Data warehousing concepts in subsequent posts.<\/p>\n

    \n","protected":false},"excerpt":{"rendered":"

    After having seen a few blog posts on security topics, we will turn our attention to \u2018Data warehousing concepts\u2019 and more specifically the modeling techniques in this post. This post assumes knowledge of basic databases like tables and fields. We have already seen the meaning of a Data warehouse, the reasons for creating a data warehouse and the components of a Data warehouse in earlier posts. Kimball approach to designing Data warehouses: Ralph Kimball preferred the bottom-up approach to designing data warehouses. Since the data warehouse is considered to be the union of all its data marts in the Kimball […]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[17],"tags":[700,714],"class_list":["post-14750","post","type-post","status-publish","format-standard","hentry","category-news-updates","tag-data-warehhouse","tag-dimensional-modeling"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"profile_24":false,"profile_48":false,"profile_96":false,"profile_150":false,"profile_300":false,"tptn_thumbnail":false,"web-stories-poster-portrait":false,"web-stories-publisher-logo":false,"web-stories-thumbnail":false},"uagb_author_info":{"display_name":"Pavan Gumaste","author_link":"https:\/\/www.whizlabs.com\/blog\/author\/pavan\/"},"uagb_comment_info":3,"uagb_excerpt":"After having seen a few blog posts on security topics, we will turn our attention to \u2018Data warehousing concepts\u2019 and more specifically the modeling techniques in this post. This post assumes knowledge of basic databases like tables and fields. We have already seen the meaning of a Data warehouse, the reasons for creating a data…","_links":{"self":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/14750","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/comments?post=14750"}],"version-history":[{"count":1,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/14750\/revisions"}],"predecessor-version":[{"id":75865,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/14750\/revisions\/75865"}],"wp:attachment":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/media?parent=14750"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/categories?post=14750"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/tags?post=14750"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}