Custom Calendars - Self Service

The custom date dimension allows you to load and update data with its own fiscal dimensions in GoodData projects/workspaces.

For this purpose, a new template exists: urn:custom:date

It can be loaded via standard API:

/gdc/md/<project>/ldm/manage2

This API is used internally for example, via Cloud Connect LDM Modeler (see Include Date Dimension in the Project/Workspace below).

The new urn:custom:date dimension contains data by default for the Gregorian calendar from 1.1.1900 to 31.12.2050.

Date dimensions in custom calendars can be extended beyond the default from 1.1.1900 to 31.12.2100. However this can have impact upon performance. Wider defined ranges will result in slower performance. To improve performance when using custom date dimensions, do not use unnecessary ranges outside of your data and analytical use cases. For example, if you only need to analyse 2010 to 2030, keep your custom dimension between those dates.

The data in the included dimension can be modified by CSV upload via /gdc/md/<project>/datedimension/pull API with usage of the user space (see the Upload New Data into the Dimension section below for more details).

Contents:

Include Date Dimension in the Project/Workspace

Include date dimension in the project/workspace using the following MAQL DDL: uri:gdc/md/<project>/ldm/manage2

Syntax Example

This example includes metadata for the date dimension and loads the Gregorian calendar data (GoodData default calendar):

INCLUDE TEMPLATE "urn:custom:date" MODIFY (title "some_title", identifier "some_identifier") 

MODIFY and the subsequent string are optional when using this.

Upload Date Dimension CSV file

To update custom data you must prepare it and upload to the Staging area first:

  • CSV file with data stored in Staging area as a zipped package (plain CSV file only - other formats are not supported).

  • The Zip package must contain one file with the same name as the zip file, without the .zip extension. 
    For example package foo-bar.csv.zip will contain foo-bar.csv.

Additional information about Staging can be found at User Specific Data Storage.

Date Dimension CSV Format

Column headers represent the attribute display form identifiers. List of mandatory columns with mandatory order, and example of values follows:


Label identifier

Value pattern

Example

Constraint

date.day.yyyy_mm_dd

yyyy-MM-dd

1900-12-31

incremental

date.day.uk.dd_mm_yyyy


31/12/1900


date.day.us.mm_dd_yyyy


12/31/1900


date.day.eu.dd_mm_yyyy


01-01-1900


date.day.us.long


Mon, Jan 1, 1900


date.day.us.m_d_yy


1/1/00


day.in.euweek.short


Mon


day.in.euweek.number

[a-zA-Z_ ]*[0-9]

1

rec. seq.

day.in.euweek.long


Monday


day.in.year.default

[a-zA-Z_ ]*[0-9]

D1

rec. seq.

quarter.in.year.default

[a-zA-Z_ ]*[0-9]

Q1

rec. seq.

month.in.quarter.number

[a-zA-Z_ ]*[0-9]

M1

rec. seq.

month.in.year.short


Jan


month.in.year.m_q


M1/Q1


month.in.year.number

[a-zA-Z_ ]*[0-9]

M1

rec. seq.

month.in.year.long


January


week.wk_qtr_year


W1/Q1/1900


week.from_to


Dec 31, 1899 - Jan 6, 1900


week.starting


Wk. of Sun 12/31/1899


week.wk_year_cont


W53/1899 - W1/1900


week.wk_year


W1/1900

non-dec.

week.wk_qtr_year_cont


W14/Q4/1899 - W1/Q1/1900


euweek.wk_qtr_year


W1/Q1/1900


euweek.from_to


Jan 1, 1900 - Jan 7, 1900


euweek.starting


Wk. of Mon 01/01/1900


euweek.wk_year_cont


W1/1900


euweek.wk_year


W1/1900

non-dec.

euweek.wk_qtr_year_cont


W1/Q1/1900


week.in.year.number_us

[a-zA-Z_ ]*[0-9]

W1

rec. seq.

day.in.week.short


Mon


day.in.week.number

[a-zA-Z_ ]*[0-9]

2

rec. seq.

day.in.week.long


Monday


week.in.quarter.number_us

[a-zA-Z_ ]*[0-9]

W1

rec. seq.

euweek.in.quarter.number_eu

[a-zA-Z_ ]*[0-9]

W1

rec. seq.

day.in.quarter.default

[a-zA-Z_ ]*[0-9]

D1

rec. seq.

month.short


Jan 1900


month.number


1/1900

non-dec

month.long


January 1900


day.in.month.default

[a-zA-Z_ ]*[0-9]

D1

rec. seq.

year.default


1900

non-dec.

euweek.in.year.number_eu

[a-zA-Z_ ]*[0-9]

W1

rec. seq.

quarter.short_us


Q1/1900

non-dec.

The maximum allowed length of each column in CSV is 50 characters.

Constraints and Restrictions to the Field Format

Some of columns have special meaning for generating columns used for the algebraic engine and therefore the format of these values and sequences are restricted.

The uploaded CSV file must be sorted by date.day.yyyy_mm_dd with the following constraints satisfied:

  • Incremental sequence (date columns)

    • The difference between two strictly following records must be exactly one.

  • Non-decreasing recurrent sequence (day in month, day in week, etc.)

    • Each two strictly following records, a and b , must comply condition b = a , b = a+1 , or b is equal to minimal value in the sequence. For instance, sequence (5,5,6,6,7,7,1,2,3,3,3,3,1,2,3,4,5) is valid.

  • Non-decreasing sequence (week, year, etc.)

    • Each two strictly following records, a and b , must comply condition b = a or b = a+1 .

  • Uploading fiscal dimension always add default records representing empty values. You cannot modify these values. 

Date Dimension CSV Example

You can download urn_custom_date.csv.zip (zip file, 2 MB) CSV with default data (Gregorian calendar). Note that this matches the default calendar in the old date dimension. 

Additional Date Dimension CSV Examples

You can download the following Date Dimension CSVs for fiscal years beginning each month:

Upload New Data into the Dimension

Through post on /gdc/md/<project>/datedimension/pull:

Find the associated API for Fiscal Calendars.

Migration of Old Date Dimensions

Usage of a custom date dimension is the prefered approach for new projects. However, migration of old date dimensions into custom date dimension brings complications because identifiers for used display forms and internal data differ. Differences exist between urn:gooddata:date and urn:custom:date dimensions containing semantically the same data. Replacing an old dimension with a new one can break already stored filters. For more information see Current Limitations on Custom Date Dimensions below.

Current Limitations on Custom Date Dimensions

  • Incremental loads are not currently supported, you can only upload whole dimensions.
  • GoodData does not store uploaded CSV files. Loaded fiscal dimensions cannot be recovered from GoodData.
  • You cannot add or remove attributes or data labels for date dimensions so that different content can be uploaded using the methods described in this section.
  • An update must start with previously loaded dimension data. Otherwise, it can cause misinterpretation with previously loaded related data and used date-filters. For example metric “select Amount where Quarter=Q1/2017” may look like “select Amount where Quarter=Q4/FY2016” after upload of a different calendar. To avoid this situation, use floating intervals in date filters (e.g. “select Amount where Quarter=THIS”).
  • New date dimensions urn:custom:date is not backward compatible with urn:gooddata:date mainly in LCM (Life Cycle Management) use cases. Changes in logical model, e.g. some AttributeDisplayForm objects identifiers were renamed with less cryptic names.
  • Attribute elements for euweek (e.g. value “W1/Q1/1900”), month (e.g. value "Jan 1900"), quarter (e.g. value "Q1/1900") and year (e.g. value "1900") have different internal IDs so you cannot easily migrate reports/metrics filtering these values in urn:gooddata:date to a project using urn:custom:date.
  • Automated migration from urn:gooddata:date to urn:custom:date is not supported by API.
  • Lifecycle management does not currently support custom dimensions.