Fact and Dimensional Table

<b>Definition of Fact Tables</b>A fact table is a central table in a star schema or snowflake schema within a data warehouse. It is designed to store quantitative data (facts) about a business process or activity. Here are the key characteristics and components of a fact table<b>Contents of Fact Tables</b><b>Numeric Metrics</b>: Fact tables primarily contain numerical measurements or metrics, such as sales revenue, quantity sold, profit margin, etc.<b>Foreign Keys</b>: Fact tables include foreign keys that reference dimension tables. These foreign keys establish relationships with various aspects (dimensions) of the business context.<b>Structure of Fact Tables</b><b>Grain</b>: A fact table is typically at a fine-grained level, capturing individual transactions, events, or occurrences that are measurable.<b>Columns</b>: It consists of columns representing facts or measures (numeric data), as well as foreign keys linking to dimension tables.<b>No Hierarchies</b>: Unlike dimension tables, fact tables do not contain hierarchies. They focus on capturing numeric data associated with specific instances.<b>Types of Fact Tables</b><b>Transactional</b>: Contains detailed data at the lowest level of granularity, such as individual sales transactions.<b>Periodic Snapshot</b>: Captures data periodically over time, such as monthly sales totals.<b>Accumulating Snapshot</b>: Tracks the progress of a process over time, capturing milestones or events as they occur.Usage of Fact Tables in Analysis<b>Aggregation</b>: Fact tables are designed to support aggregations across dimensions, allowing users to analyze data at different levels of granularity (e.g., daily, weekly, monthly).<b>Decision Making</b>: They serve as the foundation for generating reports, performing analytics, and making data-driven decisions within an organization.<b>Relationship with Dimension Tables</b><b>Foreign Keys</b>: Fact tables contain foreign keys that link to dimension tables. These foreign keys provide context to the numerical data stored in the fact table.<b>Many-to-One Relationship</b>: A fact table typically has a many-to-one relationship with each of its related dimension tables. For example, a fact table recording sales data might have foreign keys linking to a time dimension (date of sale), product dimension (product sold), and location dimension (where the sale occurred).Inconclusion, fact tables play a crucial role in data warehousing by centralizing quantitative data and providing a structured way to analyze and understand business activities and performance. They are essential components that facilitate efficient querying and reporting within a data warehouse environment.<b></b><b>=================================================================================================</b><b></b><b>Definition of Dimensional Tables</b>Dimensional tables also known simply as dimensions, are a key component of dimensional modeling in data warehousing. They provide descriptive information about the business context in which the data in a fact table is collected. Here are the key characteristics and components of dimensional tables:<b>Importance of Dimensional Tables</b><b>Descriptive Context</b>: Dimensional tables contain textual attributes or categorical data that provide context to the quantitative facts stored in the fact table.<b>Grouping and Filtering</b>: They enable grouping, filtering, and slicing of data in the fact table based on various business perspectives or dimensions.<b>Types of Dimensional Tables</b><b>Time</b>: Contains attributes related to dates and time periods (e.g., year, quarter, month, day).<b>Product</b>: Describes products or services offered by the organization, often structured hierarchically (e.g., category, subcategory, product).<b>Location</b>: Provides information about geographical locations relevant to the business (e.g., country, region, city).<b>Customer</b>: Contains details about customers or entities interacting with the business (e.g., customer ID, demographic information).<b>Other Dimensions</b>: Depending on the specific business context, additional dimensions such as employee, supplier, promotion, etc., may also be included.<b>Structures of Dimensional Tables</b><b>Attributes</b>: Dimensional tables consist of descriptive attributes that define the dimensions of the business. For example, a time dimension might include attributes like year, quarter, month, and day.<b>Hierarchies</b>: Attributes in dimensional tables often form hierarchies, allowing data to be aggregated at different levels of granularity. For instance, a product dimension might have a hierarchy from product category to subcategory to individual product.<b>Relationship with Fact Tables</b><b>Foreign Keys</b>: Dimensional tables are linked to the fact table through foreign keys. These keys establish relationships that allow analysts and users to analyze quantitative data in the fact table within the context provided by the dimensional attributes.<b>One-to-Many Relationship</b>: Each dimensional table can have a one-to-many relationship with the fact table. For example, multiple sales transactions in the fact table can be associated with different customers in the customer dimension.Usage of Dimensional Tables in Analysis<b>Drill-down and Roll-up</b>: Dimensional tables facilitate drill-down (moving from higher-level summaries to lower-level details) and roll-up (aggregating data from lower levels to higher levels) analysis.<b>Slicing and Dicing</b>: Analysts can slice (selecting a subset of data based on criteria) and dice (viewing data from different perspectives) the data based on dimensional attributes.Dimensional tables are designed to support efficient querying and analysis in data warehousing environments. By organizing data into dimensions and facts, dimensional modeling simplifies complex data relationships and enhances the ability to derive meaningful insights from large datasets.