CODEWITHSUNDEEP
data-warehouse
skyline01
skyline01

Reputation: 2101

where to put dimensions in Kimball data warehouse

I am creating a data warehouse following Kimball's theory. So, I am aiming for the greatest amount of dimension table re-use. In this theory, how should I physically organize the dimension tables? I have used databases to organize my data marts (i.e., 1 mart per database, with possibly multiple fact tables per mart). Since a given dimension can be used by multiple marts (and is what I want to aim for), I don't know where I should put my dimension tables.

For example, should I put them all under a certain schema in a certain database (e.g., schema 'Dimension' under database 'Dimensions')? Or, perhaps, should I incrementally add them to each new database as I build out new data marts?

Upvotes: 0

Views: 185

Answers (2)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Traditional data warehouses often use separate databases to create application boundaries based on either workload, domain or security.

As an example, a traditional SQL Server data warehouse might include a staging database, a data warehouse database, and some data mart databases. In this topology, each database operates as a workload and security boundary in the architecture.

You can create a schema for example for HR datamart and load all the related dimensions under it.

CREATE SCHEMA [HR]; --   name for the data mart HR
CREATE TABLE [HR].[DimEmployee] -- create dimensions related to data mart HR in the HR schema
(       EmployeeSK BIGINT NOT NULL
,       ...
);

Upvotes: 1

NickW
NickW

Reputation: 9798

A datamart is a logical subset of your data warehouse, not a physical one. Your data warehouse should (under most circumstances) reside in a single database

Upvotes: 1

Related Questions