In my last blog, I discussed the importance of the RPD and its place within the data warehouse organization. I also discussed the idea of planning adequate time and devoting the proper technical resources to RPD development. In this blog installment, I would like to carry that idea forward and talk about the benefits of proper RPD planning and development.
You have made a major investment in OBIEE as an enterprise business intelligence solution. Shouldn’t it be easy to plug in the data and get real value out of the tool? Hasn’t Oracle thought of all that? Well, yes and no.
Yes, Oracle does recognize the fact that you are essentially starting with an empty shell of an RPD. But, unfortunately, you have to purchase more products called BI Applications to get an out-of-box metadata repository. Even then there is configuration work to “fit” the canned solution to your data.
So where does this leave the rest of us who don’t have any additional budget to spend for the year? You can still build a very good quality RPD without having to spend a great deal of up-front time and resources. The steps to build an RPD are similar to those you would use to build a data mart or data warehouse. Here are some initial steps I recommend if you were just starting out and have a very limited IT support staff:
1) Taking stock of your data. Do you have a well-defined data warehouse? Perhaps you only have the beginnings of a data warehouse; some kind of an operational data store. Perhaps all you have is a transactional database. Wherever you are in terms of data, you will still be able to create an initial RPD.
2) Start with the facts and work backwards. Determine the initial facts you would like to report on. Identify all the tables they reside in. A fact is essentially anything that can be aggregated mathematically.
3) Find the dimensional data. Determine the minimal descriptive data you will need to create meaningful reports in combination with the facts.
4) What makes a unique data record? You must be able to answer this question so that you can configure the RPD to aggregate data properly. Is it a particular key column or a combination of columns?
5) Understand the level of aggregation. Is your report viewed and analyzed by month, by year, or by department? Will the underlying tables support that level of aggregation easily, or are there too many records? Perhaps you can start to think about creating specific tables to house facts that are already pre-computed at the month, year, or department level.
6) Don’t be afraid to test. Create some test queries in Toad or Oracle SQL Developer and observe the performance. Do you need to create indexes or partitions?
These are just a few things to consider, and anyone in your business intelligence department will be able to answer those questions and make suggestions to help optimize the data for querying. As I always say, it is well worth the investment to work directly with ETL developers and data modelers within your organization and get them training in OBIEE.
The ultimate goal is speed, accuracy, and value. OBIEE is an analytical tool; this is where you will build that report that will take the place of the Excel spreadsheet you give to your boss. You know the one I’m talking about, with all the rolled up numbers, charts, graphs, etc. Your boss is running that report in OBIEE, how long do you want them to wait for data to return? I say no longer than 20 seconds. That may sound outrageous, but again, think of the type of data we are talking about here – highly aggregated, drill-to-detail reports. OBIEE is a waste of money if you are not going to break the cycle of using your IT systems to dump into Microsoft Excel. I’ve seen this over and over again in the course of my experience in business intelligence.
All the time spent preparing your data and building your RPD should be focused on performance.
Accuracy of data is a given, so you must work with your business partners to unit test and sign off on the numbers these reports are producing. Accuracy is by far the easiest part of the job; performance tuning is the real challenge. It’s not all technology though. Again, it’s setting the expectations of what kind of reports you will be creating. Operational reports are always going to be there. If you absolutely must have a 100-column operational report, then please make sure that it is heavily filtered so that it returns a targeted result.
If you’re still unsure, stare at a blank screen waiting for important results to return for a minute, two minutes, or even three. Try it. Now, envision the level of frustration users will have.
If you would like to receive more information how Performance Architects could help you implement OBIEE in your organization, please send us a note at firstname.lastname@example.org or comment below.
Author: John McGale, Performance Architects