How to show budget for unsold item ?

[#1]

Hi every Body  ,
I need your help please , I have three dimension tables : Items  , Country and Budget

For all Item we have a budget by country.

My Problem is  :

I have 3 items (It1 , It2 and It3) and 2 countries ( France , US ).

Suppose for France , I sold only It1 and It3. in my table , I have :

Country	Item	Turnover	Budget
France	It1	35 €	30 €
France	It2	25 €	20 €

In my case I'll like to have this table :

Country	Item	Turnover	Budget
France	It1	35 €	30 €
France	It2	25 €	20 €
France	It3	0 €	20 €

Is it possible ? if yes how I can do  please ?

[sunny_talwar]

How is budget of 20 getting assigned for It3?

[#3]

in the Budget table  , for all iTem there is a assigned budget.

I want to see this budget even if the item is not sold.

[sunny_talwar]

I guess I don't know how your data is structured, would you be able to provide a sample?

[#5]

The ID_Budget is a composite key :

ID_Budget ==> ID_Country&'-'&ID_Item

[Colin-Albert]

How have you included the budget values into your data model?

The budget rows should be concatenated not joined to your table.

Use Concatenate with the same dimensions and a new column for BudgetAmount .

That way when you sum through the table by dimension, the Turnover totals and Budget totals will work.

You may need to add your Budget values against a dummy date of the 1st or last day of the month so your date dimensions work.

[Colin-Albert]

This post may help  Possible alternative to concatenate, or link tables?

[#8]

Hey Collin , the budget is in separate table.
In the budet Table , I create a composite key : ID_Budget = ID_Country&'-'&ID_Item.
I create the Same key in my Fat_Table , so My Bugets Table is automatically linked with my FactTables thank ID_Budget column.

[joydevdawn]

This post may help...

CountryMaster:

LOAD * INLINE [

    Country

    A

    B

];

ItemMaster:

LOAD * INLINE [

    Item

    It1

    It2

    It3

];

BUDGET:

LOAD * INLINE [

    Country, Item, Budget

    A, It1, 500

    A, It2, 300

    A, It3, 100

];

SalesFACT:

NoConcatenate

LOAD * INLINE [

    Country, Item, Value

    A, It1, 100

    A, It3, 50

];

Concatenate

LOAD * Resident BUDGET;

Drop table BUDGET;