Hub-And-Spoke: Building an EDW with SQL Server and Strategies of Implementation
SQL Server Technical Article
Writers: Mark Theissen, Eric Kraemer
Published: February 2009
To date, the implementation of a true hub-and-spoke architecture for a data warehouse environment has been an idealized and elusive goal. Although building a centralized “hub,” or enterprise data warehouse (EDW) that supports company-wide detail data is achievable, building and maintaining “spokes,” or dependent departmental data marts has proved to be the challenge. Most data warehouse environments have evolved to one of two architectures: a centralized EDW or a series of distributed and/or federated data marts.
In response to a few crucial problems with federated approaches to data warehousing, vendors and pundits alike began to promote a centralized, monolithic EDW approach. This approach has historically helped some vendors differentiate themselves from competitors and justify unreasonably high Total Cost of Ownership (TCO) for their products.
Centralization is seldom scalable or easy to manage. Conflicting end-user and management-use cases, demanding performance requirements, increasing user sophistication, and on-going demands for scalability and flexibility all work to defeat even the most efficiently managed monolithic system. In many cases, these issues cause departments and business units to create their own independent data marts.
Abstract courtesy : Microsoft
Reference : Pinal Dave (https://blog.sqlauthority.com)
You have brought to light an important issue with Data warehouse designing. I guess it could also be viewed as the debate between Bill Inmon(Top Down) and Ralph kimball approaches (Bottom Up) approaches. Designing an enterprise wide datawarehouse and then flushing it down to departments(Data marts) is a very big challenge, it takes huge amount of resources (money and people) to achieve it. These kind of initiatives seem to be undertaken at big organisations where they have this kind of leverage.
Very good article. Thanks for sharing. Kindly keep it up.
Nice article. Currently i’ve a discussion with an Inmon guy. I’m a Kimball follower at this moment. I think it’s true that the inmon EDW cost more effort to set up than the kimball approach but there are arguments that changes in the Kimball architecture is difficult.