How to model Facts with Conformed Dims on different grain level in BO XI universe
2 posters
Page 1 of 1
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:
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
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
stefan77dd- Posts : 1
Join date : 2012-11-05
Re: How to model Facts with Conformed Dims on different grain level in BO XI universe
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.
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.
min.emerg- Posts : 39
Join date : 2011-02-25
![-](https://2img.net/i/empty.gif)
» How best to model Timesheet facts against Sales Order facts
» Distinct count at different level than the grain
» Identify the facts and facts grain
» The grain level
» Dimensional Model Vs Views as Structure for BOBJ Universe
» Distinct count at different level than the grain
» Identify the facts and facts grain
» The grain level
» Dimensional Model Vs Views as Structure for BOBJ Universe
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|