Warehouse as source for another Warehouse

View previous topic View next topic Go down

Warehouse as source for another Warehouse

Post  pbackstrom on Mon Aug 20, 2012 11:54 am

We will be needing both fact and dimension information for a new subject area in our warehouse. The data we need exists in a different warehouse in the same company (star schema, but a different platform, different tool suite etc.).

The issue is whether we should source from the other warehouse, or build our own ETL from the original source. I'm wondering if there's some academic, theoretical, or common practice favoring one side or the other. We can always just decide based on what we think is best...

Some say we should pull from the system of record because a) it's the system of record and b) we don't want to be subject to changes in the other warehous. Others say we should pull from the other warehouse because a) the business rules and transformations have already been applied, why do all that extra work, and b) if we had different transformations, we'd have to worry about reconciliation and make changes in two places.

Is there a "correct" answer in theory or practice? I'm inclined to pull from the other warehouse, we can get cooperation and notification on changes there. But someone will say the only legitimate way is to pull from the source. Thanks for any direction.

(Yes we should have one warehouse, with different marts using all conformed dimensions that get loaded once from a common source. Not happening, at least anywhere near the time we need to get this new subject area going).

pbackstrom

Posts : 3
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  vickyejain on Mon Aug 20, 2012 1:53 pm

If the other data warehouse can be treated as the 'source of truth' for the processed data you are looking for - I would strongly suggest pulling from it. Applying the same logic at 2 different places can result in significant effort to reconcile data and reduce business confidence if ever the numbers are different, requiring reconciliation regularly.

vickyejain

Posts : 7
Join date : 2012-08-20

View user profile

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  LAndrews on Mon Aug 20, 2012 1:55 pm

If the other source already exists, it already is in star-schema, and it already correctly applies business rules and transformations ..... Why do you need to do anything?


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Thanks

Post  pbackstrom on Mon Aug 20, 2012 3:10 pm

Thanks vickyejain, That's what I was looking for.

pbackstrom

Posts : 3
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  BoxesAndLines on Tue Aug 21, 2012 8:51 am

You already know the correct answer in theory, "Yes we should have one warehouse, with different marts using all conformed dimensions that get loaded once from a common source...". In practice, I would look to load from the intermediate staging tables instead of the final fact and dimension tables. This will allow your ETL processing to kick off earlier and reduce your dependencies on upstream processing. It will also ideally reduce the amount of data you need to process on a daily basis. Don't be surprised when the political infighting starts when the numbers from each of the "replicated" fact tables do not match.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  John Simon on Sun Aug 26, 2012 6:23 am

I work in a Telco where we love data warehousing (we have at least 6 large scale datawarehouses, plus many more smaller reporting systems).
The principle we work on is "Always go directly to the source". If the other data warehouse is decommissioned or falls over - you're screwed. Do you know that the other system's numbers are even correct?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  pbackstrom on Mon Aug 27, 2012 2:49 pm

Thanks. Yes, your points are part of our issue too.
Right now it looks like we'll be retaining control of the "source" warehouse in our same department, so hopefully if it ever gets shut down by the business, we'll have time to code to it. I think, too, that it means the data is about as correct as it would be if we went to the source as second time.

pbackstrom

Posts : 3
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Warehouse as source for another Warehouse

Post  Sponsored content


Sponsored content


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