Creating a Repository Using the Oracle Business Intelligence Administration Tool ( part 10 )

Lab 8:  Using Aggregates

In this set of steps you set up and use aggregate tables to improve query performance. Aggregate tables store pre-computed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a popular technique for speeding up query response times in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in the tables. Aggregate tables typically have many fewer rows than the non-aggregate tables and, therefore, processing is faster.

To set up and use aggregate tables, perform the following steps:

• Import Metadata

• Create New Logical Table Sources

• Set Aggregate Content

• Test Your Work

Import Metadata

1 .	Return to the Administration Tool and open the BISAMPLE repository in offline mode.
1 .	In the Physical layer, expand orcl.
2 .	Right-click Connection Pool and select Import Metadata to open the Import Wizard.
3 .	In the Select Metadata Types screen, select Views and click Next.
4 .	In the Select Metadata Objects screen, in the data source view, expand BISAMPLE.
5 .	In the data source view, select the following for import: SAMP_REVENUE_FA2 SAMP_TIME_QTR_D
6 .	Click the Import Selected button to move the objects to the Repository View.
7 .	Expand BISAMPLE in the Repository View and confirm that the objects are visible.
8 .	Click Finish to close the Import Wizard.
9 .	Confirm that the objects are visible in the Physical layer of the repository.
10 .	Create the following aliases: Table Alias SAMP_REVENUE_FA2 F2 Revenue Aggregate SAMP_TIME_QTR_D D1 Time Quarter Grain
11 .	Right-click F2 Revenue Aggregate and select View Data. F2 Revenue Aggregate stores aggregated fact information for revenue and units at the quarter and product grain.
12 .	Right-click D1 Time Quarter Grain and select View Data. D1 Time Quarter Grain stores time data at the quarter grain. It stores one record for each quarter beginning with Q4 2006 and ending with Q2 2011.
13 .	Use the Physical Diagram to create the following physical joins: "orcl".""."BISAMPLE"."D2 Product"."PROD_KEY" = "orcl".""."BISAMPLE"."F2 Revenue Aggregate"."PROD_KEY" "orcl".""."BISAMPLE"."D1 Time Quarter Grain"."QTR_KEY" = "orcl".""."BISAMPLE"."F2 Revenue Aggregate"."BILL_QTR_KEY"

Create New Logical Table Sources

1 .	In the Physical layer, expand D1 Time Quarter Grain.
2 .	In the BMM layer, expand D1 Time.
3 .	Drag the following columns from D1 Time Quarter Grain to their corresponding columns in D1 Time: D1 Time Quarter Grain D1 Time CAL_HALF Cal Half CAL_QTR Cal Qtr CAL_YEAR Cal Year DAYS_IN_QTR Days in Qtr JULIAN_QTR_NUM Julian Qtr Num PER_NAME_HALF Per Name Half PER_NAME_QTR Per Name Qtr PER_NAME_YEAR Per Name Year This action creates a new logical table source named D1 Time Quarter Grain for D1 Time.
4 .	Rename the D1 Time Quarter Grain logical table source to LTS2 Time Quarter Grain.
5 .	Double-click LTS2 Time Quarter Grain to open the Logical Table Source dialog box.
6 .	On the Column Mapping tab make sure show mapped columns is selected and note the column mappings. The logical columns now map to columns in two physical tables: D1 Time and D1 Time Quarter Grain.
7 .	Click OK to close the Logical Table Source dialog box.
8 .	In the Physical layer expand F2 Revenue Aggregate.
9 .	In the BMM layer expand F1 Revenue.
10 .	Drag the following physical columns from F2 Revenue Aggregate to their corresponding logical columns in F1 Revenue: F2 Revenue Aggregate F1 Revenue UNITS Units REVENUE Revenue This action creates a new logical table source named F2 Revenue Aggregate for F1 Revenue.
11 .	Rename the F2 Revenue Aggregate logical table source to LTS2 Revenue Aggregate.
12 .	Double-click LTS2 Revenue Aggregate to open the Logical Table Source dialog box.
13 .	On the Column Mappings tab make sure show mapped columns is selected and note the column mappings. The Revenue and Units logical columns now map to columns in two physical tables: F1 Revenue and F2 Revenue Aggregate.
14 .	Leave the Logical Table Source - LTS2 Revenue Aggregate dialog box open.

Set Aggregate Content

1 .	Click the Content tab.
2 .	Set the following logical levels for the logical dimensions: Logical Dimension Logical Level H1 Time Quarter H2 Product Product Total H20 Product Product Total H3 Customer Customer Total H5 Sales Rep Total Explanation: 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 aggregate logical table source for the Time dimension. Note that all levels are set to the total level except for the H1 Time logical dimension, which is set to Quarter. The result is, when a user queries against a particular level, Oracle BI Server will “know” to access the aggregate tables instead of the detail tables. For example, if a user queries for total sales by product by quarter, the server will access the F2 Revenue Aggregate fact table and the corresponding aggregate dimension table, D1 Time Quarter Grain. If a user queries for a level lower than the level specified here, for example Month instead of Quarter, then the server will access the detail tables (F1 Revenue and D1 Time). If a user queries for higher level (year instead of quarter) the aggregate tables will be used, because whenever a query is run against a logical level or above, the aggregate tables are used.
3 .	Click OK to close the Logical Table Source dialog box.
4 .	Double-click the LTS2 Time Quarter Grain logical table source to open the Logical Table Source dialog box.
5 .	On the Content tab, set the logical level to Quarter.
6 .	Click OK to close the Logical Table Source dialog box.
7 .	Save the repository and check global consistency. Fix any errors or warnings before proceeding.
8 .	Close the repository. Leave the Administration Tool open. Note that you did not need to change the Presentation layer. You made changes in the business model that impact how queries are processed and which sources are accessed. However, the user interface remains the same, so there is no need to change the Presentation layer. Oracle BI Server will automatically use the appropriate sources based on the user query.

Test Your Work

1 .	Return to Fusion Middleware Control Enterprise Manager and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
2 .	Return to Oracle BI, which should still be open, and sign in.
3 .	Create the following analysis to test the aggregate tables. Time.Per Name Quarter Base Facts.Revenue
4 .	Click Results.
5 .	Leave Oracle BI open.
6 .	Use FMW Control Enterprise Manager to check the query log. If you need help, click here to review steps from earlier in this tutorial.
7 .	Inspect the log. Notice that the query uses the expected tables: D1 Time Quarter Grain and F2 Revenue Aggregate.
8 .	Return to Oracle BI.
9 .	Click New > Analysis > Sample Sales.
10 .	Create the following analysis to test the aggregate tables. Time.Per Name Year Base Facts.Revenue
11 .	Click Results.
12 .	Return to FMW Control Enterprise Manager to check the query log. Hint: Click the Refresh button to refresh the log.
13 .	Inspect the log. Notice that the query uses the same tables: D1 Time Quarter Grain and F2 Revenue Aggregate. This is because Per Name Year is at a higher level than Per Name Quarter in the logical dimension hierarchy, so the aggregate tables are still used.
14 .	Return to Oracle BI.
15 .	Click New > Analysis > Sample Sales.
16 .	Create one more analysis to test the aggregate tables. Time.Per Name Month Base Facts.Revenue
17 .	Click Results.
18 .	Return to FMW Control Enterprise Manager to check the query log.
19 .	Inspect the log. Notice that this time the query uses the detail tables: D1 Time and F1 Revenue. This is because the requested data (revenue by month) is at a lower level than what is contained in the aggregate tables. The aggregate tables do not contain the data and, therefore, the detail tables are used in the query. This aggregate navigation is controlled by the aggregate content levels you set in the logical table sources.
20 .	Sign out of Oracle BI. Leave FMW Enterprise Manager open.

Lab 8_2: Using Initialization Blocks and Variables

You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. A variable has a single value at any point in time. Variables can be used instead of literals or constants in the Expression Builder in the Administration Tool or in end-user analyses. At run time, Oracle BI Server substitutes the value of the variable.

In this set of steps you create a new initialization block, Current Periods, and three new dynamic repository variables—CurrentYear, CurrentMonth, and CurrentDay. You then use the variables as column filters in an Oracle BI analysis. You use the Variable Manager in the Administration Tool to define variables and initialization blocks.

To set up and use initialization blocks and variables, perform the following steps:

• Create an Initialization Block

• Create Variables

• Test Your Work

Create an Initialization Block

1 .	Open the BISAMPLE repository in offline mode.
2 .	Select Manage > Variables to open the Variable Manager.
3 .	Select Action > New > Repository > Initialization Block.
4 .	Name the initialization block Current Periods.
5 .	Click the Edit Data Source button to open the Repository Variable Initialization Block Data Source dialog box.
6 .	Click the Browse button to open the Select Connection Pool dialog box.
7 .	Double-click the Connection Pool object to select it. The connection pool is added.
8 .	Enter the following SQL to determine the value of the current day, month, and year by finding the maximum value of the period key (BILL_DAY_DT) in the fact table: SELECT CALENDAR_DATE, PER_NAME_MONTH, PER_NAME_YEAR FROM BISAMPLE.SAMP_TIME_DAY_D WHERE CALENDAR_DATE = (SELECT MAX(BILL_DAY_DT) FROM BISAMPLE.SAMP_REVENUE_F)
9 .	Click Test and confirm the expected results are returned.
10 .	Close the Results window.
11 .	Click OK to close the Repository Variable Initialization Block Data Source dialog box. Check your work:

Create Variables

1 .	Click Edit Data Target to open the Repository Variable Initialization Block Variable Target dialog box.
2 .	Use the New button to create three new variables: CurrentDay, CurrentMonth, CurrentYear. The order is important. The value returned from the first column in the initialization block SQL, CALENDAR_DATE, is assigned to the CurrentDay variable. The value of the second column, PER_NAME_MONTH, is assigned to CurrentMonth (the second variable), and the value of the third column, PER_NAME_YEAR, is assigned to CurrentYear (the third variable). If necessary, use the Up and Down buttons to arrange the variables.
3 .	Click OK to close the Repository Variable Initialization Block Variable Target dialog box.
4 .	Leave the default refresh interval set to every hour. This means that the variables will be reinitialized every hour.
5 .	Click the Test button and check the results: In this example, the results are determined by the data in the sample database used for this tutorial, which holds data through December 2010.
6 .	Close the Results window.
7 .	Click OK to close the Repository Variable Initialization Block dialog box.
8 .	Check your work in the Variable Manager:
9 .	Close the Variable Manager.
10 .	Save the repository and check consistency. Fix any errors or warnings before proceeding.
11 .	Close the repository. Leave the Administration Tool open.

Test Your Work

1 .	Return to Fusion Middleware Control Enterprise Manager and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
2 .	Return to Oracle BI and sign in.
3 .	Create the following analysis to test the variables. Time.Per Name Year Time.Per Name Month Time.Calendar Date Base Facts.Revenue
4 .	Click Filter for the Per Name Year column. The New Filter dialog box opens.
5 .	Select Add More Options > Repository Variable.
6 .	In the Repository Variable field, enter CurrentYear to create a filter for the Per Name Year column using the CurrentYear repository variable.
7 .	Click OK to close the New Filter dialog box. The filter is added to the Filters pane.
8 .	Repeat the steps to add the CurrentMonth and CurrentDay repository variables as filters for Per Name Month and Calendar Date columns, respectively.
9 .	Click Results and confirm that data only for the current year, month, and day is returned (based on the sample data set).
10 .	Sign out of Oracle BI.