Dimension Table

---

Dimension Table is used to maintain Hierarchy Textual Information of business process that integrated with Fact Table in the Data warehouse. FACT Table is used to store measurements, metrics or facts of a business process in the Data warehouse. Grain is the base business definition of Fact table that determine measurement of business event.

By default, 1NF and 2NF is used to design Dimension Table and then 3NF to define further more normalized Dimension Tables. A collection of unordered dimension table's descriptive information is helps to generate Drill down or Roll up Analytical Report that specify how Fact table data should be summarized.

An instance, when we buy any products in the supermarket, they sales person use Scanner to capture data from Product Label and then stores "Sales Quantity" and "Extended Price" in the Database through OLTP Application with relevant information like Store, Product, Promotion, Invoice, Customer and Employee information.

In this scenario, "Sales Quantity" and "Extended Price" are grain of Sales event. Store, Product, Promotion, Invoice, Customer and Employee are entities of Sales event that should define and maintain in the isolated dimension tables.

The following Sales Data Mart is designed with Sales_Fact and relevant shared dimension tables to maintain Sales Historical Transaction information that will help to generate Sales Analytical Reports through SQL Query.



Shared Dimensions

Data Mart is a subset of the Data Warehouse that based on Subjective of Enterprise Business Unit or Team. Each Data Marts may or may not be dependent or related to other Data Marts in the Data Warehouse.

Shared Dimensions are describes the core set of dimensions that shared across the various cube perspectives in a Data Mart. These dimensions are allows the measures in the cube to be summarized according to the different analytical questions. Shared Dimensions are used to maintain and provide consistent information for similar queries.

* * * * *

---