Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update on ID's Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:01 PM
Points: 49, Visits: 245
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
Post #1443861
Posted Thursday, April 18, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,450, Visits: 12,312
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443894
Posted Thursday, April 18, 2013 9:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:01 PM
Points: 49, Visits: 245
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.
Post #1443967
Posted Thursday, April 18, 2013 10:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,450, Visits: 12,312
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443978
Posted Thursday, April 18, 2013 10:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:21 PM
Points: 76, Visits: 291
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
SET FiscalID = 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
Post #1443983
Posted Thursday, April 18, 2013 12:03 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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
( SELECT fo.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)
SELECT FiscalID_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;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1444064
Posted Thursday, April 18, 2013 12:16 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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
SET FiscalID = 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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1444073
Posted Friday, April 19, 2013 10:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:21 PM
Points: 76, Visits: 291
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
Post #1444510
Posted Friday, April 19, 2013 12:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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
( SELECT fo.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)
SELECT FiscalID_new AS FiscalID,
new_SID AS [SID],
value
FROM new_fact;

------------------------------------------------------------------------------
--LEFT JOIN
------------------------------------------------------------------------------
WITH new_fact AS
( SELECT fo.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)
SELECT FiscalID_new AS FiscalID,
new_SID AS [SID],
value
FROM new_fact;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1444559
Posted Friday, April 19, 2013 12:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:21 PM
Points: 76, Visits: 291
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
Post #1444560
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse