How to model Facts with Conformed Dims on different grain level in BO XI universe

Hi, I have got two facts that share one dimension on different levels. I searched different forums, but could not find an answer so far.

Sample:
Conformed Dim:
D_DATE on daily grain with (unique) DAY_ID and (multiple) MONTH_ID

Facts:
F_SALES_ACTUAL with granularity DAY_ID
F_SALES_FORECAST with granularity MONTH_ID

Joins:
J_D_DATE_F_SALES_ACTUAL: F_SALES_ACTUAL.DAY_ID = D_DATE.DAY_ID
J_D_DATE_F_SALES_FORECAST: F_SALES_FORECAST.MONTH_ID = D_DATE.MONTH_ID

Contexts:
C_SALES_ACTUAL: J_D_DATE_F_SALES_ACTUAL
C_SALES_FORECAST: J_D_DATE_F_SALES_FORECAST

Report:
Month, Sales_Qty_Actual, Sales_Qty_Forecast

Queries:

Code:

Q1:
Select D_DATE.MONTH
    , sum(F_SALES_ACTUAL.SALES_QTY_ACTUAL)
  from F_SALES_ACTUAL
    , D_DATE
 where D_DATE.DAY_ID = F_SALES_ACTUAL.DAY_ID
 group by D_DATE.MONTH

Q2 (as-is):
Select D_DATE.MONTH
    , sum(F_SALES_FORECAST.SALES_QTY_FORECAST)
  from F_SALES_FORECAST
    , D_DATE
 where D_DATE.MONTH_ID = F_SALES_FORECAST.MONTH_ID
 group by D_DATE.MONTH

Q2 (to-be):
Select D_DATE_MONTH.MONTH
    , sum(F_SALES_FORECAST.SALES_QTY_FORECAST)
  from F_SALES_FORECAST
    , ( select distinct MONTH_ID
              , MONTH
          From D_DATE 
      ) D_DATE_MONTH
 where D_DATE_MONTH.MONTH_ID = F_SALES_FORECAST.MONTH_ID
 group by D_DATE_MONTH.MONTH

Question:
How do I get correct results for forecasted sales on monthly base? Currently I do get duplicate values since each MONTH_ID exists several times in D_DATE – one value for each day of the month.

In Cognos you use the construct of DETERMINANTS in order to specify, that certain dimension attributes are unique for certain level keys. Is there something similar in BO?

Your feedback is welcome.

Thx Stefan

Hi stefan77dd

I asked a similar question on the forum a little while ago (but not related to Business Objects). The predominant approach seems to conform the dimensions. In your example, you'd split your D_DATE dimension into two dimensions:

D_DATE_MONTH (MONTH_ID, YEAR_ID)
D_DATE_DAY (DAY_ID, MONTH_ID, YEAR_ID)

D_DATE_MONTH conforms to D_DATE_DAY because its fields are an exact subset of it. Your F_SALES_ACTUAL fact will join to the D_DATE_DAY dimension, while the F_SALES_FORECAST fact will join to D_DATE_MONTH.

Check out The Kimball Group Reader book. Figure 6-5 on page 202 shows an example of this (sale vs. forecast facts).

I'm not sure what you'd need to do to get this working in BO, but it's the approach I'm taking with SQL Server and Analysis Services.