Fedrated DW pros and cons and what to watch out for

View previous topic View next topic Go down

Fedrated DW pros and cons and what to watch out for

Post  Ramtin on Wed Aug 01, 2012 7:27 pm

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.





Last edited by Ramtin on Thu Aug 02, 2012 1:03 pm; edited 2 times in total

Ramtin

Posts : 12
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Fedrated DW pros and cons and what to watch out for

Post  ngalemmo on Wed Aug 01, 2012 10:53 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum