Work With Aggregate Table Fragments
This topic contains a use case that provides techniques and rules for working with aggregate table fragments.
About Aggregate Table Fragments
Data at an aggregation level can be stored in multiple physical tables. In such cases, you need to specify which logical table source contains which fragment of the data so that the Oracle Analytics query engine chooses the correct 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 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 other sodas.
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 Analytics query engine handles this type of partial aggregate navigation. To configure a semantic model 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.
Specify the Aggregate Table Content
You configure the aggregate table navigation in the logical table source's fragmentation expression.
In the soft drink example, the aggregate table contains data for Coke and Pepsi sales at the city level.
Its data fragmentation expression should be similar to the following:
SoftDrinks.Products.Product IN ('Coke', 'Pepsi')
This expression tells the Oracle Analytics query engine that the source table has data at the city and product level for two of the products.
Because this source is a fragment of the data at this level, you must select the Combine with other fragmented sources field to indicate that the source combines with other sources at the same level.
Define 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's 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, go to the physical layer on the physical schema object and create a table on the physical schema object that the SELECT statement uses for the query. 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.
Specify the SQL Virtual Table Content
Create a 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 for this source is:
Group by logical level:
GeographyDim.CityLevel, ProductDim.ProductLevel
This tells the Oracle Analytics query engine that 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 Combine with other fragmented sources field.
Create Physical Joins for the Virtual Table
This topic provides an example that shows you how to construct physical joins for the virtual table.
Construct the correct physical joins for the virtual table. Notice that CityProductSales2 joins to the Cities and Products tables.
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 don't 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 isn't stored in the aggregate tables.