Data Generator - How to generate a date dimension ?

Data Generator - How to generate a date dimension ?

Date Dimension

A 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 generator where::

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()"

Output

With the data head command and the howto directory connection, you can test your generator and see the data.

tabli data head [email protected]
The first 10 rows of the data resource ([email protected]): 
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

Next

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

Tabulify - How to create a CSV File with generated data

Powered by ComboStrap