How do I prevent an ETL, that populates cubes by deleting all data in a Star Schema database from scratch each and every time, and instead update just changes?

Paul Mauriello
Paul Mauriello used Ask the Experts™
We have an ETL process that populates a star schema database and then proceeds to create cubes. Problem being its blowing out the star schema ad re doing everything for scratch every single time. Which is incredibly time consuming. We are using SQL Server SSAS 2014 Standard. Is there a way not to do this anymore and just update the information that has changed?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Define 'ETL Process'.  In SSIS an incrimental load is handled via a MERGE component that can detect rows that are inserts/updates/deletes(?) and then take appropriate action in a database.  Warning:  Merge's are very difficult to set up.

The easier way would be to (if the files are from an outside party) negotiate with the technical contacts responsible for these files so that they only send you rows that change, or (if the source is an internal database) make sure that both source and target have a 'Last Updated By' datetime column such that only the rows where the 'Last Updated By' is greater than the previous run go through your ETL, which means they are all changes.
Paul MaurielloSoftware Programmer Developer Analyst Engineer


Thank you :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial