Handling fact tables with different grain in Business Objects

Hi, i'm new to BI and Business Objects tools in particular. We have a fairly simple "Sales" datamodel - only 4 stars. the problem we're facing is the "SalesPlan" data. "SalesPlan" is defined on two different levels and more detailed level does not sum up to the higher level.

Dimensions:

DIM_Customer
CustomerAccountKEY
CustomerAccount
AccountGroup
Company


DIM_Part
PartNumberKEY
PartNumber
PartGroup
PartGlobalGroup

DIM_Time
TimeKey
Day
Week
Month
Quarter
Year

Facts:

FACT_Sales
TimeKey
PartNumberKey
CustomerAccountKey
SalesMetric

--this table shows high level plan for all AccountGroups (Customer hierarchy)
--and PartGlobalGroups (Part hierarchy) by Month for entire for all companies
FACT_PlanByAccountGroupByPartGlobalGroupByMonth
TimeKey (last day of the month)
AccountGroup
PartGlobalGroup
SalesPlanMetric

--this table shows more detailed plan for only some CustomerAccounts (Customer hierarchy)
--and some PartGroups (Part hierarchy) by Month for entire for all companies
FACT_PlanByCustomerAccountByPartGroupByMonth
TimeKey (last day of the month)
CustomerAccountKEY
PartGroup
SalesPlanMetric


Report requirements are simple: show Sales vs. Plan for all elements starting from Company/PartGlobalGroup and allow end user to drill up and down to analyse the data. and if Plan is higher than Sales - show an alert and allow user to drill into level of details where Plan may not exist but actual Sales figures help understand the alert.

it looks like Busines objects Aggregate Aware Universe can handle this type of the data problem, but the way Webi tool processes data does not help. Webi allows you to query on the high level, then drill down into details hitting the correct aggregate tables, but on the drill up it sums up all the data that got into the engine on the drill down action and displays incorrect results. there is an option to force report to query database every time user drills up or down but then the drilling function is limited to the next level up or down only rather than drilling by or through different levels.

the question is What would be the recommended way of handling scenarios where data exists on different grains and need to be displayed together where the same metric may not be present for all elements on lower (more detailed) levels.

Thanks in advance.

Assuming that customer plan only covers a fraction of all customers, you cannot arbitrarily bounce between customer based analysis and group based analysis. Treat them as separate queries.

i really appreciate your answer - thanks a lot. there was a concern that we were missing something very important on the datamodel level that won't let us deliver metrics to the end user in the required format. unfortunately, the idea of having several different reports (views) was not approved and we ended up using a set of aggregated tables and a "QueryDrill" option in Webi tool which forces the report to re-query database every time the drill level is changed.

thanks again
Galina