Table design patterns video lecture

Loading...

En provenance du cours de University of Colorado System

Data Warehouse Concepts, Design, and Data Integration

699 notes

Explore Related Videos

At Coursera, you will find the best lectures in the world. Here are some of our personalized recommendations for you

University of Colorado System

Data Warehouse Concepts, Design, and Data Integration

699 notes

Cours 2 sur 5 dans Specialization Data Warehousing for Business Intelligence

This is the second course in the Data Warehousing for Business Intelligence specialization. Ideally, the courses should be taken in sequence. In this course, you will learn exciting concepts and skills for designing data warehouses and creating data integration workflows. These are fundamental skills for data warehouse developers and administrators. You will have hands-on experience for data warehouse design and use open source products for manipulating pivot tables and creating data integration workflows.You will also gain conceptual background about maturity models, architectures, multidimensional models, and management practices, providing an organizational perspective about data warehouse development. If you are currently a business or information technology professional and want to become a data warehouse designer or administrator, this course will give you the knowledge and skills to do that. By the end of the course, you will have the design experience, software background, and organizational context that prepares you to succeed with data warehouse development projects. In this course, you will create data warehouse designs and data integration workflows that satisfy the business intelligence needs of organizations. When you’re done with this course, you’ll be able to: * Evaluate an organization for data warehouse maturity and business architecture alignment; * Create a data warehouse design and reflect on alternative design methodologies and design goals; * Create data integration workflows using prominent open source software; * Reflect on the role of change data, refresh constraints, refresh frequency trade-offs, and data quality goals in data integration process design; and * Perform operations on pivot tables to satisfy typical business analysis requests using prominent open source software

À partir de la leçon

Data Warehouse Design Practices and Methodologies

This module emphasizes data warehouse design skills. Now that you understand the multidimensional representation used by business analysts, you are ready to learn about data warehouse design using a relational database. In practice, the multidimensional representation used by business analysts must be derived from a data warehouse design using a relational DBMS.You will learn about design patterns, summarizability problems, and design methodologies. You will apply these concepts to mini case studies about data warehouse design. At the end of the module, you will have created data warehouse designs based on data sources and business needs of hypothetical organizations.

Table design patterns video lecture8:15

Rencontrer les enseignants

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to Lesson two of Module three on Data Warehouse Design Practices and

Methodologies.

I'm going to start with an important design question

that I want you to think about throughout this lesson.

Why is historical integrity important only for selected columns in dimension tables?

Lesson two builds on the basic concepts from lesson one.

Lesson two presents table design or schema patterns that are typically used for

data warehouse designs.

You will learn about three schema patterns for dimension of fact tables.

As well as history representation for dimension of fact tables.

You have three learning objectives in this lesson.

You should be able to explain table design patterns for relationships among dimension

and fact tables and history representation of dimension and fact tables.

As a more reflective goal,

you should think about the importance of historical integrity for dimension tables.

The traditional schema pattern for

a data warehouse is known as a star schema, consisting of one fact table

surrounded by dimension tables in one-to-many relationships.

The star schema can represent one data cube.

This ERD consists of four dimension entity types, Item, Customer,

Store, and TimeDim, along with one transaction fact entity type called Sales.

When converted to a table design, the Sales table has

foreign keys to each dimension table, Item, Customer, Store, and TimeDim.

In some designs the fact entity type depends on the related dimension entity

types for its primary key.

Since fact tables can have many relationships, it is generally preferred

to have an artificial identifier rather than a large combined primary key.

In this example, SalesNo is an artificial identifier for the SalesFact table.

The previous star schema only represents a data cube for sales tracking.

Additional star schemas may be required for data cubes involving shipping and

inventory.

For related business processes that share some of the dimension tables,

a star schema can be extended into a constellation schema

with multiple fact entity types, as shown in this ERD.

When converted to a table design, the inventory entity type becomes a fact table

and one of many relationships become foreign keys in the fact table.

The inventory entity type contains a number of measures, including the quantity

on hand of an item, the cost of an item and a quantity returned.

All dimension tables are shared among both fact tables, except for the supplier and

customer tables.

The constellation pattern is important because it shows shared dimensions

to represent multiple data cubes.

A data warehouse for a medium size business may have many data cubes.

A snowflake schema has multiple levels of dimension tables related to one or

more fact tables.

In this CRD the store dimension has been split into two entity types.

Store and division along with a one to many relationship from division to store.

You should consider the snowflake schema instead of the star schema for

small dimension tables that are not fully normalized.

The Store table in the previously shown star schema is not fully normalized

because division id determines division name and division manager.

Since the Store table is relatively small query performance will not

suffer much with the need to join the division table in a snowflake design.

For large dimension tables such as customer however query performance may

suffer with extra joint operations required in a snowflake schema.

Time representation is crucial for

data warehouses, because most data warehouse queries use time in conditions.

The principle usage of time is to record to occurrence of facts.

The simplest representation is a time stamp data type for a column and

a fact table.

In place of a timestamp column, many data warehouses use a foreign key to a time

dimension table, as shown in previous examples in this lesson.

Using a time dimension table supports convenient representation of

organization's specific calendar features, such as holidays, fiscal years and

week numbers that are not represented in time stamp data types.

The granularity of the time dimension table is usually in days.

If time of day is also required for a fact table, it can be added as a column in

the fact table to augment the foreign key to the time table.

A variation identified by Kimball in 2003, is the accumulating fact table.

It records the status of multiple events, rather than one event.

For example, a fact table containing a snapshot of order processing,

would include order date, shipment date, delivery date, payment date, and so on.

Each event occurrence column can be represented by a foreign

key to the timetable, along with a time of day column if needed.

For dimension tables, time representation involves a level of historical integrity.

An issue for updates to dimension tables.

When a dimension table is updated,

related fact table rows are no longer historically accurate.

For example, if the city column of customer row changes,

the related sales rows are no longer historically accurate.

Time representation's only important for

selected columns that change quickly, such as credit score for customer.

History is not typically necessary for most columns that are relatively stable.

Query results usually involve row summaries rather than individual rows, so

some inaccuracy can be tolerated.

Ralph Kimball, an early data warehouse consultant and

author, proposed three alternatives for historical integrity,

with two alternatives providing history representation.

Type one has no historical integrity, as all values are rewritten.

In a type one example the customer table has no changes for history representation.

Type two uses version numbers for unlimited history.

In the type two example the customer's table contains multiple rows for

the same customer but the entire history is represented.

The primary key of the customer table is a combination of cust id and version number

Type III uses additional columns for a limited history representation.

In a Type III example, the customer table contains just a single row for

each customer, but only a limited history can be represented.

Three states of CustCity are preserved, the current state and two past states.

However, to find the correct city value for fact table row,

a more complex query is required to match the time associated with the fact row,

to the effective date columns in the customer table.

Lesson two extended your background about data based design concepts in Lesson one.

Lesson two covered schema patterns for dimension of fact tables.

As well as time representation.

You learned about three schema patterns.

The star schema, constellation schema, and the snowflake schema.

And time representation for fact and dimension tables.

Other lessons in module three will extend beyond your background for

ability problems and practice problems.

In answer to the opening question, historical integrity is important,

only for selective columns and

dimension tables due to the nature of data warehouse queries.

Most queries contain gross summaries in the result, so

some level of inaccuracy can be tolerated.

Even for fast changing columns,

such as credit rating, the inaccuracy may be tolerable.

The impact on data warehouse query results depends on the magnitude of changes and

the frequency updating this column in the customer dimension table.

Even though credit rating changes daily,

the updates may only be propagated to a data warehouse monthly.

In addition, typical change magnitudes may be small.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play