Modeling forecast at a different grain the facts

View previous topic View next topic Go down

Modeling forecast at a different grain the facts

Post  smc on Mon Dec 21, 2009 8:36 pm

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

smc

Posts : 2
Join date : 2009-12-21

View user profile

Back to top Go down

Re: Modeling forecast at a different grain the facts

Post  ngalemmo on Tue Dec 22, 2009 12:36 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modeling forecast at a different grain the facts

Post  Jeff Smith on Tue Dec 22, 2009 12:54 pm

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

View user profile

Back to top Go down

Re: Modeling forecast at a different grain the facts

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum