Define 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 Set Up Aggregate Navigation by Creating Sources for Aggregated Fact Data.

If a logical table is sourced from a set of fragments, it isn't 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 doesn't 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 Set Up Fragmentation Content for Aggregate Navigation.

Verify 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. Don't mix aggregation by logical level and column in the same business model.

See About WHERE Clause Filters and Set 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 Set 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've two fragments, all sales including current year, and current year sales with the fragmentation predicate set to year = 2015. You shouldn't 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've 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 don't overlap. The Oracle BI Server creates a union of all the logical table sources on this level that can't 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.

In the Business Model and Mapping layer of the Model Administration Tool, expand a logical fact table, expand Sources, and double-click a logical fact source table.
In the Logical Table Source dialog, click the Content tab.
If a logical source is an aggregate table and you've defined logical dimensions, in the Logical Table Source dialog, select Logical Level from the Aggregation content, group-by list.
Specify a logical level for each dimension, unless you're 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.
Optional: (Not recommended) To specify aggregate content by column, from the Aggregation content, group-by list, select Column.
Click the Table column, and select each logical dimension table that defines the aggregation level of the source.
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.
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.
Optional: Select This source should be combined with other sources at this level if all fragments on this level are disjointed.
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.
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 doesn't 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 fact-access-joins.gif follows

Description of the illustration fact-access-joins.gif

The table contains a list of the logical level for each dimension table that's 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 isn't 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 don't 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 Model Administration Tool doesn't 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 doesn't 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 don't 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 wouldn't 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.