Q: We have three data sources that are feeding three different stars. We wrote stored procedures in the ETL layer to load them. Also we need to join them in the detail level to calculate certain figures based on information that exists in these three data types. Shall we do this while loading with all the delay this may cause, or shall we load them, then run joining scripts and calculate these figures with the synchronization and delay issues? The same joining between these three sources is used for revenue assurance.
Joe Oates' Answer:
My advice is to run stored procedures or scripts after the stars have been loaded. Some of these calculations can be complex and may take up more time than you can afford. Also, since your revenue assurance is done this way, there is been precedent on using this approach for critical processes.
Adrienne Tannenbaum's Answer:
I am not sure that I understand the specifics of your setup, but I get the impression that your "stored procedures" are obviously assuming "ready" data. Since you are extracting from three different data sources but translating into similar relatable info, I would recommend all translation to occur before the load, then load using your existing stored procedures. The joining would then be based upon already calculated and similarly cleaned information. I need more information about the sources and their disconnects/similarities...
Tom Haughey's Answer:
This is simply an example of a trade-off. I would probably not do it during loading, but rather do during the ETL process, where you would create the summaries and place them in a load table, so that they can be rapidly loaded. After the aggregates are created, then load the data as efficiently as possible. My experiences with aggregating during load have often been unfavorable, and by that I mean slow.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access