Work With Logical Table Source Data Fragmentation

This topic provides information to help you understand and define data fragmentation.

Topics:

• About Data Fragmentation

• About Global Variables and Logical Table Source Fragmentation

• Define Data Fragmentation for a Logical Table Source

• Improve the Performance of Fragmented Logical Table Sources

• Work With Fragmentation for Aggregate Navigation

• Work With Aggregate Table Fragments

About Data Fragmentation

A logical table can include table sources that have the same level of detail, but each contains a specific range of values (or fragments of data). These tables are called fragmented tables.

When you use fragmented tables as logical table sources, you must write an expressions for each table source to indicate its range of values. The Oracle Analytics query engine uses the expression to determine which table to use to find the data requested by the query.

Fragmented logical table sources must have physical joins to the appropriate tables so that when the Oracle Analytics query engine uses the fragment, it still joins to the appropriate table sources.

Sometimes the data needed for a query overlaps between fragmented logical table sources. In these cases you might need to select the This source should be combined with other sources at this level option. Consider the following examples of how to use this option:

• Example 1 - Suppose your logical table uses a fragmented logical table source containing all sales for years 2000 to the current year (2022), and another fragmented logical table source containing current year sales and its table source fragmentation expression is set to year = 2022. In this case the table fragments overlap and you shouldn't select the This source should be combined with other sources at this level option. In this case, the Oracle Analytics query engine can use any single fragment based on query predicate or fragmentation predicate compatibility.

• Example 2 - Suppose your logical table uses a fragmented logical table source that contains all sales for years 2000 to 2021, and another fragmented logical table source containing sales for year 2022. In this case you should select the This source should be combined with other sources at this level option because the fragments don't overlap. In this case, the Oracle Analytics query engine 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.

If a logical table is sourced from a set of fragmented tables, then each fragmented table doesn't have to map the same set of columns. However, the Oracle Analytics query engine returns different answers depending on how the columns are mapped. For the best query results, Oracle recommends that all the fragments map to the same set of columns.

• If the logical table is sourced from fragmented tables that map the same set of columns, then the Oracle Analytics query engine considers the set of fragmented sources to be a complete set of logical table sources. This means that measure aggregations can be calculated based on the set of fragments.

• If the set of mapped columns differs between the fragmented tables, then the Oracle Analytics query engine assumes that the set of logical table sources is incomplete, and because some fragments are missing, won't calculate aggregate rollups. In this case, the server returns NULL as measure aggregates.

About Global Variables and Logical Table Source Fragmentation

You can use global variables in a logical table source's fragmentation expression to automatically modify a fragment's content.

For example, suppose you have two sources for information about orders where one source contains recent orders and the other source contains historical data. You need to update the global variable to use the recent orders and move the historical order data to a different view. Without using global variables, you would describe the content of the source containing recent data with an expression such as:

Orders.OrderDates."Order Date" >= TIMESTAMP '2001-06-02 00:00:00'

This content statement becomes invalid as new data is added to the recent source and older data is moved to the historical source. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. Instead you can define global variables to automatically modify the content.

Define Data Fragmentation for a Logical Table Source

A fragmented table contains a portion of the data at a specific aggregation level. When you use fragmented tables, you must write an expressions for each table source to indicate its range of values.

See About Data Fragmentation.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Logical Layer.
4. In the Logical Layer pane, browse for and double-click the table with the table source you want to define data fragmentation for.
5. In the table's tabs, click Sources.
6. In the logical table sources list table, click the logical table source that you want to define data fragmentation for and then click Detail view to open the properties pane.
7. Go to the pane's Data Fragmentation section and click Data is fragmented.
8. Click Open Expression Editor and create and validate the fragment expression. In the Expression Editor click Save.
9. Optional: If the data needed for a query is located in more than one fragmented table, then click Combine with other fragmented sources to sum the data.
10. Optional: Click Enable Data Driven Fragment Selection to improve the performance of the logical table source.
11. Click Save.

Improve the Performance of Fragmented Logical Table Sources

You can use data driven fragment selection to improve the performance of fragmented logical table sources.

Data driven fragment selection is disabled by default.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Logical Layer.
4. In the Logical Layer pane, browse for and double-click the table with the fragmented source that you want to improve performance for.
5. In the table's tabs, click Sources.
6. In the table sources list, click the logical table source that you want to improve performance for then click Detail view to open the properties pane.
7. Scroll to Data Granularity and click Data is fragmented to display the expression that you created.
8. Click Enable Data Driven Fragment Selection.
9. Click Save.