Custom Calendars - Self Service

The custom date dimension allows you to load and update data with your own fiscal dimensions in GoodData projects. For example, if the fiscal year in your project starts on April 1, you would use the custom date dimension for the month of April.

Contents:

About the Custom Date Dimension

The custom date dimension is represented in your project data logical model (LDM) by a Date dataset based on the urn:custom:date date dimension template (see Definition of Date Dataset). By default, the urn:custom:date date dimension contains data for the Gregorian calendar from 1.1.1900 to 31.12.2050.

You can extend date dimensions in custom calendars beyond the default from 1.1.1900 to 31.12.2100. However, wider defined ranges may 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 analyze 2010 to 2030, keep your custom dimension between those dates.

Depending on whether you need one or multiple date dimensions in your project (for example, you want to differentiate the purchase date and the delivery date), you can have one or multiple Date datasets with the urn:custom:date date dimension. You can add another custom date dimension to your project at any time and start using it immediately.

When using the custom date dimension, you can have the following options:

  • Use the custom date dimension as is (that is the default Gregorian calendar from 1.1.1900 to 31.12.2050)
  • Customize the custom date dimension:
    • Upload a calendar for a fiscal year beginning with an arbitrary month (for example, for a fiscal year starting in April)
    • Upload your own calendar

Whenever needed, you can customize an existing custom date dimension in your project by uploading a different calendar. If any metric/measure or report use this date dimension, they will be updated automatically.

The custom calendar that you want to upload and the current calendar must be consistent in terms of time intervals. Otherwise, uploading an inconsistent calendar may cause misinterpretation with the previously loaded data and used date filters.

For example, after a different calendar has been uploaded, the following metric:

SELECT Amount where Quarter=Q1/2017

may become this metric:

SELECT Amount where Quarter=Q4/FY2016

To avoid this situation, use floating intervals in date filters (for example, SELECT Amount where Quarter=THIS).

Use the Custom Date Dimension in Your Project

To start using the custom date dimension in your project, do the following:

  1. Include the custom date dimension in your project.
    This will upload the default calendar (the Gregorian calendar from 1.1.1900 to 31.12.2050) to your project.
  2. If you do not want to use the default calendar, update the custom date dimension with a custom calendar:
    1. Prepare a CSV file with calendar requirements.
    2. Transfer the CSV file to user-specific storage.
    3. Run a load task.

Include the Custom Date Dimension in Your Project

To include the custom date dimension in your project, add a Date dataset with the urn:gooddata:date date dimension template to your project logical data model (LDM).

Including the custom date dimension in a project does not affect or update any other date dimension that already exists in the project. If you want to update an existing custom date dimension, see Update the Custom Date Dimension with a Custom Calendar.

Steps:

  1. In CloudConnect, on the Navigator tab, open the project folder that contains your LDM.
  2. In the folder, locate the LDM file you want to update.
  3. Double-click the LDM file to open it.
    The data model is displayed in the Model Editor pane.
  4. In the Details sidebar, click Add Date.
    A Date dataset is added to your LDM.
    By default, the dataset uses the urn:gooddata:date date dimension.
  5. Replace urn:gooddata:date with urn:custom:date.
  6. Provide the dataset name and identifier.
    The properties of the Date dataset should look similar to the following:
  7. Save the changes in the LDM.
  8. Publish the logical data model. For the detailed instruction on publishing, see Publishing Your Data Model.
    The urn:custom:date date dimension with the Gregorian calendar data (GoodData default calendar) is added to your project.

Using a MAQL DDL Script to Include the Custom Date Dimension

If you cannot or do not want to use CloudConnect to update your LDM, use the API for executing a MAQL DDL script with the following request body:

{
  "manage": {
    "maql": "INCLUDE TEMPLATE "urn:custom:date""
  }
}

This API adds the urn:custom:date date dimension with the default Gregorian calendar data to your project.

Update the Custom Date Dimension with a Custom Calendar

  1. Prepare a CSV file with calendar requirements.
  2. Transfer the CSV file to user-specific storage.
  3. Run a load task.

Prepare a CSV File with Calendar Requirements

You can choose to use a calendar for fiscal years beginning with an arbitrary month or to create your own calendar.

You can also return to using the default calendar (Gregorian calendar from 1.1.1900 to 31.12.2050) from any previously uploaded calendar.

You can upload only a whole dimension. You cannot upload just a part of it.

Use a Calendar for Fiscal Years Beginning with an Arbitrary Month

Download the CSV file with the date dimension depending on what month a fiscal year starts for you:

Use Your Own Calendar

Create a CSV file with your custom fiscal calendar requirements. You can download the CSV file with the default calendar (urn_custom_date.csv.zip) and update it to meet your needs.

Your CSV file must meet the following requirements:

  • The file contains columns in the predefined order (exactly as listed in the table below) and examples of values.
  • A column contains max 50 characters.
  • Column headers represent the identifiers of attribute displayForms (attribute labels).
  • Rows are sorted by date.day.yyyy_mm_dd.
  • The following constraints are satisfied for certain columns (see the table below):
    • 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, and so on): Each two strictly following records, a and b, must meet one of the following conditions:
      • b=a
      • b=a+1
      • b is equal to the minimal value in the sequence (for example, 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, and so on): Each two strictly following records, a and b, must meet either of the following conditions:
      • b=a
      • b=a+1

Uploading the fiscal dimension adds default records representing empty values. You cannot modify these values.

Label identifier

Value pattern

Example

Constraint

date.day.yyyy_mm_dd

yyyy-MM-dd

1900-12-31

Incremental sequence

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

Recurrent sequence

day.in.euweek.long


Monday


day.in.year.default

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

D1

Recurrent sequence

quarter.in.year.default

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

Q1

Recurrent sequence

month.in.quarter.number

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

M1

Recurrent sequence

month.in.year.short


Jan


month.in.year.m_q


M1/Q1


month.in.year.number

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

M1

Recurrent sequence

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-decreasing sequence

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-decreasing sequence

euweek.wk_qtr_year_cont


W1/Q1/1900


week.in.year.number_us

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

W1

Recurrent sequence

day.in.week.short


Mon


day.in.week.number

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

2

Recurrent sequence

day.in.week.long


Monday


week.in.quarter.number_us

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

W1

Recurrent sequence

euweek.in.quarter.number_eu

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

W1

Recurrent sequence

day.in.quarter.default

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

D1

Recurrent sequence

month.short


Jan 1900


month.number


1/1900

Non-decreasing sequence

month.long


January 1900


day.in.month.default

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

D1

Recurrent sequence

year.default


1900

Non-decreasing sequence

euweek.in.year.number_eu

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

W1

Recurrent sequence

quarter.short_us


Q1/1900

Non-decreasing sequence

Use the Default Calendar

If you want to reset the calendar to the default one, download urn_custom_date.csv.zip. This date dimension matches the default calendar in the urn:custom:date date dimension (Gregorian calendar from 1.1.1900 to 31.12.2050).

Transfer the CSV File to User-Specific Storage

Steps:

  1. Zip the CSV file.
    The archive file must contain one file with the same name as the zip file, without the .zip extension. For example, the archive file urn_fcnov1_date.csv.zip will contain urn_fcnov1_date.csv.
  2. Place the archive file to your data storage (see User Specific Data Storage).

Run a Load Task

The following procedure assumes that you access your projects at https://secure.gooddata.com/.

If you are a white-labeled customer, replace secure.gooddata.com with your white-labeled domain in the procedure steps.

Steps:

  1. Use the API for uploading date dimensions:
    • API resource: https://secure.gooddata.com/gdc/md/{project_id}/datedimension/pull
    • Method: POST
    • Request body:

      {
        "dateIntegration": {
          "file": "/path/to/archive/file.zip",
          "datasets": [
            "{date_dataset_identifier}"
          ]
        }
      }
      • Do not include the /uploads section in the file path. For example, if your file is named urn_fcapr1_date.csv.zip and is stored at /uploads/, enter the path as /urn_fcapr1_date.csv.zip.

      • {date_dataset_identifier} specifies the date dataset where you want to upload the CSV file. For example, date1.dataset.dt.

    • Request headers:

      Content-Type:application/json
      Accept:application/json

    The task runs, and the link for polling for task status is returned.

  2. Poll for the status until the OK task status is returned.
    The custom date dimension is updated with the selected calendar.

Uploaded CSV files are not stored and cannot be recovered from the GoodData platform.


Instead of using the API, you can use the gray page for updating the custom date dimension:

https://secure.gooddata.com/gdc/md/{project_id}/datedimension/pull

Migration to the Custom Date Dimension

While using urn:custom:date is preferable in new projects, many existing projects use the urn:gooddata:date date dimension or customer-specific date dimensions. Migration from those date dimensions to urn:custom:date in existing projects may bring complications.

Although urn:gooddata:date and urn:custom:date date dimensions contain semantically the same data, there are certain differences between them (the identifiers of attribute displayForms and internal data differ). For example, attribute elements for euweek (for instance, the value W1/Q1/1900), month (for instance, the value Jan 1900), quarter (for instance, the value Q1/1900) and year (for instance, the value 1900) have different internal IDs. Therefore, you cannot easily migrate reports/metrics filtering these values in urn:gooddata:date to a project using urn:custom:date. Replacing urn:gooddata:date with urn:custom:date can break already stored filters.

Migration from urn:gooddata:date to urn:custom:date cannot be done automatically or via API. If migration is absolutely necessary in your project, get in touch with your GoodData contact person.

Life Cycle Management (LCM) and the Custom Date Dimension

Life Cycle Management (see Managing Projects via Life Cycle Management) supports the custom date dimension.

  • If the master workspace includes the custom date dimension, client workspaces created from this master workspace inherit this custom dimension, but with the default Gregorian calendar in it regardless of what calendar the master workspace's date dimension contains. You can then upload a different calendar to a client workspace's custom date dimension, if needed. This calendar will not be overwritten at synchronization.
  • You can have different calendars in the custom date dimension in different client workspaces within the same segment. If you need to unify the calendar in the custom date dimension in the master space and its client workspace, you have to do upload the target calendar to the custom dimension in each workspace separately.
  • Migration between the custom date dimension and other date dimension types is not supported.