Fedrated DW pros and cons and what to watch out for

[Ramtin]

The company I am working for have decided that they will use existing 3 single subject area data stores they have which were modelled as stand alone data stores (3NF) and continue to do so with new stand alone data stores and fedrate them using Oracle OBIEE semantic layer to create a fedrated data warehouse. I must say Alarm bells are making my nees shake and ear drums burst.
Since I have no exprience OBIEE Semantic layer I am here to ask for other professionals opinions
The way I see it to successfully implement the semantic layer you will need conformed dimensions in first place or or changing data structures and surrogate keys such that you can construct conformed structures.
If you are going to go backwards in your design from a conformed semantic layer then why not make it physical and reuse it.
Failing to do that you will end up with data silos. Given that upto 2/3 of effort of any form of data warehouse is in ETL it sounds like this will cost more and at the end will fail to realise the full benefit.
I would like to hear form others:
- Are my fears unfounded? Am I completely off base in what Fedrated DW is
- is there sensable design and delivery approach that will realise what I would expect from a Enterprise data Warehouse with efficiencies that we normally get from an EDW implementation using Kimball?
- Are there any advantages to this methodolgy? Because I see it as a lot of rework if conformed dimensions are not used.

Thanks for all your comments in advance.

[ngalemmo]

Depends on what you mean.

If the 'separate' data stores are physically on the same database server but different schema, you should not have any performance issues beyond what you would expect performing large analytic queries against a normalized model. If these stores are on different servers, you at risk of extremely long running queries if the federation layer needs to move large result sets to resolve joins across the servers.

I can't comment on OBIEE's semantic layer. But, all a semantic layer boils down to is a bunch of views with documentation. It would seem to me their semantic layer is probably fine. If not, you can always create views as a last resort.

Other than physical location, the other issue of concern is how well do the three existing subject area integrate and if there is redundant data between them. The redundant data issue is not about space, but about integrity. If these subject areas are updated independently, there is nothing to prevent common attributes to diverge (the value in one subject area is different than the same attribute in another subject area).

It is certainly possible to create a dimensional semantic layer over a 3NF data store. However you cannot take advantage of performance enhancements Oracle provides for dimensional models. Their star-join optimization does a good job of handing queries against dimensional schema and would provide very significant performance improvement over dimensional-like queries against a normalized model.