OBIEE 11G Working with Pivot Tables, and Master-Detail Linking

In this topic, you learn how to create an analysis with a Pivot Table view, format, add a calculated column to a pivot table, add a Gauze view, and create a master-detail linking. You also learn how to filter your results using Selection Steps in pivot tables.

Creating and Formatting a Pivot Table view and Adding Calculations

In this subtopic, you begin by creating a new analysis with a hierarchical column and then applying a named filter. Also you format and add totals.
Pivot tables provide the ability to rotate rows, columns, and section headings to obtain different perspectives of the same data.
They are interactive in that they are drillable, expandable, and navigable.
To create an analysis with a pivot table, perform the following steps:

1 .	Click New > Analysis on the global header. Select A-Sample Sales as the subject area.
2 .	In the Analysis Editor, add the following columns to the analysis criteria: Folder Columns Orders Orders Hierarchy Customers C50 Region Products P4 Brand Base Facts 1-Revenue
3 .	Click the Results tabbed page to view the analysis and inspect the pivot table. Observe that the Pivot Table view is included by default.
4 .	a. Return to the Criteria tabbed page. b. Apply the Americas and EMEA filter named filter as you did previously. c. Edit the column properties for Revenue. Click the More Options icon for 1 - Revenue and select Column Properties. The Column Properties dialog box appears.
5 .	Select the Column Format tabbed page. Select the Custom Headings check box and enter Revenue in the Column Heading text box.
6 .	Select the Data Format tabbed page. Select the Override Default Data Format checkbox and select the values as indicated below in the image. Click OK.
7 .	Click the Results tabbed page. Review the formatting changes that you made to the Revenue column.
8 .	Delete the Title view and then click the Edit View icon to format the pivot table. The Pivot Table editor appears.
9 .	Format the pivot table as follows: a. Drag P4 Brand below Measure Labels. b. Drag C50 Region to the Sections area. The pivot table should look like this:
10 .	Add a calculation to the pivot table by duplicating the Revenue column. Click the More Options icon for the Revenue column and select Duplicate Layer. The duplicated Revenue column appears.
11 .	a. Select the More Options > Format Headings to edit the properties for the duplicate column. b. In the Caption text entry box in the Edit Format dialog box, name the new column % Revenue and click OK.
12 .	Change the calculation to reflect a percentage of the parent. Select the More Options > Show Data As > Percent of > Row Parent. The Pivot Table editor looks like this: Click Done and save the analysis as Regional Revenue Pivot. The pivot table should look like this:
13 .	a. Expand the Orders Hierarchy by clicking the plus sign icon for Total Orders for the Americas. The plus and minus icons are used to expand and collapse the data for analysis. Orders Hierarchy contains Orders on the row edge and Total Orders as the parent. Revenue is the measure. Because hierarchical columns imply pivot tables, you can sort not only on members and measures, but also on rows. Hierarchical members on the row edge can include sort carat icons () , which allow you to sort the members on the column edge by that row, in either ascending or descending order. These carat icons do not appear for attribute columns, which do not have the concept of a row edge. When you sort members in a hierarchical column, you always sort within the parent; that is, children are never sorted outside of their parent. The children appear below the parent in the proper sort order; the parent is not sorted within its children. b. The Total Orders parent member represents an outline total for the orders. Row sort Total Orders, for the Americas in Descending sequence and examine the results within the pivot table. The product brands on the column edge are sorted, reflecting sorted Revenue values in descending sequence for each Total Order.
14 .	Expand Express orders and then expand 6 - Cancelled to view the % of total revenue lost from cancellations.
15 .	a. Place your cursor on top of the Orders Hierarchy and right-click. Select Collapse all items in view from the menu. Notice that you can also sort, exclude columns, and move items around using this menu. b. Place your cursor to the left of the Brand column (BizTech). A tab appears. When you hover over this tab, a swap icon appears. You use this swap icon to swap columns with rows or to reposition a column or row along a different axis. The swap icon look likes this: c. Drag Brand on top of Orders Hierarchy. The pivot table should look like this before you release the mouse button: d. Release the mouse button and review the pivot table. e. Save the analysis.

Adding a Gauze View

In this subtopic, you add a Gauze view to the Compound Layout.
To add a Gauze view, perform the following steps:

1 .	Click the New View icon and select Gauge > Default (Dial).
2 .	Scoll down and view the gauge.
3 .	Edit the gauge properties to display medium size and remove the footers. Use the pencil icon to access gauge editor. The gauge view should look like this.
4 .	Add a slider to the gauge (for C50 Region). In the Layout pane, drag C50 Region to the Sections drop target and select Display as Slider. Click Done and save the analysis.

Adding Selection Steps and Selection Steps View

In this subtopic, you begin by creating a new analysis with hierarchical columns and apply selection steps.
To create an analysis with a pivot table, perform the following steps:

1 .	Select the New > Analysis on the global header. Select A – Sample Sales as the subject area.
2 .	In the Analysis Editor, double-click the following columns: Folder Columns Products Products Hierarchy Time Time Hierarchy Base Facts 1 - Revenue
3 .	Click the Results tab. Two views appear: Title and Pivot Table. Because you are using hierarchical columns, a Pivot Table view is generated automatically. Expand Time Hierarchy.
4 .	a. Delete the Title view. b. Scroll down to view the Selection Steps pane. Expand it. Note: If you do not see the Selection Steps pane, click the Show/Hide Selection Steps pane icon
5 .	You will add members based on Hierarchy for Products Hierarchy. In the Products - Products Hierarchy section, click Step 2.Then, New Step. Select Select Members based on Hierarchy.
6 .	The New Hierarchy Selection step dialog box appears. Select “Based on Family Relationship” from the Relationship drop-down list.
7 .	The New Hierarchy Selection Step dialog box expands. Select “ Keep only”, “Siblings Of” as the action, and then expand Total Products and select FunPod. Move FunPod to the Selected pane. Click OK.
8 .	Save the analysis as My Selection Steps Analysis under the folder My Folders>Regional Revenue. Observe that the analysis only shows BizTech and HomeView, since you selected Siblings of FunPod.
9 .	Click the pencil icon  in Step 2 to edit the selection for Product Hierarchy.
10 .	In the Edit Hierarchy Selection Step dialog box, select Include selected members. Click OK and save the analysis. Observe that FunPod is included this time.
11 .	Selection Steps views are now available just as another view that can be included. From the New View drop-down list, select Selection Steps. The Selection Steps view is displayed in the compound layout. Save the analysis.
12 .	Now, you will add a Group for Products Hierarchy. In the Product – Product Hierarchy section, click “Then, New Step.” Select Add Groups or Calculated Items > New Group.
13 .	In the New Group dialog box, enter My Group in the Display Label text box, expand Total  Product, and then select FunPod and HomeView. Move them to the Selected pane and click OK. This new group is added to the Compound Layout view.
14 .	Click My Group in the Selection Steps View and you will be able to see the values in My Group. You can also see the values if you expand My Group in the Pivot table. Save the analysis. This concludes the topic of creating a Pivot table and applying selection steps to the table.

 

Creating a Master-Detail Linking

Master-detail linking of views allows you to establish a relationship between two or more views so that one view, called the master view, will drive data changes in one or more other views, called detail views. To create a Master-Detail linking, for the previously created Regional Revenue Pivot analysis perform the following steps:

1 .	Set up the master view to which you link the detail view. a. Edit the Regional Revenue Pivot analysis, and click the Criteria tabbed page. b. Click the More Options icon and select Column Properties for the C50 Region column.
2 .	The Column Properties dialog box appears. Click the Interaction tabbed page. In the Value area, click the Primary Interaction drop-down list, and select Send Master-Detail Events.
3 .	When "Send Master-Detail Events" is selected, a qualification text box, Specify channel, appears. You use this text box to enter a name for the channel to which the master view will send master-detail events. This is a case-sensitive text box. a. Enter region in the "Specify channel" text box and click OK. b. Save the analysis.
4 .	Define the detail view to which the master view should link. You can add any view that includes the same master column as the master view. a. Click the Results tabbed page to view the Compound Layout and click Edit View for the Gauge view. b. The Gauge editor appears. Click Edit gauge properties on the toolbar.
5 .	The Gauge Properties dialog box appears. a. Select the Listen to Master-Detail Events check box. b. Enter region in the Event Channels text box. Remember that this must match precisely with the text entered for the master view. c. Click OK.
6 .	Click Done and then save your analysis.
7 .	a. In the Pivot Table view (the master view), select AMERICAS to drill down. Both the Pivot Table view and the Gauge view (the detail view) update to reflect the drill. b. Save your analysis.