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::
- the d_date_id, the business key that uses the d_date column and generates the date in string format
- the d_month_seq is also a 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 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.
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 date_dim--datagen.yml@howto
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
Next
If you want to know how to create a CSV from it, check the below page