{"id":920,"date":"1999-09-16T21:07:01","date_gmt":"1999-09-17T03:07:01","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=920"},"modified":"2022-09-10T11:25:35","modified_gmt":"2022-09-10T17:25:35","slug":"basics-of-data-warehouse-dw","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/basics-of-data-warehouse-dw\/","title":{"rendered":"Basic Terminology of Data Warehousing (DW) for Business Intelligence (BI)"},"content":{"rendered":"

Why build a Data Warehouse\/ Data Lake?<\/span><\/h1>\n

Transactions are essential, but the capture is of small value compared to the data’s value.\u00a0 The purpose of a Data Warehouse (DW) is to create a place to clean, merge and keep historical data for reporting to gain insight into the business process.\u00a0 A DW is a foundation for Business Intelligence (BI).\u00a0<\/span><\/p>\n

Data is the treasure trove that we are sitting on but never notice because that data has to be turned into information, then into knowledge, and finally into income.\u00a0 A DW is where all the data is gathered and massaged.\u00a0 This stored data is now in a reportable and comprehensive format to gain insight.\u00a0<\/span><\/p>\n

All DW projects begin by standing before a board and explain why they should invest money in a DW.\u00a0 Here are the high-level reasons to consider.<\/span><\/p>\n

    \n
  1. Single Version of the Truth<\/span><\/strong>, create one place that everyone can source data and have all reporting with the same numbers as its base data.\u00a0 <\/span>See definition for SOR below.\u00a0 <\/span><\/em>A data warehouse integrates data from multiple data sources.\u00a0 Once data is in the data warehouse, it will track all history of changes.<\/li>\n
  2. Data Aggregation<\/span><\/strong>, we live in the era of big data.\u00a0 A human can’t consume and interpret this amount of data.\u00a0 DW supports one version of the truth as all reporters will have the same numbers at a high level.<\/span><\/li>\n
  3. Drilling down<\/span><\/strong>, we have to support and prove the high-level numbers.\u00a0 Consumers can view summary numbers or data aggregates called Key Process Indicators (KPI).\u00a0 Drill down on the KPIs to get the details that make up that aggregate value.\u00a0 This lineage gives more trust to the consumers of the data.<\/span><\/li>\n
  4. Trends across time:\u00a0<\/span><\/strong>Store historical information in one place and the right context or grain.\u00a0 This data will persists for the lifetime of the DW and give historical context.\u00a0 History has a way of repeating patterns.<\/span><\/li>\n
  5. Correlation<\/span><\/strong> of multiple data sources, silo business processes store its data in its databases.\u00a0 We need to see the big picture of how a business process is impacting each other if there are relationships<\/span><\/li>\n<\/ol>\n

    Some technical terms that you will need to know when making a Data Warehouse (DW)<\/span><\/h3>\n

    Two names come up as founders in the data warehouse space, and we should fundamentally know the difference between the two paradigms.<\/b><\/p>\n

    Bill Inmon’s paradigm<\/b>: Data warehouse is one part of the overall business intelligence system.\u00a0 An enterprise has one data warehouse, and data marts source their information from the data warehouse.\u00a0 In the data warehouse, information is stored in 3rd normal form.<\/p>\n

    Ralph Kimball’s paradigm<\/b>: Data warehouse is the conglomerate of all data marts within the enterprise.\u00a0 Information is always stored in the dimensional model.<\/p>\n

    The dimensional model<\/strong> includes fact tables and dimension tables.\u00a0 Fact tables connect to one or more dimension tables, but fact tables do not have direct relationships with one another.\u00a0 Dimensions and hierarchies are represented by dimension tables.\u00a0 Attributes are the non-key columns in the dimension tables.<\/p>\n

    Dimension<\/b>: <\/span><\/strong>A category of information.<\/p>\n

    Dimension<\/span><\/strong> tables – usually give detailed information about the attributes for the ids (foreign keys) stored on fact tables. It is a table of attributes for that category of information.<\/span><\/p>\n

    Attribute<\/b>: A unique level within a dimension.<\/p>\n

    Grain or Granularity – <\/span><\/strong>\u00a0The grain will be the lowest information level stored in the fact table.\u00a0 The lower the level of detail, the larger the data amount in the fact table. How\u00a0detailed your questions need to be will determine how close to the transaction you need to store the data in the DW.\u00a0<\/span><\/p>\n

    Surrogate key<\/span><\/strong> – a new key developed to be used by a new system-moves data from the source system to the new system like the DW.\u00a0 It is excellent at creating historical rows for primary system keys as changes occur.<\/span><\/p>\n

    Fact<\/span><\/strong> tables<\/strong> usually answer who, what, where, when, and how much, kind of questions.\u00a0 Many dimension tables support a fact table, and that defines the context.\u00a0 The fact usually supports measures of interest\u2014this the basis for a star schema.<\/span><\/p>\n

    There are three types of facts:<\/p>\n