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

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Software // Information Management

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.)

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
1 of 3
Comment  | 
Print  | 
More Insights
Why It's Nice to Know What Can Go Wrong with AI
James M. Connolly, Editorial Director, InformationWeek and Network Computing,  11/11/2019
Top-Paying U.S. Cities for Data Scientists and Data Analysts
Cynthia Harvey, Freelance Journalist, InformationWeek,  11/5/2019
10 Strategic Technology Trends for 2020
Jessica Davis, Senior Editor, Enterprise Apps,  11/1/2019
White Papers
Register for InformationWeek Newsletters
Current Issue
Getting Started With Emerging Technologies
Looking to help your enterprise IT team ease the stress of putting new/emerging technologies such as AI, machine learning and IoT to work for their organizations? There are a few ways to get off on the right foot. In this report we share some expert advice on how to approach some of these seemingly daunting tech challenges.
Flash Poll