Learning Pentaho Data Integration 8 CE - Third Edition

Loading  a cumulative fact table

In order to load a fact table, you have to look at the grain, or level, of detail. For example, looking at our model, you can see that the maximum level of detail is a city for the REGION dimension; the maximum level of detail is a day for the time dimension. This information is then used to aggregate the measures. For example, suppose that we want to load a sales fact table with the following definition:

CREATE TABLE FT_SALES (    date integer NOT NULL,        id_region integer NOT NULL,    id_puzzle integer NOT NULL,    quantity integer default 0 NOT NULL,    sales numeric(8,2) default 0 NOT NULL   );

In this sample fact table, we have foreign keys to three dimensions—TIME, REGION, and PUZZLES—and two measures—quantity representing ...