Select OracleBI > New Query to display the Connect (Oracle OLAP Data Source) dialog box.

In the Connect (Oracle OLAP Data Source) dialog box:

	Use the Connection Editor tab to create a new OLAP data source connection.
	Use the OLAP Connection tab to specify the user name and password and connect to an OLAP data source.

To create a new OLAP connection, first click the Connection Editor tab.

In the Connection Editor tab, enter an appropriate connection Description, and then specify the Host Name, Port Number, and SID for your OLAP connection.

Click Save to record the connection information.

Click on the OLAP Connection tab. Your new connection description will be displayed. Enter cs_olap as both the User name and Password, and then click Connect.

The Oracle OLAP Query Wizard is launched.

In the Query Wizard Welcome screen, click Next to continue.

In the Items step, you select the measures that you want to display.

The Available list contains all of the OLAP measures and dimensions that can be displayed in your worksheet. It may also include folders that were created by the database administrator when setting up Oracle OLAP. These folders organize measures of similar business use. In this case, the Computer Sales database schema has a folder called ‘Electronics-KPIs’.

In the Available list, expand Electronics-KPIs folder and select the measure Sales Revenue. Click the Add Selected Item button ('>') to move Sales Revenue to the Selected list.

Notice that Sales and its related dimensions are moved to the Selected list because the Automatically add/remove dimensions checkbox is checked.

In this report, Promotions will not be part of the analysis. You can remove any dimension that you do not want to view in the resulting report. In the Selected list, choose Promotion and click the Remove Selected Item button ('<') to remove it from the list.

The Promotion dimension is removed from the Selected list. Click Next to continue.

In the Layout step of the wizard, you can change the layout of the data by dragging and dropping the appropriate dimension or measure tiles.

Move Channel to the Page Items region, as shown here:

Then swap Product and Geography so that Product is displayed on the Row edge and Geography is displayed first in the Page Items region.

Click Next to continue.

In the next several steps of the Query Wizard, you select the dimension members for each dimension in the query.

First, select the members for the Channel dimension. In the Available list, click on the Channel total member. Then, click on the Add Item shuttle button ('>') to move it to the Selected list.

Click Next to continue.

Select members for the Geography dimension.

In the Available list, select the Members tab and click the drill symbol ('+'), which is located to the left of World total, then again click the drill symbol ('+') to expand Americas. The list of sub-regions appears.

Click the drill symbol ('+') located to the left of each sub-region to display the countries associated with each sub-region. Using the mouse and the 'Shift' key select the following 'Americas', 'Northern America', 'Canada', 'United States of America', 'Southern America', 'Argentina' and 'Brazil', as shown here:

Click the Add Selected Items shuttle button ('>') to add the dimension members to the Selected List.

Click Next to continue.

For the Product dimension, you will specify the ranking condition. For this condition, you will construct the following steps for the Product dimension in the Query Wizard:

	Select all the products in the Photo division
	Keep only the top 5 Products based on the Sales revenue

To select the Photo division, in the Members tab of the Available list, expand Product total by clicking on the drill symbol ('+'). Select Photo and click the Add Selected Items shuttle button ('>') to move Photo to the Selected list.

Still in the Choose Products step of the Query Wizard, select the Conditions tab in the Available list. This tab provides condition templates that are organized into condition types. The condition templates can be customized to specify the condition that you require.

Expand the Top/Bottom folder and select the template Top 10 based on Sales Revenue.

Click the Add Selected Items shuttle button ('>') to move the condition to the Selected list.

Then, in the Selected list, choose the Top 10 condition. Click the first hypertext link: ‘Add’. Choose ‘Then Keep’ from the list. Click the '10' hypertext link and change it to '5' so that the condition reads as follows:

Keep Product: Top 5 based on Sales Revenue

Now, you will qualify this condition so that it will be re-evaluated when a new Geography or Time member is selected from the dimension tiles in the worksheet.

First, click the Edit Step tool (pencil icon) next to the condition step to display the Edit Step dialog box.

Then in the Edit Step box, click the Qualify button.

The Qualify Measure dialog box opens. In the ‘Include Sales Revenue In’ column, select the following value for each dimension:

Click OK to close the Qualify Measure dialog box. Then, click OK to save the condition and close the Edit Step dialog box.

Click Next to continue.

For the Time dimension, display the Members tab in the Available list. Multi-select 2000 and 2001 and click the Add Selected Items shuttle button ('>') to add these two members to the Selected List.

Click Finish to execute the query.

The OLAP query returns the data for the Top 5 products in the Photo division based on Sales Revenue for the Americas, Channel total, and the year 2000.

Since the Geography and Time dimensions have been qualified for each member, the Product ranking condition is re-evaluated each time you select a new dimension member from either of the Geography or Time dimension headers at the top of the worksheet.

Click on Americas. A drop-down icon appears. Click on the drop-down icon and select Canada from the list. The query is automatically re-executed, and the correct list of top 5 photo products based on Sales Revenue is displayed.

From the Geography list, select United States. The report is refreshed with the new data for the ranking report. Finally, select Americas from the Geography list.

Click on 2000. Click on the drop-down icon and select 2001 from the list. The query is automatically re-executed, and the correct list of top 5 photo products based on Sales Revenue is displayed.

As stated previously, you can modify an existing OLAP query. Here, you will add a second measure to the query.

Select OracleBI > Edit Query. The Oracle OLAP Query Editor is displayed. In the Items tab, expand Electronics-KPIs folder in the Available list, and select Sales Cost. Click the Add Selected Items button ('>') to move Sales Cost to the Selected list.

Click OK to re-execute the query and display the results, which are shown here:

Select File > Save to save your worksheet.

Select OracleBI > Edit Query. The Oracle OLAP Query Editor is displayed.

In the Query Editor click the Dimensions tab and select Geography from the Choose box. The current selection for the Geography dimension is displayed in the Selected list.

Click the Save button at the bottom of the Selected list.

The Save Selection As dialog box is displayed.

In the Save Selection As dialog box, enter America Areas as the name. Accept the default Save Steps option and click OK.

Saved Steps: The "Save Steps" option creates an XML object that records the steps that result in the dimension selection. This object can be thought of as a dynamic saved selection. For example, if you create a saved selection that contains a condition, you should choose this option. Each time the query is run, this kind of saved selection is re-evaluated using the current state of the dimension.

Saved Members: The "Save Members" option creates an XML object that records a static list of values that are specified for the dimension when the saved selection is created. Therefore, this object contains a list of dimension members that never change.

Next, create a condition that will return the countries in Western Europe, and save the selection.

First, remove the current Geography selection by clicking the Remove All Items shuttle button ('<<').

In the Members tab of the Available list, click the drill symbol ('+'), which is located to the left of World total, then again click the drill symbol ('+') to expand Europe. Select Western Europe and then click the Add Selected Items shuttle button ('>') to add the dimension member to the Selected list.

Next, create the condition. Select the Conditions tab in the Available list and expand the Hierarchy folder. Note that the Query Editor automatically attempts to present values in the condition templates that are associated with dimension members that are identified in the preceding step.

Select the template Children of Western Europe. Click the Add Selected Items shuttle button ('>') to move the condition to the Selected list.

You can preview the members that will be returned from a set of selection steps. In the Selected list, click on the Members tab. The geography dimension members that will be returned by these two steps are displayed:

Click the Save button at the bottom of the Selected list. The Save Selection As dialog box is displayed. Enter Western European Countries as the name.

Accept the default Save Steps option and click OK.

Click OK to close the Query Wizard and refresh the query.

Select one of the Western European countries from the Geography drop-down list. For example, select United Kingdom.

Note how the top 5 product data is updated.

Select OracleBI > Edit Query to display the OLAP Query Editor. Select the Dimensions tab. In the Choose box, select Geography.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, click the Saved Selections tab. Select America Areas and then click the Add Selected Items shuttle button ('>') to move the saved selection to the Selected list.

Click OK to re-execute the query and display the worksheet.

Now, use the Western European Countries saved selection to change the query.

Once again select OracleBI > Edit Query to display the Query Editor. Click the Dimensions tab and select Geography from the choose list.

Click the Remove All Items shuttle button ('<<') to remove the current selection.

In the Available list, click the Saved Selections tab.

Select Western European Countries and then click the Add Selected Items shuttle button ('>') to move the saved selection to the Selected list.

Click OK to re-execute the query and display the worksheet.

Saved selections make it easy to refresh an OLAP query with a commonly be used dimension selections.

You may use the Query Editor to create and use other saved selections.

When you are done experimenting with saved selections, Save the worksheet.

In the worksheet, select the cells of the Sales Revenue and Sales Cost measures.

Click the Currency Style tool.

Currency formatting is applied to the data.

Remove the decimal places by clicking the Decrease Decimal tool twice.

The OLAP data should be formatted to look something like this:

Save the worksheet.

To add a Subtotal formula for Sales Revenue, first select the cell below the Sales Revenue column, and then click the Auto Sum tool.

The subtotal formula is created.

Press Enter to accept the subtotal formula.

Select United Kindom from the Geography dimension header. The new data is displayed, and the formula recalculates the subtotal.

To add a Subtotal formula for Sales Costs, simply copy the Auto Sum cell for the Sales Revenue column and paste it into the adjacent cell in the Sales Cost column.

The new subtotal formula is created.

Select Germany from the Geography dimension header. The new data is displayed, and both subtotal formulas are recalcuated.

To make the subtotal numbers stand out, select the two formula cells and click the Bold tool. Then, under the Product dimension members, enter Total as a right-justified heading for the subtotal formulas.

Save your worksheet.

As shown in the screenshot, select the following worksheet columns: Product, Sales Revenue, and Sales Cost.

Note: Do not select the totals row.

Then, click the Chart Wizard tool.

In the Chart Wizard, choose Column as the Chart type and click Finish.

The chart is displayed.

Drag the chart underneath the crosstab and resize it appropriately.

From the Geography dimension header, select France. The crosstab and the chart are updated with the new data.

When you are done experimenting with the chart, Save your worksheet.

Select OracleBI > New Calculation to display the Calculation Wizard. Click Next in the Welcome screen.

In Step 1 of the wizard, name the calculation Profit. For Calculation type, open the Basic Arithmetic folder and select Subtraction.

Click Next to continue.

In Step 2 of the wizard, select Sales Revenue in the Value box.

If necesary, select More from the Value box to display the Select Measure dialog box. Open the Electronics-KPIs folder and select the Sales Revenue for the measure.

Then click OK.

Still in Step 2 of the wizard, select Sales Costs In the Minus box. If necesary, select More from the Minus box to display the Select Measure dialog box. Open the Electronics-KPIs folder and select the Sales Costs for the measure and click OK.

Click Finish to save the calculation.

Remove the Profit calculation from the worksheet by performing the following:

Select OracleBI > New Calculation to display the Calculation Wizard.

If the Click Next in the Welcome screen.

In Step 1 of the wizard, name the calculation % Profit Margin. For Calculation type, choose Division, which is in the Basic Arithmetic folder.

Click Next to continue.

In Step 2 of the wizard, specify Profit in the Divide field. If necesary, select More to display the Select Measure dialog box. Choose Profit for the measure and click OK.

In the By field, choose Sales Revenue. If necesary, select More to display the Select Measure dialog box. Select Sales Revenue from the Electronics-KPIs folder. Then click OK.

The completed wizard step should look like this:

Click Finish to save the calculation and add it to the worksheet.

Now, format the % Profit Margin column as follows:

The worksheet should now look as follows:

From the Geography dimension header, select Spain. The crosstab and chart are refreshed with the new data.

Select File > Save to save your worksheet.

Select OracleBI > New Calculation to display the Calculation Wizard. Click Next in the Welcome screen.

In Step 1 of the wizard, name the calculation % Change Year Ago. For Calculation type, choose Percent Difference from Prior Period, which is in the Prior/Future Comparison folder.

Click Next to continue.

In Step 2 of the wizard, specify Sales Revenue in the Value field. If necesary, select More from the Value box to display the Select Measure dialog box. Select Sales Revenue from the Electronics - KPIs measure folder.

Leave the other options in their default settings, as shown here.

Then, click Finish to create the calculation and add it to the worksheet.

Select the year 2000 from the Time dimension. The worksheet data updates to reflect the new dimension selection.

Select File > Save to save your worksheet.

From the Time dimension tile, choose 2001. Then, select the % Profit Margin data cells.

Select Format > Conditional Formatting... In the Conditional Formatting dialog, specify the following condition:

Cell Value Is greater than .25

Still in the Conditional Formatting dialog, click the Format button, and select an appropriate color in the Patterns tab. Then, click OK to close the Format Cells dialog.

Click OK to apply the conditional format.

Select other members from the Geography dimension header and the Time dimension header. Note how the conditional format updates along with the data in the worksheet.

When you are done experimenting with the worksheet, click Save to save your work.