Wednesday, November 20, 2013

Groups vs Calculated Items in OBIEE 11g

Blog Index

OBIEE 11g features a new ability to create custom groupings of attribute column members, and there are two separate mechanisms that may be used: Groups and Calculated Items.  What is the difference between them?

   1.        Create this new analysis from the Sample Sales subject area.  The filter returns all values of P1 Product that contain the letter 'p'.

   2.        Modify the Table view to add a row of grand totals.  Notice that the formula for 20 Actual Unit Price is correctly recalculated on the grand total row at 8.926 per unit of quanitity sold.

Now we'll create the first new object, a Custom Group called Group P.

   3.        On the Results tab, click the New Group icon on the toolbar.

   4.        Use the same logic as shown above to select the same 5 values of P1 Product and click OK.

   5.        Notice that a new row labelled Group P has been added to the analysis, and the values on that row are correct.

Now let's create a Calculated Item

   6.        Click the New Calculated Item icon on the toolbar.

   7.        Use the same logic to make the same 5 values of P1 Product available in the left window.

   8.        One at a time, highlight a value in the left window, and shuttle it to the right window using the icon.  After shuttling a value, follow it with a + sign (except for the last one) so that the final formula looks like this:

   9.        Click OK to close the Calculated Item dialog.

10.        The results now show a new row for Item P, but not all of its values are correct.

So there we see the difference between Groups and Calculated Items.  Custom Groups respect and reevaluate the formulas for calculated columns such as Actual Unit Price.  In other words, the aggregation methods for the columns are retained.  But Calculated Items perform the exact calculation entered on the New Calculation Item screen, no matter what fact column is involved.  In this case, if you add the 5 values of Actual Unit Price, the total is 45.952.

Which begs the next question: Under what circumstances would you ever want to use a Calculated Item instead of a Custom Group?  I'm not able to think of a reason.

Each of these new objects may be saved for future use with other analyses.

11.        On the Results tab, expand the Selection Steps panel at the bottom of the screen.

12.        Click on the Group P link then select Save Group As.

13.        Save the group as Group P in the proper subject area folder.  Group P is now available for reuse in the Selection Steps panel for any other analysis.

14.        The same process applies for Item P as well.

No comments:

Post a Comment