Siebel Analytics Server Administration Guide > Setting Up Aggregate Navigation > About Aggregate Navigation >

Specify Fragmentation Content

When a logical table source does not contain the entire set of data at a given level, you need to specify the portion, or fragment, of the set that it does contain. Describe the content in terms of logical columns, using the `Fragmentation Content' edit box on the Content tab of the Logical Table Source window.

The following examples illustrate techniques and rules for specifying the fragmentation content of sources.

Single Column, Value-Based Predicates

The IN predicates can be replaced with either an equality predicate or multiple equality predicates separated by the OR connective.

Fragment 1:

logicalColumn IN <valueList1>

Fragment n:

logicalColumn IN <valueListN>

Single Column, Range-Based Predicates

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.

NOTE:  Notice the use of >= and < predicates to make sure the fragment content descriptions do not overlap. For each fragment, the upper value needs to be expressed as <. You will get an error if you use <=. Likewise, you cannot use the BETWEEN predicate to describe fragment range content.

The valueof referenced here is the value of a repository variable. (For more information about variables, see Using Variables in a Repository.) If you use repository values in your expression, note that the following construct will not work for Fragment 2:

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

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

The same variables, for example, valueof(MID_VALUE1), do not have 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)

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 will have predicates on the same M columns. If there is no predicate constraint on a logical column, the Siebel Analytics Server assumes that the fragment contains data for all values in that logical column. For exceptions using the OR predicate, see Parallel Content Descriptions.

Parallel Content Descriptions

Unfortunately, the preceding techniques are still not sufficient to handle dates because of the multiple hierarchical relationships across logical columns, such as year > year month > date; month > year month > date. For example, consider fragments delineated by different points in time, such as year and month. Constraining sufficiently far back on year should be enough to drive the selection of just the historical fragment. The parallel OR technique supports this, as shown in the next example. This example assumes that the snapshot month was April 1, 12:00 a.m. in the year 1999. The relevant OR connectives and predicates are shown in bold text.

Fragment 1 (Historical):

EnterpriseModel.Period."Day" < VALUEOF("Snapshot Date") OR

EnterpriseModel.Period.MonthCode < VALUEOF("Snapshot Year Month") OR

EnterpriseModel.Period."Year" < VALUEOF("Shapshot Year") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Month in Year" < VALUEOF("Snapshot Month") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot 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("Shapshot Year") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Month in Year" >= VALUEOF("Snapshot Month") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Monthname" IN ('Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul', 'Jun', 'May', 'Apr')

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

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

Examples and Discussion

In this section, the Track n labels in the examples are shown to make it easier to relate the examples to the discussion that follows. You would not 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("Shapshot Year") OR

Track 4  EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Month in Year" < VALUEOF("Snapshot Month") OR

Track 5  EnterpriseModel.Period."Year" = VALUEOF("Shapshot 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 Siebel Analytics Server 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 does not contain data before the Snapshot Date.

The second track on MonthCode (for example, 199912) is similar to Day. It uses the < and >= predicates as there is a nonoverlapping delineation on month (because the snapshot date is April 1). The key rule to remember is that each additional parallel track needs to reference a different column set. Common columns may be used, but the overall column set needs to be unique. The Siebel Analytics Server 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 Siebel Analytics Server that optimal (single) fragment selections can be made on queries that just constrain on year. For example, a logical query on Year IN (1997, 1998) should only hit the historical fragment. Likewise, a query on Year = 2000 needs to hit only the current fragment. However, a query that hits the year 1999 cannot be answered by the content described in this track, and will therefore hit both fragments, unless additional information can be found in subsequent tracks.

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

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

As an embellishment, suppose the snapshot date fell on a specific day within a month; therefore multicolumn 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("Shapshot Year") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Month in Year" <= VALUEOF("Snapshot Month") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot 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("Shapshot Year") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Month in Year" >= VALUEOF("Snapshot Month") OR

EnterpriseModel.Period."Year" = VALUEOF("Shapshot Year") AND EnterpriseModel.Period."Monthname" IN ('Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul', 'Jun', 'May', 'Apr')

Unbalanced Parallel Content Descriptions

In an order entry application, time-based fragmentation between historical and current fragments is typically insufficient. For example, records may still be volatile, even though they are historical records entered into the database before the snapshot date.

Assume, in the following example, that open orders may be directly updated by the application until the order is shipped or canceled. Once the order has shipped, however, the only change that can be made to the order is to enter 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. Note 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 do not cause a problem, as overlap is permissible when there are parallel tracks. The rule is that at least one of the tracks has to be nonoverlapping. The other tracks can have overlap.


 Siebel Analytics Server Administration Guide 
 Published: 23 June 2003