Thursday, February 12, 2009

OBIEE "Selling" federated query's

One of the powerful features of OBIEE is the possibility of federated query's. Sadly in most sales pitches we tend to emphases the technical part instead of the practical part. Every now and then there is a BI-manager in the audience who really knows his stuff and starts asking the wright/wrong questions. In this article I want to show you some of the pitfalls when trying to "sell" federated query's.

The most common demo is we get all the data up to last night from our DWH and add today's data from the OLTP system.

Pitfall #1: Creating unstable reports.

Most managers tend to bring paper printouts of there reports to the meeting. This means that the printout for manager 1 at 09:10 will different from the printout for manager 2 at 09:15. This leads to useless discussing of the numbers on the report.

Pitfall #2: Wasted energy

A DWH generally contains data over a long period of time. For strategic or tactical discussions today's unstable data is not significant compared to the amount of historical data. A federated query would be a unnecessary claim of recourses of the OLTP system.

Pitfall #3: Uncleansed data

The ETL process which loads the DWH often cleanses the data from the OLTP system. (FI: Don't count invoices from department '999'). If these rules aren't covered in the RPD it will lead to the wrong numbers.

Does this mean you should use federated query's against an OLTP system at all? Of course not! Just think about the result your are trying to get. Try confining the federated query to a specific level. (FI: Use in only when a day to day comparison is made.)

Till Next Time


Stijn said...

Hi John,
agree with the pitfalls you described, but in my opinion these pitfalls are not related to federated queries but related to using real-time data for reporting.
So the question I have after reading this article: do you think that the "OLTP+OLAP demo" is the best business case for "selling" federated queries?


John Minkjan said...

Hi Stijn,

No I don't think the standard "OLTP+OLAP" demo is a good business case "selling" federated queries. There are far better demos to be made if you are willing to invest some time.
Yes you are right that these pitfalls are related to using real-time data for reporting. Sadly in many sales pitches federated query's are sold with the demo metioned in the article and therefore "mentally" connected with federated querys's.

Regards john

Uli said...

In my opinion federated queries are also a bad idea to report across multiple OLTP systems with medium to high data volumes.

From my experience this just works with the smallest of systems.

Have a look at Bill Inmon's rant about data federation or virtual data warehouses as he calls them. Actually one of the items both Kimball and Inmon agree on.