title: star-schema - fact table modeling
body: <p>I need to model a star schema for some business needs about liquidity stress testing.</p>
  <p>i will try to find an analogy example.
  let's say we have deals about financing/financial securities etc</p>
  <p>in the fact table,
  at a given date, this deals have the real value of X euros
  but will have a variation in time. thus we have some projection values.</p>
  <p>my concern is about how to represent this projection values for this deals, and more specifically what granularity to choose.</p>
  <p>( the example below is oversimplification of the fact table -- and yes it's Dimension Id's  that
  are used otherwise )</p>
  <p>Method 1 : as many metric columns as projection values calculated</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">AsofDate</th>
  <th style="text-align: center;">DealId</th>
  <th style="text-align: right;">0Day</th>
  <th style="text-align: right;">1Day</th>
  <th style="text-align: right;">7Days</th>
  <th style="text-align: right;">1Month</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal1</td>
  <td style="text-align: right;">100</td>
  <td style="text-align: right;">99</td>
  <td style="text-align: right;">98</td>
  <td style="text-align: right;">85</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal2</td>
  <td style="text-align: right;">150</td>
  <td style="text-align: right;">150</td>
  <td style="text-align: right;">120</td>
  <td style="text-align: right;">120</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal3</td>
  <td style="text-align: right;">100</td>
  <td style="text-align: right;">99</td>
  <td style="text-align: right;">98</td>
  <td style="text-align: right;">85</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal4</td>
  <td style="text-align: right;">100</td>
  <td style="text-align: right;">99</td>
  <td style="text-align: right;">98</td>
  <td style="text-align: right;">85</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Method 2 : add a granularity : a row is not anymore only a deal on a given that. it's a deal in a given date and it's projection in the next few days/months</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">AsofDate</th>
  <th style="text-align: center;">DealId</th>
  <th style="text-align: right;">projection</th>
  <th style="text-align: right;">value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal1</td>
  <td style="text-align: right;">0Day - actual</td>
  <td style="text-align: right;">100</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal1</td>
  <td style="text-align: right;">1Day</td>
  <td style="text-align: right;">99</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal1</td>
  <td style="text-align: right;">7Days</td>
  <td style="text-align: right;">99</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-01-01</td>
  <td style="text-align: center;">financingDeal1</td>
  <td style="text-align: right;">1Month</td>
  <td style="text-align: right;">85</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>from where i see it :
  for method 1, the main inconvenient is if in the futur, we have a new projection value for 3 months, we will need to add a column in the ETL/ in the OLAP cube for '3months'</p>
  <p>for method 2:
  we will have as many rows as (deals * projections) and we do have 11 projections so it's 11 rows for each deal and we do have 1Million+ of them.</p>
  <p>what is your opinion on this topic?</p>
  <p>Thanks for your consideration</p>