The last modeling issue is slightly different from the ones described thus
far since it does not address modeling a single dimension. The case is this:
suppose you have a fact table containing transaction level data and you want
to compare these actuals to a budget or forecast. Most organizations do not
create budgets on the individual customer and product level, but do this, for
instance, at the month and product group level, while omitting the customer
and other dimensions. So how do you accommodate for this difference in
granularity? One thing is very clear: you cannot compare a monthly value to
a daily one without first summarizing the daily values to the month level. So the first thing needed is a summarized fact table which can be done both in
a physical (extra table plus load process) or a virtual way (view). The latter
is easier to create but might be prohibitive due to performance issues. The
accompanying dimension tables need to be available at the same level as well,
either by creating a separate table or by creating a view. (Remember that when
you join a fact table at the month level with a dimension table at the day level,
the results are multiplied by the number of days in the month!)
Table below shows an example based on the WCM data warehouse that
contains both budget and actual data at a consolidated level of granularity.
This is why Ralph Kimball calls this consolidated fact tables.
The tables can be created directly from the existing tables in
the data warehouse. Also note that when data is available in a dimension table
at a higher level of granularity, it is kept in the derived dimension table as
well. This is the case for the quarter and year columns in the month dimension,
and the country information in the region dimension.
