FACTS AND DIMENSION TABLES

<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-outline-level:3"><b>Fact Table</b></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l3 level2 lfo1; tab-stops:list 1.0in">o&nbsp;&nbsp; A fact table is like a big central table in a database that stores the main data you want to analyze.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l3 level2 lfo1; tab-stops:list 1.0in">o&nbsp;&nbsp; It holds numerical data or facts, like sales amounts, quantities sold, or transaction counts.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l3 level2 lfo1; tab-stops:list 1.0in">o&nbsp;&nbsp; Each row in the fact table is a unique event or transaction, like a single sale.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l3 level2 lfo1; tab-stops:list 1.0in">o&nbsp;&nbsp; <b>EXAMPLE: </b>sales fact table<b>&nbsp; &nbsp; &nbsp; &nbsp;</b>Contains data like the total sales amount, number of items sold, and the date of the sale.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-outline-level:3"><b>Dimension Table</b></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l0 level2 lfo2; tab-stops:list 1.0in">o&nbsp;&nbsp; Dimension tables are smaller tables that provide context to the data in the fact table.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l0 level2 lfo2; tab-stops:list 1.0in">o&nbsp;&nbsp; They describe the "who, what, when, where, and how" of the facts.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l0 level2 lfo2; tab-stops:list 1.0in">o&nbsp;&nbsp; They hold descriptive data, like names, categories, and dates.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:1.0in;text-indent:-.25in;line-height:normal;mso-list:l0 level2 lfo2; tab-stops:list 1.0in">o&nbsp;&nbsp; They help make the data in the fact table meaningful.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;line-height:normal"><b>Example: </b>Product Dimension Table contains&nbsp;details about products, like product name, category, and price.</p><p class="MsoNormal" style="margin-left: 1in; line-height: normal; text-indent: -0.25in;">&nbsp;&nbsp;</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-outline-level:3"><b>How They Work Together</b></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;line-height:normal"><b>Fact Table c</b>ontains the main data points (facts) you want to analyze while <b>Dimension Tables p</b>rovide detailed information that explains the facts.</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-outline-level:3"><b>Example</b></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal">Imagine you have a database for a retail store:</p> <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in"><b>Sales Fact Table:</b></li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in"><b>Columns:&nbsp;</b>Sale ID, Product ID, Store ID, Date ID, Quantity Sold, Total Sales Amount.</li> </ul> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in"><b>Product Dimension Table:</b></li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in"><b>Columns:&nbsp;</b>Product ID, Product Name, Category, Price.</li> </ul> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in"><b>Date Dimension Table:</b></li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in"><b>Columns:&nbsp;</b>Date ID, Year, Month, Day.</li> </ul> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l1 level1 lfo3;tab-stops:list .5in"><b>Store Dimension Table:</b></li> <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;line-height:normal;mso-list:l1 level2 lfo3;tab-stops:list 1.0in"><b>Columns:&nbsp;</b>Store ID, Store Location, Store Size.</li> </ul> </ul> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal">When you want to analyze sales data, you look at the facts (like total sales amount) in the fact table and use the dimension tables to understand more about those facts (like which products were sold, when they were sold, and where they were sold).</p> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal">In summary:</p> <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo4;tab-stops:list .5in"><b>Fact Table:&nbsp;</b>Stores the main data points to be analyzed.</li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; line-height:normal;mso-list:l2 level1 lfo4;tab-stops:list .5in"><b>Dimension Tables:&nbsp;</b>Provide additional details to explain and give context to those data points.</li> </ul>