Dimension table
A Dimension Table is a table in a star schema of a data warehouse. Data warehouses are built using dimensional data models which consist of fact and dimension tables. Dimension tables are used to describe dimensions; they contain dimension keys, values and attributes.
For example, the time dimension would contain every hour, day, week, month, quarter and year that has occurred since you started your business operations. Product dimension could contain a name and description of products you sell, their unit price, color, weight and other attributes as applicable. Attributes would be a customer’s first and last name, age, gender etc.
Dimension tables are typically small, ranging from a few to several thousand rows. Occasionally dimensions can grow fairly large, however. For example, a large credit card company could have a customer dimension with millions of rows. Dividing a data warehouse project into dimensions, provides structured information for reporting purpose.
When you create a dimension, you logically create a structure for your projects. This dimension table can be utilized across for reports and it’s about re-usability. If there are any changes to be made, it is evident that only a particular table will get affected. When a company wants to create a report, they can read the data from the dimension table since the table consists of necessary information.
For example, an e-commerce company can create a dimension table with various columns depending on different subjects they would like to gain information from like the name of the person, address, date of order, shipment etc. This kind of an information becomes very crucial because in case of any clarification, the company can refer to this dimension table. Business users who generate these reports fire queries on these dimension tables as they contain descriptive information.
For an in-depth understanding of how Dimension Table works, here’s a video tutorial recorded by our Data warehousing and business intelligence Expert to explain the topic in a crisp manner with elaborate examples.
Fact Table
A fact table is a table that contains the measures of interest. For example, sales amount would be a measure and this measure is stored in the fact table. For example, it can be the sales amount by each store, each day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.
Got a question for us? Please mention them in the comments section and we will get back to you.
Related Posts: