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.
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.
See About WHERE Clause Filters and Setting Up Fragmentation Content for Aggregate Navigation.
In the Business Model and Mapping layer of the Administration Tool, double-click a logical table source.
In the Logical Table Source dialog, click the Content tab and perform the following steps using the table as a guide.
If a logical source is an aggregate table and you have defined logical dimensions, select Logical Level from the Aggregation content, group-by list. Then, in the Logical Level list, select the appropriate level for each logical dimension table to which the logical fact 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 are interpreted as being at the most detailed level.
Caution:
Although you have the option to specify aggregate content by logical level or column, it is recommended that you use logical levels exclusively. If you must define content by columns, do the following:
Select Column from the Aggregation content, group-by list.
In the Table pane, select each logical dimension table that defines the aggregation level of the source.
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.
Do not mix aggregation by logical level and column in the same business model.
To specify fragmented table definitions for the source, use the Fragmentation content box 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 box, or click the Expression Builder button to the right of the box. 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.
(Optional) Select This source should be combined with other sources at this level.
Choose this option only if all fragments on this level are disjointed. Consider the following examples:
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). In this case you should select the This source should be combined with other sources at this level option because the fragments do not overlap. In this case Oracle BI server will union all the logical table sources on this level that cannot be disqualified based on query predicate or fragmentation predicate compatibility.
(Optional) To limit the number of rows the source uses in the resultant table, specify WHERE clause filters in the box labeled 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.
If the values for the source are unique, select the option Select distinct values.
| Options | Description |
|---|---|
|
Aggregation content, group by |
How the content is aggregated. |
|
More button |
When you click More, the following options appear:
|
|
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. |
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.