The Single Most Important Thing to Know About the OBI RPD
Over my OBI career I’ve had to review dozens of customer and consultant developed RPDs as part of either a formalized health check engagement or simply a developer code review. Frequently the customer has some problems that they can’t solve, or that they have solved with a work-around of some sorts. In some cases this is simply due to not fully understanding the concepts and what you can do with those concepts, but sometimes there is something far worse at play: bad advice from supposed experts.
Twice this year I have come across customers who have been told by other “experts” that Dimensional Hierarchies aren’t that important or they should leave the Content tab empty. One of these so called experts was even an actual Oracle employee! This is disastrous advice as I will demonstrate in this post.
Luckily following a few simple practices will ensure you are in good shape and don’t end up painting yourself in a corner. Along the way you will begin to understand how the BI Server actually “thinks”. Once you are able to learn how to predict what it will do with 100% certainty, you can control it to do what you want it to do and diagnose the solutions when it doesn’t.
Dimensional Hierarchies in OBIEE are used for a variety of important features in the RPD, many of which you may not know about. This post will explore the fundamental reason for their existence and some specialized features that require their existence. But mostly we’ll discuss the single most important thing to know about the RPD.
Hierarchies – Not just for Drill Down
We all know that users drill down along dimensional hierarchies, but what does the OBI BI Server use them for? When I ask this question, I tend to get a lot of silence on the end of the phone during interviews. It is important to understand why we need Dimensional Hierarchies in the first place. My goal here is to put to bed anyone who says they are not needed or optional. I’ll leave the most important reason for last as it segues into a larger discussion.
1. Level Based Metrics
A Level Based Metric (LBM) is a regular metric that has been pinned to a fixed level in a hierarchy. The simplest example of this is a Monthly value, such as Monthly Revenue $. In this scenario, the regular Revenue $ metric is free to operate at whatever levels are needed by the query, whether it is the basic grain of the report, a subtotal or grand total, free to operate with drilldowns and drill acrosses.
The Monthly version however is forcing the metric to be pinned to the Month level of the Date hierarchy. In effect, it is forcing a GROUP BY MONTH in all cases. When you have Day level on the report along with Revenue $ and Monthly Revenue $, you can expect the Monthly Revenue value to be repeated for all days in each month. Behind the scenes this will be either 2 queries or one query with an AGGREGATE function in it.
This forcing of GROUP BY MONTH occurs in the flip scenario as well – when the grain on the report is higher than that of the LBM, it will still issue that GROUP BY MONTH regardless of the grain of your report, resulting in breakouts and rows you might not have expected:
2. Level Based Metrics for Non Conformed Dimensions (NCDs)
This topic is one of the more common questions I see on the discussion forums and with customers. The way OBI works makes it more difficult than it needs to be to make a NCD work properly with other facts. However, the solution to making these NCDs work involves setting the content tab for each metric to the Totals level. Effectively, you are saying that the metric is at the All or Total level of that dimension, which also means the None level. This part gets conceptually tricky, but to simplify it just think: when it’s at the Total level for a dimension, any filter or reference to that dimension for this metric will be ignored. Thus, a group by or a filter on a NCD when used with a metric at the Totals level will be ignored completely.
Suppose you wanted to see Customer, # SRs and Order $ amounts on a single report, but you wanted to filter down the SRs based on SR status of ‘Open’. Since SR Status dimension does not work with Order amount facts, OBI will not be able to answer the question: “Order $ by Customer and SR Status”. Setting the Order $ Metric to the totals level for the SR Status dimension will force OBI to ignore that relationship completely, thereby converting it into: “Order $ by Customer” UNION/STITCH JOIN “# SRs by Customer where SR Status = ‘Open’”
This topic alone might serve well to have its own post. However, I have written this up in a presentation here, on pages 10-16
Note: Setting the totals level on the Content tab of the LTS is meaningless and is identical to leaving it blank. I have seen some developers be incorrect about this.
3. Time Series
Moving on to a more straightforward example, you cannot use the time series functions without establishing a Time Hierarchy with appropriate levels.
4. Nested Aggregation
Nested aggregation is where you can have a 2+ step aggregation sequence. For example, you may need a Sum(Last(metric)) if you are computing total month end headcount using daily snapshot tables. However, you need to be a bit more descriptive when you tell OBI the sequence – you need to tell it what dimensions you want to apply Last() to and what to apply Sum() to – otherwise it has no idea. Thus, your actual nested aggregation rule should be more like: Sum(Last(metric) by Date.Month)). For more details, please check out a more comprehensive article here.
The Content Tab
Before moving on to the next few items, I need to introduce you to the Content Tab on each Logical Table Source (LTS). This tab is without any question the most important portion of OBI RPD modeling. Unfortunately it is also the one that is most complicated to master. As a result it is also the one that is the most incorrect and results in more RPD errors and problems than anything else. In my experience it has been and still is the #1 cause of broken repositories.
What is so important about the content tab? The top portion is where the grain of each LTS is fully described to OBI. OBI uses this information when picking LTSs, both dimensions and facts. It is what is used to identify what LTS is a higher source (or aggregate source) vs. another. The joins to other LTSs (across dim & facts) are the skeleton of the OBI RPD and is fundamental to how it operates and the SQL it generates. It is how it knows that ‘SR Status’ can be used with the SR Facts LTS but not with the Orders Facts LTS. It is how it knows that a Monthly table is better than a daily table, and when it can use the monthly source and when it cannot.
Without the content tab properly filled out, the rules of how OBI selects a LTS become a bit muddled. Frankly I cannot tell you how it does what it does without them; the only ones who probably can are some of the original nQuire engineers that taught me the product many years ago. I have also avoided even bothering with it by always completely and properly filling it out as the first step in diagnosing problems – get the foundation solid first then worry about the remaining problems.
Without the content tab properly filled out, many of the other features of OBI do not work properly. I came across a perfect example of this just last week. The customer had followed the approach above on using the totals level to deal with Non-Conformed Dimensions. It was working fine when they had 2 fact tables and 2 NCDs. But when they added a 3rd fact and 3rd NCD, it stopped working completely. The reason? The Content tabs were not filled in at all. Thus, OBI did not have enough information on how to solve the problem. Setting up the content tabs on the facts and dimensional LTS solved the problem perfectly on the first try.
Let me reiterate how important Content Tabs are: When I do a RPD review, the first thing I look at are the Content tabs. If they are missing or incomplete, all bets are off; I cannot solve any problems until they are properly and completely filled out. Once that is done, it is quite common for the problem to be corrected without further effort, but if not at least the BI Server has the right information and we have the language and understanding to work with the RPD to do what it should be doing.
For those who still don’t believe me, take a look at Oracle’s own BI Apps RPD – all of the content tabs are properly filled out. The engineers thought it was important to do so – so should you.
Proper use of content tabs allows us to do a few more very important functions:
5. Grain Leveling
This is where you want to mix two fact table grains on the same report. For example, you’d like to show a monthly report with actuals from the Day table and Budgets from the month table. As OBI knows that both link into the Date dimension, it will issue the appropriate query for each source. In this example, it knows how to roll up Day to Month and then equalize it with Monthly budgets.
6. Aggregate Navigation
Aggregate Navigation is a fundamental capability of the product. I’d in fact say Aggregate Navigation is a subset of just general LTS Selection – the process by which OBI selects a LTS to use. The content tab tells OBI that a LTS at the Month level is a better source than an LTS at the Day level. It tells us that we cannot use the LTS without the Product Dimension when we have Product on the report – OBI has to choose a different one if available.
7. Vertical Federation
Vertical Federation conceptually is very simple: put your aggregate table in a different database. That’s all it is. Thus, you can have an aggregate at the Month level built from a Daily table. You can elect to put that aggregate in the same physical database as the Daily table, or you could throw it in an Essbase cube or a TimesTen database as Exalytics does. It doesn’t matter to OBI that much; it still has to write a query and get the result sets. Where the data lies doesn’t matter at all to selecting the right LTS.
Doing it Right
So, how about something practical to follow to make sure you are set up correctly from the beginning? Follow these three simple rules to ensure that you don’t paint yourself in a corner and become stuck. On smaller systems it may seem like overkill, but the point here is to a) get into the habit of doing it correctly from the beginning, b) make sure that the stuff you build works, c) be prepared for when your RPD grows much larger – pay a dollar now or 10 later.
If you haven’t been following them, I strongly urge you to stop everything and spend development and regression test cycles to properly and fully set up your content tabs. You may find that it is a bit painful to add these in, as you will start to get a lot of RPD warnings on Consistency Check, but fight through them and clean them all up. You may start to notice that some of your problems disappear.
- For every Logical Dimension table, always make a Hierarchy, even if it is just Totals & Details. This only takes a little bit of effort, and sets you up for all of the items discussed in this article.
- Always fully and completely fill in the Content tab for every single LTS, both Fact and Dimension. The two will ultimately need to point to each other. If they do not, you’ll get a RPD warning. Do this all of the time, no matter if you have only 3 tables in your RPD. Doing so enables the more powerful features of OBI, but also makes just about anything work more properly in more scenarios.
- Don’t lie to OBI about grains or Hierarchy structure. If you miss one or add an extra, if you assign the wrong grain, OBI will make you pay for it with a very difficult to diagnose problem. If a relationship between two fields is M:M, do not model them as a 1:M parent:child – OBI will bite you at some point. Go through the effort to give OBI the right information to make the right decision.
The alternative of course is to have RPD problems and take much longer to solve new ones and get frustrated with the product in general. In my years of working with OBI, the RPD is very reliable – it generates the right SQL. If it is not generating it correctly, it is nearly always (99.99%) the architect’s fault for not telling OBI the right details – either through omission our incorrectly.
Posted on September 10, 2013, in BI Theory and Best Practices, OBI EE Development, Repository and tagged OBIEE, dimensions, Hierarchy, Hierachies, Content Tab, Levels, Level Based Metrics, Non Conformed Dimensions. Bookmark the permalink. 8 Comments.