data warehouse - Aggregate Transformation vs Sort (remove Duplicate) in SSIS -

-

i'm trying populate dimension tables on regular basis , i've thought of 2 ways of getting distinct values dimension:

  1. using aggregate transformation , using "group by" operation.
  2. using sort transformation while removing duplicates.

i'm not sure 1 better (more efficient), or 1 adopted more in industry.

i tried perform tests using dummy data, can't quite solid answer.

p.s. using select distinct source not option here.

my first choice correct in source query if possible. realise isn't option, sake of completeness future readers: first check whether had problem in source query creating duplicates. whenever distinct seems necessary, first see whether there's problem query needs resolving.

my second choice distinct - if possible - because 1 of cases quicker resolve in sql in ssis; realise that's not option you.

from point, you're getting situation might need try out remaining options. aside using aggregate or sort in ssis, dump results staging table, , have separate data flow use distinct in source query. aggregate , sort both blocking transactions in ssis using staging table might end being faster - fastest depend on number of factors including nature of data, , nature of infrastructure. might want keep in mind else running in parallel if use ssis options, can memory-hungry.

if data (or can be) sorted in source or source query, there's clever idea in link below creating "semi-blocking" versions of aggregate , sort using script tasks:

http://social.technet.microsoft.com/wiki/contents/articles/30703.ssis-implementing-a-faster-distinct-sort-or-aggregate-transformation.aspx


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more