---json
{
    "aliases": [
        { "path": ":howto:generator:d_date" }
    ],
    "description": "A date dimension is a typical case for data generation and this article shows you how to generate it.\n",
    "low_quality_page": "false",
    "name": "Create a Date Dimension",
    "page_id": "sbo5vdkk4k03u1lu74dzn"
}
---
====== Data Generator - How to generate a date dimension ? ======




===== Date Dimension =====

A <icon name="mdi:clock-time-five" width="16"/>  ''date dimension'' is a typical case for data generation. ''Tabulify'' provides one ''date dimension'' that you can use out of the box and modify to your needs.



===== Steps =====

==== Date dimension explained ====

The ''date_dim--generator.yml'' is a [[docs:resource:generator|generator]] where::
  * the ''d_date_sk'', a [[kb>surrogate_key|surrogate key]] has a [[docs:generator:sequence|sequence generator]] that generates a ''integer'' in [[docs:generator:sequence|sequence]]
  * the ''d_date'', a [[kb>business_key|business key]] has a [[docs:generator:expression|expression generator]] that generates a ''date'' in [[docs:generator:sequence|sequence]]
  * the ''d_date_id'', the [[kb>business_key|business key]] that uses the ''d_date'' column and generates the date in string format
  * the ''d_month_seq'' is also a [[docs:generator:expression|expression generator]] that uses the ''d_date'' column and generates an ascendant sequence for the month. This column demonstrates how you can use a javascript function.
  * the ''d_day_name'' is also a [[docs:generator:expression|expression generator]] that demonstrates how to do a lookup of an array.
  * the last columns have been added as snippet and does not demonstrate any technic.

<unit >
<file yaml date_dim--generator.yml>
kind: generator
spec:
  Comment: An example of date dimension generator based on the `date_dim` table of TPCDS
  primary-columns: [ "d_date_sk" ]
  Columns:
    - name: d_date_sk
      comment: A surrogate key
      Type: integer
      data-supplier:
        type: sequence
    - name: d_date
      comment: A business key in date format
      Type: date
      data-supplier:
        type: sequence
        arguments:
          start: 2025-05-13
    - name: d_date_id
      comment: A business key in string
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.toISOString().substring(0,10)"
    - name: d_month_seq
      comment: An ascendant sequence for the month
      Type: integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "function pad(number) {if (number < 10) { return '0' + number; } return number; }; d_date.getFullYear()+''+(pad(d_date.getMonth()+1))"
    - name: d_day_name
      comment: The name of the day
      Type: varchar
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday']; days[d_date.getDay()]"
    - name: d_moy
      comment: the month number in year
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getMonth()+1"
    - name: d_year
      comment: The year number
      Type: Integer
      data-supplier:
        type: expression
        arguments:
          column-variable: d_date
          expression: "d_date.getFullYear()"

</file>
</unit>

==== Output ====
With the [[docs:tabul:data:head|data head command]] and the [[docs:connection:howto|howto directory]] connection, you can test your [[docs:resource:generator|generator]] and see the data.

<unit>
<code bash>
tabul data head date_dim--generator.yml@howto
</code>
<console>
The first 10 rows of the data resource (date_dim@memgen):
d_date_sk   d_date       d_date_id    d_month_seq   d_day_name   d_moy   d_year
---------   ----------   ----------   -----------   ----------   -----   ------
        1   2025-05-12   2025-05-12        202505   Monday           5     2025
        2   2025-05-11   2025-05-11        202505   Sunday           5     2025
        3   2025-05-10   2025-05-10        202505   Saturday         5     2025
        4   2025-05-09   2025-05-09        202505   Friday           5     2025
        5   2025-05-08   2025-05-08        202505   Thursday         5     2025
        6   2025-05-07   2025-05-07        202505   Wednesday        5     2025
        7   2025-05-06   2025-05-06        202505   Tuesday          5     2025
        8   2025-05-05   2025-05-05        202505   Monday           5     2025
        9   2025-05-04   2025-05-04        202505   Sunday           5     2025
       10   2025-05-03   2025-05-03        202505   Saturday         5     2025
</console>
</unit>


==== Next ====
If you want to know how to create a CSV from it, check the below page

<btn>
[[..:csv:generate]]
</btn>