BusinessObjects Board

Budget / Goal vs Actual Universe Design

building semantic layer
#1

I have been implementing this design pattern in a couple of universes to enable my users to create reports including revenue plan and actual at different granularity levels without having to merge queries, but found a problem in it and want to see if I can get some suggestions on how to fix it.

For example, I have a “charge” fact that includes date, transaction id, service, net revenue, gross revenue, etc and another “plan” fact that includes month, service and revenue goal. I also have multiple levels of aggregation for my charge fact.

I create a many to one join between my “charge” fact (and different summary tables) and my “plan” fact based on month and service. Then, to solve the fan trap between these tables, I create an alias of plan and tie the plan measures to it. Contexts are setup as needed.

If my users drag and drop a plan measure, actual measure and the Plan Month (or only Plan dimensions), everything works perfectly. The issue is that, if they use actual measures, plan measures and an actual dimension (e.g., Charge Month, which is the same as the Plan Month), BO doesn’t know how to join the actual and the plan alias tables.

Should I just consider this an “acceptable” solution?
Should I not solve the fan trap between my plan an actual facts?

Thank you for your time and help!

riccarrasco :peru: (BOB member since 2010-03-10)

#2

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)