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

Question regarding staging tables and fact tables for a Data Warehouse in a sql server 2012 database Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 11:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:55 PM
Points: 13, 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.
Post #1453235
Posted Thursday, May 16, 2013 6:01 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:26 AM
Points: 705, Visits: 773
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
Post #1453458
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse