Setting Up Fragmentation Content for Aggregate Navigation
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.
You describe the content in terms of logical columns in the Fragmentation content box in the Content tab of the Logical Table Source dialog.
The examples in this section illustrate techniques and rules for specifying the fragmentation content of sources.
This section contains the following topics:
Specifying Fragmentation Content 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>
Specifying Fragmentation Content for Single Column, Range-Based Predicates
When a logical table source does not contain the entire set of data at a given level, you need to specify the fragment of the set that it does contain.
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:
Use >= and < predicates to make sure the fragment content descriptions do not overlap. For each fragment, the upper value must 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. If you use repository values in your expression, note that the following construct does 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)
Specifying 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 BI Server assumes that the fragment contains data for all values in that logical column. See Specifying Parallel Content Descriptions for exceptions using the OR predicate.
Specifying 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, as shown in the next example. This example assumes that the snapshot month was April 1, 12:00 a.m. in the year 1999.
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 does not 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.
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 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("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 BI 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 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 BI 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 Oracle BI 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 should only hit the current fragment. However, a query that hits the year 1999 cannot 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')
Specifying Unbalanced Parallel Content Descriptions
In an order entry application, time-based fragmentation between historical and current fragments is typically insufficient.
For example, records might 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 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 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 non-overlapping. The other tracks can have overlap.
Specifying Fragmentation Content for Aggregate Table Fragments
Information at a given level of aggregation is sometimes stored in multiple physical tables.
Information at a given level of aggregation is sometimes stored in multiple physical tables. When individual sources at a given level contain information for a portion or fragment of the domain, the Oracle BI Server needs to know the content of the sources in order to pick the appropriate source for the query.
For example, suppose you have a database that tracks the sales of soft drinks in all stores. The detail level of data is at the store level. Aggregate information, as described in the image, is stored at the city level for the sales of Coke and Pepsi, but there is no aggregate information for the sales of 7‐Up or any other of the sodas.
The goal of this type of configuration is to maximize the use of the aggregate table. If a query asks for sales figures for Coke and Pepsi, the data should be returned from the aggregate table. If a query asks for sales figures for all soft drinks, the aggregate table should be used for Coke and Pepsi and the detail data for the other brands.
The Oracle BI Server handles this type of partial aggregate navigation. To configure a repository to use aggregate fragments for queries whose domain spans multiple fragments, you need to define the entire domain for each level of aggregate data, even if you must configure an aggregate fragment as being based on a less summarized physical source.
This section contains the following topics:
Specifying the Aggregate Table Content
You configure the aggregate table navigation in the logical table source mappings.
In the soft drink example, the aggregate table contains data for Coke and Pepsi sales at the city level. Its Aggregate content specification, in the Content tab of the Logical Table Source window, is similar to the following:
Group by logical level:
GeographyDim. CityLevel, ProductDim.ProductLevel
Its Fragmentation content specification also in the Content tab of the Logical Table Source dialog is similar to the following:
SoftDrinks.Products.Product IN ('Coke', 'Pepsi')
This content specification tells the Oracle BI Server that the source table has data at the city and product level for two of the products. Additionally, because this source is a fragment of the data at this level, you must select This source should be combined with other sources at this level, in the Content tab of the Logical Table Source dialog, to indicate that the source combines with other sources at the same level.
Defining a Physical Layer Table with a Select Statement to Complete the Domain
The data for the rest of the domain (the other types of sodas) is all stored at the store level.
To define the entire domain at the aggregate level (city and product, in this example), you need to have a source that contains the rest of the domain at this level. Because the data at the store level is at a lower (that is, more detailed) level than at the city level, it is possible to calculate the city and product level detail from the store and product detail by adding up the product sales data of all of the stores in a city. This can be done in a query involving the store and product level table.
One way to do this is to define a table in the Physical layer with a Select statement that returns the store level calculations. To define the table, create a table in the Physical layer by right-clicking the physical schema object that the SELECT statement will be querying and selecting New Physical Table. Choose Select from the Table Type list, and type the SQL statement in the Default Initialization String box.
The SQL statement must define a virtual table that completes the domain at the level of the other aggregate tables. In this case, there is one existing aggregate table, and it contains data for Coke and Pepsi by city. Therefore, the SQL statement has to return all of the data at the city level, except for the Coke and Pepsi data.
Specifying the SQL Virtual Table Content
Create a new logical table source for the Sales column that covers the remainder of the domain at the city and product level.
This source contains the virtual table created in the previous section. Map the Dollars logical column to the US Dollars physical column in this virtual table.
The Aggregate content specification, in the Content tab of the Logical Table Source dialog, for this source is:
Group by logical level:
GeographyDim.CityLevel, ProductDim.ProductLevel
This tells the Oracle BI Server this source has data at the city and product level.
The Fragmentation content specification might be:
SoftDrinks.Products.Product = '7-Up'
Additionally, because it combines with the aggregate table containing the Coke and Pepsi data at the city and product level to complete the domain, you need to select the option in the Content tab of the Logical Table Source dialog indicating that the source is combined with other sources at the same level.
Creating Physical Joins for the Virtual Table
Provides an image that shows how to construct physical joins.
Construct the correct physical joins for the virtual table. Notice that CityProductSales2 joins to the Cities and Products tables in the image below.
In this example, the two sources comprise the whole domain for soda sales. A domain can have many sources. The sources have to all follow the rule that each level must contain sources that, when combined, comprise the whole domain of values at that level. Setting up the entire domain for each level helps ensure that queries asking for Coke, Pepsi, and 7‐Up do not leave out 7‐Up. It also helps ensure that queries requesting information that has been precomputed and stored in aggregate tables can retrieve that information from the aggregate tables, even if the query requests other information that is not stored in the aggregate tables.