SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question regarding staging tables and fact tables for a Data Warehouse in a sql server 2012 database...


Question regarding staging tables and fact tables for a Data Warehouse in a sql server 2012 database

Author
Message
srobinson 596
srobinson 596
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 69
So I have a staging customer table that holds combined customer information for several different databases. All our customer information across different platforms is combined and standardized in this staging table. Based on this data - we normalize it and then either update the customer information in our Fact table if that customer already exists - or we insert a new customer record if that customer doesn't exist.

Our current Fact table has 10+ million records in it. Our staging customer table has millions in it as well. I know these numbers aren't exactly big - but I am also planning for the future as well. So - my question is are there any 3rd party tools on the market that can help with the update and the inserts? Or is my only option scripting it all out in either a stored procedure or an SSIS package. I looked at Red gate's SQL compare - but it looks like it works better if you run it ad hoc. I am looking for something to create and then schedule it so it runs automatically.

Any suggestions is greatly appreciated.
Ells
Ells
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 931
I would prefer a separate staging table for each of the different versions of customer.

Then I would look ate combined table with the conformed customer dimension.


I would use merge to get the final table.
Not sure if the above is ok for your volumes. You would really need to add more detail.

E
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search