Friday, December 16, 2016

Important features of Fact tables in Data Warehouse

Facts tables play a very dynamic role in the data warehouses or data marts because they consist of the measurements, metrics or facts of a business process e.g., sales revenue by month by product.
A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).
Note: Special care must be taken whenever you are handling ratios and percentage because one good design rule is to never store percentages or ratios in fact tables but only calculate these in the data access tool. Thus only store the numerator and denominator in the fact table, which then can be aggregated and the aggregated stored values can then be used for calculating the ratio or percentage in the data access tool.

Important Features of Fact Tables

  1. They placed at the centre of a star schema or a snowflake schema surrounded by dimension tables. Where multiple fact tables are used, these are arranged as a fact Constellation schema or Galaxy schema. 
  2. A fact table typically has two types of columns: those that contain facts and those that are a foreign key to dimension tables.
  3. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. 
  4. Fact tables contain the content of the data warehouse and store different types of measures like additive, non-additive, and semi additive measures.
  5. Mostly fact tables contain numerical data (facts) that can be summarized to provide information about the history of the operation of the organization.
  6. Each fact table also includes a multi-part index that contains as foreign keys the primary keys of related dimension tables, which contain the attributes of the fact records.
  7. Fact tables should not contain descriptive information or any data other than the numerical measurement fields and the index fields that relate the facts to corresponding entries in the dimension tables.
  8. Fact tables provide the (usually) additive measures that act as independent variables by which dimensional attributes are analyzed. 
  9. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined.
  10. Additive measures allow summary information to be obtained by adding various quantities of the measure, such as the sales of a specific item at a group of stores for a particular time period.
  11. Non-additive measures such as inventory quantity-on-hand values can also be used in fact tables, but different summarization techniques must then be used.
Before design a fact table, we must have to decide what business process to model by gathering and understanding business needs and available data such as Identify a business process for analysis (like sales) where we should know the granularity level of the facts means describing exactly what a fact table record represents and also need to determine dimensions for the fact table. 

No comments:

Post a Comment