OracleBIBlog Search

Friday, December 4, 2009

GL Segments in the BI 7.9.6 GL Balance Sheet subject area

The OBIEE 7.9.6 GL Balance out-of-the-box subject area provides for segment reporting. A segment reporting hierarchy provides users with a drag-and-drop way to produce segment reports. Segments can be any subset of a dimension, but in our example let’s use the set of accounts in the Gross Margin report.

The value of managing the hierarchies for the segment is that multiple levels of the segment can be created, from most aggregated to most granular, and can be saved and exposed for drag-and-drop off the presentation layer in Answers and Office Add-Ons. For example, a user would be able to take Level3 of the Gross Margin Dimension, the Ending Amount from the Fact table, and get the Gross Margin report. Level4 can be just slightly different than Level3 for some reason specific to Gross Margin reporting, and that is perfectly fine since this hierarchy is dedicated specifically to Gross Margin reporting. The user should also be able to take Level 1, and then drill down to levels to see further breakouts of gross margin lines. Expense might break down to lower levels on the hierarchy like Indirect, Direct, and then further down at further levels to more granular groupings of accounts.

It is interesting to see that the GL Balance Sheet subject area has delivered 10 different account hierarchies. There is an assumption that there will be a need for many hierarchies for various segments in reporting. The hierarchies are labeled GL Segment1 through GLSegment10.

What's the value of hierarchy management?

Hierarchy management introduces the notion that hierarchies need neither be fixed, as is often seen in OLTP systems, nor unmanaged, as suggested by needs of ad-hoc end user reporting needs. In fact hierarchies are so important that they deserve the opportunity to have any number of iterative “what-if” scenarios to quickly come to the truth hidden behind their aggregates. In order to accomplish this, the hierarchy is abstracted away from both the operational and analytical systems (i.e. OLAP). Since most data distills into groups with lists of members, the art and science of manipulation of these taxonomies becomes its own managed system, improves analysis of business processes, and also enforces data quality. Hierarchy management has become more recognized as expectations have increased for consolidated reporting, service oriented architecture and data integrations. In these environments, not only do different account numbers in underlying systems need to be resolved, but there needs to be apples-to-apples reporting where accounts are segmented using hierarchies. Different roles in the organization need different hierarchies. According to Eric Kavanaugh of TDWI Research Institute:

“Hierarchy management serves as a translation layer between values in a data warehouse or other business intelligence environment, and the reporting or analysis view that business users see. By abstracting this translation layer, individual business users can manipulate variables within that layer without overwriting data in source systems or requiring hard-coded changes to those systems. This abstraction opens up an entire realm of analysis that can help organizations maintain competitive advantage in an increasingly complex and demanding marketplace” (The Magic of Abstraction: Hierarchy Management and Decision-Making

TDWI Research article, By Eric Kavanagh)

Implementation Implications

Exposing multiple hierarchies helps unlock the advantages of the managed hierarchies using OBIEE. The 11g release is going to have some improvements in the presentation of the levels on the hierarchies, but this does not solve the whole problem. The challenge I have found in implementation is twofold. If the hierarchy management is not effective, it is difficult to find effective definitions of levels for segments such as a Gross Margin segment. But in the past the bigger problem has come around the decision whether to expose a hierarchy at all in OBIEE. In the past it was difficult to convince an organization to expose a hierarchy called Gross Margin, since all the accounts were in the Account hierarchy already. The problem was very serious though. The Levels were not tailored specifically for Gross Margin segment reporting, which made it impossible to do easy drag-and-drop reporting. OBIEE reporting was not seen as a viable option compared to other tools. Now that BI Apps 7.9.6 comes with 10 segment hierarchies out of the box, it is more clear that multiple segment hierarchies are valuable to the organization, and many hierarchies need to be exposed.

Another important tool to help with hierarchies is the BICG product IMPACT(c), because the hierarchies need to be analyzed at many levels. BICG IMPACT(c) provides a great way to analyze which hierarchies are being used where, how often, and by whom. When integrated with the ETL source, the legacy source can be exposed for information purposes using the BICG IMPACT(c) subject area to create OBIEE iBots, answers and dashboards.