Though reports are generated on fact tables, data in dimension tables is used to describe the data in fact tables।
Dimensions are mainly below types.
Slowly Changing Dimension:
Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.For example, you may have a dimension in your database that tracks the sales records of your company's salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good.
Conformed Dimension:
At the most basic level, conformed dimensions mean the exact same thing with every possible fact table to which they are joined. The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.
These dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example consider a model containing multiple fact tables representing different data marts. Now look for a dimension that is common to these facts tables. In this example let’s consider that the product dimension is common and hence can be reused by joining the different fact tables.
Degenerate Dimension:
The data items thar are not facts and data items that do not fit into the existing dimensions are termed as Degenerate Dimensions।They can be used as primary key for the fact table but they cannot act as foreign keys।
A column of the key section of the fact table that does not have the associated dimension table but used for reporting and analysis such column is called degenerate dimension or line item dimension।
For example we have a fact table with customer_id, product_id, branch_id, employee_id, bill_no, date in key section and price, quantity, amount in measure section. In this fact table bill_no from key section is a single value it has no associated dimension table. Instead of cteating a seperate dimension table for that single value we can
include it in fact table to improve performance.SO here the column bill_no is a degenerate dimension or line item dimension.
A Degenerate dimension is a Dimension which has only a single attribute.
This dimension is typically represented as a single field in a fact table.
When the cardinality of column value is high instead of maintaining a separate dimension and having the overhead of making a join with fact table degenerated dimensions can be build.
For example In sales fact table Invoice number is a degenerated dimension. Since Invoice Number is not tied up to an order header table hence there is no need for invoice number to join a dimensional table; hence it is referred as degenerate dimension.
Junk Dimension:
When you consolidate lots of small dimensions and instead of having 100s of small dimensions that will have few records in them all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table। (Since we are storing all the junk in this one table) .
Inferred Dimension:
Inferred dimensions are the dimensions that are not retrieved when the fact data that are related to them, is ready to insert into the fact table।
Role Playing Dimension:
Dimensions are often recycled for multiple applications within the same database. For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension".
Dirty dimension:
A dimension which can exits more than once in dimension table.
No comments:
Post a Comment