Hubs, Spokes and Buses: How to Get to a Better Data Warehouse - InformationWeek
Software // Information Management
09:56 AM

Hubs, Spokes and Buses: How to Get to a Better Data Warehouse

Are you frustrated by the inefficiency, rigidity and latency of a first-generation, hub-and-spoke-architecture data warehouse? Here's a six-step guide to evolving to a streamlined, robust Kimball Dimensional Bus Architecture that will reduce time to reporting, lower data latency, and deliver more detailed, analytically useful information.

Now that data warehousing has become ubiquitous in the corporate world, "clean slate" designs of entirely new decision support systems are becoming somewhat rare. Instead, designs and roadmaps are more often intended to improve, modernize or otherwise evolve existing production data warehouses that may be showing signs of age.

caption goes here
Hub-and-Spoke Architecture
(click image for larger view)
Many first-generation data warehouses have a hub-and-spoke architecture, an approach that presents aggregated and departmentally-aligned information to end users in dimensional form. These "departmental data marts" can be either centralized or distributed, but a foundational tenet of this approach is that they must be sourced from a centralized and normalized repository — often referred to as a Third Normal Form Data Warehouse (3NF DW), as depicted in the diagram at right — that contains the most detailed information available. In this architecture, Sales, Inventory and Payment information might appear in several departmental data marts, aggregated as needed by each department and reflecting each department's preferences for metric calculations or dimensional attribution.

caption goes here
Kimball Bus Architecture
(click image for larger view)
The Kimball Dimensional Bus Architecture, in contrast, makes the most detailed data directly available to end users (also in dimensional form) but in a business-process-aligned (rather than departmentally aligned) manner. Thus, Sales, Inventory and Payment information is stored only once — each in a single Business Process Dimensional Model, as pictured at right — rather than appearing in various incarnations within several hub-and-spoke departmental data marts. Dimensions are conformed in an organization-wide manner, tied to atomically-grained and single-business-process facts, allowing users to creatively sum the data any which way, using any combination of the attributes from the (conformed) dimensions. Special departmental needs are handled by adding department-specific attributes to dimensions, or by creating department-specific measures in the facts, which retains the benefits of a unified cross-department perspective.

Notice that the hub-and-spoke architecture's normalized data warehouse (3NF DW) and its associated ETL are completely eliminated, simplifying and streamlining the solution. (A more thorough discussion of these two data warehousing approaches can be found in the article "Differences of Opinion," by Margy Ross and Ralph Kimball.)

1 of 3
Comment  | 
Print  | 
More Insights
Newest First  |  Oldest First  |  Threaded View
How Enterprises Are Attacking the IT Security Enterprise
How Enterprises Are Attacking the IT Security Enterprise
To learn more about what organizations are doing to tackle attacks and threats we surveyed a group of 300 IT and infosec professionals to find out what their biggest IT security challenges are and what they're doing to defend against today's threats. Download the report to see what they're saying.
Register for InformationWeek Newsletters
White Papers
Current Issue
IT Strategies to Conquer the Cloud
Chances are your organization is adopting cloud computing in one way or another -- or in multiple ways. Understanding the skills you need and how cloud affects IT operations and networking will help you adapt.
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
Join us for a roundup of the top stories on for the week of November 6, 2016. We'll be talking with the editors and correspondents who brought you the top stories of the week to get the "story behind the story."
Sponsored Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.
Flash Poll