Incremental Essbase Metadata Imports Now Possible with OBIEE 11g

Mark Rittman -

If you were an early adopter of Essbase integration with OBIEE, the 10g release had a number of restrictions that made it hard to use in production. Chief amongst these was the lack of any ability to refresh the physical model of the Essbase database within the Oracle BI Repository, such that if you added a new dimension, or just a new UDA or generation to the Essbase outline, you more or less had to delete the existing model, re-import it from Essbase and re-work any integration you'd performed with any other data sources.

These two blog posts by Christian Berg back in 2010, based on OBIEE 10.1.3.4.1 and Essbase 9.3.1, describe the problem well and unless you were prepared to do some unsupported hacking with UDML and Notepad, working with an evolving Essbase outline was pretty much impossible with OBIEE 10g, and when 11g came out many customers re-defined their Essbase-derived physical model hierarchies as value-based to get around this problem.

But value-based hierarchies have their own issues, such as not being able to make use of UDAs or alias tables, or as outlined in this previous posting, not being suitable for vertical federation (drill-to-detail) with relational and file sources. So, it was with interest that I noticed a new section in the Essbase metadata modeling section of the OBIEE 11g documentation (11.1.1.3 and 11.1.1.5) entitled "About Incremental Import", that says:

"You can choose to incrementally import Essbase metadata. In other words, you can perform an initial import, and then import again. You might want to import incrementally when information in the data source has changed, or when your first import only included a subset of the metadata. Note the following about incremental import:

  • When you re-import metadata that already exists in the Physical layer, a message appears, warning you that your Physical objects will be overwritten.
  • If you delete data in the source, re-importing the metadata does not automatically perform the deletion in the Physical layer. Instead, you must manually delete the corresponding Physical objects.
  • If you rename an object in the source, the renamed object is imported as a new object. In this case, both the old object and the new (renamed) object are displayed in the Physical layer.
  • In general, customizations that you have performed on the Physical layer data, such as determining the alias column to use for display, are retained after an incremental import. If you want to revert to the default imported view, you must delete the existing Physical layer objects and then re-import the metadata."

So, how does this work, and how easy is it to implement? With this question in mind, I thought I'd give it a try.

To start off, I've imported a copy of the Sample.Basic Essbase database into the Physical layer of an Oracle BI Repository. Running this import creates a physical database named after the Essbase server (essdemo), a physical catalog named after the Essbase application (Sample), and a cube table named after the Essbase database (Basic). I performed the import using OBIEE 11.1.1.5 with all the import options left at their default value.

As you can see from the screenshot below, the Market physical dimension has three UDAs associated with it; Major Market, Small Market and New Market.

So, as my first test, what if I use Essbase Administration Services to add a new UDA value to the West Member called Development Market, a UDA value that's new to the outline and therefor wouldn't have been imported into the OBIEE repository in the past?

I save the updated outline and then go and re-run the Import Metadata wizard in the Oracle BI Administration tool. Selecting the same Essbase database brings up a warning asking me to confirm I want to re-import.

After running the re-import, I check the Market dimension, and yes, it contains the new UDA value.

But what if what the Administration tool has done, is just overwrite the old cube table definition with the new one? This could cause me problems if I'd fine-tuned the physical model previously by, for example, setting the hierarchy type for the Market dimension to Fully Balanced, or I'd taken the Measure hierarchy and flattened it into a flat list of measures.

So let's perform these customizations on the physical model, by firstly setting the Market hierarchy type to Fully Balanced (I would choose Value, but you can't access UDAs if you set your hierarchy to Value):

I then right-click on the cube table and select the Convert measure dimension to flat measures option.

So at this point, I'm looking to preserve the change I've made to the hierarchy type for the Market dimension, and also the new set of flattened measures in the cube table. Note that converting the measures to flat measures still leaves the Measure dimension in the cube table, and I would generally delete this from the business model after dragging and dropping the physical model into the Business Model and Mapping layer.

 

After saving the changed physical model, I then go back into Essbase Administration Services and add another UDA to one of the Market dimension members.

I then use the Import Metadata wizard in the Oracle BI Administration tool to bring in the updated outline. Checking the resulting updated cube table, the new UDA is there as expected.

The flattened measures are still there, but unfortunately, the Hierarchy Type for the Market hierarchy has been set back to Unbalanced.

So now, even thought I know that I can't make use of UDAs when I set the Market hierarchy type to Value, I'll set it to this anyway, and then go and add another UDA to the dimension, to see if it does preserve the Value hierarchy type setting after a re-import. I set the Hierarchy Type to Value:

I then add another UDA to the outline, re-import it using the Import Metadata wizard, and interestingly find that the Value hierarchy-type setting has been preserved. If I then switch the Hierarchy Type back to Unbalanced, Fully Balanced or any other level-based hierarchy type, I can then also access the new UDA that was added to the outline.

So the rule here seems to be: if you've added UDAs to your outline, and you re-import the outline using OBIEE 11.1.1.5 (I've also checked this for 11.1.1.3, it's the same behaviour), existing changes to the outline are preserved, except Hierarchy Type settings where all level-based hierarchy types are reset back to Unbalanced. Any changes you've made to flatten the measure hierarchy, for example, are preserved OK.

So what about aliases? If we add a new alias table to the Essbase outline, and use it to add a new alias value to, for example, the East member, what happens?

At the moment, if I view the list of Alias tables imported into the cube table definition, I can see the standard Member_Name, Default and Long Names aliases.

Now again, as with UDAs, you can't access alias tables when you set your Essbase-derived physical hierarchy type to Value, so I set it back to Fully Balanced and then import the updated outline.

As with UDAs, the new alias table has been brought in with the re-import, the hierarchy type has been reset back to Unbalanced, but other that that, the rest of the cube table definition seems unchanged.

So what about something a bit more drastic? What if one of the hierarchies in your Essbase outline suddenly grew an extra generation (which would correspond to a new hierarchy level and set of cube columns in the OBIEE physical layer), which in 10g would mean you were pretty-much out-of-luck. To test this out, first I add a new generation to the Scenario hierarchy, adding Initial Budget and Revised Budget members as children to the existing Budget member. I save the outline and restructure the cube, in preparation for importing into the OBIEE repository.

At the moment, the Scenario dimension and hierarchy within the cube table has two levels, corresponding to the two generations in the Essbase hierarchy.

I've also created a corresponding business mode, and subject area, off of this cube table, so that I can see how easy it'll be to incorporate the additional levels into already-derived business models and subject areas.

So, here we go. I use the Import Metadata wizard to re-import the database definition into the physical layer, and check out the updated physical model.

And there we have it. The new child members in the Scenario dimension hierarchy have caused a new level to be created in the physical model, something that (unless i'm much mistaken) wasn't possible in OBIEE 10g. And this is quite a big deal - for many of our customers, not being able to refresh their physical model off of a changed outline was a showstopper, and it's been the major driver towards adoption of value-based hierarchies in OBIEE 11g. The fact that this is now possible is actually quite big news, and I'm surprised the dev team within Oracle responsible for OBIEE/Essbase integration hasn't publicised this - I only came across it when trawling through the online docs whilst writing the Essbase chapter of the OBIEE 11g book.

So how easy it is to incorporate these changes into  your business model, which remember is automatically generated for you when you drag and drop your cube table into the business model and mapping layer of the Oracle BI Repository. To find out, I start by dragging three of the four cube columns that are contained within the new Gen3, Scenario physical level across to the correspondng business model logical table, like this:

Then I add this additional key column to the logical table key, like this:

Next, I add a new child level to the existing Scenario logical dimension, and add these three logical columns to the level.

Now I add the logical level keys, copying how they are set up for the other levels.

Then, to finish off the business model changes, I set the detail (content) level for the logical dimension and logical fact table sources, to reflect the additional level that's in the Scenario dimension.

Finally, I update the subject area in the physical model, deleting the old presentation hierarchy within the Scenario presentation table and replacing it with the updated one from the business model and mapping layer.

Now I can verify the updated repository, and then create a sample analysis. First off, I create an analysis that just includes the updated Scenario dimension, to check that the new measure comes through as expected.

You can see the two new scenarios listed under the Budget member. Now I add a measure into the analysis, which should lead to these two new members being removed, as no data has been recorded against these scenarios.

Very good. The hierarchical column indicates that there are members under the Budget member, but if you try and click on the "expand" icon, nothing happens.

So we've shown that we can import new generations into the repository, and then feed these through to the business model and presentation layers, so that users can make use of the new members at this new level. Interestingly, if you then remove these two new members so that the hierarchy goes back to the original number of generations, and then re-import the outline back into the repository, the Administration tool removes the additional generation from the physical model, so you're back to two generations again (contrary to what the manual says, actually). You've then got to go back and adjust your business model and subject area to reflect the new number of generations, but at least it's possible to delete generations this way, as well as add them.

But what about something bigger - say, adding a dimension to an Essbase database? Given what we've seen so far, it's likely that the incremental import from the outline will pick up the new dimension, but is it then possible to model it in the business model and subject area?

To test this out, I create another copy of the Sample.Basic Essbase database, delete the Population attribute dimension, and then import it into the Oracle BI Repository using the BI Administration tool. For good measure, I change the Market dimension hierarchy type to Fully Balanced, and convert the measure dimension to a flat list of measures.

I also create a corresponding business model and subject area for the database.

Then, I restore the missing attribute dimension to the source Essbase database (by dropping it, and then re-copying it from Sample.Basic using Essbase Administration Services), and then use the Administration tool to re-import the outline again. How does it work out?

Well, as you can see, the Population attribute dimension has been imported incrementally, and the measure dimension is kept as a flat list. Interestingly, the Market dimension's hierarchy has been reset back to Unbalanced, which indicates that the re-import process sets all the level-based hierarchies back to Unbalanced regardless of whether the hierarchy has any other changes. So how easy is it to introduce this new attribute dimension to the business model?

Dragging and dropping the new attribute dimension from the Physical into the Business Model and Mapping layer adds two items to the business model; a logical table for the cube columns, and a logical dimension, for the physical hierarchy and physical levels. Notice how the logical table has the "fact' indicator in it's icon? To remove this, I create a logical join between this new logical table and the fact logical table, like this:

I also set the content level for the fact table logical table source, to reference the detail-level from the new Population logical dimension hierarchy.

Finally, I copy the new logical table over to the Presentation layer of the repository and add it to the existing subject area, along with it's logical dimension. Now, to try it out in an analysis.

So there you go. It all appears to work fine. So to me, it looks like the limitation that we had with OBIEE 10g, where we couldn't incrementally refresh a physical model in the repository based off of an Essbase database, is no longer the case. There's a couple of gotchas - level-based hierarchies, even for those that haven't been touched - get reset back to Unbalanced from any other level-based hierarchy setting, and the actual process of amending the business model and subject area are a bit tricky (at one point, I kept hitting a drilling error with the new scenario members and generation, but after a couple of restarts and re-imports it just started working).

But if your concern about working with OBIEE and Essbase is not being able to handle outline changes, and value-based hierarchies have other restrictions that you can't otherwise work with, I'd say this is worth taking a look at. This was certainly an eye-opener for me, and I can't help wondering why Oracle haven't made more noise about this, apart from burying the news hidden away in the documentation. Strange, but good news nonetheless.