<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Michael on Medium]]></title>
        <description><![CDATA[Stories by Michael on Medium]]></description>
        <link>https://medium.com/@michael-ow?source=rss-9d80fe825254------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*IxMt-pfyZ4x-Yv47Ezz4ow.jpeg</url>
            <title>Stories by Michael on Medium</title>
            <link>https://medium.com/@michael-ow?source=rss-9d80fe825254------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 08:30:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@michael-ow/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Data Warehouse Tips and Best Practices I Wish I Knew Earlier]]></title>
            <link>https://medium.com/analytics-vidhya/data-warehouse-tips-and-best-practices-i-wish-i-knew-earlier-51740bedb4b6?source=rss-9d80fe825254------2</link>
            <guid isPermaLink="false">https://medium.com/p/51740bedb4b6</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Michael]]></dc:creator>
            <pubDate>Wed, 10 Nov 2021 04:54:22 GMT</pubDate>
            <atom:updated>2026-06-08T00:55:15.337Z</atom:updated>
            <content:encoded><![CDATA[<p>A Beginner’s Guide to Building Your First Data Warehouse</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*FkRjSkj3uBbltyZ-" /><figcaption>Photo by <a href="https://unsplash.com/@ismailenesayhan?utm_source=medium&amp;utm_medium=referral">İsmail Enes Ayhan</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><blockquote>A data warehouse is <strong>a type of data management system</strong> that is designed to enable and support business intelligence (BI) activities, especially analytics. Data warehouses are solely intended to perform queries and analysis and often contain large amounts of historical data. - Oracle.com</blockquote><h3>Introduction</h3><p>This article summarizes what I learned from <em>The Data Warehouse Toolkit </em>by Ralph Kimball and Margy Ross. The book was recommended to me by my manager, and I have to admit it completely revamped my data warehousing knowledge. I wish I had read this book earlier in my career.</p><p>I highly recommend this book to anyone who is working in the field of data. Whether you’re a data analyst, engineer or scientist, you may be tasked with building a data warehouse to support your analytical needs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*OCUYTAGDSoEdX6pP.jpg" /><figcaption>Source: <a href="https://www.wiley.com/">https://www.wiley.com/</a></figcaption></figure><p>The book emphasizes three important data warehouse principles:</p><ul><li>Simple — Straight-forward structure, easy to understand</li><li>Fast — Queries should return results quickly</li><li>Flexible — Analysis from any angle and breakdown</li></ul><h3>Overview</h3><ol><li>Fact and Dimension Tables</li><li>Keys and Normalization</li><li>Other Fact Table Best Practices</li><li>Other Dimension Table Best Practices</li></ol><h3><strong>1. Fact and Dimension Tables</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*acP4taURDmv5w7bL" /><figcaption>Photo by <a href="https://unsplash.com/@mitchel3uo?utm_source=medium&amp;utm_medium=referral">Mitchell Luo</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><blockquote>Data involved in calculations should be in fact tables, and data involved in constraints, groups, and labels should be in dimensional tables. <br>- The Data Warehouse Toolkit</blockquote><h4>Fact Tables</h4><p>Let’s use a retail case study like the one in the book. Here are some sales transaction data:</p><pre>+------------+---------+--------+-----+--------+<br>|    <strong>date    </strong>|  <strong>store  </strong>|  <strong>item  </strong>| <strong>qty </strong>| <strong>amount </strong>|<br>+------------+---------+--------+-----+--------+<br>| 2021-01-01 | Store 1 | Gloves |   2 |     20 |<br>| 2021-01-02 | Store 2 | Hammer |   1 |     28 |<br>+------------+---------+--------+-----+--------+</pre><p>This is an example of a <strong>Fact Table</strong>. It consists of:</p><ul><li>Dimensions: date , store , item</li><li>Measures: qty, amount</li></ul><h4>Table Grain</h4><p>The grain is the <strong>level of detail captured in a single row</strong>. In this case, it is a single transaction in a particular store.</p><h4>Grain Do’s and Don’ts</h4><ol><li><strong>Do determine the grain of the fact table early</strong><br>Grain definition is a critical step as it affects all design decisions afterward. Always get commitment from your stakeholders when choosing the grain, to prevent costly reimplementation.</li><li><strong>Do store the lowest-level grain (realistically)</strong><br>There may be a need to aggregate the grain at a higher level (to reduce storage space or analyze at higher levels). This is alright but make sure to keep a copy of the lowest level grain data in another table, to maintain the flexibility for lower-level analysis.</li><li><strong>Do not mix the grain</strong><br>Storing facts at different aggregation levels may lead to potential errors when fact table measures are aggregated. These errors might be avoided by educating users to apply certain filters, but this violates the “simple to use” principle.</li></ol><pre>Do not store different grains in the same table.<br>Bad example:<br>+------------+---------+-------------+-----+--------+<br>|    date    |  store  |    item     | qty | amount |<br>+------------+---------+-------------+-----+--------+<br>| 2021-01-01 | Store 1 | <strong>All         </strong>|   <strong>3</strong> |     <strong>28 </strong>|<br>| 2021-01-01 | Store 1 | Gloves      |   2 |     20 |<br>| 2021-01-01 | Store 1 | Hammer      |   1 |      8 |<br>+------------+---------+-------------+-----+--------+</pre><h4>Dimension tables</h4><p>Let’s add country and item_cat information. One way is to add new columns to the fact table like so:</p><pre>+------+-----------+---------+-----------+--------+-----+--------+<br>| date |  <strong>country  </strong>|  store  | <strong>item_cat  </strong>|  item  | qty | amount |<br>+------+-----------+---------+-----------+--------+-----+--------+<br>|  ... | <strong>Singapore </strong>| Store 1 | <strong>Materials </strong>| Gloves |   2 |     20 |<br>|  ... | <strong>Thailand  </strong>| Store 2 | <strong>Tools     </strong>| Hammer |   1 |     28 |<br>+------+-----------+---------+-----------+--------+-----+--------+</pre><p>Unfortunately, this method is not scalable. Fact tables can go to the millions and billions of rows. Adding more columns exponentially increases the storage space required.</p><p>Since these dimension <strong>attributes</strong> have a one-to-one relationship with their respective dimensions, it makes sense to create separate <strong>dimension tables</strong>:</p><pre><strong>Fact Table</strong><br>+------------+---------+--------+-----+--------+<br>|    date    |  <strong>store  </strong>|  <strong>item  </strong>| qty | amount |<br>+------------+---------+--------+-----+--------+<br>| 2021-01-01 | Store 1 | Gloves |   2 |     20 |<br>| 2021-01-02 | Store 2 | Hammer |   1 |     28 |<br>+------------+---------+--------+-----+--------+<br><br><strong>Dimension Tables</strong><br>+---------+-----------+<br>|  <strong>store  </strong>| country   |<br>+---------+-----------+<br>| Store 1 | Singapore |<br>| Store 2 | Thailand  |<br>+---------+-----------+<br>+--------+-----------+<br>|  <strong>item  </strong>| item_cat  |<br>+--------+-----------+<br>| Gloves | Materials |<br>| Hammer | Tools     |<br>+--------+-----------+</pre><h4>Star Schema</h4><p>If we lay the dimensions around the fact table, we get the famous <a href="https://en.wikipedia.org/wiki/Star_schema">star schema</a>. Star schemas are popular for their practicality and ease of use.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*VQsbgOtuut2cYcYV2UK6iA.png" /><figcaption>Star schema example. Made using <a href="https://dbdiagram.io/">dbdiagram.io</a>. Star added by me.</figcaption></figure><h4>Querying a Star Schema</h4><p>When a user wants to include these <strong>dimension attributes</strong> in their output, they just have to include the relevant joins in their SQL query:</p><pre>select<br>    f_txn.*<br>    d_store.country<br>from<br>    f_txn<br><strong>left join<br>    d_store<br>    on d_store.store = f_txn.store<br>where<br>    d_store.country = &#39;Singapore&#39;</strong></pre><h3>2. Keys and Normalization</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*65kCr4umYniL5M3B" /><figcaption>Photo by <a href="https://unsplash.com/@theblowup?utm_source=medium&amp;utm_medium=referral">the blowup</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>Natural Keys</h4><p>Natural keys are <strong>unique keys used by the operational system</strong> outside the data warehouse. They are usually long codes that contain certain business meanings that are understood by the operational side.</p><p>The keys we used (e.g. Store 1, Gloves) can be classified as natural keys.</p><h4>Surrogate Keys</h4><p>Surrogate keys are <strong>unique keys generated by the database (usually integers)</strong>, mapped to one or many natural keys. It contains no business meaning and is created for the sole purpose of database joins.</p><p>Let’s upgrade to use <strong>Surrogate Keys</strong>. This is before:</p><pre><br>Fact Table<br>+------------+---------+--------+-----+--------+<br>|    date    |  <strong>store  </strong>|  <strong>item  </strong>| qty | amount |<br>+------------+---------+--------+-----+--------+<br>| 2021-01-01 | Store 1 | Gloves |   2 |     20 |<br>| 2021-01-02 | Store 2 | Hammer |   1 |     28 |<br>+------------+---------+--------+-----+--------+<br><strong><br></strong>Dimension Tables<br>+---------+-----------+<br>|  <strong>store  </strong>| country   |<br>+---------+-----------+<br>| Store 1 | Singapore |<br>| Store 2 | Thailand  |<br>+---------+-----------+<br>+--------+-----------+<br>|  <strong>item  </strong>| item_cat  |<br>+--------+-----------+<br>| Gloves | Materials |<br>| Hammer | Tools     |<br>+--------+-----------+</pre><p>This is after:</p><pre>Fact Table<br>+------------+----------+---------+-----+--------+<br>|    date    | <strong>store_id </strong>| <strong>item_id </strong>| qty | amount |<br>+------------+----------+---------+-----+--------+<br>| 2021-01-01 |        1 |       1 |   2 |     20 |<br>| 2021-01-01 |        2 |       3 |   1 |     28 |<br>+------------+----------+---------+-----+--------+<br><br>Dimension Tables<br>+----------+------------+--------------+<br>| <strong>store_id </strong>| store_name | country_name |<br>+----------+------------+--------------+<br>|        1 | Store 1    | Singapore    |<br>|        2 | Store 2    | Thailand     |<br>+----------+------------+--------------+<br>+---------+-----------+---------------+<br>| <strong>item_id </strong>| item_name | item_category |<br>+---------+-----------+---------------+<br>|       1 | Gloves    | Material      |<br>|       3 | Hammer    | Tools         |<br>+---------+-----------+---------------+</pre><h4>Benefits of Surrogate Keys</h4><p>While surrogate keys take effort to implement, they often have significant benefits:</p><ol><li><strong>Buffer from operational changes</strong><br>If a natural key is modified for whatever reason, the value need only be updated in a single row of the dimension table, instead of a potentially huge number of rows on the fact table.</li><li><strong>Reduced space and improved performance</strong><br>Natural keys are often string datatypes that take up significantly more space than integers. Joins on string columns are also less efficient. When fact tables get into the millions/billions, space savings and performance become significant.</li><li><strong>Handling of null values</strong><br>Natural keys may occasionally have no values (nulls) due to errors in the operational system or other special cases. It is bad practice to have null keys in a fact table because of inconsistent query behavior when users join and filter on these keys. This can be solved by reserving a surrogate key to represent these cases.</li><li><strong>Integrate multiple source systems</strong><br>Duplicate natural keys found in more than one source system can be handled simply by assigning individual surrogate keys to each duplicate key.</li></ol><h4>Avoid having null foreign keys</h4><p>For various real-world reasons, fact tables may from time to time have null foreign keys. The beginner approach would be to leave it as it is.</p><pre>Bad Example:<br>+------------+----------+---------+-----+--------+<br>|    date    | store_id | item_id | qty | amount |<br>+------------+----------+---------+-----+--------+<br>| 2021-01-01 |        1 | 1       |   2 |     20 |<br>| 2021-01-01 |        2 | <strong>(NULL)</strong>  |   1 |     28 |<br>+------------+----------+---------+-----+--------+</pre><p>This is bad practice because tables cannot be joined on a null, and certain WHERE and GROUP clauses may not function as expected. The proper way is to create a dimension table entry reserved for such cases.</p><pre>Good Example:<br>+------------+----------+---------+-----+--------+<br>|    date    | store_id | <strong>item_id </strong>| qty | amount |<br>+------------+----------+---------+-----+--------+<br>| 2021-01-01 |        1 |       1 |   2 |     20 |<br>| 2021-01-01 |        2 |       <strong>0</strong> |   1 |     28 |<br>+------------+----------+---------+-----+--------+<br><br>+---------+-----------+---------------+<br>| <strong>item_id </strong>| item_name | item_category |<br>+---------+-----------+---------------+<br>|       <strong>0</strong> | <strong>unknown   </strong>| <strong>unknown       </strong>|<br>|       1 | Gloves    | Materials     |<br>|       2 | Hammer    | Tools         |<br>+---------+-----------+---------------+</pre><h4>Normalization</h4><p>Normalization is the restructuring of a database to reduce data redundancy and improve data integrity. When we upgrade to surrogate keys, we are actually <strong>normalizing </strong>the fact table.</p><h4>Normalizing Dimension tables and Snowflaking</h4><p>Let’s add more columns to our store dimension:</p><pre>+----------+------------+-----------+--------------+---------------+<br>| store_id | store_name | size_sqft | <strong>country_name </strong>| <strong>currency_code </strong>|<br>+----------+------------+-----------+--------------+---------------+<br>|        1 | Store 1    |     10000 | Singapore    | SGD           |<br>|        2 | Store 2    |     15000 | Thailand     | THB           |<br>|        3 | Store 3    |     15000 | Thailand     | THB           |<br>+----------+------------+-----------+--------------+---------------+</pre><p>We could normalize this <strong>dimension table</strong> by adding another dimension table for country-related attributes:</p><pre>Store dimension<br>+----------+------------+-----------+------------+<br>| store_id | store_name | size_sqft | <strong>country_id </strong>|<br>+----------+------------+-----------+------------+<br>|        1 | Store 1    |     10000 |          1 |<br>|        2 | Store 2    |     15000 |          2 |<br>|        3 | Store 3    |     15000 |          2 |<br>+----------+------------+-----------+------------+<br><br>Country dimension<br>+------------+--------------+---------------+<br>| <strong>country_id </strong>| <strong>country_name </strong>| <strong>currency_code </strong>|<br>+------------+--------------+---------------+<br>|          1 | Singapore    | SGD           |<br>|          2 | Thailand     | THB           |<br>+------------+--------------+---------------+</pre><p>This second level of branching on a dimension table creates what is known as a <strong>snowflake schema</strong> (more and more sub-branches can eventually form a complex snowflake). This is not recommended for Data Warehouses due to:</p><ol><li>Increased complexity for users</li><li>Increased difficulty of maintenance for developers</li><li>Potential performance reduction (hinders database optimizers)</li></ol><p>Since dimension tables are relatively small, the storage space saved by normalization is often not worth the drop in usability and performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SWjCBifXshbp_XZR5rq9Zw.png" /><figcaption>Star vs snowflake. Made using <a href="http://draw.io/">draw.io</a></figcaption></figure><h3>3. Other Fact Table Best Practices</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0l6o6tvG8Gws8ReC" /><figcaption>Photo by <a href="https://unsplash.com/@nachocapelo?utm_source=medium&amp;utm_medium=referral">Nacho Capelo</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>Measures</h4><p>Measures or measurements are numerical values that are mean to be aggregated (e.g. sum, average, min, max).</p><pre><strong>qty </strong>and <strong>amount </strong>are measures.<br>+------------+----------+---------+-----+--------+<br>|    date    | store_id | item_id | <strong>qty </strong>| <strong>amount </strong>|<br>+------------+----------+---------+-----+--------+<br>| 2021-01-01 |        1 |       1 |   2 |     10 |<br>| 2021-01-01 |        2 |       3 |   1 |     20 |<br>+------------+----------+---------+-----+--------+</pre><h4>Null Measures are okay</h4><p>Unlike dimension keys, having null-valued measures are a good practice. It is okay because it is more meaningful, and SQL aggregation functions can handle them appropriately.</p><pre>+------------+----------+---------+-----+--------+<br>|    date    | store_id | item_id | qty | <strong>amount </strong>|<br>+------------+----------+---------+-----+--------+<br>| 2021-01-01 |        1 |       1 |   2 | 10     |<br>| 2021-01-01 |        2 |       3 |   1 | 11     |<br>| 2021-01-01 |        2 |       4 |   1 | <strong>(NULL)</strong> |<br>+------------+----------+---------+-----+--------+</pre><p>Querying the average amount returns a meaningful<strong> </strong>result:</p><pre><strong>Query</strong>:<br>SELECT AVG(amount) AS amount_avg<br>FROM f_txn<br><br><strong>Result</strong>:<br>+------------+<br>| amount_avg |<br>+------------+<br>|       10.5 | &lt;-- This is more meaningful than (10 + 11)/3 = 7<br>+------------+</pre><h4>Long vs Wide Fact Tables</h4><p>Up till now, we have been using the <strong>wide format </strong>for fact tables. Where each measure has its own column. Let&#39;s add more measures.</p><pre>Wide Format<br>+------------+----------+-----+-----+--------+----------+---------+<br>|    date    | store_id | ... | <strong>qty</strong> | <strong>amount</strong> | <strong>discount </strong>| <strong>revenue </strong>|<br>+------------+----------+-----+-----+--------+----------+---------+<br>| 2021-01-01 |        1 | ... |   2 |     10 |   (NULL) |      10 |<br>| 2021-01-01 |        2 | ... |   1 |     20 |        2 |      18 |<br>+------------+----------+-----+-----+--------+----------+---------+</pre><p>Designers are sometimes tempted to unpivot this table into a <strong>long format</strong>, where there is a <strong>measure type dimension</strong>. This was also my natural tendency when I was starting as a data analyst.</p><pre>Long Format<br>+------------+----------+-----+----------+-------+<br>|    date    | store_id | ... | <strong>measure  </strong>| <strong>value </strong>|<br>+------------+----------+-----+----------+-------+<br>| 2021-01-01 |        1 | ... | qty      | 2     |<br>| 2021-01-01 |        1 | ... | amount   | 10    |<br>| 2021-01-01 |        1 | ... | discount |(NULL) |<br>| 2021-01-01 |        1 | ... | revenue  | 10    |<br>| 2021-01-01 |        2 | ... | qty      | 1     |<br>| 2021-01-01 |        2 | ... | amount   | 20    |<br>| 2021-01-01 |        2 | ... | discount | 2     |<br>| 2021-01-01 |        2 | ... | revenue  | 18    |<br>+------------+----------+-----+----------+-------+</pre><p>A measure type dimension is generally a <strong>bad idea</strong> for a data warehouse:</p><ol><li>Significantly increases the size of the table, based on the number of measures</li><li>Increased difficulty for calculations involving multiple measures e.g. dividing <strong>revenue </strong>by <strong>amount</strong></li></ol><p>However, a measure type dimension has some <strong>benefits</strong>:</p><ol><li>Flexibility — New measures can be added simply by adding a new value to the measure type dimension, instead of adding a new table column</li><li>Improved grouping, filtering of measures — With measure type as a dimension, it benefits from having attributes associated with it that can be used to group or filter it e.g. monetary vs non-monetary</li></ol><p>Long format fact tables <strong>make sense</strong> in extreme cases where there are:</p><ol><li>A large number of measures — 100+ measures</li><li>Sparsely populated — Only a few measures apply to any one row</li></ol><h4>Different Types of Measures</h4><p>Measures can be grouped into 3 categories:</p><pre>1. <strong>Additive </strong>(Summable)<br>These measures can be summed up across <strong>any dimension</strong>.<br>e.g. quantity, amount<br><br>+------------+------+--------+<br>|    date    | ctry | amount |<br>+------------+------+--------+<br>| 2021-10-01 | SG   |    100 |<br>| 2021-10-01 | TH   |    200 |<br>| 2021-10-15 | SG   |    300 |<br>+------------+------+--------+<br><br>+------------+-------+--------+<br>|    date    | <strong>ctry  </strong>| amount |<br>+------------+-------+--------+<br>| 2021-10-01 | &lt;<strong>all</strong>&gt; |    300 |<br>| 2021-10-15 | &lt;<strong>all</strong>&gt; |    300 |<br>+------------+-------+--------+<br><br>+-------+------+--------+<br>| <strong>date  </strong>| ctry | amount |<br>+-------+------+--------+<br>| &lt;<strong>all</strong>&gt; | SG   |    400 |<br>| &lt;<strong>all</strong>&gt; | TH   |    200 |<br>+-------+------+--------+<br><br>+-------+-------+--------+<br>| <strong>date  </strong>| <strong>ctry  </strong>| amount |<br>+-------+-------+--------+<br>| &lt;<strong>all</strong>&gt; | &lt;<strong>all</strong>&gt; |    600 |<br>+-------+-------+--------+<br><br><br><br>2. <strong>Semi-Additive</strong> (Semi-Summable)<br>These measures can only be summed up across some dimensions. Usually it is the time dimension that cannot be used.<br>e.g. account balances<br><br>+------------+------+---------+<br>|    date    | ctry | balance |<br>+------------+------+---------+<br>| 2021-10-01 | SG   |     100 |<br>| 2021-10-01 | TH   |     200 |<br>| 2021-10-31 | SG   |     300 |<br>| 2021-10-31 | TH   |     400 |<br>+------------+------+---------+<br><br>+------------+-------+---------+<br>|    date    | <strong>ctry  </strong>| balance |<br>+------------+-------+---------+<br>| 2021-10-01 | &lt;<strong>all</strong>&gt; |     300 |<br>| 2021-10-31 | &lt;<strong>all</strong>&gt; |     700 |<br>+------------+-------+---------+<br><br>The example below is an invalid aggregation. Summing balances across time does not make sense.<br>+-------+------+---------+<br>| <strong>date  </strong>| ctry | balance |<br>+-------+------+---------+<br>| &lt;<strong>all</strong>&gt; | SG   |     400 |<br>| &lt;<strong>all</strong>&gt; | TH   |     600 |<br>+-------+------+---------+<br><br>When aggregating by [date], we can use other types of aggregations.<br>+-------+------+---------------+-------------+-------------+<br>| date  | ctry | <strong>balance_start </strong>| <strong>balance_end </strong>| <strong>balance_avg </strong>|<br>+-------+------+---------------+-------------+-------------+<br>| &lt;all&gt; | SG   |           100 |         300 |         200 |<br>| &lt;all&gt; | TH   |           200 |         400 |         300 |<br>+-------+------+---------------+-------------+-------------+<br><br><br><br>3. <strong>Non-Additive</strong> (Non-Summable)<br>These measures cannot be summed across any dimension. These are usually things like ratios, distinct counts, summarizations (e.g. mean, median).<br>For example, margin is (revenue-cost)/cost.<br><br>+---------+------+---------+------+--------+<br>| country | city | revenue | cost | <strong>margin </strong>|<br>+---------+------+---------+------+--------+<br>| SG      | SIN  |     200 |  100 |  0.500 |<br>| TH      | BKK  |     300 |  100 |  0.667 |<br>| TH      | CNX  |     400 |  100 |  0.750 |<br>+---------+------+---------+------+--------+<br><br>Summing on the margin column leads to a non-meaningful result.<br>+---------+-------+---------+------+--------+<br>| country | city  | revenue | cost | <strong>margin </strong>|<br>+---------+-------+---------+------+--------+<br>| &lt;all&gt;   | &lt;all&gt; |     900 |  300 |  1.917 |<br>+---------+-------+---------+------+--------+<br><br>Margin should be re-calculated using the aggregated revenue and cost.<br>+---------+-------+---------+------+--------+<br>| country | city  | revenue | cost | <strong>margin </strong>|<br>+---------+-------+---------+------+--------+<br>| &lt;all&gt;   | &lt;all&gt; |     900 |  300 |  0.667 |<br>+---------+-------+---------+------+--------+<br><br>For non-additive measures, we usually rely on the visualization&#39;s BI engine to calculate it dynamically. This allows the user to generate the measure for any cutof data she selects.<br>In some cases however, the <strong>scale of data makes it infeasible</strong> for the BI engine to calculate the aggregations on the fly. One solution is to implement mixed-grain stage tables.<br>In the example below, the margin values are <strong>pre-calculated</strong> and stored in a <strong>mixed-grain</strong> table.<br>+---------------+---------+-------+--------+<br>|     grain     | country | city  | margin |<br>+---------------+---------+-------+--------+<br>| all_countries | &lt;all&gt;   | &lt;all&gt; |  0.667 |<br>| country       | SG      | &lt;all&gt; |  0.500 |<br>| country       | TH      | &lt;all&gt; |  0.714 |<br>| city          | SG      | SIN   |  0.500 |<br>| city          | TH      | BKK   |  0.667 |<br>| city          | TH      | CNX   |  0.750 |<br>+---------------+---------+-------+--------+</pre><h3>4. Other Dimension Table Best Practices</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9ZSiAlcq_ihDe_6O" /><figcaption>Photo by <a href="https://unsplash.com/@waldemarbrandt67w?utm_source=medium&amp;utm_medium=referral">Waldemar Brandt</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><blockquote>In many ways, the data warehouse is only as good as the dimension attributes; the analytic power of the DW/BI environment is directly proportional to the quality and depth of the dimension attributes.<br>- The Data Warehouse Toolkit</blockquote><h4>Avoid having null dimension attributes</h4><p>Similar to null foreign keys, null dimension attributes should be avoided as tables cannot be joined on a null, and certain WHERE and GROUP clauses may not function as expected.</p><pre>Bad:<br>+---------+-----------+--------+<br>| item_id | item_name |  color |<br>+---------+-----------+--------+<br>|       1 | Glass     | (<strong>NULL</strong>) |<br>|       2 | Gloves    |  Blue  |<br>+---------+-----------+--------+<br><br>Good:<br>+---------+-----------+----------------+<br>| item_id | item_name |     color      |<br>+---------+-----------+----------------+<br>|       1 | Glass     | <strong>Not Applicable</strong> |<br>|       2 | Gloves    | Blue           |<br>+---------+-----------+----------------+</pre><h4>Include various verbose textual attributes</h4><p>Dimension tables should provide the <strong>final dimension names and groups that appear on the visualization</strong> the user interacts with. Don’t be afraid to create detailed attributes to improve usability.</p><p>Dimension tables are generally small, and the additional space required is insignificant. For example, in date tables, it is good practice to include all sorts of variations of date attributes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PJTAy2Ce_MNu0nW8ethKNA.png" /></figure><h4>Too many dimensions</h4><p>When a fact table has too many dimensions, becomes what’s called a <strong>centipede table</strong>. Too many dimensions significantly increase the storage space required and are also cumbersome to slice and dice.</p><p>Below is an example of a centipede table with many dimensions that are correlated. These are easy to fix; we just need to group all the related dimensions into one dimension.</p><pre>+-------------------------+      +-------------------------+<br>|     f_transactions      |      | f_transactions_improved |<br>+-------------------------+      +-------------------------+<br>| <strong>date_id                 </strong>|      | <strong>date_id                 </strong>|<br>| week_id                 |      | <strong>product_id              </strong>|<br>| month_id                | ---&gt; | <strong>store_id                </strong>|<br>| quarter_id              |      | qty                     |<br>| year_id                 |      | amount                  |<br>| fiscal_month_id         |      +-------------------------+<br>| fiscal_year_id          |<br>| <strong>product_id              </strong>|<br>| product_category_id     |<br>| product_package_type_id |<br>| <strong>store_id                </strong>|<br>| store_country_id        |<br>| qty                     |<br>| amount                  |<br>+-------------------------+</pre><h4>Junk Dimensions</h4><p>Sometimes dimensions are not related so there is no obvious way to group them up. In these cases, we can implement a <strong>Junk Dimension</strong>. The analogy is like junk drawers, where we dump loose items into one place. Let&#39;s understand how a junk dimension works:</p><pre>1. Let&#39;s assume dim1 - dim4 are not related to each other.<br>+------------+------+------+------+------+-----+<br>|  date_id   | <strong>dim1 </strong>| <strong>dim2 </strong>| <strong>dim3 </strong>| <strong>dim4 </strong>| qty |<br>+------------+------+------+------+------+-----+<br>| 2021-09-01 | A    |    1 | D1   | Z001 | 100 |<br>| 2021-09-01 | B    |    2 | D2   | Z002 | 200 |<br>| 2021-09-01 | B    |    3 | D2   | Z002 | 250 |<br>+------------+------+------+------+------+-----+<br><br>2. We first create a junk dimension table for dim1 - dim4.<br>+---------+------+------+------+------+<br>| <strong>junk_id </strong>| <strong>dim1 </strong>| <strong>dim2 </strong>| <strong>dim3 </strong>| <strong>dim4 </strong>|<br>+---------+------+------+------+------+<br>|       1 | A    |    1 | D1   | Z001 |<br>|       2 | A    |    1 | D1   | Z002 |<br>|       3 | A    |    1 | D2   | Z001 |<br>|       4 | A    |    1 | D2   | Z002 |<br>|       5 | A    |    2 | D1   | Z001 |<br>|       6 | A    |    2 | D1   | Z002 |<br>|       7 | A    |    2 | D2   | Z001 |<br>|       8 | A    |    2 | D2   | Z002 |<br>|       9 | A    |    3 | D1   | Z001 |<br>|      10 | A    |    3 | D1   | Z002 |<br>|      11 | A    |    3 | D2   | Z001 |<br>|      12 | A    |    3 | D2   | Z002 |<br>|      13 | B    |    1 | D1   | Z001 |<br>|      14 | B    |    1 | D1   | Z002 |<br>|      15 | B    |    1 | D2   | Z001 |<br>|      16 | B    |    1 | D2   | Z002 |<br>|      17 | B    |    2 | D1   | Z001 |<br>|      18 | B    |    2 | D1   | Z002 |<br>|      19 | B    |    2 | D2   | Z001 |<br>|      20 | B    |    2 | D2   | Z002 |<br>|      21 | B    |    3 | D1   | Z001 |<br>|      22 | B    |    3 | D1   | Z002 |<br>|      23 | B    |    3 | D2   | Z001 |<br>|      24 | B    |    3 | D2   | Z002 |<br>+---------+------+------+------+------+<br><br>3. Replace dim1 - dim4 with a junk_id.<br>+------------+---------+-----+<br>|  date_id   | <strong>junk_id </strong>| qty |<br>+------------+---------+-----+<br>| 2021-09-01 |       1 | 100 |<br>| 2021-09-01 |      19 | 200 |<br>| 2021-09-01 |      24 | 250 |<br>+------------+---------+-----+</pre><p>As you can see above, junk dimensions can get extremely large if the dimensions involved have high cardinality (unique values). Hence it is only recommended for low cardinality dimensions.</p><h4>Too few dimensions</h4><p>Even if a fact table can capture all required information with only a few dimensions, designers should try to provide additional dimensions that can reduce complexity, improve usability, and help insights generation. Some examples found in the book:</p><ul><li>Casual/Status dimensions</li><li>Date-related dimensions</li><li>Degenerate dimensions</li><li>Role-playing dimensions</li></ul><p>As a rule-of-thumb, the Data Warehouse Toolkit recommends that fact tables should have between <strong>5 to 20 dimensions</strong>.</p><h3>Things I Didn’t Cover</h3><p>Here are some useful topics I have skipped in the interest of time. (Perhaps I may write a part 2 to go over them.)</p><ul><li><strong>Slowly Changing Dimensions</strong> — Dimensional attributes may change over time, but just overwriting the old attributes will prevent analysis of any historical data that rely on them. We can solve these through <a href="https://en.wikipedia.org/wiki/Slowly_changing_dimension">traditional</a> or <a href="https://www.holistics.io/blog/scd-cloud-data-warehouse/">new-age</a> methods.</li><li><strong>Fact Tables Variants</strong>: Transaction, Periodic and Accumulating Snapshot — In this guide, I have only shown the transactional type of fact table.</li><li><strong>Enterprise Data Warehouse Bus Architecture</strong> and <strong>Opportunity/Stakeholder Matrix</strong> — These are useful frameworks to organize multiple user requirements, making it easier to formulate the best data warehouse design. The book covers these topics in great detail.</li></ul><h3>Conclusion</h3><p>These best practices may not fit every use-case. When in doubt, remember the guiding principles of <strong>simple, fast and flexible.</strong></p><p>I highly recommend reading <em>The Data Warehouse Toolkit </em>yourself. It includes many real-world case studies and goes into more detail on the concepts and techniques I have highlighted, as well as other advanced ones I did not cover.</p><p>Thank you for dropping by and have a wonderful day (:</p><blockquote>Unlike an operational implementation where business users have no choice but to use the new system, DW/BI usage is sometimes optional. Business users will embrace the DW/BI system if it is the simple and fast source for actionable information. — The Data Warehouse Toolkit</blockquote><h3>References</h3><p>[1] Ralph Kimball, Margy Ross, The Data Warehouse Toolkit, 3rd Edition, The Definitive Guide to Dimensional Modeling</p><h3>Check out my other articles!</h3><p><a href="https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-i-9c00d96d254c">How I used a Random Forest Classifier to Day Trade for 2 Months — Part 1</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=51740bedb4b6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/analytics-vidhya/data-warehouse-tips-and-best-practices-i-wish-i-knew-earlier-51740bedb4b6">Data Warehouse Tips and Best Practices I Wish I Knew Earlier</a> was originally published in <a href="https://medium.com/analytics-vidhya">Analytics Vidhya</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How I used a Random Forest Classifier to Day Trade for 2 Months — Part 2]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-2-56f5b388bbd4?source=rss-9d80fe825254------2"><img src="https://cdn-images-1.medium.com/max/2600/0*VeIuA103MtHeyiLK" width="4898"></a></p><p class="medium-feed-snippet">An end-to-end machine learning project.</p><p class="medium-feed-link"><a href="https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-2-56f5b388bbd4?source=rss-9d80fe825254------2">Continue reading on Analytics Vidhya »</a></p></div>]]></description>
            <link>https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-2-56f5b388bbd4?source=rss-9d80fe825254------2</link>
            <guid isPermaLink="false">https://medium.com/p/56f5b388bbd4</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[random-forest]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[trading]]></category>
            <dc:creator><![CDATA[Michael]]></dc:creator>
            <pubDate>Sun, 03 Jan 2021 15:20:14 GMT</pubDate>
            <atom:updated>2021-01-11T17:09:34.531Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[How I used a Random Forest Classifier to Day Trade for 2 Months — Part 1]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-i-9c00d96d254c?source=rss-9d80fe825254------2"><img src="https://cdn-images-1.medium.com/max/2600/0*WyYcqgmf9c69_-6B" width="5140"></a></p><p class="medium-feed-snippet">An end-to-end machine learning project.</p><p class="medium-feed-link"><a href="https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-i-9c00d96d254c?source=rss-9d80fe825254------2">Continue reading on Analytics Vidhya »</a></p></div>]]></description>
            <link>https://medium.com/analytics-vidhya/how-i-used-a-random-forest-classifier-to-day-trade-for-2-months-part-i-9c00d96d254c?source=rss-9d80fe825254------2</link>
            <guid isPermaLink="false">https://medium.com/p/9c00d96d254c</guid>
            <category><![CDATA[trading]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[random-forest]]></category>
            <category><![CDATA[machine-learning]]></category>
            <dc:creator><![CDATA[Michael]]></dc:creator>
            <pubDate>Sun, 03 Jan 2021 15:18:09 GMT</pubDate>
            <atom:updated>2021-05-14T01:40:29.743Z</atom:updated>
        </item>
    </channel>
</rss>