Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Easy Question: Organizing Data Flows Expand / Collapse
Author
Message
Posted Sunday, December 01, 2013 11:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:15 PM
Points: 327, Visits: 387

Since I'm new to this and data warehousing, I'm not sure what is the best way to do what I'm wanting. It just seems best and more impressive to move my different packages into one package.

What I'm doing is pulling information from various sources into a Data Mart. My Data Mart has a set of 6 dimensional tables and one fact table. Each data flow task is in its own package (currently).

So the way I'm thinking of organizing this is this:

1) In Control Flow, get two sequence containers. The first container will contain all the dimension tables' ETL processes or Data flows.

2) The second container would have the fact table.

I don't know if this is a good design or if I will run into problems, but logically it seems I should make sure my dimensions are loaded first and then my fact table. So why not put them all in the same package?

I know that the info doesn't have to be placed in the dimensions first, but i don't really know how to do that yet. I'm just familiar.

Thanks!
Post #1518707
Posted Sunday, December 01, 2013 5:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
I don't use SSIS but, speaking from an old dude's perspective, I'd likely make the loading of each table a separate package for reusability and then have a control package (dunno if it's possible... like I said, I don't use SSIS) that defined the order of execution. That could also allow parallel execution if you needed it and the system could tolerate it. Makes for a hellova performance boost when it works right.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518729
Posted Sunday, December 01, 2013 6:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:15 PM
Points: 327, Visits: 387
That really sounds like a good point. I'd still like to get some feedback from someone who uses SSIS.

But like you said from a re-usability standpoint, these dimension tables will be re-used in other data marts. It is probably not wise to presume we will load each dimension before loading all the different fact tables that use them.
Post #1518734
Posted Sunday, December 01, 2013 6:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 547, Visits: 1,880
Jeff Moden (12/1/2013)
I don't use SSIS but, speaking from an old dude's perspective, I'd likely make the loading of each table a separate package for reusability and then have a control package (dunno if it's possible... like I said, I don't use SSIS) that defined the order of execution. That could also allow parallel execution if you needed it and the system could tolerate it. Makes for a hellova performance boost when it works right.


Yes, master and child packages are possible. There is an execute package task.

From the sound of things that would be my approach here, especially since the child packages are already built.
Post #1518737
Posted Sunday, December 01, 2013 7:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
Nevyn (12/1/2013)
[quote]
Yes, master and child packages are possible. There is an execute package task.


Thanks for the confirmation on that, Nevyn.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518739
Posted Monday, December 02, 2013 1:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 4,832, Visits: 11,197
Jeff Moden (12/1/2013)
I don't use SSIS but, speaking from an old dude's perspective, I'd likely make the loading of each table a separate package for reusability and then have a control package (dunno if it's possible... like I said, I don't use SSIS) that defined the order of execution. That could also allow parallel execution if you needed it and the system could tolerate it. Makes for a hellova performance boost when it works right.


Confirmed that this is completely feasible and sounds like a good idea to me.

Bear in mind that you'll have to tune the level of parallelism you introduce & keep an eye on locks to get the best solution.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1518789
Posted Monday, December 02, 2013 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
Phil Parkin (12/2/2013)
Jeff Moden (12/1/2013)
I don't use SSIS but, speaking from an old dude's perspective, I'd likely make the loading of each table a separate package for reusability and then have a control package (dunno if it's possible... like I said, I don't use SSIS) that defined the order of execution. That could also allow parallel execution if you needed it and the system could tolerate it. Makes for a hellova performance boost when it works right.


Confirmed that this is completely feasible and sounds like a good idea to me.

Bear in mind that you'll have to tune the level of parallelism you introduce & keep an eye on locks to get the best solution.


When I've done similar in T-SQL, I generally load to a "staging" table that looks and smells exactly the same as the original. When it's done, I simply repoint a synonym and there's virtually no contention or locking issues. Of course, I've also had the luxury of having enough disk space to do such a thing (I don't believe that disk space is as cheap as some would think but I do press for it for such things).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518915
Posted Wednesday, December 04, 2013 8:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:15 PM
Points: 327, Visits: 387
Thanks guys! I really appreciate this feedback! I'll do some more studying on this. Greatly appreciated.
Post #1519655
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse