Dimensional Model comprises a fact table and many dimension tables and is used for calculating summarized data. Since Business Intelligence reports are used in measuring the facts(aggregates) across multiple dimensions, dimensional data modeling is the preferred modeling technique in a BI environment. A Fact table contains various measures or facts like sales amount, loan amount etc., whereas a Dimension table describes the particular entity like time, state etc., based on which the required facts are measured.
Star Schema:
In a Dimensional Data Model, a fact table is the centralized table which is connected to multiple dimensions related to that fact. This type of approach is known as the Star Schema design based on which data warehouse and data marts are built. Since BI takes advantage of data displayed in the form of multidimensional cubes, this star schema approach helps analyze and produce complex reports very easy by slicing and dicing the dimensions of interest.
From the sample diagram shown below, the required Fact ‘Loan Amount’ can be calculated across various dimensions like state, branch, time, product, loan officer and investor dimensions.
