I am trying to come up with a way to move metric data from the staging server to the data warehouse servers.
I have a bunch of dimension tables that gets populated during the ETL process (on the staging server), these are being shifted to all the data warehouse servers (in a scale-out environment) using transactional replication... this is working fine due to the nature of these tables (basically since they are dimension tables, they can just be copied over as-is to all servers).
Now am having issues moving the ETL'ed data in the metrics (or data) tables to the warehouse server from the staging server...
because this type of data requires data (from the staging server) to be "Merged" (as in, Update, Insert, Delete) with the existing data in the warehouse server, and once that's done, the data in the staging server that has just been processed needs to be deleted (so it staging server doesn't keep building up data and becoming huge). The data in the data-warehouse must be maintained when the data in the staging server is truncated (so if data is deleted in the staging environment, it doesn't get deleted in the warehouse).
The only way I can think of doing this is by moving the data from "staging server" to "staging table on the data-warehouse server" (through replication), then perform the merge in the data-warehouse to the proper metric data warehousing tables. In this case the main data warehousing tables aren't involved in the replication directly, and so data in those tables doesn't get deleted once I truncate the metric tables in the staging server.
It just sounds in-efficient!, isn't there a way to get replication to handle all this (maybe through using clever replication filters?)
Maybe I shouldn't use replication at all, and just do custom linked server queries that shift and truncates data?, although from experience linked server queries tend not to perform very well.
Any guidance or just general idea dumps would go a long way!
P.S. Sorry if this isn't clear, any questions you have just fire them over