declare @loaddate int
set @loaddate = convert(int,convert(varchar(8),getdate(),112))


------------------------------fact starts here------------------------------


--INSERT INTO #fact_sales
--(DW_FACT_KEY, DW_KPI_KEY, DW_Branch_KEY, DW_Employee_KEY, DW_Department_KEY, DW_Division_KEY, DW_group_KEY,DW_Unit_KEY, DW_Product_KEY, DW_Time_key,DW_Bank_KEY, DW_Load_Date, Actual, Budget)
select 
	--isnull(fact.DW_FACT_KEY,0) DW_FACT_KEY
	isnull(a.DW_KPI_KEY,-1) DW_KPI_KEY
	,isnull(b.DW_BRANCH_KEY,-1) DW_BRANCH_KEY
	,isnull(c.DW_EMPLOYEE_KEY,-1) DW_EMPLOYEE_KEY
	,isnull(d.DW_DEPARTMENT_KEY,-1) DW_DEPARTMENT_KEY
	,isnull(e.DW_DIVISION_KEY,-1) DW_DIVISION_KEY
	,isnull(f.DW_GROUP_KEY,-1) DW_GROUP_KEY
	,isnull(g.DW_UNIT_KEY,-1) DW_UNIT_KEY
	,isnull(h.DW_PRODUCT_KEY,-1) DW_PRODUCT_KEY
	,isnull(i.period,-1) DW_Time_key
	,'9999' DW_Bank_KEY
	,@loaddate DW_Load_Date
	,k.Actual 
	,k.Budget
--INTO #fact
from
(
select 
 cte.Periodkey
, cte.kpi_Key
, cte.branch_Key
, cte.employee_Key
, cte.product_Key
, cte.division_Key
, cte.group_Key
, cte.unit_Key
, cte.dept_Key
,sum(isnull(actual,0.0)) Actual
,sum(isnull(budget,0.0)) Budget

from 
(
select
x.Periodkey
, x.kpi_Key
, x.branch_Key
, x.employee_Key
, x.product_Key
, x.division_Key
, x.group_Key
, x.unit_Key
, x.dept_Key
, actual = case when x.Scenario = 1 then 
				[value]
			else
				null
			end
, budget = case when x.Scenario = 2 then 
				[value]
			else
				null
			end

FROM
(
SELECT
 Periodkey
, kpi_Key
, branch_Key
, employee_Key
, product_Key
, division_Key
, group_Key
, unit_Key
, dept_Key
, Scenario
, [value]
FROM
dbo.STG_FACT_ASO
)x
)cte
	
group by
 cte.Periodkey
, cte.kpi_Key
, cte.branch_Key
, cte.employee_Key
, cte.product_Key
, cte.division_Key
, cte.group_Key
, cte.unit_Key
, cte.dept_Key
)k
left join KPI_EDW.dbo.DIM_KPI a on k.kpi_Key = a.label
left join KPI_EDW.dbo.DIM_Branches b on k.branch_Key = b.Label
left join KPI_EDW.dbo.DIM_Employee c on k.employee_Key = c.Label
left join KPI_EDW.dbo.DIM_DEPARTMENT d on k.dept_Key = d.Label
left join KPI_EDW.dbo.DIM_DIVISION e on k.division_Key = e.Label
left join KPI_EDW.dbo.DIM_GROUP f on k.group_Key = f.Label
left join KPI_EDW.dbo.DIM_UNIT g on k.unit_Key = g.Label
left join KPI_EDW.dbo.DIM_Product h on k.product_Key = h.Label
left join KPI_EDW.dbo.dim_time i on k.Periodkey = i.period
left join KPI_EDW.dbo.Fact_ASO_SCORECARD fact on i.period = fact.DW_Load_Date