¿Qué son los hechos y las dimensiones en el almacenamiento de datos?

What are facts and dimensions in data warehousing?

Facts and dimensions are data warehousing terms. A fact is a quantitative piece of information – such as a sale or a download. Facts are stored in fact tables, and have a foreign key relationship with a number of dimension tables.

In most dimensions, each fact joins to one and only one dimension member, and a single dimension member can be associated with multiple facts. In relational database terminology, this is referred to as a one-to-many relationship. However, it is frequently useful to join a single fact to multiple dimension members.

a cross-reference table in the data vault really is a table that stands alone, but more than that – it’s business key is used to describe transactions and other business keys. in other words an easy hit would be something like: “type code” or “code / description”.

In data warehousing, a dimension is a collection of reference information about a measurable event. In this context, events are known as “facts.” Dimensions categorize and describe data warehouse facts and measures in ways that support meaningful answers to business questions.

There are two types of dimensioning systems one is Aligned system and another is Unidirectional system.

Identify the dimensions that are true to the grain of your model. Dimension tables contain columns that describe the fact records in the fact table. Some of these columns provide descriptive information. Other columns specify how the data in the fact table is summarized to provide useful information.

Fact table contains measurements, metrics, and facts about a business process while the Dimension table is a companion to the fact table which contains descriptive attributes to be used as query constraining. Fact table helps to store report labels whereas Dimension table contains detailed data.

Cross-reference tables, sometimes referred to as XREF tables, are used for tracking the lineage of data, which systems and which records from those systems contributed to consolidated records, and also for tracking versions of data.

Conformed dimensions (otherwise known as common, master, standard or reference dimensions) are essential for enterprise data warehousing.

In a data warehouse context, a fact is the part of your data that indicates a specific occurrence or transaction, like the sale of a product or receiving a shipment a certain number of items from a supplier. If you think of our example company, Best Run Shoes, some of the facts that would appear in their data warehouse are:

Your “Promotion” dimension simply is a record of each promotion, with its attributes (start date, end date, coupon code, POS promo code, Ad Name, etc). The relationship from promo to product isn’t modeled here, since it will be reflected in the fact table.

The qualitative measures can be then linked to specific characteristics of that measure, which are called dimensions. In the data warehouse context, dimensions are pieces of data that allow you to understand and index measures in your data models. Dimensions are either characteristics of a measure or pieces of data that help contextualize the fact.