we’ll move to the front-end and see how dimensional support has been
enhanced in Oracle Answers 11g, in particular through a new feature
called Hierarchical Columns.
If you followed the development process for OBIEE 11gR1 and in particular, the new features planned for Oracle Answers, you’ll probably be aware of enhancements to how Answers handles OLAP-style hierarchies, and how pivot table views in Answers have been updated to more suit the type of analysis performed by Essbase users. In a previous posting in this series I looked at how the underlying metadata in OBIEE 11gR1 has been extended to now encompass ragged, skip-level and parent-child hierarchies, and in this posting I’ll look at how the 11g release of Answers (now part of the 11g Unified Framework) can make use of these through hierarchical columns and general enhancements to pivot table views.
In this example, I have a logical model called Store Sales – Sales that has four logical dimensions. This model has then been used to create a corresponding presentation model, that exposes these dimensions as hierarchical columns.
The details of these dimensions, and hierarchical columns, are as follows:
I start by logging in to the OBIEE 11gR1 unified framework and create a new analysis. I select this presentation model, and then take a look at what’s available to me in the Criteria panel.
Starting with the Sales logical fact table and the Stores logical
dimension table, I can see the one measure (Amount), the columns in the
Stores table (now called Attribute Columns in 11gR1), and the dimension
plus its levels just below the attribute columns. This dimension is
referred to in 11gR1 as a Hierarchical Column, which gives us three
types of column in this release (measure columns, attribute columns and
hierarchical columns).
I start off by adding the Region Name attribute column to my criteria, along with the measure. I switch to the Results panel and see that the output looks very similar to what I’d have seen in OBIEE 10g. This is to preserve backwards compatibility for products such as the BI Applications, which will still be able to show reports and dashboards in the same way as if you’d been using OBIEE 10g.
When I select an attribute column in my criteria and then switch to the Results panel, the data is automatically shown in a table view, as it would have been in OBIEE 10g.
I now create a new analysis, but this time select the Stores (Ragged & Skip-Level) hierarchical column, along with the measure. Starting with the All Stores grand total level, I click on the + signs next to each member, and start drilling into the hierarchy. Notice how the SFO concession doesn’t have a store as a parent, and instead rolls up into the North CA region (this is the skip-level). Notice also how the Fisherman’s Wharf store has two concessions under it, whereas the other stores don’t (this is the ragged element of the hierarchy, where leaf members aren’t necessarily at the same hierarchy level). Finally, note how the All Stores grand total level is calculated based on the dimension members that are its descendants.
I can do a similar thing with the Staff (Parent-Child) hierarchy. As
the name suggests, this is a parent-child, or value-based, hierarchy,
and again I can display it in the Results panel and
look at the numbers. Note however that with parent-child hierarchies,
the values for each member aren’t calculated based on its descendants,
and instead they just display the total associated with that member
(i.e. there is no automatic roll-up of numbers).
Now this difference in behaviour between parent-child backed
hierarchical columns, and level-based backed hierarchical columns, is
interesting as there is no way to tell from the Answers side what type
of hierarchy you are working with. I wonder if this will change in
future releases?
You can include more than one hierarchical column in a pivot table, and indeed you can mix and match attribute columns and hierarchical columns in the same view. In the example below, I have the Staff (Parent-Child) hierarchical column down the left-hand side, which is then broken down by the Quarter attribute column from the Times (Time Dimension) logical table, with the Products (Level-Based) hierarchical column along the top.
You can also nest hierarchical columns within each other, such as in
the analysis below where I’ve nested Times (Time Dimension) within the
Staff (Parent-Child) hierarchical column.
So far, so good. As I mentioned before, pivot tables themselves have
had a revamp in this release, with one of the key features being the
ability to swap dimensions about when the pivot table is displayed in
the dashboard (in 10g, you had to return to Answers to rearrange the
layout). In the example below, I’m moving the Times (Time Dimension)
hierarchical column so that it’s under the Product (Level-Based)
hierarchical column, by grabbing the grab-bar above the Times (Time
Dimension) hierarchical column and then dragging it to the new
position.
You can also sort the pivot table by clicking the up and down arrows
that appear over columns or along rows, or you can right-click anywhere
in the pivot table and access a contextual menu from there.
Another feature in this new release is the ability to create dynamic
groups (often referred to as custom aggregates); for example, to create a
custom aggregate made up of Alison Chisel, Cassandra Barry and Pete
sims (all of which are at different levels in the hierarchy), I press
the New Group button in the Results panel menu, and then select these members of staff for the group.
After pressing OK to create the group, I can see
them in the hierarchical column, and I can also drill into the group too
(the old Calculated Item option is still there as well, for backward
compatibility).
Hierarchical columns also bring another bonus, in the form of being
able to access alternate hierarchies in a dimension. In the Times
logical table I’ve expanded in the view below, I have two hierarchical
columns, one for the calendar hierarchy and one for the fiscal
hierarchy. In the past, I could only drill-down on one of these but now
you can see both hierarchies listed in the Criteria
view, and I can select which one I want to drill-down on (not quite as
good as Discoverer, which let me display both on the report at the same
time, but it’s getting there).
There’s one other major change with the introduction of hierarchical
columns. For attribute columns, you can still filter in the same way,
picking the column and then setting up the filter (Product Name = ‘Shoes’, or Amount < 100,
for example). With hierarchical columns though you can set up
step-by-step filters, which will seem familiar to anyone who used Oracle
BI Beans, or Discoverer for OLAP, in the past. In this example, I’m
displaying the Amount measure by the Stores (Ragged & Skip-Level)
hierarchical column, and I’ve opened up the Selection Steps
window at the bottom, which allows me to define step-by-step
restrictions and additions to the set of stores that are displayed in
the pivot table.
I want to change this to start with a list of stores, then restrict
this list to just stores that have sold over 100, but then add back in
any stores that are in the top 10% based on sales.
This clearly owes a iot to Oracle Express and Oracle OLAP, where you
would progressively restrict, expand and redefine the “status” for a
dimension, and the dialog owes a lot to a similar one in Discoverer for
OLAP. From an initial look, it doesn’t look as easy to create complex,
multi-dimensional conditions such as “give me the list of stores whose
sales are in the top 5% of stores based on last year’s sales across just
the food and beverage product lines”, but this is something I’ll need
to play around with a lot in a future posting. For now though, we’ve got
the traditional ability to apply sorts against columns (attribute
columns in OBIEE 11gR1-speak), or we can use these selection steps for
when we’re working with hierarchical columns. This feature will be
especially useful for parent-child hierarchies (including ones with
Essbase as a data source) that don’t have levels (aka columns) to filter
against, instead having members that are arranged into an arbitrary
ragged hierarchy.
In the next posting in this series, we’ll be taking this one step
further by looking at how Essbase support has been improved in the 11g
release of OBIEE. In the meantime, if you’re interested in seeing more
of OBIEE 11g in action, BIWA SIG (the Business Intelligence, Warehousing
and Analytics SIG) are running a webcast on Wednesday on Data Visualization Best Practices using OBIEE 11g, run by our friends Dan and Tim Vlamis. Attendance is free and it’ll be a good chance to see a demo of 11g.
If you followed the development process for OBIEE 11gR1 and in particular, the new features planned for Oracle Answers, you’ll probably be aware of enhancements to how Answers handles OLAP-style hierarchies, and how pivot table views in Answers have been updated to more suit the type of analysis performed by Essbase users. In a previous posting in this series I looked at how the underlying metadata in OBIEE 11gR1 has been extended to now encompass ragged, skip-level and parent-child hierarchies, and in this posting I’ll look at how the 11g release of Answers (now part of the 11g Unified Framework) can make use of these through hierarchical columns and general enhancements to pivot table views.
In this example, I have a logical model called Store Sales – Sales that has four logical dimensions. This model has then been used to create a corresponding presentation model, that exposes these dimensions as hierarchical columns.
- Products (Level-Based) dimension has a single regular, balanced, level-based hierarchy (as per the 10g release)
- Staff (Parent-Child) has a parent-child hierarchy, with an automatically generated closure table behind it
- Stores (Ragged & Skip-Level) has a single skip-level and ragged level-based hierarchy, and
- Times (Time Dimension) has two balanced, level-based time hierarchies (one for calendar, one for fiscal)
I start by logging in to the OBIEE 11gR1 unified framework and create a new analysis. I select this presentation model, and then take a look at what’s available to me in the Criteria panel.
I start off by adding the Region Name attribute column to my criteria, along with the measure. I switch to the Results panel and see that the output looks very similar to what I’d have seen in OBIEE 10g. This is to preserve backwards compatibility for products such as the BI Applications, which will still be able to show reports and dashboards in the same way as if you’d been using OBIEE 10g.
I now create a new analysis, but this time select the Stores (Ragged & Skip-Level) hierarchical column, along with the measure. Starting with the All Stores grand total level, I click on the + signs next to each member, and start drilling into the hierarchy. Notice how the SFO concession doesn’t have a store as a parent, and instead rolls up into the North CA region (this is the skip-level). Notice also how the Fisherman’s Wharf store has two concessions under it, whereas the other stores don’t (this is the ragged element of the hierarchy, where leaf members aren’t necessarily at the same hierarchy level). Finally, note how the All Stores grand total level is calculated based on the dimension members that are its descendants.
You can include more than one hierarchical column in a pivot table, and indeed you can mix and match attribute columns and hierarchical columns in the same view. In the example below, I have the Staff (Parent-Child) hierarchical column down the left-hand side, which is then broken down by the Quarter attribute column from the Times (Time Dimension) logical table, with the Products (Level-Based) hierarchical column along the top.
No comments:
Post a Comment