---json
{
    "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": "Date Dimension",
    "page_id": "sbo5vdkk4k03u1lu74dzn",
    "title": "How to generate a date dimension ?"
}
---
====== 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--datagen.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--datagen.yml>
Comment: An example of date dimension generator based on the `date_dim` table of TPCDS
Columns:
  - name: d_date_sk
    comment: A surrogate key
    Type: integer
    DataGenerator:
      type: sequence
  - name: d_date
    comment: A business key in date format
    Type: date
    DataGenerator:
      type: sequence
  - name: d_date_id
    comment: A business key in string
    Type: varchar
    DataGenerator:
      type: expression
      ColumnParents: d_date
      expression: "x.toISOString().substring(0,10)"
  - name: d_month_seq
    comment: An ascendant sequence for the month
    Type: integer
    DataGenerator:
      type: expression
      ColumnParents: d_date
      expression: "function pad(number) {if (number < 10) { return '0' + number; } return number; }; x.getFullYear()+''+(pad(x.getMonth()+1))"
  - name: d_day_name
    comment: The name of the day
    Type: varchar
    DataGenerator:
      type: expression
      ColumnParents: d_date
      expression: "var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday']; days[x.getDay()]"
  - name: d_moy
    comment: the month number in year
    Type: Integer
    DataGenerator:
      type: expression
      ColumnParents: d_date
      expression: "x.getMonth()+1"
  - name: d_year
    comment: The year number
    Type: Varchar
    Precision: 4
    DataGenerator:
      type: expression
      ColumnParents: d_date
      expression: "x.getFullYear()"

</file>
</unit>

==== Output ====
With the [[docs:tabli: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 dos>
tabli data head date_dim--datagen.yml@howto
</code>
<console>
The first 10 rows of the data resource (date_dim--datagen.yml@howto): 
d_date_sk   d_date       d_date_id    d_month_seq   d_day_name   d_moy   d_year   
---------   ----------   ----------   -----------   ----------   -----   ------   
        1   2020-12-11   2020-12-11        202012   Friday          12   2020     
        2   2020-12-10   2020-12-10        202012   Thursday        12   2020     
        3   2020-12-09   2020-12-09        202012   Wednesday       12   2020     
        4   2020-12-08   2020-12-08        202012   Tuesday         12   2020     
        5   2020-12-07   2020-12-07        202012   Monday          12   2020     
        6   2020-12-06   2020-12-06        202012   Sunday          12   2020     
        7   2020-12-05   2020-12-05        202012   Saturday        12   2020     
        8   2020-12-04   2020-12-04        202012   Friday          12   2020     
        9   2020-12-03   2020-12-03        202012   Thursday        12   2020     
       10   2020-12-02   2020-12-02        202012   Wednesday       12   2020
</console>
</unit>


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

<btn>
[[csv]]
</btn>