Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Wednesday, May 15, 2013 11:27 AM


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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 17, 2016 1:48 AM
Points: 780, 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.

Post #1453458
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse