Relational vs. Dimensional Databases, what's the difference?
- by grautur
I'm trying to learn about OLAP and data warehousing, and I'm confused about the difference between relational and dimensional modeling. Is dimensional modeling basically relational modeling, but allowing for redundant/un-normalized data?
For example, let's say I have historical sales data on (product, city, # sales). I understand that the following would be a relational point-of-view:
Product | City | # Sales
Apples, San Francisco, 400
Apples, Boston, 700
Apples, Seattle, 600
Oranges, San Francisco, 550
Oranges, Boston, 500
Oranges, Seattle, 600
While the following is a more dimensional point-of-view:
Product | San Francisco | Boston | Seattle
Apples, 400, 700, 600
Oranges, 550, 500, 600
But it seems like both points of view would nonetheless be implemented in an identical star schema:
Fact table: Product ID, Region ID, # Sales
Product dimension: Product ID, Product Name
City dimension: City ID, City Name
And it's not until you start adding some additional details to each dimension that the differences start popping up. For instance, if you wanted to track regions as well, a relational database would tend to have a separate region table, in order to keep everything normalized:
City dimension: City ID, City Name, Region ID
Region dimension: Region ID, Region Name, Region Manager, # Regional Stores
While a dimensional database would allow for denormalization to keep the region data inside the city dimension, in order to make it easier to slice the data:
City dimension: City ID, City Name, Region Name, Region Manager, # Regional Stores
Is this correct?
