{"id":13488,"date":"2015-09-18T11:00:59","date_gmt":"2015-09-18T11:00:59","guid":{"rendered":"https:\/\/www.whizlabs.com\/?p=13488"},"modified":"2020-08-31T12:23:34","modified_gmt":"2020-08-31T12:23:34","slug":"data-warehousing-concepts","status":"publish","type":"post","link":"https:\/\/www.whizlabs.com\/blog\/data-warehousing-concepts\/","title":{"rendered":"Data warehousing concepts"},"content":{"rendered":"

Long before Hadoop and Big Data made a big splash on the IT stage, the necessity for \u201cData warehouses\u201d was growing in the business world. There was disparate data on Excel sheets, flat files, data bases and there was no single understanding of this data. We have seen all this and more in our previous blog post.<\/p>\n

\u201cData warehouses\u201d were created and a well designed data warehouse comprised of cleaned data from different sources that enables an organization to make critical business decisions.\u00a0 We dig deeper into Data warehousing by discussing the Father of Data warehousing followed by the different Data warehousing concepts in this blog post.<\/p>\n

 <\/p>\n

Father of Data warehousing:<\/h3>\n

Bill Inmon known as the father of data warehousing preferred the top down approach to designing data warehouses. Ralph Kimball on the other hand preferred a bottom up approach to building a data warehouse. Both approaches have their pros and cons and it is up to the organization to choose the approach that best suits their business needs.<\/p>\n

 <\/p>\n

Data warehousing concepts:<\/h3>\n

The data that comes from CRM systems, SCM systems, ERPs and other legacy systems will be duplicated and in different formats. Hence, this data is cleansed, extracted and loaded into the data warehouse. The data warehouse is then used to generate reports on different business goals. This post will cover the concepts related to Kimball\u2019s approach to data warehousing.<\/p>\n

Source systems:\u00a0<\/strong>The data from different systems will be in the form of flat files, legacy systems and these are the different source systems.\u00a0 The data from the different systems are natural \u201cstovepipes\u201d as their dimensions are not conformed.<\/p>\n

Data staging area:\u00a0<\/strong>The data staging area is the area between the source systems and the presentation server. It is a temporary area that holds data that needs to be cleaned, de-duplicated and transformed into data for the data warehouse. It is important to note that the data in the data staging area is not present on a single machine but it is scattered across multiple machines.<\/p>\n

Presentation server:\u00a0<\/strong>The data on the presentation server is ready for querying and preparing reports. The data can be in the form of dimensional model (Kimball model) or as relational model (Inmon model)<\/p>\n

Dimensional model:\u00a0<\/strong>The Kimball approach to data modeling is \u2018dimensional modeling\u2019. In dimensional models, data is not represented by ER diagrams but rather by dimension tables and fact tables.<\/p>\n

Dimension tables:\u00a0<\/strong>Dimension tables hold a type of information and \u2018Attributes\u2019 are values within a dimension. As an example, \u2018customer\u2019 is a type of \u2018dimension\u2019 and \u2018custid\u2019 is an attribute. Dimension tables are connected to fact tables.<\/p>\n

Fact tables:\u00a0<\/strong>\u201cFact tables\u201d contain the \u201cmeasurements of business\u201d\u00a0(Ralph Kimball). Each fact table is connected to the dimension table but not to each other.<\/p>\n

Data mart:\u00a0<\/strong>An EDW (Enterprise Data warehouse) is the sum of all \u201cdata marts\u201d. A \u201cdata mart\u201d is a smaller portion of the data warehouse and each data mart can have their dimensional models for different business requirements.<\/p>\n

OLAP:\u00a0<\/strong>OLAP is \u2018Online Analytic Processing\u2019 that is primarily used to query the data warehouse and generate reports. OLAP tools \u201cdrill down\u201d and \u201cdrill up\u201d to query the data that is in multidimensional format.<\/p>\n

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

We have explored the various concepts of Data warehousing in this blog post. Understanding these concepts will enable us to make better business decisions.<\/p>\n","protected":false},"excerpt":{"rendered":"

Long before Hadoop and Big Data made a big splash on the IT stage, the necessity for \u201cData warehouses\u201d was growing in the business world. There was disparate data on Excel sheets, flat files, data bases and there was no single understanding of this data. We have seen all this and more in our previous blog post. \u201cData warehouses\u201d were created and a well designed data warehouse comprised of cleaned data from different sources that enables an organization to make critical business decisions.\u00a0 We dig deeper into Data warehousing by discussing the Father of Data warehousing followed by the different […]<\/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],"class_list":["post-13488","post","type-post","status-publish","format-standard","hentry","category-news-updates","tag-data-warehhouse"],"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":0,"uagb_excerpt":"Long before Hadoop and Big Data made a big splash on the IT stage, the necessity for \u201cData warehouses\u201d was growing in the business world. There was disparate data on Excel sheets, flat files, data bases and there was no single understanding of this data. We have seen all this and more in our previous…","_links":{"self":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/13488","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=13488"}],"version-history":[{"count":1,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/13488\/revisions"}],"predecessor-version":[{"id":75872,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/posts\/13488\/revisions\/75872"}],"wp:attachment":[{"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/media?parent=13488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/categories?post=13488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.whizlabs.com\/blog\/wp-json\/wp\/v2\/tags?post=13488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}