11 Managing Logical Table Sources (Mappings)

This chapter explains how to work with logical table source objects and their mappings in the Business Model and Mapping layer of the Oracle BI repository.

This chapter contains the following topics:

• About Logical Table Sources

• Creating Logical Table Sources

• Setting Priority Group Numbers for Logical Table Sources

• Defining Physical to Logical Table Source Mappings and Creating Calculated Items

• Defining Content of Logical Table Sources

• About Working with Parent-Child Settings in the Logical Table Source

• Setting Up Aggregate Navigation by Creating Sources for Aggregated Fact Data

• Setting Up Fragmentation Content for Aggregate Navigation

About Logical Table Sources

Logical table sources define the mappings from a single logical table to one or more physical tables.

Use the physical to logical mapping to specify transformations that occur between the Physical layer and the Business Model and Mapping layer, and to enable aggregate navigation and fragmentation.

You can view logical table sources in the Business Model and Mapping layer.

Logical tables can have many physical table sources. A single logical column might map to many physical columns from multiple physical tables, including aggregate tables that map to the column such as if a query asks for the appropriate level of aggregation on that column.

This section contains the following topics:

• How Fact Logical Table Sources Are Selected to Answer a Query

• How Dimension Logical Table Sources Are Selected to Answer a Query

• Consistency Among Data in Multiple Sources

How Fact Logical Table Sources Are Selected to Answer a Query

The system uses criteria to select the fact logical table source to answer a query.

The following criteria is listed from the highest precedence to the lowest precedence:

• Logical table source priority group. A higher priority logical table source is used before a lower priority logical table source, even if the higher priority source is at a more detailed grain. A lower number indicates a higher priority. See Setting Priority Group Numbers for Logical Table Sources.

• The grain of the logical table source . A higher-grain logical table source is used before a lower-grain logical table source, given that the priority group numbers are the same.

• Number of elements at this level. If the grains are not comparable, the number specified for the Number of elements at this level field is considered.

  For example, assume you have the following two logical table sources with grains that are not comparable: LTS1(year, city) and LTS2(month, state). If you have 10 years, 100 cities, 120 months, and 9 states, the worst case size of LTS1 is 10 x 100 = 1000, and the worst case size of LTS2 is 120 x 9 = 1080. In this scenario, LTS1 is selected because the source with the lowest estimated number of total elements is assumed to be the fastest.

  See Creating Logical Levels in a Dimension.

• First logical table source listed. If all other criteria are equal, the first logical table source listed is selected, as shown in the Business Model and Mapping layer.

Every column in a query is sourced from a single logical table source based on these expected performance factors. Queries are not load-balanced across multiple logical table sources.

How Dimension Logical Table Sources Are Selected to Answer a Query

After the appropriate fact logical table sources have been selected, the system selects the best dimensional logical table sources to answer the query.

Oracle BI Server uses the following criteria to select the dimension logical table source. The criteria are listed from the highest precedence to the lowest precedence:

• Logical table source priority group

  A higher priority dimension logical table source is used before a lower priority dimension logical table source. A lower number indicates higher priority.

• Lower join cost

  The dimension logical table source with the lowest join cost is selected before dimension logical tables sources with higher join costs, given that the priority group numbers are the same.

• Higher level

  If the priority group and join cost are the same, the higher level logical table source is chosen, because that logical table source could require joining fewer rows.

Changing the Default Selection Criteria for Dimension Logical Table Sources

You can change the default logical table source selection criteria to favor dimension logical table sources that are at the same level as the fact logical table source before considering the higher level logical table source.

In Oracle BI Administration Tool, set the DIMENSION_LTS_JOIN_RESTRICTIONS session variable to PREFER_SAME_LEVEL.

If a suitable dimension logical table source at the same level as the fact logical table source does not exists, then Oracle BI Server selects the highest level dimension logical table source that is joinable to the fact. These factors are only considered after priority group and join cost.

The PREFER_SAME_LEVEL value for the DIMENSION_LTS_JOIN_RESTRICTIONS session variable sets the following criteria for selecting the dimension logical table source to answer the query:

• Logical table source priority group

• Lower join cost

• Same level as the fact logical table source

• Higher level than other dimension logical table sources if no other logical table source is at the same level as the fact logical table source

When DIMENSION_LTS_JOIN_RESTRICTIONS is set to NONE, the default value, you can join fact logical table sources to a higher level dimension logical table source even if there is another joinable dimension logical table source at the same level as the fact.

Consistency Among Data in Multiple Sources

It is important to ensure that the data in your sources is consistent.

For example, your year-level logical table source and your month-level logical table source for your time dimension should cover the same time period.

Be aware that consistency issues with data in your sources might become apparent when you issue queries that override null suppression, in other words, when you create an analysis in Oracle BI Answers and select Include Null Values. For example, some aggregate tables might not include the dimension records that correspond to the null fact values such as a yearly sales aggregate table that does not include years with no sales. All years in the year dimension must exist for the null values to be included in the result.

Creating Logical Table Sources

When you create logical tables and columns by dragging and dropping from the Physical layer, the logical table sources are generated automatically.

When you create logical tables and columns by dragging and dropping from the Physical layer, the logical table sources are generated automatically. If you create the logical tables manually, you need to also create the sources manually.

You also add new logical table sources when multiple physical tables can be the source of information. For example, many tables could hold information for revenue. You might have three different business units, each with its own order system, where you get revenue information. In another example, you might periodically summarize revenue from an orders system or a financial system and use this table for high-level reporting.

Use the Allow Unmapped Tables option for snowflake physical tables in an A > B > C configuration, where a logical table only maps to columns in A and C, but B needs to be included in the logical table source because it is in the join path between A and C.

1. In the Business Model and Mapping layer of the Administration Tool, right-click a logical table and select New Object, then select Logical Table Source.
2. In the Logical Table Source dialog, on the General tab, type a name for the logical table source.
3. (Optional) Select Disabled to make this logical table source inactive.
4. (Optional) Select Allow Unmapped Tables to enable this logical table source to have physical tables that are not mapped to logical columns.
5. Click the Add button. In the Browse dialog, you can view joins and select tables for the logical table source. When there are two or more tables in a logical table source, all of the participating tables must have joins defined between them.
6. (Optional) To view the joins, in the Browse dialog, select a table and click View. After reviewing the joins in the Physical Table dialog, click Cancel.
7. (Optional) To add tables to the table source, select the desired tables in the Name list and click Select.
8. Click OK.

Setting Priority Group Numbers for Logical Table Sources

You can set priority group numbers to determine which logical table source to use for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have user queries that are fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is expensive, while access to a data warehouse is cheap. In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.

The priority group of a given logical table source does not always ensure that a particular query is fulfilled by that source. Priority group assignments are only one of many factors used by the Oracle BI Server to determine which logical table source to select for a given query. However, the logical table source priority is the most significant metric and is considered before any other cost metric.

To assign priority group numbers, rank your logical table sources in numeric order, with 0 being the highest-priority source. You can assign the same number to multiple sources. For example, you can have two logical table sources in priority group 0, two logical table sources in priority group 1, and so on. Often, only two priority groups are necessary (0 and 1).

Assigning priority groups is optional. All logical table sources are set to priority 0 by default. It is important that you do not use priority groups as a method fine tuning the choice of logical table sources used to answer queries. Oracle BI Server tries to automatically use the most optimal logical table sources, but only within the same priority group. When you set a different priority group to each logical table source, it might cause Oracle BI Server to use suboptimal logical table sources.

In some situations, you might want to allow users to reverse the normal logical table source priority ranking at query time. To accomplish this, you can use a combination of session variables and request variables with logical table source priority groups. This feature provides a way to dynamically select a source at run time, depending on user preference.

To enable this dynamic selection, you must first create the REVERSIBLE_LTS_PRIORITY_SA_VEC session variable in the repository. Create this variable as a string vector session variable that uses a row-wise session initialization block. REVERSIBLE_LTS_PRIORITY_SA_VEC should list the subject areas for which you want to allow users to reverse the logical table source priority ranking. You must define this variable to enable priority ranking reversal.

After you have defined the set of subject areas for which you want to allow priority ranking reversal, users can include the request variable REVERSE_LTS_PRIORITY with their queries to reverse the logical table source priority ranking. You can set this request variable to 1 to reverse the logical table source priority, or 0 to keep the normal logical table source priority.

As an alternative to using a request variable at query time, you can define a predetermined set of subject areas for which the logical table source priority is permanently reversed. To do this, create the session variable REVERSED_LTS_PRIORITY_SA_VEC in the repository. Create this variable as a string vector session variable that uses a row-wise session initialization block. REVERSED_LTS_PRIORITY_SA_VEC should list the subject areas for which you want the logical table source priority set to permanently reversed.

See Creating Session Variables.

REVERSIBLE_LTS_PRIORITY_SA_VEC

You could create a table called SA_TABLE that contains two columns: SUBJECT_AREA_NAME and REVERSIBLE. This table could contain rows mapping subject area names to their reversible values (1 or 0), as follows:

SUBJECT_AREA_NAME	REVERSIBLE
my_sa_1	1
my_sa_2	0

Then, you would create a string vector session variable called REVERSIBLE_LTS_PRIORITY_SA_VEC with a row-wise session initialization block. The initialization string for this initialization block is similar to the following:

SELECT 'REVERSIBLE_LTS_PRIORITY_SA_VEC', SUBJECT_AREA_NAME FROM SA_TABLE
WHERE REVERSIBLE=1

The image shows the Session Variable Initialization Block dialog for this example.

Description of the illustration GUID-9B9DD1CB-D82E-4749-A751-03739974C5EC-default.gif

Defining Physical to Logical Table Source Mappings and Creating Calculated Items

Use the Column Mapping tab of the Logical Table Source dialog to map logical columns to physical columns.

The physical to logical mapping can also be used to specify transformations that occur between the Physical layer and the Business Model and Mapping layer. The transformations can be simple, such as changing an integer data type to a character, or more complex, such as applying a formula to find a percentage of sales per unit of population. Applying these transformations is typically referred to as creating calculated items.

The data type of a logical column is determined by its logical table source mappings. For example, if a logical column has one physical source with a data type of VARCHAR(50) not-nullable, and another physical source with a VARCHAR(20) data type, nullable, then the data type of the logical column is VARCHAR(50) nullable. This final type is called a promoted type. Because of the rules governing logical table source mappings, you cannot map physical sources with data types that are promotable such as an INT with a VARCHAR.

1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical table source.
2. In the Logical Table Source dialog, click the Column Mapping tab.
3. In the Column Mapping tab, maximize or enlarge the dialog to show all the contents, as shown in the image.

   In the Column Mapping tab, in the Logical column to physical column mapping area, sort the rows by clicking a column heading.

   
   Description of the illustration GUID-F69370C7-8697-4F0C-BE36-9ED4725D96DC-default.gif
4. In the Physical Table column, select the table that contains the column you want to map.

   When you select a cell in the Physical Table column, a list appears. It contains a list of tables currently included in this logical table source.

5. In the Expression column, select the physical column corresponding to each logical column.

   When you select a cell in the Expression column, a list appears. It contains a list of physical columns currently included in this logical table source.

6. To open Expression Builder, click the Expression Builder button.

   All columns used in creating physical expressions must be in tables included in the logical table source. You cannot create expressions involving columns in tables outside the source.

   You can use Expression Builder to create calculated items, in which formulas are applied pre-aggregation. For example, to create the measure tons sold using the columns units_sold and unit_weight, you apply a pre-aggregation formula (fact.units_sold*product.unit_weight), and then apply the aggregation rule SUM in the measure object. Another example is using CAST to transform a column of type TIMESTAMP to type DATE for faster display in Answers and other clients, for example, CAST("DB"."."TABLE"."COL" AS DATE).

   You can also change data sources by creating expressions that perform transformations on physical data. For example, you can use the CAST function to transform a column with a character data type to an integer data type, to match data coming from a second logical table source. Other examples include using CONCATENATE or math functions to make similar transformations on physical data.

   See Answers for calculations that need to occur post-aggregation.

7. To remove a column mapping, click the Delete button.
8. After you map the appropriate columns, click OK.

Unmapping a Logical Column from Its Source

You can edit the logical table sources from which the column derives its data, or unmap it from its sources.

In the Logical Column dialog, the Column Source tab contains information about the logical column.

1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column.
2. In the Logical Column dialog, click the Column Source tab.
3. In the Logical Table Source list, select a source and click Unmap.
4. Click OK.

Defining Content of Logical Table Sources

To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model.

Therefore, you need to define aggregation content for each logical table source of a fact table. The aggregation content rule defines at what level of granularity the data is stored in this fact table. For each dimension that relates to this fact logical table, define the level of granularity, making sure that every related dimension is defined. See Setting Up Aggregate Navigation by Creating Sources for Aggregated Fact Data.

If a logical table is sourced from a set of fragments, it is not required that every individual fragment maps the same set of columns. However, the server returns different answers depending on how columns are mapped.

• If all the fragments of a logical table map the same set of columns, then the set of fragmented sources is considered to be the whole universe of logical table sources for the logical table. This means that measure aggregations can be calculated based on the set of fragments.

• If the set of mapped columns differ across the fragments, then the Oracle BI Server assumes that it does not have the whole universe of fragments, and therefore it would be incorrect to calculate aggregate rollups, since some fragments are missing. In this case, the server returns NULL as measure aggregates.

  Note:

  Oracle highly recommends that all the fragments map to the same set of columns.

Use the Content tab of the Logical Table Source dialog to define any aggregate table content definitions, fragmented table definitions for the source, and WHERE clauses, if you want to limit the number of rows returned. See Setting Up Fragmentation Content for Aggregate Navigation.

Verifying Joins from Dimension Tables to Fact Tables

Joins tells the Oracle BI Server where to send queries for physical aggregate fact tables joined to and constrained by values in the physical aggregate dimension tables.

Oracle recommends that you use logical levels exclusively as the Aggregation content, group-by option. Do not mix aggregation by logical level and column in the same business model.

See About WHERE Clause Filters and Setting Up Fragmentation Content for Aggregate Navigation.

You can type the formula directly into the Fragmentation content text area , or click Expression Builder. In the Expression Builder for Fragmentation Content, you can specify content in terms of existing logical columns. See Setting Up Fragmentation Content for Aggregate Navigation.

Choose This source should be combined with other sources at this level if all fragments on this level are disjointed. Consider the following examples:

• Example 1 - Suppose you have two fragments, all sales including current year, and current year sales with the fragmentation predicate set to year = 2015. You should not select the This source should be combined with other sources at this level option because the two fragments overlap. Oracle BI Server can use any single fragment based on query predicate or fragmentation predicate compatibility.

• Example 2 - Suppose you have two fragments, sales for year 2000 and before, according to the fragmentation predicate, and sales for year 2001 and after, according to the fragmentation predicate. You should select the This source should be combined with other sources at this level option because the fragments do not overlap. The Oracle BI Server creates a union of all the logical table sources on this level that cannot be disqualified based on query predicate or fragmentation predicate compatibility.

See Logical Table Source Options Reference to learn which option to use in the Logical Table Source dialog.

1. In the Business Model and Mapping layer of the Administration Tool, expand a logical fact table, expand Sources, and double-click a logical fact source table.
2. In the Logical Table Source dialog, click the Content tab.
3. If a logical source is an aggregate table and you have defined logical dimensions, in the Logical Table Source dialog, select Logical Level from the Aggregation content, group-by list.
4. Specify a logical level for each dimension, unless you are specifying the Grand Total level. Dimensions with no level specified are interpreted as being at the most detailed level, in the Logical Level list, select the appropriate level for each logical dimension table to which the logical fact table is joined.
   1. After specifying the appropriate logical level, skip to step 8.
5. (Optional, not recommended) To specify aggregate content by column, from the Aggregation content, group-by list, select Column.
6. Click the Table column, and select each logical dimension table that defines the aggregation level of the source.
7. Click Column, and select the logical column for each dimension that defines how the aggregations were grouped.

   When there are many logical columns to choose from, select the column that maps to the key of the source physical table. For example, if data has been aggregated to the Region logical level, pick the logical column that maps to the key of the Region table.

8. (Optional) Use the Fragmentation content text are to describe the range of values included in the source when a source represents a portion of the data at a given level of aggregation to specify fragmented table definitions for the source.
9. (Optional) Select This source should be combined with other sources at this level if all fragments on this level are disjointed.
10. (Optional) To limit the number of rows the source uses in the resultant table, specify WHERE clause filters in Use this "WHERE clause" filter to limit rows returned (exclude the "WHERE"). You can enter the WHERE clause directly, or you can click the Expression Builder button to open the Expression Builder, create the WHERE clause, and click OK.
11. (Optional) If the values for the source are unique, select the option Select distinct values.

Joins from Dimension Tables to Fact Tables

You must create joins between the aggregate fact tables and the aggregate dimension tables in the Physical layer.

You can verify joins by selecting a fact logical table and opening a Business Model Diagram (Selected Tables and Direct Joins). Only the dimension logical tables that are directly joined to this fact logical table appear in the diagram. It does not show dimension tables if the same physical table is used in logical fact and dimension sources.

The image shows a Fact- Assess logical fact table in a Business Model Diagram in the Selected Tables and Direct Joins view.

Description of the illustration GUID-0773E444-F8C7-421F-9F44-E8EB6313F6A8-default.gif

The table contains a list of the logical level for each dimension table that is directly joined the Fact - Assess fact table.

Dimension	Logical Level
Account Geography	Postal Code Detail
Person Geography	Postal Code Detail
Time	Day Detail
Account Organization	Account Detail
Opportunity	Opty Detail
Primary Visibility Organization	Detail
Employee	Detail
Assessment	Detail
Contact (W_PERSON_D)	Detail
FINS Time	Day
Positions	Details

Logical Table Source Options Reference

Learn how to use the options from the Logical Table Source dialog.

Options	Description
Aggregation content, group by	Specifies how the content is aggregated.
Copy	The Copy option is only available with fact tables. Copies aggregation content to the Windows clipboard. You can paste the Dimension.Level info into a text editor and use it for searching or for adding to documentation. Copy is not available if the expression is empty.
Copy from	The Copy from option is available for fact tables and dimension tables. Copies aggregation content from another logical table source in the same business model. You need to specify the source from which to copy the aggregation content. Multiple business models appear but only the logical table sources from the current business model are selectable.
Get Levels	The Get Levels option is only available for fact tables. Changes aggregation content. If joins do not exist between fact table sources and dimension table sources, for example, if the same physical table is in both sources, the aggregation content determined by the Administration Tool does not include the aggregation content of this dimension.
Check Levels	The Check Levels option is only available for fact tables. Checks the aggregation content of logical fact table sources, not dimension table sources. The information returned depends on the existence of dimensions and hierarchies with logical levels and level keys, and physical joins between tables in dimension table sources and the tables in the fact table source. If the same tables exist in the fact and dimension sources and there are no physical joins between tables in the sources, Check Levels does not include the aggregation content of this dimension.
Fragmentation content	A description of the contents of a data source in business model terms. Data is fragmented when information at the same level of aggregation is split into multiple tables depending on the values of the data. A common situation would be to have data fragmented by time period. .
This source should be combined with other sources at this level	Select this option when data sources at the same level of aggregation do not contain overlapping information. In this situation, all sources must be combined to get a complete picture of information at this level of aggregation.
Select distinct values	Used if the values for the source are unique.

About WHERE Clause Filters

The WHERE clause filter is used to constrain the physical tables referenced in the logical table source.

If there are no constraints on the aggregate source, leave the WHERE clause filter blank.

Each logical table source should contain data at a single intersection of aggregation levels. You would not want to create a source, for example, that had sales data at both the Brand and Manufacturer levels. If the physical tables include data at multiple levels, add an appropriate WHERE clause constraint to filter values to a single level.

Any constraints in the WHERE clause filter are made on the physical tables in the source.

About Working with Parent-Child Settings in the Logical Table Source

Learn about when a logical table is part of a dimension with a parent-child hierarchy that is based on relational tables.

When this is the case, the logical table includes both a physical source and a source for the parent-child relationship table required for the parent-child hierarchy. Parent-child relationship tables explicitly define the inter-member relationships for parent-child hierarchies.

Typically, logical table sources for parent-child relationship tables are created automatically when you run the scripts created by the Generate Parent-Child Table Wizard. You access this wizard from the Parent-Child Table Settings dialog, available in the dimension object.

The Generate Parent-Child Table Wizard feature is not available from the Logical Table Source dialog. You must go to the dimension object to create scripts to generate the parent-child relationship table.

You can view details for the parent-child relationship table source in the Parent-Child Settings tab of the Logical Table Source dialog. The following information appears in the tab:

• Parent-Child Table: Shows the name of the parent-child relationship table on which this source is based.

• Member Key: The name of the column in the parent-child relationship table that identifies the member.

• Parent Key: The name of the column in the parent-child relationship table that identifies an ancestor of the member.

• Relationship Distance: The name of the column in the parent-child relationship table that specifies the number of parent-child hierarchical levels from the member to the ancestor.

• Leaf Node Identifier: The name of the column in the parent-child relationship table that indicates if the member is a leaf member (1=Yes, 0=No).

See Creating Dimensions with Parent-Child Hierarchies.

Setting Up Aggregate Navigation by Creating Sources for Aggregated Fact Data

Aggregate tables store precomputed results from measures that have been aggregated over a set of dimensional attributes.

Each aggregate table column contains data at a given set of levels. For example, a monthly sales table might contain a precomputed sum of the revenue for each product in each store during each month. You configure this metadata in the Content tab of the Logical Table Source dialog.

When you create a logical table source for an aggregate fact table, you should create corresponding logical dimension table sources at the same levels of aggregation.

You need to have at least one logical dimension table source for each level of aggregation. If the sources at each level already exist, you do not need to create new ones.

For example, you might have a monthly sales fact table containing a precomputed sum of the revenue for each product in each store during each month. You need to have the following three other dimension sources, one for each of the logical dimension tables referenced in the example:

• A source for the Product logical table with one of the following content specifications:

  • By logical level: ProductDimension.ProductLevel

  • By column: Product.Product_Name

• A source for the Store logical table with one of the following content specifications:

  • By logical level: StoreDimension.StoreLevel

  • By column: Store.Store_Name

• A source for the Time logical table with one of the following content specifications:

  • By logical level: TimeDimension.MonthLevel

  • By column: Time.Month

At query time, the Oracle BI Server first determines which sources have enough detail to answer the query. Out of these sources, the Oracle BI Server chooses the most aggregated source to answer the query, because it is assumed to be the fastest. The most aggregated source is the one with the lowest multiplied number of elements. See Creating Logical Levels in a Dimension to learn how to specify the number of elements at each level.

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

• Specifying Fragmentation Content for Single Column, Range-Based Predicates

• Specifying Fragmentation Content for Aggregate Table Fragments

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 ensure that the fragment content descriptions do not overlap. For each fragment, you must express the upper value as <. An error occurs if you use <=. 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, 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), are not 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)

See Using Variables in the Oracle BI Repository.

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

Description of the illustration GUID-D056C63C-4F8F-4DC5-A649-C3F75488F780-default.png

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

• Defining a Physical Layer Table with a Select Statement to Complete the Domain

• Specifying the SQL Virtual Table Content

• Creating Physical Joins for the Virtual Table

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, for example, city and product, 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, 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. You can use 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 on the physical schema object that the SELECT statement uses for the query 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.

Description of the illustration GUID-5EAD2607-5001-4949-B533-8D5BA61DD7FF-default.gif

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.