Aggregate Navigation In OBIEE10g

To import the aggregate tables, perform the following steps:

1.	Return to the SH repository, which should still be open in online mode.
2.	Select File > Import > from Database.
3.	In the Select Data Source dialog, select the SH data source, enter SH as User Name and Password, and click OK.
4.	In the Import dialog box, expand the SH schema and select the three aggregate tables:AGG_ST_CAT_DAY_SALES_F, AGG_CUSTOMER_STATE_D, and AGG_PRODUCTS_CATEGORY_D.
5.	Click Import.
6.	When the import process completes, close the Import dialog box.
7.	Expand the SH folder in the Physical layer and verify that the aggregate tables are added.
8.	Check in changes.
9.	Select the three aggregate tables in the Physical layer, right-click and select Update Row Count to verify connectivity. You should see the expected row counts after each table.
10.	Check in changes.
11.	Save the repository.

Back to Topic

To create physical joins for the aggregate tables, perform the following steps:

1.	In the Physical layer, use Ctrl+Click to select the three new aggregate tables and the Times table.
2.	Click the Physical Diagram icon on the toolbar.
3.	In the Physical Diagram, use the New Foreign Key button to create the following joins.  Click Yes when prompted to create matching table keys.: AGG_CUSTOMER_STATE_D.CUST_STATE_PROVINCE_ID = AGG_ST_CAT_DAY_SALES_F.CUST_STATE_PROVINCE_ID AGG_PRODUCTS_CATEGORY_D.PROD_CATEGORY_ID = AGG_ST_CAT_DAY_SALES_F.PROD_CATEGORY_ID TIMES.TIME_ID = AGG_ST_CAT_DAY_SALES_F.TIME_ID
4.	Close the Physical Diagram.
5.	Check in changes.
6.	Save the repository.

To map existing logical columns to aggregate columns and set aggregation content, perform the following steps:

1.	Map existing logical columns to new sources by dragging columns from the Physical layer to corresponding columns in the Business Model and Mapping layer: Logical Column Physical Column Sales Facts.Amount Sold AGG_ST_CAT_DAY_SALES_F.AMOUNT_SOLD Customers.Cust State Province AGG_CUSTOMER_STATE_D.CUST_STATE_PROVINCE Customers.Country AGG_CUSTOMER_STATE_D.COUNTRY_NAME Customers.Country Subregion AGG_CUSTOMER_STATE_D.COUNTRY_SUBREGION Customers.Country Region AGG_CUSTOMER_STATE_D.COUNTRY_REGION Customers.Country Total AGG_CUSTOMER_STATE_D.COUNTRY_TOTAL Products.Prod Category AGG_PRODUCTS_CATEGORY_D.PROD_CATEGORY Products.Prod Total AGG_PRODUCTS_CATEGORY_D.PROD_TOTAL
2.	In the Business Model and Mapping layer, expand Sales Facts > Sources and verify that a new logical table source, AGG_ST_CAT_DAY_SALES_F, is created.
3.	Double-click the AGG_ST_CAT_DAY_SALES_F logical table source to open the  Logical Table Source dialog box, click the Column Mapping tab, and verify the mapping.
4.	Click the Content tab.
5.	Use the drop-down menu in the Logical Level field to set the following logical levels: CustomersDim = State Province ProductsDim = Category TimesDim = Times Detail You are setting aggregation content for the fact table to the corresponding levels in the dimension hierarchies. In a subsequent step you set similar levels for the dimension table aggregate sources. Later, when a user queries against a particular level, Oracle BI Server will access the aggregate tables instead of the detail tables. For example, after setting aggregation content, if a user queries for Amount Sold by Country Region, the server will access the AGG_ST_CAT_DAY_SALES_F aggregate fact table and the corresponding aggregate dimension table, AGG_CUSTOMER_STATE_D. If a user queries for a level lower than the levels specified here, for example, Subregion or Country, then the server will access the detail tables. If a user queries for higher level, for example, Country Total, the aggregate tables will be used as well, because whenever a query is run against a logical level or above, the aggregate tables are used.
6.	Click OK to close the Logical Table Source dialog box.
7.	Expand Customers > Sources and verify that a new logical table source, AGG_CUSTOMER_STATE_D, is created.
8.	Expand Customers > Sources and double-click the AGG_CUSTOMER_STATE_D logical table source,  click theColumn Mapping tab, and verify the mappings.
9.	Click the Content tab.
10.	Use the drop-down menu in the Logical Level field to set the following logical level to specify  what level of detail is stored in the aggregate table: CustomersDim = State Province
11.	Click OK to close the Logical Table Source dialog box.
12.	Expand Products > Sources and verify that a new logical table source, AGG_PRODUCTS_CATEGORY_D, is created.
13.	Double-click the AGG_PRODUCTS_CATEGORY_D logical table source, click the Column Mapping tab, and verify the mappings.
14.	Click the Content tab.
15.	Use the drop-down menu in the Logical Level field to set the following logical level to specify what level of detail is stored in the aggregate table: ProductsDim = Category
16.	Click OK to close the Logical Table Source dialog box.
17.	Check in changes.
18.	Check consistency. Fix any errors before proceeding.
19.	Save the repository. Note that you did not need to make any changes to the Presentation layer. You made changes in the business model that impact how the queries are processed and which sources are accessed. Based on how you specified the aggregation content, Oracle BI Server automatically uses the new sources when appropriate.

To test in Answers and examine the query log, perform the following steps:

1.	Return to Answers.
2.	Reload Server Metadata.
3.	Create the following query: Customers.Country Region, Sales Facts.Amount Sold.
4.	Hold down the Ctrl key and click Times > Calendar > Calendar Year. The Create/Edit Filter dialog box opens.
5.	Create the filter Calendar Year is equal to / is in 2001.
6.	Click OK to close the Create/Edit Filter dialog box. The filter is added to the query.
7.	Click Results.
8.	Click Settings > Administration.
9.	Click Manage Sessions.
10.	Locate the last query run under Cursor Cache and click View Log.
11.	Examine the log and verify that the aggregate tables AGG_CUSTOMER_STATE_D andAGG_ST_CAT_DAY_SALES_F were accessed.
12.	Close the query log.
13.	Click Finished to close the Session Management screen.
14.	Click Close Window to close the Oracle BI Presentation Services screen.