Snowflake schema dimension

Question

This is the first time i'm working on a BI project and Pentaho products are not yet familiar to me, so i needed to know if the following models are correct and that i won't face difficulties later when creating hierarchies on the BI Server !

Thank you.

Time dimension :

Complication dimension, every complication can have sub-complications :

Answer 1

Not a good idea.

Your calendar dimension table should look like this:

create table calendar (
  calendar_id int primary key,
  name text not null unique,
  date_iso date unique,
  year smallint,
  quarter smallint,
  month smallint,
  month_name text
  ...
);

insert into calendar values 
(0, 'N/A', null, null, null, null, null),
(20130826, 'Aug 26, 2013', '2013-08-26', 2013, 3, 8, 'August');

The point of a data warehouse is to ease analysis. Making your BI analyst do three joins to get a date does not ease analysis.

calendar_id is a "smart key", i.e. not a meaningless surrogate key. Your calendar table is the only table that should use a smart key, as it greatly aids table partitioning by date. Also note the nullable fields, which allows for the "N/A" (Not Available) date. There's no year 0, so 0 is a good "N/A" value.

Basically, you should have one level of normalization: your fact tables, and your dimension tables.