1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Manage Logical Table Sources
  4. Work With Logical Table Source Data Fragmentation
  5. Work With Fragmentation for Aggregate Navigation

Work With Fragmentation for Aggregate Navigation

This topic contains examples that provide techniques and rules for specifying data fragmentation.

See Define Data Fragmentation for a Logical Table Source.

Topics:

Specify Fragmentation for Single Column, Value-Based Predicates

You can replace the IN predicates with either an equality predicate or multiple equality predicates separated by the OR connective.

Fragment 1:

logicalColumn IN <valueList1>

Fragment n:

logicalColumn IN <valueListN>

Specify Fragmentation for Single Column, Range-Based Predicates

Use >= and < predicates to ensure that the fragment content descriptions don't overlap. For each fragment, you must express the upper value as <. An error occurs if you use <=. You can't use the BETWEEN predicate to describe fragment range content.

Fragment 1:

logicalColumn >= valueof(START_VALUE) AND logicalColumn < valueof(MID_VALUE1)

Fragment 2:

logicalColumn >= valueof(MID_VALUE1) AND logicalColumn < valueof(MID_VALUE2)

Fragment n:

logicalColumn >= valueof(MID_VALUEN-1) AND logicalColumn < valueof(END_VALUE)

Pick your start point, midpoints, and endpoint carefully.

The valueof referenced here is the value of a semantic model variable. If you use semantic model values in your expression, the following construct doesn't work for Fragment 2: 

logicalColumn >= valueof(MID_VALUE1)+1 AND logicalColumn < valueof(MID_VALUE2)

Use another semantic model variable instead of valueof(MID_VALUE1)+1.

The same variables, for example, valueof(MID_VALUE1), aren't required to appear in the content of both fragments. You could set another variable, and create statements of the following form:

Fragment 1:

logicalColumn >= valueof(START_VALUE) AND logicalColumn < valueof(MID_VALUE1)

Fragment 2:

logicalColumn >= valueof(MID_VALUE2) AND logicalColumn < valueof(MID_VALUE3)

Specify Multicolumn Content Descriptions

An arbitrary number of predicates on different columns can be included in each content filter. Each column predicate can be value-based or range-based.

Fragment 1:

<logicalColumn1 predicate> AND <logicalColumn2 predicate > ... AND <logicalColumnM predicate>

Fragment n:

<logicalColumn1 predicate> AND <logicalColumn2 predicate > ... AND <logicalColumnM predicate>

Ideally, all fragments have predicates on the same M columns. If there is no predicate constraint on a logical column, The Oracle Analytics query engine assumes that the fragment contains data for all values in that logical column.

Specify Parallel Content Descriptions

Use the parallel OR to handle dates that cross logical columns such as across years, or across months in a date range.

Use the parallel OR technique to handle the multiple hierarchical relationships across logical columns such as from year to year month to date, and from month to year month to date. For example, consider fragments delineated by different points in time such as year and month. Constraining sufficiently far back in a year is enough to drive the selection of just the historical fragment. The parallel OR technique supports this.

This example assumes that the snapshot month was April 1, 12:00 a.m. in the year 2022.

Fragment 1 (Historical):

EnterpriseModel.Period."Day" < VALUEOF("Snapshot Date") OR
EnterpriseModel.Period.MonthCode < VALUEOF("Snapshot Year Month") OR
EnterpriseModel.Period."Year" < VALUEOF("Snapshot Year") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Month in Year" < VALUEOF("Snapshot Month") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Monthname" IN ('Mar', 'Feb', 'Jan')

Fragment 2 (Current):

EnterpriseModel.Period."Day" >= VALUEOF("Snapshot Date") OR
EnterpriseModel.Period.MonthCode >= VALUEOF("Snapshot Year Month") OR
EnterpriseModel.Period."Year" > VALUEOF("Snapshot Year") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Month in Year" >= VALUEOF("Snapshot Month") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Monthname" IN ('Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul',
  'Jun', '', 'Apr')

If the logical model doesn't go down to the date level of detail, then omit the predicate on EnterpriseModel.Period."Day" in the preceding example.

Notice the use of the OR connective to support parallel content description tracks.

Specify Unbalanced Parallel Content Descriptions

In an order entry application, time-based fragmentation between historical and current fragments is insufficient.

For example, records might still be volatile, even though they're historical records entered into the database before the snapshot date.

For the following example, assume that open orders can be directly updated by the application until the order is shipped or canceled. After the order has shipped, however, the only change that can be made to the order is to type a separate compensating return order transaction.

There are two parallel tracks in the following content descriptions. The first track uses the multicolumn, parallel track techniques described in the preceding section. Notice the parentheses nesting the parallel calendar descriptions within the Shipped-or-Canceled order status multicolumn content description.

The second parallel track is present only in the Current fragment and specifies that all Open records are in the Current fragment only.

Fragment 1 (Historical):

Marketing."Order Status"."Order Status" IN ('Shipped', 'Canceled') AND
  Marketing.Calendar."Calendar Date" <= VALUEOF("Snapshot Date") OR
Marketing.Calendar."Year" <= VALUEOF("Snapshot Year") OR
Marketing.Calendar."Year Month" <= VALUEOF("Snapshot Year Month")

Fragment 2 (Current):

Marketing."Order Status"."Order Status" IN ('Shipped', 'Canceled') AND
  Marketing.Calendar."Calendar Date" > VALUEOF("Snapshot Date") OR
Marketing.Calendar."Year" >= VALUEOF("Snapshot Year") OR
Marketing.Calendar."Year Month" >= VALUEOF("Snapshot Year Month") OR
Marketing."Order Status"."Order Status" = 'Open'

The overlapping Year and Month descriptions in the two fragments don't cause a problem because overlap is permissible when there are parallel tracks. The rule is that at least one of the tracks has to be non-overlapping. The other tracks can have overlap.

Examples of Parallel Content Descriptions

These examples explain how to use labels with fragment content statements.

The Track number labels in the examples are shown to help relate the examples to the discussion that follows. You wouldn't include these labels in the actual fragmentation content statement.

Fragment 1 (Historical)

Track 1  EnterpriseModel.Period."Day" < VALUEOF("Snapshot Date") OR
Track 2  EnterpriseModel.Period.MonthCode < VALUEOF("Snapshot Year Month") OR
Track 3  EnterpriseModel.Period."Year" < VALUEOF("Snapshot Year") OR
Track 4  EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Month in Year" < VALUEOF("Snapshot Month") OR
Track 5  EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Monthname" IN ('Mar', 'Feb', 'Jan')

For example, consider the first track on EnterpriseModel.Period."Day." In the historical fragment, the < predicate tells the Oracle Analytics query engine that any queries that constrain on Day before the Snapshot Date fall within the historical fragment. Conversely, the >= predicate in the current fragment on Day indicates that the current fragment doesn't contain data before the Snapshot Date.

The second track on MonthCode, for example, 202112, is similar to Day. It uses the < and >= predicates, as there is a non-overlapping delineation on month because the snapshot date is April 1. The key rule to remember is that each additional parallel track must reference a different column set. You can use common columns, but the overall column set must be unique. The Oracle Analytics query engine uses the column set to select the most appropriate track.

The third track on Year, < in the historical fragment and > in the current fragment, tells the Oracle Analytics query engine that optimal (single) fragment selections can be made on queries that just constrain on year. For example, a logical query on Year IN (2019, 2020) should only hit the historical fragment. Likewise, a query on Year = 2022 should only hit the current fragment. However, a query that hits the year 2021 can't be answered by the content described in this track, and therefore hits both fragments, unless additional information can be found in subsequent tracks.

The fourth track describes the fragment set for Year and Month in Year (month integer). Notice the use of the multi-column content description technique, described previously. Notice the use of < and >= predicates, as there is no ambiguity or overlap for these two columns.

The fifth track describes fragment content in terms of Year and Month name. It uses the value-based IN predicate technique.

As an embellishment, suppose the snapshot date fell on a specific day within a month: therefore, multi-column content descriptions on just year and month would overlap on the specific snapshot month. To specify this ambiguity, <= and >= predicates are used.

Fragment 1 (Historical):

EnterpriseModel.Period."Day" < VALUEOF("Snapshot Date") OR
EnterpriseModel.Period.MonthCode <= VALUEOF("Snapshot Year Month") OR
EnterpriseModel.Period."Year" < VALUEOF("Snapshot Year") OR 
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Month in Year" <= VALUEOF("Snapshot Month") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Monthname" IN ('Apr', 'Mar', 'Feb', 'Jan')

Fragment 2 (Current):

EnterpriseModel.Period."Day" >= VALUEOF("Snapshot Date") OR
EnterpriseModel.Period.MonthCode >= VALUEOF("Snapshot Year Month") OR
EnterpriseModel.Period."Year" > VALUEOF("Snapshot Year") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Month in Year" >= VALUEOF("Snapshot Month") OR
EnterpriseModel.Period."Year" = VALUEOF("Snapshot Year") AND
  EnterpriseModel.Period."Monthname" IN ('Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul',
  'Jun', '', 'Apr')