![\"\"](\"https:\/\/people.utm.my\/suraya\/wp-content\/uploads\/sites\/615\/2017\/09\/contoh-fact-and-dimensional-table-300x294.jpg\")
<\/p>\nDimensions<\/b>\u00a0are the foundation of the\u00a0fact table<\/b>, and is where the data for the\u00a0fact table<\/b>\u00a0is collected. Typically\u00a0dimensions<\/b>\u00a0are nouns like date, store, inventory etc. These\u00a0dimensions<\/b>\u00a0are where all the data is stored. For example, the date\u00a0dimension\u00a0<\/b>could contain data such as year, month and weekday.<\/p>\n
<\/p>\n
Fact tables contain the data corresponding to a particular business process. Each row represents a single event associated with that process and contains the measurement data associated with that event.<\/p>\n
\nThe information contained within a fact table is typically numeric data and it is often data that can be easily manipulated, particularly by summing together many thousands of rows. For example, the retailer described above may wish to pull a profit report for a particular store, product line or customer segment. The retailer can do this by retrieving information from the fact table that relates to those transactions meeting the specific criteria and then adding those rows together.<\/p>\n
When designing a fact table, developers must pay careful attention to the grain of the table — the level of detail contained within the table.<\/p>\n
The developer designing the purchase fact table described above would need to decide, for example, whether the grain of the table is a customer transaction or an individual item purchase. In the case of an individual item purchase grain, each customer transaction would generate multiple fact table entries, corresponding to each item purchased.<\/p>\n
The choice of grain is a fundamental decision made during the design process that can have \u200ba\u00a0significant impact on the business intelligence effort down the road.<\/p>\n
Dimensions describe the objects involved in a business intelligence effort. While facts correspond to events, dimensions correspond to people, items, or other objects. For example, in the retail scenario, we discussed that purchases, returns, and calls are facts. On the other hand, customers, employees, items and stores are dimensions and should be contained in dimension tables.<\/p>\n
Dimension tables contain details about each instance of an object. For example, the items dimension table would contain a record for each item sold in the store. It might include information such as the cost of the item, the supplier, color, sizes, and similar data.<\/p>\n
Fact tables and dimension tables are related to each other. Again returning to our retail model, the fact table for a customer transaction would likely contain a\u00a0foreign key<\/a>\u00a0reference to the item dimension table, where the entry corresponds to a\u00a0primary key<\/a>\u00a0in that table for a record describing the item purchased.<\/p>\n","protected":false},"excerpt":{"rendered":" Dimensions\u00a0are the foundation of the\u00a0fact table, and is where the data for the\u00a0fact table\u00a0is collected. Typically\u00a0dimensions\u00a0are nouns like date, store, inventory etc. These\u00a0dimensions\u00a0are where all the data is stored. For example, the date\u00a0dimension\u00a0could contain data such as year, month and … Continue reading