OBIEE Subject Area Planning (Part 2)

Originally posted 1/15/2007

In the previous blog entry, I discussed ideas about which Subject Areas you may want to have in your system. This follow up post will discuss some thoughts on what the insides of a Subject Area might look like.

Now that you’ve identified which areas you are going to have, lets talk about how to organize them. As before, there is no one right way of doing things, but I’ll cover some ideas to consider that might work for your system.

I think there are a few things you should strive to achieve – directional goals when doing your design in other words. As such, it doesn’t mean that you will achieve it, but it may help you make a better decision when doing your design. The primary goal of a Subject Area is to allow a user in Answers to easily construct a report. To achieve this, your Subject Areas must be organized into an easily usable format for your target audience.

  • Not too big: Try not to let your tables get too large. Employ the capability of table folders to organize a long list of fields into meaningful subsets. Perhaps for a large Customer table you might have a subfolder for Geographical fields and another for Demographic fields.
  • Not too small: Frequently this means abstracting away from what your Business Model looks like by combining several smaller logical tables into one larger one that to a user is the same thing, but due to how the BM was built you had to alter that view. An example of this would be the need to have several small dimension tables that are on a similar topic, but had to be broken up to make the RPD work correctly. Perhaps these could all be merged into one Presentation table to make things easy to use.
  • Alphabetical vs. Most Commonly Used: Withing a table, will you order your fields alphabetically or put the most commonly used ones at the top? Perhaps subfolders can be used to remove all of the lesser used fields into subfolders while leaving the most commonly used fields in the main folder.
  • Poor Use of Subfolders: I have seen some deployments where a single table holds all facts, but has several subfolders for each grouping of fact tables. This is a waste of a layer (you only have 2!) and eliminates any further organizational capabilities such as the ones discussed in this post. I’ve also seen this done with Dates or Geography or People. Instead, use the proper name of the table, in the case of dimensions it would be the role of the dimension. For example, don’t do a Dates folder with subfolders for Open Date and Close Date – instead just have two top level folders for Open Date and Close Date.
  • Identify the Land Mines: Although it would be great if it were true, there are nearly always some special fields that don’t work well within the model. They may require special usage and only work in certain cases, and will blow up a report if used incorrectly or at all. If you are unable to separate them out from the subject area completely, then one trick I’ve used is to create a special folder or subfolder to store the Land Mines in. I also make sure to clearly identify the folder with a warning label, such as ‘for internal use only – do not use’.
  • Maintainability: All of these things will mean more work for the development team. Try to find a balance between usability and maintenance effort. However, in the majority of circumstances usability should win out.
  • Consistency: Be as consistent as possible with your Subject Area layouts. If you have decided to put all Metrics at the bottom of the Catalog, then always do so.

Finally, when you have completed your Subject Area layout and filled it up with fields, part of your deployment to any ad-hoc user community should include Subject Area Documentation. This is effectively a road map to the area – how it works, where it comes from, what works with what, which tables or fields require special usage and any Land Mines to be aware of.

There is a process for determining what these areas look like, and it begins during the requirements gathering process. If done in a good, top-down fashion, a highly organized and usable Subject Area will come relatively easily. Over the years I’ve developed techniques and processes to ensure that there is a tight relationship between the requirements we gather, our supporting designs, and the resultant Subject Areas.

Advertisements

Posted on July 16, 2009, in BI Theory and Best Practices, OBI EE Development, Presentation Catalog and tagged , , , . Bookmark the permalink. 2 Comments.

  1. Hi Jeff,

    I just stumbled upon your blog today. I have really enjoyed many of your posts and agree with a lot of what you have to say. We have had many discussions within our organization about RPD design. I think we are at a cross roads as far as how to design the rpd for enterprise use. Is it common practice to have one large business model, then seperate it out in the presentation layer. Also, with that approach does every fact table then use the same physical dimension on the database. For instance does every fact table use the same physical day dimension. How does this affect loading of the data and loading of aggregates? I would greatly appreciate any help you can give me.

    Thanks,

    Pat

    • Making things look integrated (or dis-integrated in the case of multiple Subject areas) in the BI layer is not really your goal – its to integrate your data in the data model. So yes, have 1 day table that everyone can use. But as I mention in several posts – don’t force it if your organization is not ready. If you honestly can cross-group coordinate on the definition of a comment dimension or metric, then don’t force it. I’ve errors in the past (long long time ago of course 🙂 trying to force customers to make a single version of everything, but they simply couldn’t handle it – they had multiple independent teams – getting them on the same page was not something anyone had planned on or budgeted for. The sophistication of your Data Warehouse and support organizations (Data Stewardship, etc) drive how integrated you can be in a BI tool. Perhaps you can hide some layer of dis-integration, but you’ll only be able to do a little bit of it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: