I am with Sean and venkyzrocks about providing more detail. That said, I think you are looking for this:

--SETUP SAMPLE DATA

------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#old_fact') IS NOT NULL

DROP TABLE #old_fact;

IF OBJECT_ID('tempdb..#Dim_FiscalYear_Old') IS NOT NULL

DROP TABLE #Dim_FiscalYear_Old;

IF OBJECT_ID('tempdb..#Dim_Scenario_Old') IS NOT NULL

DROP TABLE #Dim_Scenario_Old;

IF OBJECT_ID('tempdb..#Dim_FiscalYear_new') IS NOT NULL

DROP TABLE #Dim_FiscalYear_new;

IF OBJECT_ID('tempdb..#Dim_Scenario_new') IS NOT NULL

DROP TABLE #Dim_Scenario_new;

CREATE TABLE #old_fact (FiscalID int primary key, [SID] int NOT NULL, value decimal(5,2));

CREATE TABLE #Dim_FiscalYear_Old (id int primary key, FiscalName int NOT NULL);

CREATE TABLE #Dim_Scenario_Old (id int primary key, Name varchar(10) NOT NULL);

CREATE TABLE #Dim_FiscalYear_new (id int primary key, FiscalName int NOT NULL);

CREATE TABLE #Dim_Scenario_new (id int primary key, Name varchar(10) NOT NULL);

GO

INSERT INTO #old_fact

SELECT 1, 344, 78.44 UNION ALL SELECT 2, 345, 44.33 UNION ALL

SELECT 3, 346, 44.55 UNION ALL SELECT 4, 347, 223.44;

INSERT INTO #Dim_FiscalYear_Old

SELECT 1, 2008 UNION ALL SELECT 2, 2009 UNION ALL

SELECT 3, 2010 UNION ALL SELECT 4, 2011;

INSERT INTO #Dim_Scenario_Old

SELECT 344, 'Model1' UNION ALL SELECT 345, 'Model2' UNION ALL

SELECT 346, 'Model3' UNION ALL SELECT 347, 'Model4';

INSERT INTO #Dim_FiscalYear_new

SELECT 7, 2008 UNION ALL SELECT 8, 2009 UNION ALL

SELECT 9, 2010 UNION ALL SELECT 10, 2011;

INSERT INTO #Dim_Scenario_new

SELECT 546, 'Model1' UNION ALL SELECT 547, 'Model2' UNION ALL

SELECT 548, 'Model3' UNION ALL SELECT 549, 'Model4';

GO

------------------------------------------------------------------------------

--New Fact Table Values

------------------------------------------------------------------------------

WITH new_fact AS

(SELECTfo.FiscalID AS FiscalID_old,

fn.id AS FiscalID_new,

fo.SID AS [old_SID],

dn.id AS [new_SID],

d.FiscalName,

fo.value

FROM #old_fact fo

LEFT JOIN #Dim_FiscalYear_Old d ON fo.FiscalID=d.id

LEFT JOIN #Dim_FiscalYear_new fn ON d.FiscalName=fn.FiscalName

LEFT JOIN #Dim_Scenario_Old do ON do.id=fo.SID

LEFT JOIN #Dim_Scenario_new dn ON dn.Name=do.Name)

SELECTFiscalID_new AS FiscalID,

new_SID AS [SID],

value

FROM new_fact;

DROP TABLE #old_fact;

DROP TABLE #Dim_FiscalYear_Old;

DROP TABLE #Dim_Scenario_Old;

DROP TABLE #Dim_FiscalYear_new;

DROP TABLE #Dim_Scenario_new;