Outline

• Extraction Transformation and Loading
• Data Marts
• Data Lakes
• Quiz
• Data Integration, Quality and Governance

Introduction

• Last week, we looked at variety of RDBMS schemas that might be used to implement a data warehouse.
• We considered the star-schema which has a collection of dimension tables connected via foreign key references to a central fact table which also has 0 or more dependent attributes.
• We said if the dimension tables further split into sub-dimension tables one can get a snowflake schema.
• We said if our warehouse has two or more star schemas potentially sharing dimension tables we get a fact constellation.
• We looked at how SQL is used in building a data warehouse's formal data cube using queries with a group by clause using either with cube or with roll up.
• We had then started looking at some specific issues related to the choice of data warehouse schema tables and attributes such as factless fact tables, and surrogate keys.
• We begin today by considering a couple more of such issues.

More Schema Specific Issues

• There are a variety of situations tat may affect how we choose and implement our dimension tables or sub-dimension tables:
  • To optimize the efficiency of a warehouse, the dimension tables should be indexed because they are frequently used for selection. Some dimensions with a fixed small number of tuples such as time (for a fixed window like ten years) can actually be pre-built.
  • Dimensions with a small number of values like booleans can be combined together into a single dimension (a so-called junk dimension) that enumerates all their possibilities.
  • When we have highly correlated data of low cardinality, we might combine/arrange dimension tables as dimension sub-dimension tables and call it an outrigger table. This might be done to reduce duplication. For example, you might have a sub-dimension of a customer dimension (which has address) that has demographic info such as average state income, average state age, state unemployment, etc.
  • If we have a rapidly changing dimension, we might split it into mini dimension tables.

Extraction, Transformation and Loading (ETL)

• Once the data warehouse has been designed, it can start to be populated with data from operational sources.
• This involves:
  • extracting the data from source systems
  • transforming the data to fit the data warehouse schema.
  • loading the data into the data warehouse.
• This ETL step is not an easy. The class DB book says this can be up 80% of the effort in setting up a data warehouse
• It also needs to be repeated at regular points in time: weekly, daily, monthly.
• Often data that accumulates between updates is temporarily housed in a staging area where all ETL operations can be executed.

ETL - What's Involved

• The extraction strategy can be either full or incremental. To do the latter you need some kind of changed data capture mechanism which may or may not be possible as some of the sources might be highly optimized and hence closed-box.
• The transformation process involves:
  • formatting - make the data into a uniform and consistent format. Say all using same character encoding, using consistent choices for male/female versus m/f versus something else, metric versus imperial, commas versus periods in numbers, etc..
  • cleansing - get rid of inconsistencies or irregularities in the data. For example, dealing missing missing or impossible values such as 01/01/1000 for a birth date.
  • aggregation and merging - multiple records referring to the same entity and refer to such entities with a consistent unique identifier. This process is often called deduplication.
  • enrichment - this involves adding derived data elements or external data to records. For example, calculating current age based on birth date or adding demographic data based on an external source.
• The loading step involves populating first the dimension tables, usually in some parallelized way. This is then followed by populating the fact table and appropriately linking up the surrogate keys. Finally, necessary indexes sould be adjusted or recreated.
• This whole process should be carefully documented and automated.

Quiz

Which of the following is true?

1. A star schema consists of a fact table and one or more associated dimension tables.
2. Switching from a finer partition of a data cube to a coarser one is called drilling down.
3. A GROUP BY clause WITH ROLLUP contains more aggregation info than a GROUP BY clause WITH CUBE.

Data Marts

• A data mart is a small scale data warehouse designed to meet the information needs of a small group of end users such as a department or business unit.
• They are often used for report generation and analysis by such groups.
• A data mart can provide more focused finance, sales, or accounting information in a format tailored to the group.
• They can also be used to offload complex queries from other data sources such as a data warehouse.
• They also allow for reporting tools that assume the data is in some customized format.
• Data marts are often implemented in an RDBMS are come in two flavors:
  • Dependent data marts which pull their data from a central data warehouse (left figure above)
  • Independent data marts which are standalone are pull data directly from the operational sources (right figure above).
• Independent data marts are often used by smaller companies that don't want to dedicate the resources to set up a full fledged data warehouse that could be used across the whole business.
• Dependent data marts have the advantage that all departments would be drawing from the same formatted and cleansed data. The same dimensions table might be used in the different data mart schemas and will mean the same thing across these schemas. I.e., it is a so-called conformed dimension.

Virtual Data Warehouses, Virtual Data Marts, Operation Data Stores

• A disadvantage of a physical data warehouse or data mart is that is both consumes physical storage and must be updated periodically.
• This can lead to the data they contain always being somewhat out of date.
• One approach called a virtual data warehouse/virtual data mart to solve this problem is to use middleware and database views to create and implement a logical data warehouse or data mart.
• Middleware is being used here to logically integrate data that may be in several databases.
• Since middleware and non-materialized database views are being used, such a set-up does not take up much physical storage beyond the middleware and view definitions.
• Any changes to the underlying data are also immediately reflected in the virtual data warehouse.
• The drawback though is that query performance of such a set-up is often much slower.
• Another approach to the out-of date data problem, is to add query facilities to the data staging area for the data warehouse. Such a set-up is called an operational data store.

Data Warehouses Versus Data Lakes

• A data lake is a large store of raw data in its native format which may be structured, unstructured, or semi-structured.
• For example, one could have a data lake of server logs, sensor data, clickstreams, social media feeds, etc.
• Analysing such lakes for trends is an important topic in big data and analytics.
• Unlike data warehouses, very little cleansing or transformation of data occurs before it enters the lake, so substantial processing is needed to perform any analysis.
• Data lake technologies are generally open-source (Hadoop) and run on low commodity hardware so tend to be cheaper in term of storage costs.
• These technologies tend to be also less mature and end-user friendly than data warehouses.

Data Integration, Data Quality, and Data Governance

• Businesses and Organizations tend to accumulate many different databases and database systems over time.
• On the other hand, they often need to provide a unified view of this data to applications and users of the companies' resources.
• Companies also often want to share their data with other companies.
• Data integration is the general set of techniques that allow us to create a unified view of data from different sources.
• We have already seen some aspects of it in terms of the ETL process for data warehouses, but we are now going to look at it for other use cases.
• As part of this, we will discuss the managerial aspects of data quality.
• Finally, we will consider data governance standards to facilitate measuring, monitoring, and improving data integration and quality practices.

Data and Process Integration

• As we said on the previous slide, data integration aims at providing a unified view and/or unified access over heterogeneous, and possibly distributed, data sources.
• To realize integration, we might need to make design trade-offs between data quality, performance, the ability to transform the data, etc.
• Since data integration is often connected to businesses, it will interact with business managements techniques such as process integration - the sequencing of tasks in a business process and the governing of data flows in such a process.
• Data and process integration are complementary because the latter aims at making the right data available to applications and human actors in order that they are able to perform their tasks with the appropriate input data.
• We will look at both in more detail on Wednesday.