The point that distinguishes the fact table and the table of facts dimensions that the size chart contains attributes along which measurements are taken in fact table . There are some other factors that create differences between the fact table and the size table to view them, let's take a look at the comparison table below.
|Basic||The fact table contains the measurement along the attributes of a dimension table.||The dimension table contains the attributes along which the fact table calculates the metric.|
|Attribute and record||The fact table contains fewer attributes and more records.||The dimension table contains multiple attributes and fewer records.|
|Dimensions of the table||The fact table grows vertically.||The size chart grows horizontally.|
|Key||The fact table contains a primary key which a concatenation of primary keys of all dimension tables.||Each dimension table contains its primary key.|
|Creation||The fact table can only be created when the dimension tables are completed.||Dimension tables must be created first.|
|scheme||A schema contains fewer fact tables.||A scheme contains multiple number of dimensional tables.|
|attributes||The fact table can contain numeric and textual data.||The dimension table always contains textual attributes.|
Definition of the fact table
A fact table a table that contains measures along the attributes of the dimension tables. It can hold the information as low as possible. A fact table contains only summary data, called Aggregate Facts Table . The fact table almost contains the data with the printed date . Let us discuss the characteristics of a fact table.
Chained key The fact table contains the concatenated key that concatenates the primary keys of all dimension tables. The concatenated key of the fact table must uniquely identify the row in a fact table.
Data Grain The data grain shows how much measurements were stored in the fact table. The data grain should be as high as possible.
Additive measures The attributes of the fact table can be completely additives or semi-additives . The completely additive measures are those that can be easily summarized for all dimensions in the fact table. For example quantity_ordered, an attribute that can be summarized for all dimensions. For example, we can take total order quantities, for a particular customer, region, date, brand, etc. Semi-additive measures are those that can be added along some dimensions of the fact table but not all dimensions. For example, the balance amount cannot be summarized in the time dimension as it changes over time.
Scattered data Sometimes we can see the records in the fact table it has attributes with null measures . For example, there may be no order on a vacation. Hence, the attributes for this date will have zero measures. It is not necessary to archive measures for this type of record as it does not provide any information.
Degenerate dimensions Sometimes you may come across some dimensions in the fact table, which are not additive at all. For example order_number, customer_id, these types of dimensions cannot be added. However, in the event, you need to find the order placed by a particular customer this month; then you will need customer_id to relate your search. These types if the attributes or dimensions of the fact table are called degenerate dimension .
Definition of the dimension table
The dimension table is a key component for the initial scheme. A dimension table contains the attributes that represent the dimensions, along which the measurement is performed in the fact table. In addition, we will discuss some features of a size table.
Attributes and keys Each Dimension table must have one primary key that uniquely identifies each record in the table. It is commonly observed that the dimension table contains many attributes. So it seems to be wide that is, when you create a size table, you will find it spread in horizontal .
Attribute values The attribute values in the dimension table are rarely numeric, most of the times the values are found in the attributes they are in text format . For example product name, brand, category, subcategory, etc.
Relationship between attributes Often you can observe, the attributes you find in a dimensional table are not directly related. For example, Product_brand doesn't have to do anything with the data_package, but both could be attributes of the product size table.
normalization The size chart not it should be normalized . This is because normalizing a table would create many intermediate tables. When a query takes an attribute from the dimension table and retrieves the measurements along the one for the fact table, the query must go through those intermediate tables that become inefficient. Therefore, the dimension tables are not normalized.
Drilling down, rolling up The attributes of the dimension table allow you to get the details by going through the highest level of attributes aggregated to the lowest level attributes. For example, if you want to find the total sale in a region, you can drill down to find the sales by state, city, zip. You can even roll up to find the total sales first by zip, then by city and then by state.
Multiple hierarchy Often the dimension table offers multiple hierarchies. For example, we have a product size table for a departmental store. We now have two marketing and accounting departments.
The marketing department will analyze the attributes of the product size table in a given hierarchy to obtain measurements for the fact table.
In other hands, the accounting department will analyze the attributes of the product size table in the different hierarchy to obtain measurements for the fact table.
Therefore, the dimension table must have multiple hierarchies or levels of attribute aggregation to allow the user to drill down along one of the multiple hierarchies.
Records Although a dimensional table has too many attributes, it has fewer records.
Key differences between fact table and dimension table
- The fact table contains the measurement along the dimension / attributes of a dimension table.
- The fact table contains more records and fewer attributes than the dimension table, while the dimension table contains more attributes and fewer records.
- The size of the fact table increases vertically while the size of the dimension table increases horizontally.
- Each dimension table contains a primary key to identify each record in the table, while the fact table contains a chain key which is a combination of all primary keys of all dimension tables.
- The dimension table must be registered before the fact table is created.
- A schema contains fewer fact tables but more dimensional tables.
- The attributes in the fact table are numeric and textual, but the attributes in the dimension table have only textual attributes.
Both are equally important for creating the schema, but the dimension table must be registered before the fact table. Since it is impossible to create fact tables without dimensions.