пятница, 19 сентября 2014 г.

Consolidating Multi-Grain Tables


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.