Dynamic Time Series

Dynamic Time Series (DTS) provides period-to-date and rest-of-period calculations as declarative cube configuration rather than member formulas.

DTS supports:

• YTD — Year to Date
• QTD — Quarter to Date
• HTD — Half Year to Date
• ROY — Rest of Year
• ROQ — Rest of Quarter
• ROH — Rest of Half Year

The calculations are evaluated dynamically at query time. DTS values are not precomputed or stored in the fact table.

Note

Dynamic Time Series is not a formula function. It is cube configuration evaluated by the query engine. When DTS owns a member, its member formula is suppressed so the DTS calculation determines the value.

What Dynamic Time Series Solves

Period-to-date calculations can be cumbersome to maintain as member formulas.

A YTD formula, for example, must determine the current period, accumulate all periods from the beginning of the year through that period, and handle flow and balance accounts differently. Rest-of-period calculations require similar logic in the opposite direction.

DTS replaces that formula logic with configuration describing:

• The member that represents the DTS calculation, such as YTD or ROY
• The member containing periodic data, such as MTD
• The calculation grain: YEAR, QUARTER, or HALFYEAR
• The first position in the grain to include
• The direction of the calculation: TODATE or FROMDATE

The query engine uses this configuration to construct the appropriate time window dynamically.

The DTS Mental Model

A DTS member does not live on the Time dimension.

DTS operates on a separate, non-Time dimension, conventionally named VIEW or VIEWS. The DTS dimension describes how a value should be read, while the Time dimension identifies which period is being evaluated.

For example:

Period	VIEW = MTD	VIEW = QTD	VIEW = YTD
Jan	Jan	Jan	Jan
Feb	Feb	Jan–Feb	Jan–Feb
Mar	Mar	Jan–Mar	Jan–Mar
Apr	Apr	Apr	Jan–Apr

The requested Time member is the anchor period. You do not query a special YTD time member. Instead, you query a leaf period such as Mar together with VIEW = YTD.

For example:

PERIOD = Mar
VIEW   = YTD

For a flow account, the result represents January through March.

To-Date and Rest-of-Period Directions

DTS supports two directions.

TODATE runs backward from the anchor period to the beginning of the configured grain.

Typical members are:

• YTD
• QTD
• HTD

FROMDATE runs forward from the anchor period to the end of the configured grain.

Typical members are:

• ROY
• ROQ
• ROH

For example:

Period	YTD (TODATE, YEAR)	ROY (FROMDATE, YEAR)	ROQ (FROMDATE, QUARTER)
Jan	Jan	Jan–Dec	Jan–Mar
Mar	Jan–Mar	Mar–Dec	Mar
Apr	Jan–Apr	Apr–Dec	Apr–Jun
Dec	Jan–Dec	Dec	Dec

Both directions include the anchor period.

Each requested period receives its own independently calculated window. A query for Jan, Feb, and Mar with VIEW = YTD therefore returns three different running totals.

Prerequisites

DTS requires the cube to have:

1. A dimension configured as the cube’s Time dimension
2. A separate, non-Time, non-attribute dimension containing the DTS members
3. A Time hierarchy with the calendar structure required by the configured grain
4. A Time dimension that has been built with DTS support

An Accounts dimension is not required for DTS to execute.

However, if the cube contains balance accounts such as cash, headcount, or inventory, an Accounts dimension with Time Balance configuration is strongly recommended. Without Time Balance metadata, DTS treats all accounts as flow accounts and sums them across the window.

Calendar Requirements

DTS identifies grain boundaries from the Time hierarchy.

Supported grains are:

Grain	Required calendar structure
YEAR	A year grouping containing 12 leaf periods
QUARTER	Quarter groupings containing 3 leaf periods each
HALFYEAR	Half-year groupings containing 6 leaf periods each

The Time dimension must be nested at least two levels below its top member. Leaf periods directly beneath the top member are not used as DTS windows.

DTS is designed for monthly calendars. Weekly, 13-period, and other irregular calendars do not provide the supported DTS grain structure.

DTS Configuration

Each DTS member has a configuration containing:

Setting	Description
DTS_DIM	Dimension containing the DTS members
DTS_MEMBER	Member that returns the DTS value, such as YTD or ROY
BASE_MEMBER	Member containing periodic data, such as MTD
GRAIN	YEAR, QUARTER, or HALFYEAR
SKIP_POS	First period position in the grain to include
DIRECTION	TODATE or FROMDATE
ACTIVE	Whether the DTS entry is active
SOURCE	AUTO for automatically seeded entries or MANUAL for administrator-managed entries

SKIP_POS defaults to 1, which includes the entire grain.

For example, this configuration:

DTS_DIM     = VIEW
DTS_MEMBER  = QTD
BASE_MEMBER = MTD
GRAIN       = QUARTER
SKIP_POS    = 1
DIRECTION   = TODATE

means that querying VIEW = QTD at PERIOD = Mar calculates the appropriate value over January through March.

A rest-of-quarter configuration uses the same model with:

DTS_MEMBER = ROQ
DIRECTION  = FROMDATE

At PERIOD = Feb, the window is February through March.

Automatic Configuration

Casabase Cube can automatically seed common period-to-date DTS configurations during a dimension build.

For eligible non-Time, non-attribute dimensions, member names are compared with punctuation removed and case ignored.

The following naming patterns are recognized:

Role	Recognized names	Configuration
Base member	MTD, Periodic	Used as BASE_MEMBER
DTS member	YTD	GRAIN = YEAR
DTS member	QTD	GRAIN = QUARTER
DTS member	HTD	GRAIN = HALFYEAR

Automatically seeded entries use:

SKIP_POS = 1
DIRECTION = TODATE
ACTIVE = TRUE
SOURCE = AUTO

Only leaf, non-shared members are considered.

If no recognizable base member exists, DTS configuration is not automatically created.

Rest-of-Period Members

ROY, ROQ, and ROH are not automatically seeded.

Configure rest-of-period members explicitly with MANAGE_CUBE_DTS and:

DIRECTION = FROMDATE

If an imported member already has a rest-of-period formula, compare the DTS result with the imported formula before replacing it. The imported formula may contain additional business logic that is not represented by a DTS window.

Automatic vs. Manual Entries

Automatically generated entries have:

SOURCE = AUTO

A dimension rebuild can refresh these entries.

Entries configured through MANAGE_CUBE_DTS have:

SOURCE = MANUAL

Manual entries are preserved during automatic DTS seeding.

Use MANAGE_CUBE_DTS when changing an automatically seeded configuration so that the resulting entry is treated as administrator-managed rather than being replaced during a later rebuild.

Manual Configuration

Use CUBE.MANAGE_CUBE_DTS to create, update, deactivate, or remove DTS configuration.

The procedure requires CUBE_ADMIN.

CUBE.MANAGE_CUBE_DTS(
    P_CUBE_NAME,
    P_ACTION,
    P_DTS_DIM,
    P_DTS_MEMBER,
    P_BASE_MEMBER,
    P_GRAIN,
    P_SKIP_POS,
    P_ACTIVE,
    P_IDS,
    P_DIRECTION
)

For the complete procedure signature and parameter reference, see Variables & Dynamic Time Series.

Configure Period-to-Date Members

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'YTD',
    'MTD',
    'YEAR',
    1,
    TRUE,
    NULL
);

For QTD:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'QTD',
    'MTD',
    'QUARTER',
    1,
    TRUE,
    NULL
);

For HTD:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'HTD',
    'MTD',
    'HALFYEAR',
    1,
    TRUE,
    NULL
);

When P_DIRECTION is omitted, the entry uses TODATE.

Configure Rest-of-Period Members

Set P_DIRECTION to FROMDATE.

For ROY:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'ROY',
    'MTD',
    'YEAR',
    1,
    TRUE,
    NULL,
    'FROMDATE'
);

For ROQ:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'ROQ',
    'MTD',
    'QUARTER',
    1,
    TRUE,
    NULL,
    'FROMDATE'
);

For ROH:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'ROH',
    'MTD',
    'HALFYEAR',
    1,
    TRUE,
    NULL,
    'FROMDATE'
);

Caution

Always include P_DIRECTION = 'FROMDATE' when updating a rest-of-period entry. An UPSERT that omits the direction uses the default TODATE behavior.

MANAGE_CUBE_DTS does not verify that the named DTS member and base member exist. Confirm that both are valid members of the configured DTS dimension.

Skip the First Period

Set SKIP_POS to 2 to exclude the first period of each grain.

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'YTD',
    'MTD',
    'YEAR',
    2,
    TRUE,
    NULL
);

This can be useful when the first period represents an opening balance that should not participate in a flow calculation.

Deactivate an Entry

Set P_ACTIVE to FALSE:

CALL CUBE.MANAGE_CUBE_DTS(
    'ENT_RPTG',
    'UPSERT',
    'VIEW',
    'YTD',
    'MTD',
    'YEAR',
    1,
    FALSE,
    NULL
);

Deactivation is useful while testing because it preserves the configuration while releasing DTS ownership of the member.

Querying DTS

Query a DTS member by referencing it directly on its DTS dimension and selecting leaf anchor periods on the Time dimension.

For example:

CALL CUBE.QUERY_CUBE(
    'ENT_RPTG',
    '{
      "ACCOUNT":["21950"],
      "PERIOD":["Jan","Feb","Mar"],
      "YEARS":["FY25"],
      "SCENARIO":["Actual"],
      "CURRENCY":["Local"],
      "ENTITY":["A1056"],
      "VIEW":["YTD"]
    }'
);

For a flow account, this returns a separate YTD value for each requested anchor period:

Jan -> Jan
Feb -> Jan..Feb
Mar -> Jan..Mar

A rest-of-year query uses the same pattern:

CALL CUBE.QUERY_CUBE(
    'ENT_RPTG',
    '{
      "ACCOUNT":["21950"],
      "PERIOD":["Jan","Apr","Oct"],
      "YEARS":["FY25"],
      "SCENARIO":["Actual"],
      "CURRENCY":["Local"],
      "ENTITY":["A1056"],
      "VIEW":["ROY"]
    }'
);

With ROY configured as FROMDATE, the flow-account windows are:

Jan -> Jan..Dec
Apr -> Apr..Dec
Oct -> Oct..Dec

Query Rules

DTS queries must follow four important rules.

1. Reference the DTS Member Directly

Use a direct member reference such as:

{"VIEW":["YTD"]}

or:

{"VIEW":[{"member":"YTD"}]}

Do not select a DTS member indirectly through expansion operators such as ichildren.

If an expansion would include a DTS member, the query is rejected rather than returning an empty or misleading value.

2. Query One DTS Member at a Time

Do not combine a DTS member with other members from the same DTS dimension in one query.

For example, do not use:

{"VIEW":["MTD","YTD"]}

Query the members separately and combine the results in the consuming application or report if necessary.

3. Anchor on Leaf Periods

The Time dimension selection must contain leaf periods.

For example:

{"PERIOD":["Jan","Feb","Mar"]}

A summary member such as Qtr1 is not a supported DTS anchor.

Period selections can be expressed as:

{"PERIOD":["Mar"]}

{"PERIOD":"Mar"}

or:

{"PERIOD":[{"member":"Mar"}]}

4. The Grain Must Exist in the Calendar

The Time hierarchy must contain the structure required by the configured grain.

A QUARTER entry requires quarter groupings containing three leaf periods. A HALFYEAR entry requires half-year groupings containing six leaf periods.

If the required grouping does not exist, DTS cannot construct that window.

Time Balance

Time Balance is what allows DTS to produce meaningful results for both flow and balance accounts.

A flow account such as Revenue or Expense accumulates over time.

A balance account such as Cash, Headcount, or Inventory generally should not be summed across periods.

When an Accounts dimension has Time Balance metadata, DTS applies the configured behavior to each account.

To-Date Behavior

For TODATE calculations:

Time Balance	DTS behavior
SUM / FLOW	Sum every period in the window
LAST	Use the last populated period in the window
FIRST	Use the value at the opening period of the window
AVERAGE	Average the periods in the window

For example, at March:

• YTD Revenue can be Jan + Feb + Mar
• YTD Cash with LAST returns the March closing balance

LAST and FIRST intentionally behave differently. LAST can use the last populated period in the window, while FIRST reads the fixed opening position.

Rest-of-Period Behavior

For a FROMDATE window such as ROY or ROQ, the window runs from the anchor period forward to the end of the grain.

Time Balance	DTS behavior
SUM / FLOW	Sum from the anchor through the end of the grain
LAST	Use the value at the anchor period
FIRST	Use the last populated period in the window
AVERAGE	Average the periods from the anchor through the end of the grain

For example, ROY Revenue at April can represent April through December.

For a LAST balance account, ROY at April returns the balance at April rather than the December closing balance.

Note

If your reporting requirement is specifically the closing balance at the end of the year, ROY on a LAST account does not represent that calculation.

Skip Settings

Account Time Balance configuration can also specify that missing values, zero values, or both should be skipped.

These settings affect which periods participate in FIRST, LAST, and AVERAGE behavior.

A DTS window that contains no qualifying periods after the skip rules are applied returns no row rather than a zero.

DTS and Member Formulas

When DTS is configured for a member, DTS owns that member’s value.

The member’s active formula is suppressed so that a formula does not compete with the DTS calculation.

The original formula source remains available in the consumer-facing dimension view.

Conceptually, a DTS-owned member appears as:

FORMULA        = NULL
FORMULA_SOURCE = <original source expression>

Formula suppression is durable across dimension rebuilds and formula re-imports.

When the DTS entry is deactivated or removed, the suppression is released. The member’s formula can then be restored through formula import.

Caution

Do not assign a formula to an active DTS member. DTS owns the member’s value and the formula will be suppressed.

Security

DTS operates within the Casabase Cube security model.

Row-level security is applied to the underlying periodic data before the DTS window is evaluated.

EXCLUDE Security

Under EXCLUDE, DTS calculates the result from the data the user is permitted to read.

As with ordinary aggregation, the resulting value is not marked as a partial total.

MASK Security

Under MASK, if any period contributing to a DTS cell includes data the user cannot access, the result is:

#NOACCESS

This prevents a DTS calculation from exposing a numeric result when part of its underlying window is protected.

Aliases, Saved Queries, and Calculated Members

Aliases apply to DTS results in the same way they apply to other query results.

DTS queries can also be saved and scheduled. Because a query should contain only one DTS member from a DTS dimension, separate saved queries are typically used for Periodic, YTD, ROY, and other views.

A DTS member is not a calculated member and does not have a solve order. If a DTS query includes calculated members on other dimensions, those members follow their normal formula dependency and solve-order behavior.

Lifecycle

DTS configuration participates in the cube lifecycle as follows:

Event	Effect
Dimension rebuild	Refreshes AUTO DTS entries while preserving MANUAL entries. Rebuilds the Time structures used by DTS.
Data load	No configuration change and no recalculation step. New periodic data is reflected dynamically.
Drop dimension	Removes DTS entries that use the dropped dimension as their DTS axis.
Drop cube	Removes the cube’s DTS configuration.
Clone cube	Copies DTS configuration and associated formula suppression to the target cube.
Application upgrade	Preserves DTS configuration. Existing entries created before DIRECTION was introduced use TODATE.

If a Time dimension was last built before DTS support was available, rebuild that dimension before relying on DTS. An application upgrade by itself does not rebuild dimensions.

Important Considerations

Validate DTS Results Before Publishing

Before using a DTS member in production reporting, compare it with the underlying periodic values.

For a flow account, query the base member across the applicable periods and confirm that the DTS value matches the expected window.

For example, to validate YTD at March:

MTD Jan + MTD Feb + MTD Mar = YTD Mar

For ROY at April:

MTD Apr + MTD May + ... + MTD Dec = ROY Apr

Balance accounts should be validated according to their Time Balance behavior rather than by summing periods.

Some Unsupported DTS Conditions Can Return No Rows

DTS has explicit protections for several unsupported query patterns, including expansion of DTS members and mixing DTS with ordinary members on the same DTS dimension.

Other conditions can still prevent DTS from resolving without producing a DTS-specific query error. These include:

• The Time dimension has not been rebuilt with DTS support
• The calendar does not contain the configured grain
• The query does not select leaf anchor periods
• DTS members are requested from two different DTS dimensions in the same query

Because a DTS-owned member normally has neither stored data nor an active formula, a DTS calculation that does not resolve can appear as a blank result.

Treat an unexpected blank DTS result as a configuration or query issue to investigate rather than assuming that no source data exists.

Troubleshooting

Symptom	What to check
DTS member returns no rows	Confirm the Time dimension has been rebuilt, the entry is active, leaf periods are selected, and the calendar contains the configured grain.
DTS result is not period-to-date	Verify the DTS calculation against the base member and expected window.
DTS member cannot be selected via expansion	Reference the DTS member directly and query it separately.
Query cannot determine whether DTS is involved	Reference members on the DTS dimension explicitly and query one DTS member at a time.
Balance accounts are summed	Confirm an Accounts dimension is configured with the correct Time Balance metadata.
ROY on a LAST account returns the anchor-period balance	This is the expected FROMDATE behavior for LAST.
ROY or ROQ returns to-date values	Reapply the configuration with P_DIRECTION = 'FROMDATE'.
ROY, ROQ, or ROH still uses an imported formula	Rest-of-period members are not automatically seeded. Configure them explicitly with MANAGE_CUBE_DTS.
Configuration changes after a rebuild	Configure the entry through MANAGE_CUBE_DTS so it is maintained as SOURCE = MANUAL.
P_GRAIN validation error	Only YEAR, QUARTER, and HALFYEAR are supported.
P_DIRECTION validation error	Use TODATE or FROMDATE.
Formula on a DTS member has no effect	Remove or deactivate the DTS entry before using a formula on that member.
DTS cell returns #NOACCESS	Under MASK, one or more periods in the DTS window contain protected data.

Quick Reference

Item	Behavior
DTS calculations	YTD, QTD, HTD, ROY, ROQ, ROH
DTS dimension	Separate non-Time dimension, conventionally VIEW
Anchor	Requested leaf Time member
Grains	YEAR, QUARTER, HALFYEAR
Directions	TODATE and FROMDATE
Base member	Periodic member such as MTD
Automatic seeding	MTD/Periodic with YTD, QTD, or HTD
Rest-of-period seeding	ROY, ROQ, and ROH must be configured manually
Accounts dimension	Optional, but recommended when balance accounts exist
Flow accounts	Sum the DTS window
DTS member formula	Suppressed while DTS owns the member
Stored values	None. DTS is evaluated dynamically at query time.
Query pattern	Direct DTS member reference with leaf Time anchors
Security	Applied to underlying periodic data before DTS evaluation

Related Documentation

• Time Navigation
• Formula Overview
• Formula Syntax
• Variables & Dynamic Time Series Reference
• Querying Overview
• Row-Level Security