![]() ![]() These dimension tables are tables that their surrogate key (or primary key) is part of the fact table. Relationship Between Fact Table and Dimension TablesĮvery fact table will be related to one or more dimensions in the model. ![]() ![]() Although, you should keep in mind that it means your fact table will have more data rows. If you build your fact table from the lowest grain (the most detailed list of dimensions), you always get the ability to expand easily in the future. On the other hand, more fields, also mean row numbers will increase too, and you will need more memory to store the data. The more fields you have as a grain in your fact table means the more dimension you are connected to, and it means more power for slicing and dicing. The grain for the second fact table is one record per combination of Product, Order Date, Customer, Promotion, and Sales Territory. The grain for the first fact table is one record per combination of Product, Order Date, and Customer. The Grain of a fact table is the level of details stored in the fact table. However, the second fact table, in addition to those dimensions, will allow me to slice and dice by Promotion and Sales Territory too. The first fact table gives me the power to slice and dice data of sales by Product, Date or Customer dimension. The second one, has more dimension keys, it also has PromotionKey and SalesTerritoryKey In below screenshots, I have two Sales fact tables, which are slightly different! The first one has three dimension keys of ProductKey, OrderDateKey, and CustomerKey For example, if the table above analyzing sales data, then it can be called FactSales, or just simply Sales. Usually, fact tables are named based on their main entity of analysis. The screenshot below shows a fact table with keys from dimension tables Īs you can see in the above screenshot, A fact table includes two types of fields Fields from Dimension tables (Keys from dimension table, Surrogate keys from dimension tables), and Facts (numeric and aggregatable fields).Ī Fact table is a table in the data model which includes Facts and Keys from dimension tables. The fact table has another set of fields too Keys from Dimension tables. With the structure of the table above, you cannot do that. For example, you should be able to see what was the Sales Amount for each product category, for each client, in each store, etc. The data in a fact table should be sliced and diced by the data of dimensions. The screenshot above can be a representation of a fact table based on the definition, however, it lacks something very important. So you may think a fact table is like the below screenshot. A fact table is a table full of those fields. Examples of Fact is Sales Amount, Order Quantity, Profit, Cost, etc. What is a Dimension table and why say No to a single big table What is a Fact Table?Ī fact table is a table full of Facts! If you have read the definition of Fact from the previous article, you know that fact is a numeric field which usually needs to be aggregated, and will be set as the value part of visualizations. What is the Direction of the Relationship?ĭata preparation First and Foremost Important task What is the Cardinality of the Relationship? However, I highly recommend you to read below articles beforehand There is no prerequisite for this article. To learn more about Power BI, read Power BI book from Rookie to Rock Star. Examples of this article are built using Power BI, however, all of these concepts can be used regardless of the technology. In this article, you will learn about the fact table, and how it positioned in a data model, you will also learn how fact table and dimension table are related to each other to build a proper data model. In the previous article, I explained what a dimension table is, and why we cannot have everything in one big table. Fact tables are the core of analysis in a data model. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |