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 Dimensions

The custom date dimension is represented in your project data logical model (LDM) by a Date dataset (see Dates in Logical Data Models) based on the urn:custom_v2:date date dimension template. By default, this template contains data for the Gregorian calendar from 01/01/1900 to 12/31/2050.

You can extend date dimensions in custom calendars beyond the default to cover dates from 01/01/1900 to 12/31/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 a custom 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 01/01/1900 to 12/31/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).

Legacy Date Dimensions

The following date dimensions are in use but are not recommended for new implementations:

  • urn:custom:date: This date dimension is similar to the urn:custom_v2:date date dimension and offers all the capabilities that urn:custom_v2:date does except it does not support the option of comparing the data to the same period of the last year for weeks in date filters in Analytical Designer and KPI Dashboards.
  • urn:gooddata:date: This date dimension contains the default Gregorian calendar from 01/01/1900 to 12/31/2050 and cannot be customized to a custom fiscal calendar.

If you are using a legacy date dimension and want to migrate to the urn:custom_v2:date date dimension, see Migrate from a Legacy Date Dimension to urn:custom_v2:date.

Add the Custom Date Dimension to Your Project

To add the custom date dimension in your project, add a Date dataset 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.

Steps:

  1. From the Data Integration Console (see Accessing Data Integration Console), click Projects to open the Projects page, and click the name of the project where you want to add the custom date dimension.
  2. Click Model data on the top.
    The logical data model is displayed.
  3. Drag Date from the left panel and drop it in the canvas area.
    A Date dataset based on the urn:custom_v2:date date dimension is added. By default, it is named "Date".

    Before you publish the LDM, you can change the date dimension in the newly created Date dataset by clicking Edit and manually rewriting the date dimension. However, we do not recommend that and we encourage you to use the default date dimension, which is urn:custom_v2:date.

    After the LDM is published, the date dimension cannot be changed.

  4. Enter the name for the dataset.
  5. Create a relation between the Date dataset and another dataset that you want to be able to analyze by date.
    1. Select the Date dataset.
    2. Click the blue dot on the right border of the Date dataset and drag the arrow that appears to connect the Date dataset to the other dataset.
      The relation is created.
  6. Publish the LDM:
    1. Click Publish.
      You are prompted to choose the mode of publishing. The Preserve data mode is selected by default.
    2. Keep the Preserve data mode, and click Publish.
      The publishing process starts. When the publishing completes, you see a message that the LDM has been published. Close this message.

The urn:custom_v2:date date dimension with the Gregorian calendar data (GoodData default calendar) is now added to your project.

Using a MAQL DDL Script to Include the Custom Date Dimension

You can use API to add the custom date dimension to your project.

To do so, use the API for executing a MAQL DDL script with the following request body:

{
  "manage": {
    "maql": "INCLUDE TEMPLATE \"urn:custom_v2:date\""
  }
}

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

Update urn:custom_v2:date with a Custom Calendar

By default, urn:custom_v2:date contains the default calendar (the Gregorian calendar from 01/01/1900 to 12/31/2050). 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.

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 01/01/1900 to 12/31/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_v2_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 displayForm's (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 identifierValue patternExampleConstraint
date.day.yyyy_mm_ddyyyy-MM-dd1900-12-31Incremental 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]1Recurrent sequence
day.in.euweek.long
Monday
day.in.year.default[a-zA-Z_ ]*[0-9]D1Recurrent sequence
quarter.in.year.default[a-zA-Z_ ]*[0-9]Q1Recurrent sequence
month.in.quarter.number[a-zA-Z_ ]*[0-9]M1Recurrent sequence
month.in.year.short
Jan
month.in.year.m_q
M1/Q1
month.in.year.number[a-zA-Z_ ]*[0-9]M1Recurrent 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/1900Non-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/1900Non-decreasing sequence
euweek.wk_qtr_year_cont
W1/Q1/1900
week.in.year.number_us[a-zA-Z_ ]*[0-9]W1Recurrent sequence
day.in.week.short
Mon
day.in.week.number[a-zA-Z_ ]*[0-9]2Recurrent sequence
day.in.week.long
Monday
week.in.quarter.number_us[a-zA-Z_ ]*[0-9]W1Recurrent sequence
euweek.in.quarter.number_eu[a-zA-Z_ ]*[0-9]W1Recurrent 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

year.for.week.number
1900Non-decreasing sequence
year.for.euweek.number
1900Non-decreasing sequence
quarter.for.week.number
Q1/1900Non-decreasing sequence
quarter.for.euweek.number
Q1/1900Non-decreasing sequence

Use the Default Calendar

If you want to reset the calendar to the default one, download urn_custom_v2_date.csv.zip. This date dimension matches the default calendar in the urn:custom_v2:date date dimension (Gregorian calendar from 01/01/1900 to 12/31/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_v2_date.csv.zip will contain urn_fcnov1_v2_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_v2_date.csv.zip and is stored at /uploads/, enter the path as /urn_fcapr1_v2_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

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 upload the target calendar to the custom dimension in each workspace separately.

Migrate from a Legacy Date Dimension to urn:custom_v2:date

Why You Would Want to Migrate to urn:custom_v2:date

If you are using Analytical Designer and KPI Dashboards, we recommend that you switch to the urn:custom_v2:date date dimension. With the urn:custom_v2:date date dimension, you can use the option of comparing the data to the same period of the last year for weeks in date filters (for example, compare data for Week 25 in 2018 and Week 25 in 2017). When you compare your data to the same period last year, weeks may not have the same dates. If a week is split between two years/quarters (starts in the previous year/quarter and ends in the current year/quarter), the criteria that define what year/quarter this week belongs to must be established. The urn:custom_v2:date date dimension addresses these use cases.

For more information about date filters in Analytical Designer and KPI Dashboards, see Filters in Analytical Designer and Date Filters in KPI Dashboards.

Migrating to the urn:custom_v2:date date dimension only enables the GoodData platform to properly compute reports comparing data to the same period of the last year for weeks in date filters. To be able to fully use the filtering by weeks, you also have to complete other steps. For more information, see Enable Weeks in Date Filters.

How Migrating to urn:custom_v2:date Affects Your Data

Additional Columns in urn:custom_v2:date

Comparing to the legacy date dimensions, urn:custom_v2:date contains four additional columns:

  • year.for.week.number

  • year.for.euweek.number

  • quarter.for.week.number

  • quarter.for.euweek.number

These columns enable the option of comparing the data to the same period of the last year for weeks in date filters. These columns are not visible in the UI of Analytical Designer or KPI Dashboards and are used by the Extensible Analytics Engine (XAE) for report computation.

If a week is split between two years/quarters (starts in the previous year/quarter and ends in the current year/quarter), this is how it is established what year/quarter this week belongs to:

  • year.for.week.number and quarter.for.week.number: The week belongs to the year that the Saturday of this week falls into.

  • year.for.euweek.number and quarter.for.euweek.number: The week belongs to the year/quarter that the Thursday of this week falls into.

After switching to urn:custom_v2:date, you can expect the following:

  • euweek.in.year.number_eu is calculated according to ISO 8601 (that is, the week belongs to the year that the Thursday of this week falls into); a year has 52 (or 53 in a leap "week-based year") weeks.

  • week.in.year.number_us begins on a Sunday and belongs to the year that its last day belongs to.

  • euweek.in.quarter.number_eu is aligned with euweek.in.year.number_eu:
    • The first week in a year is the first week in a quarter.
    • Each quarter has 13 weeks (or 14 weeks in the last quarter of a leap "week-based year").
  • week.in.quarter.number_us is aligned with week.in.year.number_us, following the same rules as the European week but using the US data.

Different Identifiers of Attribute Labels

This applies only if you switch from the urn:gooddata:date date dimension.

Comparing to the urn:gooddata:date date dimension, urn:custom_v2:date contains different identifiers of attribute labels (displayForm).

  • If you load data to the GoodData platform using the API (see Loading Data via REST API), update yout SLI manifest with the new identifiers.
  • If you load data to the GoodData platform via CloudConnect graphs, check field mapping in the GD Dataset Writer components in your graphs.

First Week of 1900 Removed

This applies only if you switch from the urn:gooddata:date date dimension.

Comparing to the urn:gooddata:date date dimension, the urn:custom_v2:date date dimension no longer contains the first week of 1900 (1900-01-01 – 1900-01-07).

Migrate from a Legacy Date Dimension to urn:custom_v2:date

To migrate the Date datasets in your project from the urn:gooddata:date or urn:custom:date date dimension to urn:custom_v2:date date dimension, use the API for migrating Date datasets to the custom date dimension. You can migrate all the Date datasets at once or only some of them.

When migrating the datasets, do not perform other tasks (especially data loading) at the same time because it may cause data inconsistency.

If you use LCM and migrate the Date datasets in the master workspace to urn:custom_v2:date, the client workspaces created from this master workspace will inherit this custom dimension at the next synchronization.

Calendars for Fiscal Years Beginning with an Arbitrary Month for urn:custom:date

If you use the urn:custom:date date dimension and need to update it to a custom calendar, choose the CSV file depending on what month a fiscal year starts for you.

 Click here to see the details.
Powered by Atlassian Confluence and Scroll Viewport.