Hello,

I have a sales cube with DimProduct, DimEmployee, DimClient, DimTime, DimGeography and FactSales. Now I want to add budget to let users compare actual (FactSales) versus Budget (FactBudget).

I have some rules that result in granularity mismatch between FactSales and FactBudget.

• FactSales has a date granularity with DimTime
• FactBudget has a month granularity with DimTime
• FactSales has a one to one relationship with DimEmployee
• FactBudget.Employee1 = DimEmployee1 + DimEmployee2. I have a sumarized DimEmployee list for FactBudget, some employees are composed of several members of DimEmployee, this is my big problem. I built a DimEmployeeBudget but don't know if this is the correct way.
• FactSales has a one product ganularity with DimProduct
• FactBudget has a category granularity with DimProduct (I think this is not a big problem as with DimTime)

This is the model I was trying without success:

In dimension usage on SSDT I tried the following relationships

• From FactBudget.month to DimTime.month with a regular relationship
• From FactBudget.category to DimProduct.category with a regular relationship
• From FactBudget.id_emp_bud to DimEmployee.id_emp with a referenced relationship using IntermediateTable as intermediate.

In BI applications I'd like to create reports like this.

BudgetEmployee	Actual	Budget
Employee1	100	200
Employee2	300	100

I'd like to read your recommendations, I am not a expert in DWH designing so I am looking for tips for adding budget fact.

Thanks for considering my question.

• Edited by Alejandro_Zuleta Thursday, June 18, 2015 4:16 PM table

Hi Alejandro_Zuleta,

According to your description, you have problems on creating models. Right?

In your scenario, I am not clear about "FactBudget.Employee1 = DimEmployee1 + DimEmployee2". In my opinion, since both sales and budget are for specific employees, you should join build the relationship between DimEmployee and FactBudget directly. If you need to have some cumulative calculation, you can set specific aggregation calculation within [Measures].[Budget] SCOPE.

Regards,