0

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 : enter image description here

Complication dimension, every complication can have sub-complications : enter image description here

YOo Slim
  • 54
  • 1
  • 8
  • As per snowflake schema : You can have multiple dimension table in hierarchies. However, in your case , for DT_Month table - you are not required to keep 2 FK (for year and quarter). Only , quarter FK is sufficient based on access pattern schema you have designed. Also, I couldn't get your DT_Complication dimension. – Channa Jun 20 '20 at 16:37

1 Answers1

3

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.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • But then, what's the point on using a snowflake schema ?!, the model that you showed goes with a star schema doesn't it !, and i read that when we find an hierarchy inside a dimension, we can normalize it, which added to a fact table give us a snowflake schema ! Or am i just using the snowflake schema in the wrong way ? thank you. – YOo Slim Aug 26 '13 at 19:44