Manage Custom Date Dimensions

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

About the Custom Date Dimensions

The custom date dimension is represented in your workspace 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.

Depending on whether you need one or multiple date dimensions in your workspace (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 workspace 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 workspace by uploading a different calendar. If any metric or report use this date dimension, they will be updated automatically.

SELECT Amount where Quarter=Q1/2017

SELECT Amount where Quarter=Q4/FY2016

Extended Date Dimensions

By default, the urn:custom_v2:date date dimension 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.

If you need to cover dates up to 12/31/2100 but you want to avoid potentials performance issues, you can download urn_custom_v2_date_2100.csv.zip. This date dimension template contains dates from 01/01/1950 to 12/31/2100.

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 Workspace

To add the custom date dimension in your workspace, add a Date dataset to your workspace logical data model (LDM).

Steps:

1. From the Data Integration Console (see Accessing Data Integration Console), click Workspaces, then and click the name of the workspace 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 (for the instructions, see Publish a Logical Data Model). When publishing, use Preserve data to ensure that data loaded into the workspace is not deleted.

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

• If you want to use the default calendar, you can immediately start using it in your workspace. No further action is needed.
• If you want to upload a different calendar, proceed to Update urn:custom_v2:date with a Custom Calendar.

Using a MAQL DDL Script to Include the Custom Date Dimension

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

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 workspace.

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.

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

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

Steps:

1. Use the API for uploading date dimensions:

   • API resource: https://secure.gooddata.com/gdc/md/{workspace_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.

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

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

Life Cycle Management (LCM) and the Custom Date Dimension

Life Cycle Management (see Managing Workspaces 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.

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

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

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).

Different Method of Calculating Date Aggregations

Comparing to the urn:gooddata:date date dimension, the urn:custom_v2:date date dimension uses a different method of calculating date aggregations.

For example, a metric returning the latest year (such as SELECT MAX(Year)) would return the ID of the attribute value corresponding to the year, not the actual year (for instance, 121 instead of 2020).

If you need to display the actual year, update the metric:

1. Subtract a numeric value that equals the ID of the attribute value for the year. To do so, select the corresponding year from Attribute Values in the right-hand side menu. For information about editing a metric in the Custom Metric Editor, see Get Started with Using MAQL to Write Metrics.
2. Add a numeric value that equals the year.

For example, for the year of 2020, the updated metric would look like the following:

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

To migrate the Date datasets in your workspace 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.

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.