Modeling forecast at a different grain the facts
3 posters
Page 1 of 1
Modeling forecast at a different grain the facts
First time forum poster, I've attended KimballU a few years back.
Kimball obviously teaches to maintain true transaction grain in the fact. Budget and forecast often do not involve all dimensions (vendor, customer, or order type for example). In your opinion, what is the cost of short-cutting and storing a "N/A" member for the uninvolved dimensions instead of creating a 2nd fact table with conformed dimensions and drill across? I've got a client asking (insisting?) for this approach and I need to defend the fundamental approach. More specifically, we've already got "Customer N/A" in our customer dimension for customers that are not found to keep from losing transactional data in ETL, client wants budget and forecast also loaded to this customer for example.
Thoughts? It works in a sandbox environment but I would like to keep the architecture fundamentally sound.
Thanks
SMC
Kimball obviously teaches to maintain true transaction grain in the fact. Budget and forecast often do not involve all dimensions (vendor, customer, or order type for example). In your opinion, what is the cost of short-cutting and storing a "N/A" member for the uninvolved dimensions instead of creating a 2nd fact table with conformed dimensions and drill across? I've got a client asking (insisting?) for this approach and I need to defend the fundamental approach. More specifically, we've already got "Customer N/A" in our customer dimension for customers that are not found to keep from losing transactional data in ETL, client wants budget and forecast also loaded to this customer for example.
Thoughts? It works in a sandbox environment but I would like to keep the architecture fundamentally sound.
Thanks
SMC
smc- Posts : 2
Join date : 2009-12-21
Re: Modeling forecast at a different grain the facts
Basically, an atomic fact table, that is a fact table at the lowest business level (and highest grain) represent a specific business event or state. Sales would be one such event. Budget and forecasts, which may be related to sales are different states. Budget is the result of a planning process within the business while forecasts are a different planning process (which may drive budgeting) partially derived from historical sales facts. The facts in both of these are independent of sales, and each other. They should be in separate fact tables.
You can draw associations between sales, forecast and budget by combining these facts across conforming dimensions. This can be done on-the-fly with most BI tools, or if this is a frequent mode of analysis, you can construct an aggregate fact table to improve performance. But, to be clear, this aggregation can only be where conforming dimensions exists. For example, if you forecast by customer but do not budget by customer, an aggregation of forecast and budget cannot include customer as it would not make sense for the budget numbers.
You can draw associations between sales, forecast and budget by combining these facts across conforming dimensions. This can be done on-the-fly with most BI tools, or if this is a frequent mode of analysis, you can construct an aggregate fact table to improve performance. But, to be clear, this aggregation can only be where conforming dimensions exists. For example, if you forecast by customer but do not budget by customer, an aggregation of forecast and budget cannot include customer as it would not make sense for the budget numbers.
Re: Modeling forecast at a different grain the facts
Couldn't the Forecast and Budget figures in the fact table be considered degenerate dimensions? Some reporting tools allow fields to be set up so that they cannot be aggregated. Technically, since they are different grains, they cannot be treated as facts or measures. Of course, in such a table, the data could only be aggregated up to the level of the budget and forecast figures, at which point the degenerate dimensions would become facts.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» Identify the facts and facts grain
» Facts with different grain
» Facts with different grain from different sources but related
» Facts with same grain: some shared and some different measures
» Beginner Modeling Question - Determining the right grain
» Facts with different grain
» Facts with different grain from different sources but related
» Facts with same grain: some shared and some different measures
» Beginner Modeling Question - Determining the right grain
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum