April 18, 2013 at 7:39 am
Hi Guys,
Need a more efficient way to do this :
Old Fact Table
FiscalID SID Value
1 344 78.44
2 345 44.33
3 346 44.55
4 347 223.44
Now this join to Dimensions on ID's
Dim_FiscalYear_Old
ID FiscalName
1 2008
2 2009
3 2010
4 2011
Dim_Scenario_Old
ID Name
344 Model1
345 Model2
346 Model3
347 Model4
Now basically the fact table stores ID's from DIM tables, so I want to retain old values (Names will remain same but ID's have changed in the Dimension Tables)
Dim_FiscalYear_New
ID FiscalName
7 2008
8 2009
9 2010
10 2011
Dim_Scenario_New
ID Name
546 Model1
547 Model2
548 Model3
549 Model4
Old Fact Table Updated or New Fact Table
FiscalID SID Value
7 546 78.44
8 547 44.33
9 548 44.55
10 549 223.44
Note : ID's are not auto increment by 1
April 18, 2013 at 8:18 am
You haven't posted anywhere near enough details to offer much help here. I don't even know what the question is. I don't know that in this case you need to post all the ddl but you certainly need to post the details of the question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2013 at 9:55 am
The question is the last part . The fact table needs to updated with the news ids.
Please see the last table. That is the result form I need it in.
April 18, 2013 at 10:07 am
venkyzrocks (4/18/2013)
The question is the last part . The fact table needs to updated with the news ids.Please see the last table. That is the result form I need it in.
Can you post ddl and sample for these tables along with what you are trying to do? I am certain we can do this fairly easily but I just can't wrap my head around the details of it with nothing to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2013 at 10:14 am
venkyzrocks,
I agree with Sean Lange. More clarity and some ground to get others started would be nice to have.
Having said that, I have created table variables to simulate your scenario. Please check to see if this meets your requirement.
DECLARE @FactTable TABLE (
FiscalID INT, SID INT, Value Float)
INSERT @FactTable
VALUES (1, 344, 78.44), (2, 345, 44.33), (3, 346, 44.55), (4, 347, 223.44)
SELECT * FROM @FactTable
DECLARE @Dim_FiscalYear_Old TABLE (ID INT, FiscalName VARCHAR (20))
INSERT @Dim_FiscalYear_Old VALUES (1, 2008), (2, 2009), (3, 2010), (4, 2011)
SELECT * FROM @Dim_FiscalYear_Old
DECLARE @Dim_Scenario_Old TABLE (ID INT, Name VARCHAR (20))
INSERT @Dim_Scenario_Old VALUES (344, 'Model1'), (345, 'Model2'), (346, 'Model3'), (347, 'Model4')
SELECT * FROM @Dim_Scenario_Old
DECLARE @Dim_FiscalYear_New TABLE (ID INT, FiscalName VARCHAR (20))
INSERT @Dim_FiscalYear_New VALUES (7, 2008), (8, 2009), (9, 2010), (10, 2011)
SELECT * FROM @Dim_FiscalYear_New
DECLARE @Dim_Scenario_New TABLE (ID INT, Name VARCHAR (20))
INSERT @Dim_Scenario_New VALUES (546, 'Model1'), (547, 'Model2'), (548, 'Model3'), (549, 'Model4')
SELECT * FROM @Dim_Scenario_New
UPDATE@FactTable
SETFiscalID = fyn.ID, sid = sn.ID
FROM@FactTable f
INNER JOIN @Dim_FiscalYear_Old fyo ON f.FiscalID = fyo.ID
INNER JOIN @Dim_FiscalYear_New fyn ON fyo.FiscalName = fyn.FiscalName
INNER JOIN @Dim_Scenario_Old so ON f.SID = so.ID
INNER JOIN @Dim_Scenario_New sn ON so.Name = sn.Name
SELECT * FROM @FactTable
- Rex
April 18, 2013 at 12:03 pm
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;
-- Itzik Ben-Gan 2001
April 18, 2013 at 12:16 pm
RexHelios (4/18/2013)
venkyzrocks,I agree with Sean Lange. More clarity and some ground to get others started would be nice to have.
Having said that, I have created table variables to simulate your scenario. Please check to see if this meets your requirement.
DECLARE @FactTable TABLE (
FiscalID INT, SID INT, Value Float)
INSERT @FactTable
VALUES (1, 344, 78.44), (2, 345, 44.33), (3, 346, 44.55), (4, 347, 223.44)
SELECT * FROM @FactTable
DECLARE @Dim_FiscalYear_Old TABLE (ID INT, FiscalName VARCHAR (20))
INSERT @Dim_FiscalYear_Old VALUES (1, 2008), (2, 2009), (3, 2010), (4, 2011)
SELECT * FROM @Dim_FiscalYear_Old
DECLARE @Dim_Scenario_Old TABLE (ID INT, Name VARCHAR (20))
INSERT @Dim_Scenario_Old VALUES (344, 'Model1'), (345, 'Model2'), (346, 'Model3'), (347, 'Model4')
SELECT * FROM @Dim_Scenario_Old
DECLARE @Dim_FiscalYear_New TABLE (ID INT, FiscalName VARCHAR (20))
INSERT @Dim_FiscalYear_New VALUES (7, 2008), (8, 2009), (9, 2010), (10, 2011)
SELECT * FROM @Dim_FiscalYear_New
DECLARE @Dim_Scenario_New TABLE (ID INT, Name VARCHAR (20))
INSERT @Dim_Scenario_New VALUES (546, 'Model1'), (547, 'Model2'), (548, 'Model3'), (549, 'Model4')
SELECT * FROM @Dim_Scenario_New
UPDATE@FactTable
SETFiscalID = fyn.ID, sid = sn.ID
FROM@FactTable f
INNER JOIN @Dim_FiscalYear_Old fyo ON f.FiscalID = fyo.ID
INNER JOIN @Dim_FiscalYear_New fyn ON fyo.FiscalName = fyn.FiscalName
INNER JOIN @Dim_Scenario_Old so ON f.SID = so.ID
INNER JOIN @Dim_Scenario_New sn ON so.Name = sn.Name
SELECT * FROM @FactTable
- Rex
FYI: you can optimize that query by using LEFT joins instead of INNER JOINS.
-- Itzik Ben-Gan 2001
April 19, 2013 at 10:31 am
Would you mind elaborating, AJB? I didn't quite get it. I used inner join since I thought, if there is no match no update is required.
- Rex
April 19, 2013 at 12:21 pm
RexHelios (4/19/2013)
Would you mind elaborating, AJB? I didn't quite get it. I used inner join since I thought, if there is no match no update is required.- Rex
I am going on the information provided which is incomplete so I could be wrong here but my assumption was that there was a 1-to-1 replacement of the IDs, that EVERY id would be getting a new value. If that is the case then a left join will produce the same results as an inner join.
I don't have a lot of time and I am not a query plan guru so I hope I not explaining this too badly but...
If you setup the sample data that I used to build the temp tables and run these queries you will see that they both produce the same result. In the query plan produced by the LEFT joins we have few clustered index scans doing all the work as the data is fed to nested loops. With the plan produced by the inner join we have index scans feeding data to nested loops that feed the data to a Hash Match. Both produce the same results but the LEFT join is producing a better plan.
------------------------------------------------------------------------------
--INNER JOIN
------------------------------------------------------------------------------
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
INNER JOIN #Dim_FiscalYear_Old d ON fo.FiscalID=d.id
INNER JOIN #Dim_FiscalYear_new fn ON d.FiscalName=fn.FiscalName
INNER JOIN #Dim_Scenario_Old do ON do.id=fo.SID
INNER JOIN #Dim_Scenario_new dn ON dn.Name=do.Name)
SELECTFiscalID_new AS FiscalID,
new_SID AS [SID],
value
FROM new_fact;
------------------------------------------------------------------------------
--LEFT JOIN
------------------------------------------------------------------------------
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;
-- Itzik Ben-Gan 2001
April 19, 2013 at 12:28 pm
I really appreciate you taking time to explain that, Alan. Since the assumption was 1-1, I thought INNER JOIN would work, but never thought of anything further, and how it would work in conjunction with the nested loop. Thanks to you, I have learnt something new today :).
- Rext
April 19, 2013 at 2:40 pm
Thanks you guys. All the solutions work. I'm sorry for not following rules and posting the DDL statements. I'll keep this in mind when I post next time.
Sorry if I have caused any inconvenience.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply