In our previous postings, we looked at the new user interface for OBIEE 11g, and how the metadata layer for the BI Server has been enhanced to better handle OLAP-style data. In this posting, 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:
- 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)
The data source behind this model is relational (Oracle 11gR2), and now I want to start reporting on this using the new hierarchical columns feature in OBIEE 11gR1.
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.