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. For more information, refer to Example of Creating Sources for Each Level of 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, than 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, than we assume that we do 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 we return NULL as measure aggregates.

NOTE:  It is recommended that all the fragments map the same set of columns

Use the Content tab of the Logical Table Source dialog box 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).

NOTE:  For examples of how to set up fragmentation content for aggregate navigation, refer to Setting Up Fragmentation Content in an Oracle BI Repository for Aggregate Navigation.

Verify Joins Exist From Dimension Tables to Fact Table

This source content information tells the Oracle BI Server what it needs to know to send queries to the appropriate physical aggregate fact tables, joined to and constrained by values in the appropriate physical aggregate dimension tables. Be sure that joins exist between the aggregate fact tables and the aggregate dimension tables in the Physical layer.

One recommended way to verify joins is to select a fact logical table and request 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.

Figure 12 is an example of how the Fact - Asset fact logical table appears in a Business Model Diagram (Selected Tables and Direct Joins) view.

Figure 12. Diagram of Direct Joins for a Fact Table

Table 17 contains a list of the logical level for each dimension table that is directly joined the Fact - Assess fact table shown in Figure 12.

Table 17. Dimension and Logical Level as Shown in Content Tab

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

To create logical table source content definitions

1. In the Business Model and Mapping layer, double-click a logical table source.
2. In the Logical Table Source dialog box, click the Content tab and perform the following steps using Table 18 as a guide.
3. If a logical source is an aggregate table and you have defined logical dimensions, perform the following steps:
   1. Select Logical Level from the Aggregation content, group-by drop-down list.
      CAUTION:  Although you have the option to specify aggregate content by logical level or column, it is recommended that you use logical levels exclusively.
   2. In the Logical Level drop-down list, select the appropriate level for each dimension logical table to which the fact logical table is joined.
      You should specify a logical level for each dimension, unless you are specifying the Grand Total level. Dimensions with no level specified will be interpreted as being at the Grand Total level.
4. If a logical source is an aggregate table and you want to define content for columns, do the following:
   1. Select Column from the Aggregation content, group-by drop-down list.
      CAUTION:  Although you have the option to specify aggregate content by logical level or column, it is recommended that you use logical levels exclusively.
   2. In the Table pane, select each logical dimension table that defines the aggregation level of the source.
   3. In the Column pane, select the logical column for each dimension that defines how the aggregations were grouped.
      When there are multiple logical columns that could be used, select the one 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.
      NOTE:  Do not mix aggregation by logical level and column in the same business model. It is recommended that you use aggregation by logical level.
5. To specify fragmented table definitions for the source, use the Fragmentation content window 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.
   You can type the formula directly into the window, or click the Expression Builder button to the right of the window. In the Fragmentation Content Expression Builder, you can specify content in terms of existing logical columns. For examples of how to set up fragmentation content for aggregate navigation, seeSpecify Fragmentation Content.
6. Select the following option:
   This source should be combined with other sources at this level
   NOTE:  This option is only for multiple sources that are at the same level of aggregation.
7. (Optional) Specify Where clause filters in the Where Clause Filter window to limit the number of rows the source uses in the resultant table. For more information, refer to About WHERE Clause Filters.
   1. Click the Expression Builder button to open the Physical Where Filter Expression Builder.
   2. Type the Where clause and click OK.
8. Select the option Select distinct values if the values for the source are unique.

Table 18. Content Tab Fields for Logical Table Source

Field	Description
Aggregation content, group by	How the content is aggregated.
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. For examples of how to set up fragmentation content for aggregate navigation, seeSpecify Fragmentation Content.
More (button)	When you click More, the following options appear: Copy. (Available only for 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 from. (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. (Available only 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 will not include the aggregation content of this dimension. Check Levels. (Available only for fact tables) check 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 will not include the aggregation content of this dimension.)
Select distinct values	Used if the values for the source are unique.
This source should be combined with other sources at this level (check box)	Check this box 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