Association of Computer Technocrats ...: Oracle Business Intelligence(OBI)

Hai Technocrats,

Now we will see Star and Snowflakes Schema.

Before going to the data models(Star and Snowflakes) let we discuss the dimension and fact tables.

Dimension Table:

Dimension Table is nothing but a general table. It doesn’t have any special. It contains a description of each and every attribute(column).
Dimension tables are usually large with number of columns but contains less number of rows.
Dimension table is defined by its single primary key and it is used to join it to the fact table.

Fact Table:

Fact table is a primary table in a dimensional model where the numerical, measurements are stored. Simply say Fact table contains Primary key, foreign key and Numerical values, nothing more than that.
The most useful facts in a Fact table are numeric and additive.
Fact table is little bit reverse to dimension table, in fact table usually large with number of rows but contains less number of columns.
Fact table generally contains its own primary key and it also contains foreign keys. All those foreign keys are primary keys of Dimension tables.
This is about Dimension and Fact tables. Now let we see what is Star Schema and Snowflak Schema.

Star Schema:

A Fact table consist of numeric measurements, it is joined to a set of dimension table.
A Star Schema is a database design which contains a centrally located Fact table surrounded by dimension table.

In the above Figure CUSTOMER, DATE, PRODUCT, VENDOR are the Dimension tables and ORDER is the Fact Table.
If we observe all Dimension table contains primary key value and numeric value and character value attributes are there, but in the Fact table we have only primary key value, foreign key value and numerical value attributes.

Snowflake Schema:

In a Snowflake Schema a denormalized dimension table is split into one or more dimensional tables which results in partially normalized and fully normalized dimension.
This schema is used to split the dimension into multiple, if the dimensions has too many columns in it. This helps to reduce the size of the dimension table.

In next posting let we see few simple what is BRIDGE Table and CONFORMED Dimension Table.

Association of Computer Technocrats ...