5 Analyze the Data

In this chapter, you analyze the data in the data mart. To extract business intelligence from the data mart, you use Oracle BI Answers to:

• Create queries, format views, charts, and add user interactivity and dynamic content to enhance the user experience.

• Work with views, including pivot tables, and narratives.

• Create selectors to drive interactivity in your BI Answers requests.

This chapter contains the following topics:

• Section 5.1, "Prerequisites"

• Section 5.2, "Creating a Query and a Chart"

• Section 5.3, "Working with Pivot Tables"

• Section 5.4, "Creating Column Selectors"

• Section 5.5, "Creating a Narrative View"

• Section 5.6, "Creating View Selectors"

• Section 5.7, "Creating Conditional Formats, Gauges, and Navigation"

5.1 Prerequisites

If you have not completed the previous chapters, you must first populate the BISE1_TUTORIALWH schema before proceeding with this chapter. Section 2.2.2.3, "BISE1_TUTORIALWH Schema" explains how to do this.

If you have not completed the previous chapters, you must also replace the BI metadata repository file, as follows:

1. Shut down the BI Server service.

2. Copy tutorial\bi_ad\bise1_soln.rpd to the bi\oraclebi\server\repository directory.

3. Rename bi\oraclebi\server\repository\bise1_soln.rpd to bise1.rpd.

4. Start the BI Server service.

If you specified a password other than welcome1 during installation, perform the following steps as well:

1. Go to Start > Programs > Oracle Business Intelligence > Administration.

2. Select File > Open > Online.

3. Enter Administrator in the Password field. Click OK.

4. In the Physical Layer pane, expand BISE1_SALESWH. Double-click Connection Pool.

5. In the Password field, enter the password you specified during installation. Click OK.

6. Enter the password again in the Confirm Password dialog box. Click OK.

7. Repeat steps 4 through 6 for the BISE1DB node in the Physical Layer.

8. Select File > Save. Click No when asked whether your wish to check global consistency. Exit the Oracle BI Administration tool.

If you have not completed the previous chapters, you must also create the BI aggregate tables in the database, as follows:

1. Open a command prompt window.

2. Go to the directory where Oracle BI Standard Edition One is installed, then go to the tutorial\bi_ad directory.

3. Run the cr_tabs.bat batch script. Enter the password for the BISE1_TUTORIALWH database account when prompted. Close the command prompt window when done.

5.2 Creating a Query and a Chart

You build and format a business intelligence request using Oracle BI Answers, and create and format a chart.

This section contains the following topics:

• Section 5.2.1, "Create an Answers Query"

• Section 5.2.2, "Add Filters on Columns"

• Section 5.2.3, "Create Totals and Format Results"

• Section 5.2.4, "Create a Chart"

5.2.1 Create an Answers Query

To create an Answers query:

1. Select Start > Programs > Oracle Business Intelligence > Presentation Services. Log in to Oracle Business Intelligence as Administrator with password Administrator.

2. Explore the Sales Analysis Dashboard. Click the Answers link to navigate to the Answers start page, and explore the requests created from subject area GEC_DW. Expand GEC_DW under My Folders from the left pane. Under GEC_DW, there are several folders. Open each of the folders and double-click the requests to view them.

3. To ensure the latest changes in the BI metadata repository are reflected in the Oracle BI Presentation Server, click Settings > Administration. Click Reload Files and Metadata. Click Close Window.

4. You will be creating requests using the GEC_DW_TUTORIAL subject area you just created in the Oracle BI Administration Tool. Select the GEC_DW_TUTORIAL subject area by clicking the GEC_DW_TUTORIAL link in the Subject Areas list.

5. In this example, there are two subject areas, but there could be more depending on the metadata that is defined in the Oracle Business Intelligence repository, which can contain multiple subject areas. Subject areas are sets of related information with a common business purpose.

6. In the left-hand selection pane of the Answers interface, click the Plus icon next to Channels to expand it. Click the Channel_name column to add it to your query criteria, which appears in the right pane. The query you are building has two measures and three attributes.

   Figure 5-1 Adding Channel_name to the Query Criteria

   
   

7. From Geography click Region, from Times click Year, and from Sales click Amount_Sold and Gross_Profit. Your query should look like the one shown in Figure 5-2.

   Figure 5-2 Query with Channel_name, Region, Year, Amount_Sold, and Gross_Profit

   
   

8. You can reorder the columns in your query by clicking and dragging them. Drag the Year column in front of the columns from the Times table in your query. Your query criteria should now look like the one shown in Figure 5-3.

   Figure 5-3 Reordered Query

   
   

9. Now drag the column back to its original location.

10. Click the Advanced tab. The Advanced tab can be made available only to specific users. The XML fully defines the query (including chart formats when charts are used). The SQL defines the content of the query. Note that any query or reporting tool that can issue SQL over an ODBC connection can issue a query to the BI server, just like Answers. Examine the request XML that defines the view and the logical SQL that will be issued for the query.

    The Request XML defines the whole analysis, including logical SQL and views for the query. The SQL Issued field contains only the logical SQL that will be issued to the Oracle BI Server for processing. Note the saw_X column aliases, which are added automatically. Editing the logical SQL will change your view definitions.

11. Click the Criteria tab.

5.2.2 Add Filters on Columns

To add filters on columns:

1. Create a filter for the Channel_name column. Click the Filter button below the Channel_name column to add a filter on that column.

   Figure 5-4 Filter Button on Channel_name Column

   
   

2. In the Create/Edit Filter dialog box, click the All Choices link to display all values for the column.

   Figure 5-5 Create/Edit Filter: All Choices Link

   
   

3. You can also limit the list of choices by setting a match criteria using the Match drop-down menu and entering a string, then clicking the All Choices link. The Limited Choices link will limit choices to those that are consistent with any preexisting filters.

4. Verify that the Operator for the filter is set to is equal to / is in and then click the Direct Sales value in the list of choices. Direct Sales is added as a value in the filter. Click OK to create the filter.

   Figure 5-6 Filter for Direct Sales

   
   

5. Click the Results tab to view your results.

   Figure 5-7 Results for Direct Sales

   
   

6. Click the Criteria tab. Add another filter on Year = 2006.

   Figure 5-8 Create/Edit Filter for Year=2006

   
   

7. You should have two filters now joined with an AND operator.

   Figure 5-9 Two Filters Joined with an AND Operator

   
   

8. Click the Results tab to view the results of your query.

   Figure 5-10 Results for Direct Sales and Year 2006

   
   

9. Other ways to view results are by clicking the view buttons below the tabs in the Criteria pane, or by clicking the Display Results button below the columns in your query criteria. By default, the results are displayed in the Compound Layout view, which contains two other views, a Title view and a Table view. As you will see, you can delete these default views and add other views to the Compound Layout. Later, you will add these different types of views to a dashboard.

10. Notice Amount_Sold for the Middle East Region is significantly lower than the other Regions. Return to the Criteria tab and create a filter for Regions that does not include Middle East, as shown in Figure 5-11.

    Figure 5-11 Filter for Regions

    
    

11. Remove the other two filters by clicking the Delete icon next to the filters.

12. Save the filter by clicking the Save filter button. In the Save Filter dialog box, select My Filters. Enter Filter on Region in the Name field and click OK.

5.2.3 Create Totals and Format Results

To create totals and format results:

1. Click the Combine individual views for display on dashboards button.

   Figure 5-12 Combine Individual Views for Display on Dashboards

   
   

2. To open the Edit Table view for your results, click the Edit view icon for the Table view in the Compound Layout.

   Figure 5-13 Edit View Icon

   
   

   The column controls for each column are displayed with the results. Using the view-level controls, you can also set table-wide formatting properties, import formatting from other queries, and set grand totals for the entire table.

3. Click the Total By icon above the Channel_name column to add subtotals by channel to your results.

   Figure 5-14 Total By Icon

   
   

4. The measure is totaled each time the value in the Channel_name column changes. In this case, the default aggregation rule (SUM) is applied. The default aggregation rule is set in the Oracle BI repository metadata, but can be overridden using controls in the Edit Formula dialog box accessed through the Edit Formula icon in the Edit Table view or the Criteria tab.

   Figure 5-15 Subtotals by Channel

   
   

5. Select Table in the View menu. Click the Grand Total button at the table view level to add a grand total to your results.

   Figure 5-16 Grand Total Button

   
   

6. Scroll down to the bottom of the Results pane and verify that the grand total that you set for the results is present.

7. Click the Column Properties icon above the Amount_Sold column.

   Figure 5-17 Column Properties Icon

   
   

8. In the Column Properties dialog box, click the Data Format tab, select the Override Default Data Format option, then select the Use 1000's Separator option and click OK. This displays a comma separator in the number results for the column.

   If the column is already formatted, ignore these steps.

   Figure 5-18 Edit Column Format: Data Format Tab

   
   

9. In the Edit Column Format dialog box, click Save. If you have permissions as a Web administrator, you can save the data format as the systemwide default for the column you are working with, or for all columns with the same data type.

   Figure 5-19 Saving as the System-Wide Default

   
   

10. Click OK and verify that a 1000's separator is displayed in the results for the Amount_Sold column.

    Figure 5-20 Amount_Sold with 1000's Separator

    
    

11. In the Display drop-down menu, select Results Only to eliminate the Header Toolbars.

    Figure 5-21 Display: Results Only

    
    

5.2.4 Create a Chart

To create a chart:

1. Click the Create a new request icon and select the GEC_DW_TUTORIAL subject area.

   Figure 5-22 Creating a New Request for the GEC_DW_TUTORIAL Subject Area

   
   

2. Create a request with Product_Category under Products, and Amount_Sold and Gross Profit under Sales.

3. In the Results tab, verify that Amount_Sold and Gross Profit have the $ currency symbol. If necessary, format them so that they have the currency symbol in all requests.

4. Pick Chart in the View menu.

   Figure 5-23 Selecting Chart

   
   

   By default, charts display as a Vertical Bar graph.

   Figure 5-24 Chart View

   
   

5. Select the y-axis box for the Gross Profit measure. Click the Redraw button at the bottom. Notice that the chart redraws with a legend for measures.

   Figure 5-25 Chart with Legend for Measures

   
   

6. Click the General chart properties icon (first icon from the left). Click the Custom Title box. Enter Amount Sold vs Gross Profit, then click OK.

   Figure 5-26 General Chart Properties

   
   

7. Explore some of the other options in the Axis Titles & Labels dialog box (second icon from the left). For example, you can toggle whether scale labels displays on the axes and set orientation guidelines for your labels. In addition, you can set overrides for the default data format on measures.

8. Click the Additional Charting Options icon.

   Figure 5-27 Additional Charting Options icon

   
   

9. In the Additional Charting Options dialog box, click the Borders & Colors tab. Click the color box for Background Color and, in the Color Selector dialog box, select light green from the palette and click OK.

   Figure 5-28 Additional Charting Options: Borders & Colors

   
   

10. In the Additional Charting Options dialog box, click the Grid Lines tab and set the major grid line color to white and the minor grid line color to light gray, using the same method as described in the previous step. After you have set both colors, click OK to apply your changes.

11. Select the graph Type as 3D. Your chart should look like the one shown in Figure 5-29.

    Figure 5-29 Chart After Selecting Additional Charting Options

    
    

12. Sort the request by Gross Profit. Click the Criteria tab and click the Order By icon (the icon with two arrows) in the Gross Profit column. The arrow points up to indicate an ascending sort. Click again, and the arrow points downward to indicate descending order.

13. Click the Results tab to verify that your sort has been applied to your chart.

    Figure 5-30 Chart with Sorting By Gross Profit

    
    

14. Select Compound Layout from the View drop-down menu.

    Figure 5-31 Selecting Compound Layout

    
    

15. Click the Add View link and select Chart to add the Chart view to the Compound Layout view.

    Figure 5-32 Adding the Chart View

    
    

16. Click the Delete View icon in the Title view to delete it from the Compound Layout.

    Figure 5-33 Deleting a View

    
    

17. Click the Save Request icon.

    Figure 5-34 Save Request icon

    
    

18. In the Save Request dialog box, click My Folders, then click the Create Folder button. In the Create Folder In My Folders dialog box, enter GEC_DW_TUTORIAL and click OK.

19. Click Create Folder again. In the Create Folder In My Folders dialog box, enter Learn and click OK.

20. Enter Category Gross Profits as the Name and click OK.

5.3 Working with Pivot Tables

To work with pivot tables:

1. Create a new request with Region under Geography, Product_Category under Products, and Gross Profit under Sales.

2. Apply the previously saved filter called Filter on Region. Click Filter on Region in the left navigation tree. In the Apply Saved filter dialog box, select Apply contents of filter instead of a reference to the filter and click OK.

   Figure 5-35 Applying a Previously Saved Filter

   
   

3. In the Results tab, pick Pivot Table in the Compound Layout View menu. This opens the Pivot Table Layout page.

   Figure 5-36 Opening the Pivot Table Layout Page

   
   

4. Examine the default pivot table layout and the pivot table that is created. Measure labels for the measures in your query appear in blue as columns in the pivot table. Row headings for the dimensional attributes in your query are displayed in gray as rows by default. The measures are displayed at the intersection of the rows and columns.

   Figure 5-37 Default Pivot Table Layout

   
   

   Figure 5-38 Pivot Table

   
   

5. Deselect the Display Results option. This option displays the results of any layout modifications you make as you work in the Pivot Table layout. To speed performance, you will only view your results periodically.

   Figure 5-39 Deselecting Display Results

   
   

6. Drag the Product_Category column below the Measure Labels in the Columns area in your layout controls. When you see a blue line appear, you have a valid insertion point and can drop the column.

   Figure 5-40 Product_Category

   
   

7. Click the Display Results link to verify your changes in the pivot table.

8. Drag the Product_Category column above the Measure Labels in the column section.

   Your pivot table should look like the one shown in Figure 5-41.

   Figure 5-41 Pivot Table with Product_Category Column

   
   

9. Interchange Region with Product_Category. Your pivot table should like the one shown in Figure 5-42.

   Figure 5-42 Pivot Table with Interchanged Columns

   
   

10. Select Compound Layout from the View drop-down menu. Click the Add View link and select Pivot Table to add the Pivot table view to the Compound Layout view.

    Figure 5-43 Selecting Compound Layout > Add View > Pivot Table

    
    

11. The Pivot table is added at the bottom. Drag the Pivot Table and place it above the Table.

    Figure 5-44 Moving the Pivot Table Up

    
    

12. You will be adding Column Selector to this request, so save the request as Region Pivot Table with Column Selector under the folder GEC_DW_TUTORIAL > Learn.

5.4 Creating Column Selectors

In this section, you build a Column Selector and see the effects in a request. Column Selectors allow users to select from a group of columns, substituting columns in their queries for comparative analysis.

To create column selectors:

1. In the Results tab, select Column Selector from the View > Other Views drop-down menu.

   Figure 5-45 Choosing the Column Selector View

   
   

2. Select the Include Selector option in Column 3, currently Gross Profit.

   Figure 5-46 Selecting the Include Selector Option

   
   

3. In the selection pane on the left, click the following columns to make them available in the Column Selector: Amount_Sold, Cost, and Gross Profit.

   Figure 5-47 Choosing Amount_Sold, Cost, and Gross Profit

   
   

   Your Column Selector in the Results pane should look like the one shown in Figure 5-48.

   Figure 5-48 Column Selector

   
   

4. Select Compound Layout from the View drop-down menu. Click the Add View link and select Column Selector to add the Column Selector view to the Compound Layout view.

   Figure 5-49 Adding the Column Selector View to the Compound Layout View

   
   

   Notice that views that you have built for the active request appear in the View and Add View drop-down menus with green circle icons next to them.

5. Scroll to the bottom of the Compound Layout and drag the Column Selector view above both Pivot Table and Table views.

   Figure 5-50 Positioning the Column Selector View

   
   

6. Scroll down, locate the Title box, and drag it above the Column Selector view.

7. In the Column Selector, select Amount_Sold. Your Compound Layout view should look like the one shown in Figure 5-51.

   Figure 5-51 Compound Layout View with Column Selector

   
   

8. Note that Amount_Sold is selected in the Column Selector, and both the Pivot Table and the Table displays values for Amount_Sold. Select Cost and see the change.

9. In the Compound Layout, you now have a Column Selector, a Pivot Table, a Table, and a Title.

10. Save the request with the same name: Region Pivot Table with Column Selector.

5.5 Creating a Narrative View

In this section, you build and format a business intelligence request using Oracle BI Answers, and create and format a Narrative View. The Narrative view lets you add text that appears with the results to provide information such as context, explanatory text, or extended descriptions.

To create a Narrative View:

1. Create a new request with the following columns: Month under Times, Region under Geography, and Gross Profit under Sales. Add a filter on Month to get the last transaction month in the data source. The filter will use the repository variable maxMonthDesc_01 you created earlier.

2. Click the filter icon on the Month column. In the Create Filter dialog box, set the Operator to be is equal to/is in. Click Add>Variable>Repository.

3. In the Value field, enter maxMonthDesc_01. Click OK.

   Your request should look like the one shown in Figure 5-52.

   Figure 5-52 Request with maxMonthDesc_01 Filter

   
   

   In the Results tab, the request should display like the one shown in Figure 5-53.

   Figure 5-53 Results Tab with maxMonthDesc_01 Filter

   
   

4. Select Narrative from the View > Other Views drop-down menu. In the Narrative view workspace, in the Narrative field, enter the following: Gross Profit for @2 for the last year and month of@1 was @3.

   The narrative is a combination of text and query column values. In this example, @1 refers to the first column in the query, Month, @2 refers to the second column, Region, and @3 refers to Gross Profit. Note that you can control the number of row values returned in the Narrative view by setting the Rows to display value. By default, all queried rows are displayed.

5. To highlight the column values in the narrative, select @1 in the narrative and click the Bold button. Also add bold tags to @2 and @3.

   Figure 5-54 Choosing Bold for Portions of the Narrative Field

   
   

6. Position the cursor at the end of the narrative text, and then click the Line Break button to insert a line feed between each sentence. Enter 2 in the Rows to display field.

7. In the Compound Layout, add the Narrative View and delete the Table and Title views.

8. Save the request as Narrative View for Region Gross Profit under GEC_DW_TUTORIAL > Learn.

5.6 Creating View Selectors

In this section, you build a View Selector and experiment with its use in requests. View Selectors allow users to quickly navigate between different views of their queries (for example, viewing different charts of the same data, or quickly navigating to a pivot table to do trend analysis).

To create view selectors:

1. In Oracle BI Answers, click the Answers link. Go to the My Folders > GEC_DW_TUTORIAL > Learn folder and open the first request, Category Gross Profit, that you saved.

2. Click Modify, then click the Results tab.

   Figure 5-55 Category Gross Profit Request

   
   

3. From the View drop-down menu, select Other Views > View Selector. In the View Selector design workspace, select Table and Chart in the Available Views field, using Ctrl+Click to select multiple views.

4. Click the Move Right icon to add them to the Views Included field.

   Figure 5-56 Selecting the Table and Chart Views

   
   

5. Select Table in the Views Included field, click the Rename button, and, in the Rename dialog box, rename it to Product Category Table. Click OK.

6. Rename Chart to Product Category Chart. Click OK.

7. Use the View drop-down menu to go to the Compound Layout view. Delete the Table and Chart view from the Compound Layout.

8. Add the View Selector view using the Add View button. Also add the Title view.

9. Select Product Category Chart from the View Selector.

   Figure 5-57 Choosing Product Category Chart from the View Selector

   
   

   The chart should change to the Chart view, as shown in Figure 5-58.

   Figure 5-58 Product Category Chart View

   
   

10. Save the request as Product Category View Selector under GEC_DW_TUTORIAL > Learn.

5.7 Creating Conditional Formats, Gauges, and Navigation

In this section, you create a report with conditional formats, add gauges, and from one measure field, navigate to a different report to get further details.

This section contains the following topics:

• Section 5.7.1, "Create a Report with Conditional Formats"

• Section 5.7.2, "Add Gauges to the Report"

• Section 5.7.3, "Add Navigation to the Report"

5.7.1 Create a Report with Conditional Formats

To create a report with conditional formats:

1. Create a request with Year, Region, Amount_Sold, and Gross Profit. Create a filter condition on the Year column, where Year=2006.

2. Delete the Year column.

   The request criteria should appear as shown in Figure 5-59.

   Figure 5-59 Creating a Request with Year=2006 Filter

   
   

3. Click the Column properties icon for the Gross Profit column. Click the Conditional Format tab. Add the following conditions by clicking Add Condition and selecting Gross Profit each time. Click the Value Property icon, and set the Cell Background Color for the conditions as follows:

   Gross Profit < 500,000 = red
   Gross Profit between 500,000 and 5,000,000 = yellow
   Gross Profit > 5,000,000 = green
   

   Figure 5-60 Conditions for Gross Profit Column

   
   

4. Click the Results tab. The report should appear like the one shown in Figure 5-61.

   Figure 5-61 Results with Conditions for Gross Profit

   
   

5. Save the request as Advanced Report under the GEC_DW_TUTORIAL > Learn folder.

5.7.2 Add Gauges to the Report

To add gauges to the report:

1. In the Results tab for the Advanced Report, select Gauge from the View drop-down list. In the Gauge drop-down list, select Dial. In the Size drop-down list, select Small. In the Measure drop-down list, select Gross Profit. In the Marker Type drop-down list, select Line. Click the color button to change the color for the indicator needle. Select a bright red color. Click OK.

   Figure 5-62 Formatting Results for Advanced Report

   
   

2. Click the Gauge Canvas Properties button at the top of the Gauge view page. In the Title section, select Override default. In the Title field, type Regional Sales Analysis. Click OK.

   Figure 5-63 Gauge Canvas Properties

   
   

   The gauges should look like the ones shown in Figure 5-64.

   Figure 5-64 Gauges for Advanced Report

   
   

3. Add the gauge to the compound layout, and remove the title. The report should like the one shown in Figure 5-65

   Figure 5-65 Results and Gauges for Advanced Report

   
   

4. Save the request with the same name.

5.7.3 Add Navigation to the Report

To add navigation to the report:

1. In the Criteria tab for the Advanced Report, select Column Properties for the Amount_Sold column. Click the Column Format tab. In the Value Interaction section, select Navigate from the Type drop-down list.

   Figure 5-66 Column Format Tab of Column Properties

   
   

2. Click the Add Navigation Target button. Click the Browse button and select the Product Category View Selector request under the GEC_DW_TUTORIAL > Learn folder. Click OK, then click OK again.

   Figure 5-67 Selecting the Product Category View Selector Request

   
   

3. In the Results tab, click the Amount_Sold column for any region.

   Figure 5-68 Choosing the Amount_Sold Column

   
   

   A new windows appears with the Product Category View Selector request that shows the product categories for the region.

   Figure 5-69 Product Category View Selector Request

   
   

4. Save the request with the same name.