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.
Screenshot for Step
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
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step
7 .
Click the Results tabbed page. Review the formatting changes that you made to the Revenue column.
Screenshot for Step
8 .
Delete the Title view and then click the Edit View icon Show Screenshot for Stepto format the pivot table.
The Pivot Table editor appears.
Screenshot for Step
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:
Screenshot for Step
10 .
Add a calculation to the pivot table by duplicating the Revenue column. Click the More Options icon Screenshot for Stepfor the Revenue column and select Duplicate Layer.
Screenshot for Step
The duplicated Revenue column appears.
Screenshot for Step
11 .
a. Select the More Options > Format Headings to edit the properties for the duplicate column.
Screenshot for Step
b. In the Caption text entry box in the Edit Format dialog box, name the new column % Revenue and click OK.
Screenshot for Step
12 .
Change the calculation to reflect a percentage of the parent. Select the More Options > Show Data As > Percent of > Row Parent.
Screenshot for Step
The Pivot Table editor looks like this:
Screenshot for Step
Click Done and save the analysis as Regional Revenue Pivot. The pivot table should look like this:
Screenshot for Step
13 .
a. Expand the Orders Hierarchy by clicking the plus sign icon Screenshot for Step 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.
Screenshot for Step
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 (Show Screenshot for Step) , 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.
Screenshot for Step
14 .
Expand Express orders and then expand 6 - Cancelled to view the % of total revenue lost from cancellations.
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step The swap icon look likes this:
Screenshot for Step
c. Drag Brand on top of Orders Hierarchy. The pivot table should look like this before you release the mouse button:
Screenshot for Step
d. Release the mouse button and review the pivot table.
Screenshot for Step
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).
Screenshot for Step
2 .
Scoll down and view the gauge.
Screenshot for Step
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.
Screenshot for Step

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.
Screenshot for Step
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.
Screenshot for Step
2 .
In the Analysis Editor, double-click the following columns:
Folder Columns
Products Products Hierarchy
Time Time Hierarchy
Base Facts 1 - Revenue
Screenshot for Step
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.
Screenshot for Step
Expand Time Hierarchy.
Screenshot for Step
4 .
a. Delete the Title view.
Screenshot for Step
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 Show Screenshot for Step
Screenshot for Step

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.
Screenshot for Step
6 .
The New Hierarchy Selection step dialog box appears.
Screenshot for Step
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.

Screenshot for Step
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.
Screenshot for Step
9 .
Click the pencil iconScreenshot for Step  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.
Screenshot for Step
Observe that FunPod is included this time.
Screenshot for Step
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.
Screenshot for Step
The Selection Steps view is displayed in the compound layout.
Screenshot for Step
Save the analysis.

12 .
Now, you will add a Group for Products Hierarchy.
In the Product – Product Hierarchy section, click “Then, New Step.”
Screenshot for 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.
Screenshot for Step
This new group is added to the Compound Layout view.
Screenshot for Step
14 .
Click My Group in the Selection Steps View and you will be able to see the values in My Group.
Screenshot for Step
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.
Screenshot for Step
2 . The Column Properties dialog box appears. Click the Interaction tabbed page.
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step
b. The Gauge editor appears. Click Edit gauge properties on the toolbar.
Screenshot for Step
5 . The Gauge Properties dialog box appears.
Screenshot for Step
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.
Screenshot for Step
7 . a. In the Pivot Table view (the master view), select AMERICAS to drill down.
Screenshot for Step
Screenshot for Step
Both the Pivot Table view and the Gauge view (the detail view) update to reflect the drill.
Screenshot for Step
b. Save your analysis.

No comments:

Post a Comment

Popular Posts