Budget / Goal vs Actual Universe Design

Hi

This is a common scenario. Basically you put your charge fact and its dimensions in one context and then your plan fact in another. Any shared (conformed) dimensions will be in both. Your joins are only from dims to facts and you dont need to join fact to fact.

When reporting and you select objects (measures) from both facts Web Intelligence will automatically generate 2 queries and then join the resulting data set in the report layer. This avoids your fan trap.

However you can still incorrect calculations in the report as the granularity of the 2 facts is different. For example you can create a query that brings back say daily totals from your charge fact and this, when joining to your query against the plan fact, will multiply up the rows in the plan fact so in the report any sum() formula will overstate plan.

User education is really the only way out of this, or only have month objects in your time dimension so that users can’t query to a finer grain.

Have a read of other articles here - search “Fan Trap” or “Context” and also check the stickies in the designer forum. See also What is a fan trap, why is it a problem, and how do I resolve it? and also the discussion here

Hope this makes sense

regards

AL


agulland :uk: (BOB member since 2004-03-17)