• Safe and secure

  • Quick and easy

  • web-based solution

  • 24/7 Customer Service

Rate form

4.6 Statisfied

990 votes

How to Finish the Zions Cross Sell Mortgage Certificate Elite in 9 Steps on the Internet?

CocoSign aids each user to simplify the workflow and increase work productivity by having a better management of the files. Check the below steps to have a better understanding of how to finish Zions Cross Sell Mortgage Certificate Elite more quickly.

Click the form

eSign the form online

Send the signed form

  3. Fill out your personal details in the blank form.
  7. Save the document by selecting "Done" button.

By utilizing CocoSign, you can fill in Zions Cross Sell Mortgage Certificate Elite and add your digital signature shortly. It will definetely increase your productivity and make your life much easier.

Thousands of companies love CocoSign

Create this form in 5 minutes or less
Fill & Sign the Form

How CocoSign Can Assist to You substitute Zions Cross Sell Mortgage Certificate Elite

youtube video

Instruction of Finishing the Zions Cross Sell Mortgage Certificate Elite

[Music].alright enjoy the webinar everyone Brian.we can't hear you you're gonna have to.turn off your webcam it's okay I just.bumped my docking station okay all right.sorry all right guys well good morning.or good afternoon and happy Valentine's.Day those that are on the phone right.now we have a Wow we have a huge amount.of fountains question went over 1300.people registered and who knows anybody.show up we're over bro well over 500.right now so my Twitter handle is on the.screen right now at Brian Knight and my.email address is on there as well it's.been a while since I've done the webinar.for this lane police or some of these.demos here so my HR had actually sent me.a note asking me not to do certain.things here are some of the things they.asked me not to do here so I want to.make sure I Liz will keep me honest here.and make sure I don't do these things.but Thank You Liz.all right a little bit myself mine at.Twitter handles at Brian Knight I'm a.sequel server MVP out of Jacksonville.Florida I'm the founder of pragmatic.works where we're based out of here in.Jacksonville we have a number of other.offices around the country I also.authored a number of books around sequel.server and I've logged in at website all.pragmatic works calm you'll find a blog.out there today actually about some of.the new about do dictionaries and those.kind of things this for those that said.they were intermediate that is perfect.we actually are the session is geared to.intermediate or advanced level session.we're focusing going to focus on mostly.around the data warehouse Sykes we can.we can demonstrate some good faults of.SSIS and some good pros best is by doing.some of these these topics right here so.we're gonna focus on the data warehouse.load the dimension load the back table.load how we can do some incremental load.patterns and then lastly performance.tuning pieces around us is also so by.focusing on these topics we can.ultimately kind of show you some of the.faults that will happen there as well.and I also saw quite a few guys had some.issues with data warehouses also I'm.going to keep an eye on the.insofar as well to question from the top.we are recording these all of our.webinars were actually recorded me about.800 hours of free content on our website.under webinars if you go to our homepage.you'll see a little ribbon up top it.says free training Tuesdays and.Thursdays and you can see all we're out.of our older webinars there as well.all right so disconnects question Doug.building our first warehouse so to make.sure that we have quite a few people.that were that were not have not built a.warehouse yet or were in the early.stages we have a we have our first piece.of this webinar it's all about kind of.design in the winter house to make sure.everybody is on the same page before we.start to load that data warehouse we are.going to have rates in the middle of.this session as well so about every top.of the bottom bring our we'll do a quick.five minute break to make sure.everything can get to us and water bio.bio break and come right back so first.question is really why did we do an.either warehouse to begin with and there.are only three core reasons that that.really you know encourage us to do data.warehouses the first one is you know.I've got data for example in my own.system we're building one this week and.in the conference room and we've got.data our own system that is not the.cleanest in the world so when I try to.ask our CRM how many sales I have by.state.I might get five answers from that so I.might get you know Florida has you know.I just felt four different ways or five.different ways so one of the things you.want to do in our data warehouse is.clean the data as it comes in so if you.have USA US United States and u dot s.dot a dot we want to make sure that we.come out of that with one clean record.one USA record so that's one of the.goals of the data warehouse so to clean.that data as it comes in also archival.of data so for example in our our CRM.with the Salesforce comm we're trying to.keep it pretty lean.because salesforce.com charges us by the.amount of gigs were storing on their.platform so for a lot of activities we.want to kind of get that off the core.platform and get it into our warehouse.and kind of purge those event every six.months to a year or what one of the.number is for you so archival of data we.wanna make sure that's really really.for a narc narc system and then lastly.the consolidation of data so we want to.get the data from in my case I might.have oh ok gonna Brown the the.consolidation it might have data then.it's in in salesforce.com and and we.also have you in QuickBooks and we have.data inside of our our own training.system like what I mean is we're using.right now and want to consolidate that.into one view the data warehouse is a.great way of doing that so if it's so.great then why is it a bad word at many.organizations.so looks like the webcam keeps on going.off and on here I'm sorry guys let's.developer squared and L is that you.think as stopping it or is that might.myself I don't even see it well you just.came back up I would just kill it it.really messes with stop Brian yeah no.problem let's do that.alright cool so the ultimate that why is.it a bad word for most couples to put so.many companies and it's really around.the success rate of the BI projects in.general back you know even five years.ago we had a success rate that was just.abysmal so about 50% of data warehousing.projects are bi projects failed and on.two-thirds of those bi projects really.didn't deliver the ROI that was promised.and there's some really core reasons for.that the main run is really the.communication of these two guys right.here the the IT guy is a guy who's.driving in many cases and the business.doesn't know why they're driving it so.there's a lot of communication gaps they.have and relatively a lack of.deliverables because they're waiting for.that one big punch which ultimately.creates a lack of trust now this is a.reason why there's a lack of trust I.think it's ultimately because most data.warehousing projects especially like the.government projects in many cases.ultimately try to navigate the project.like like this lady is from Edina we're.from infirmary I am a a waterfall.approach while she's trying to get down.this waterfall she plans it all out and.then she ties up repels down and.realizes oh I'm in a mistake so when she.makes that mistake she has to go climb.back up find the new path its tie up.again and then navigate down so.traditional data warehousing.Jaques for that are using waterfall.approaches don't typically work very.well they're prone to failure in many.cases see they're really really watch.out for these they have long planning.cycles where it might take you six.months so just plan how to the data.warehouse and at that point your.company's already kind of shifted and.already veered from the main core goal.it had before it also it might take you.six months before you can deliver the.first result and makes it very very hard.to react there's a really well-known.example here in Florida from the banking.company that they spent about about.eight months just planning the data.warehouse this is back in 2008 and right.in 2008 you can imagine what happened.then the banking industry collapsed.you know the mortgage industry collapsed.so they had spent almost got show's.millions and millions of dollars on.resources on hardware for their big data.warehouse has become this big huge.enterprise data warehouse and there was.no it had no results because of that so.that's one of the core core problems.that we have in many many cases so the.ultimate answer is typically going to be.to the agile approach.so the an agile approach has a 1-month.iterations and this is where we try to.deliver something the business every one.month we have a complete deliverable so.what have an ETL done will have will.have some kind of reporting done at some.sort well hopefully the cue know some.rough QA done and it becomes it very.very easy to adapt so as as customers.change we can change as well okay now.with the approach that most of us use in.part a lot of you guys on the phone also.use is something called scrum so scrum.is an agile technique where we basically.build a board like this and we have all.of our stuff all of our items and in.little sticky notes like you see right.here where nothing is more than eight.hours of work and there's lots of ways.of doing this digitally Microsoft has.been called called planner there's.there's loads loads of our tools at.Trello III we use Trello here and bsts.also and ultimately meet around this the.scrum board every day and we say what.about I accomplished yesterday and what.am I going to accomplish until tomorrow.and then what kind.of of road blocks do I have and the goal.of project manager is to remove those.road blocks but anybody in that room.that that should be part of that project.is is never in that room so let's jump.into really how do we design this data.warehouse and it's got this before you.mostly and for anybody else in the room.it that is also in a design stage so how.we design our data warehouse is really.broken up into two types of tables.dimension tables and back tables the.dimension tables are ultimately going to.answer the where clocks the slicer you.know what I want to see my sales by.country buy products by you know by.resident whatever that might be so the.where clause is typically going to be.the dimension table it's very very wide.table because we're consolidating a lot.of injuries into one table so I might.have for example products in three.different areas or employees in two.different areas well that's all done.I've consolidated those and I have a.very very large wide table because of.that now we have this no sin that notion.of a surrogate key and this is.incredibly important when it comes to.dimension loads our surrogate key looks.like this they see right here we have a.potion key and then we also have this.thing called an alternate key the.promotion key is going to be our our.primary key in our data warehouse and.it's a artificial ID it's usually.identity column or it's some kind of.artificial identity column that just a.number from one to a million that has.it's automatically seeded for us and.there's some reasons why we'll get into.in this the Scott's asking the question.is at a table with over 200 columns too.wide you see that allow a lot with.government into these especially uh you.may well look at the Zion depends there.really depends right I have you behave.indeed a consulting answer here I had to.kind of see the column to see you know.is there really the too granular is that.you know why have we have 200 columns.but I have seen columns that are that.are very very wide like that it's not.unusual especially census data we had.had since this table that was easily.like 300 300 columns one time I got from.the Census Bureau so it's it is tough I.said it's a government entity so that's.very very common you'll find they I love.them innovative use board that way.and they may be ways of pivoting that.metadata but it that could that could be.a helpful there okay the circuit key.though there's a few benefits of this.first of all let's imagine we are Scott.and we have a very very wide table and.what represents a unique instance of a.resident in our state for example might.be four or five columns it might be.there so security dress and I'm after.day of birth for example a concatenation.is a is a column that why I care about.now the Zurich key in this case will.help us with the compound keys because.we only have one key on the way out so.we're not having to join all over the.crib all over the place this is also.going to insulate us from our sources so.let's imagine we switched from s AP to.Siebel ideally we want to make sure that.we're we have we limit the impact of.that so Surrey keys can also limit the.impact of that we also want to track.historical changes now it's not a notion.called a tight two dimension which we'll.get into a moment and then this is gonna.this will allow us to do those.historical changes now you want to keep.those keys as small as possible so if.you have an employee table with only a.few employees in it and it goes small it.tube you can and the reason why is when.you give them things like analysis.services and you're doing massive back.tables later you're gonna find that it.comes in especially helpful okay.now the last thing here on the dimension.table well my different types of.dimensions in a moment here and how we.load those but when we have comes of.fact tables we might have a fact table.is our center of our universe so let's.imagine for example we are a law.enforcement agency and I've got I've got.maybe a customer right here alright for.a law enforcement agency we'll call that.a perp and then we've got maybe the.types of reasons I'm pulling over so.type incident type go and then in our.fact table in blue here is in the center.of our world so we're kind of keeping us.simple here and only had a two fact.eight only two dimension tables but the.fact table is the center of our world.and it has the foreign keys to these two.tables right.here and in so on and so on and so on so.I call this what I call this a star.schema cuz it looks like it's is it able.to kind of fans out from there so you.would have things like the Zurich use in.here perp perp SK the incidence SK and.then that we'd have maybe the fine they.paid and time served or or whatever so.those but these two numbers that you're.seeing right here below this line are.going to be our measures are our.numerical values that we want to slice.that we want to see in our sliced report.and the top two numbers are what we.slicing on so once you slap us and.outside of any kind of BI tool like.Cognos or now services or even power bi.in some cases it will go ahead and help.you you know build these reports very.very quickly because of this it's kind.of refined for this okay so that's where.I short keys kind of come into play in.the fact table and become the foreign.keys now there's there's really two.types of dimensions free types will will.refer to here that will refer to when it.comes to SSIS the first type is a type.one dimension a type one dimension.allows us is really about how we track.changes here and this is a column by.column decision you're going to make so.for example if you have I want to update.this customer and the rights address.information to go from black Shire.Street to Main Street.what will it will call this column a.type one column which means we're.keeping track we are not keeping track.at history all we do would you say all.right the update comes in and the.customer never lived at Black Shark.Street they've always lived on Main.Street in this point forward again this.is a column by column decision so for.example last name you might say oh I.care about history there but first name.I don't care about history there so you.can get very very granular on that a.type two column its call so called a.historical attribute not changing.attributes little typo there and sorry.and a type two column is where we keep.track of changes so in this case it.might be a big deal for example pipe.that they address line one Main Street I.don't really care that somebody changes.that I'm gonna go ahead and just overlay.that however if somebody changes the zip.that is a big deal because now you live.a sizable distance from my store so when.I pass this record unless I have my.surrogate key right here of ten and.notice I have a start date end date.column here right now because this end.date is null this record right here is.my active record and when I pass this.data in I'm going to expire this first.record create a new customer Myles.Harris again at the new address new zip.code who just started and just ended so.so yes he the sizing active record now.now it's two ways you can do that active.record we can have an active file or we.have a start date end date like you see.right here there's a number of options.number of options we have here some.people prefer to have the active flag.true/false and they will prefer to have.start date end date most people I see.that do it's active flag also do the.start date end dates because because the.start date end date is very interesting.metadata to keep so there's nothing.against having all three columns start.date end date active flag and I'll show.you ways of doing that once we get into.this the other thing that we now get.questions around my Lots here is that.indeed of null some people prefer to not.have a ended of null but to have an end.date like 1231 99 for example that's a.great date arbitrarily in the future.like 2099 or a nine and a nine or some.like that in the future as absolutely.possible and I'm going to show you in.SAS how we'll accomplish that also so a.question here about when do we use.snowflake inside of a data warehouse.that's a good question so let's imagine.for example kind of draw my example we.had it for rich my there's my fact table.and I've got a product dimension in my.case here so I'm gonna go from here to a.product dimension all right Big Al's.called Prague now in my product.dimension though I've got things like.I've got columns like let me shrink this.down just a bit so we can all see this.of course we have a product SK we have.product a K the product a K is the.primary key from our source system that.is our alternate key or as.also the the primary key from Siebel.from sa P from your inventory system.whatever your whatever your primary key.from that system is an asura key is just.an artificial number that we created in.our warehouse.all right so below that the product name.we'll have we might have a category of.the product you might have a sigh as we.might have a subcategory we might have.you know the category name and all that.as well description and all that so.let's imagine we have millions and.millions of products and I want to.change the name of the subcategory from.apparel to clothing for example if I do.that I'm having to go an update.potentially millions of products and.really cause a big ETL challenge in many.cases and that's where snow flicking.makes a lot of sense let's imagine these.two columns that you're seeing right.here this category and subcategory right.here if I have let's say more than five.or six columns that represent that group.of that group of columns it might make.sense to separate it out into its own.category dimension right here and you.have basically a one a minute going this.way so it always fans out we start the.most granular that's attached to your.fact table you fan out to product them.cattigan and subcategory it's been a.subcategory and category and so on but.if I have a sizable amount of columns.right here for example then it makes.sense to go ahead and separate that out.so I'm only updating that one record we.have changing it from apparel to.clothing we just have to go and update.all million records in my product.dimension so hopefully answer your.question it's one of the one of the many.answers around there's a lot of.subjectivity around it all so that may.or may not you know yeah there's a whole.bunch of gray area there so it's not.always a 100 percent best answer there.but to me it all depends on the number.of columns and depends on the number of.rows I have a table for that granularity.ok the last type of dimension we'll.cover here is actually quite a few more.types of dimension but the last one that.really depends that really shows in sis.is a type 0 attribute.the type-0 attribute is where I'm.changing somebody's value like for.example I'm changing somebody's social.security dress perhaps or in this case.I'm a health care company and I'm.changing their gender for example or.their social security dress in this.instance now I'm saying that this is.going to cause an exception because I.don't expect this kind of update so when.I send this in I have two options in.SSIS one option could be to fail this.and one option could be to go ahead and.ignore the update what most people do is.they go ahead and they they ignore the.update and they they write that row.somewhere else into an exception table.and that exception table can be.addressed at a later time so if somebody.breaks the rules and sends in a social.security dress change that's a big big.change I want to know about that and.maybe my source data is bad name you.like that stations do things bad but I.want to least mainly make that change.versus and and and do some gut checks.first okay that's called a fixed.attribute or a type-0 attribute so again.we have three types type one is for is.called a changing attribute in SAS it's.for that where I don't care about.history if I care about history it's.called a historical attribute it's.called a type 2 change and the last one.is where I where I don't allow change at.all it's called type 0 or a fixed.attribute now those are all terms are.the ones in parentheses or the Esaias.way of calling it and the hex zeros more.the standard modeling term the last.piece here is called inferred number you.see this a lot and healthcare especially.this is called also in big common terms.called a 8a late arriving fact so let's.imagine in this case and this is this is.also supportiveness is that I've got a a.fact table load and I am trying to I'm.going to try to look up the product.dimension say I have this product coming.in bkt 800 I don't have that product.over here it doesn't exist so I have two.choices right now.one of my options could be to flag that.sale coming in to an unknown records.to it and this is most common you'll see.you'll see people flag it to a it's.called a negative one record just a stub.record out there so that's one option we.can makes me flag that say we don't want.to throw the seal away but when I.tribute that sale to a a product called.unknowns now this happens a lot when.systems are out of sync so excite let's.imagine you were the insurance industry.and you have a agent who made a sale who.just joined our agency but he made a.sale but is not through with HR all the.way yet so he hasn't master record for.Europe but the agent is my HR system but.if he's not fully through that system.yet so in this case we can have two.options we can flag that to an unknown.agent like we're doing right now.or we can create a stub record with what.we actually know about so we know this.product is at BK 8800 that's that that's.this alternate key it's its primary key.from our swar system we know that.products name is at or elite and that's.all we know about this product so we'll.win put unknowns all the way down now.it's going to happen you'll notice the.inferred number flag down here it's set.to true right there and because of that.what SS is is going to do is whenever.that dimensional record comes in.whenever that bike finally comes in or.that agent Bonnie comes in it will go.ahead and change all these unknowns you.see right here to their actual values.and then change that true right there.the false once it makes that change so.we'll see that actually change and it's.all done so you can imagine there's a.lot of rules around this right it's a.crazy amount of rules that creates a.huge ordeal if you're if you're loading.these so imagine doing this in T sequel.it's not fun my-my-my in the past I had.to dissing T sequel and it was a I mean.every dimension.I would I would have busy guest and.think about a day to do in many cases.depends on how hard it was once you give.a type twos and type ones it became.really really hard and that's before we.had things like the merge trance merge.uh syntax that we have now and SSI and.then not to sequel so let's jump into.how we do the.sighs at last okay so an sis there's a.number of ways of doing this we're gonna.cover the the easy way and get a little.bit harder but a little bit better.performance as we get deeper so I'm.gonna show you a good dad an a I'm sorry.good good way a better way and I.hopefully the best way so the good way.of doing it that works about 90% of time.it's something called the SCD that's.slowly changing dimension with Izzard.now this is a transform you're gonna.find an SS I asked and it essentially is.a code gen tool it's gonna produce all.this code for you the reason I say this.works about 90% of time is it will.handle about 90% of the use cases that.you might have so it will humble I say.80% 90% it's it's relative here is very.very fast from a design time perspective.so we'll go ahead and we can pump out.these dimensions you know super super.fast and it is customizable slightly but.it has some faults and we'll cover those.faults as we get in this so let's cover.our first demo of the day so what I have.here is I have a club table this is my.ol TPN bottom at my transactional.environment and you'll see I have a club.name and I had the club ID now this club.ID is my alternate key that's a primary.key for my score system now I'm trying.to get this into my data warehouse right.here this is this is considered a.college here it won't turn off Skype I.apologize there we go.all right so my data warehouse here go.away my dude a warehouse here has a.nation Dez's Colin call artists table.called dim Club apologize for the.underscores here I hate underscores.myself and I have again my sri key I.have an alternate key so that club ID.you saw before will map to club a K down.here and then I've got a club column.right here so we're gonna basically load.that and see how we can make this this.work all right cool so let's go ahead.and US I have a few people that argue.about underscores I understand I like.Kim okay seeing if the religious battle.isn't it wrong.it's some people like them some people.don't alright so it's a pet peeve of.mine I guess my case so let's go ahead.and and let's load that load that table.so now I'm gonna get there from this.club table into dem club table down here.I got a whole bunch people saying they.like underscores I I got you guys I'm.sorry I shouldn't even gone there it's.my pet peeve not not nothing after that.some people's alright so let's start.from a brand new entry here all I have.is an old lady new source I have my two.connections down here this part is not.going to be an intermediate level it's.going to be you know it's kind of.starting us off here and you'll see here.is I have my OLTP on the left my data.warehouse on the right and I have a.simple data flow oh but I dragged over.and the data flow I dragged over an old.lady B source over okay in my only.resource I want to get some data now the.one rule in run sis is sure is easy to.go ahead and pick club here isn't it.it's my source whatever you do in sis.this is not a whole lot of always rules.but from a performance side never ever.ever take this option right here and.there's a real whole bunch of reasons.around that one is around metadata will.cover in a second but the main reasons.performance reason so I've started the.battle now in chat to underscore is the.no underscore is that looks like sorry.guys we should we need a poll question.around underscores don't we alright.let's quit a poll question for.underscores or no underscores I'm.curious later for that alright so the.what we have up here is is I want to go.ahead and change this to tea sequence.then there's a reason why so let's.imagine for example a system TV assembly.or select star from them I'll let us it.let's list.I show you the only first here let's.take a call alright so let's imagine for.example I go into financial aid a to.some sort here are standing whatever and.I that's too simple the table let me.find a little more interesting here.participation okay so let's imagine if I.have this and I want to ahead uncheck.some these columns I don't.we care about these forests for columns.so by doing this what I have what I have.had to do is I've ultimately said I.don't want to send those in the memory.which is great idea I don't care about.three columns by unchecking those it.will they will they will not be sitting.in the memory the challenge here is if I.do this it also has some issues though.so it's still going to run a select star.against this core table let's.participation here with the select star.against participation brings the entire.table down and then remove those tables.from from from RAM as a client-side.filter so that's one of the core issues.with this is you if you do it this way.it's doing the client-side filter and.it's still gonna bring all those tables.over the network.so be very very careful when it comes to.this this option right here instead.let's do the sequel option only and that.makes it much much easier.I'm just go ahead and get all my columns.from the club table a little trick I'm.gonna do is gonna say select star at.first I'll have built query and then hit.OK and it'll blow up select star into.the right column so you're at least.would not have to deal with two columns.but if I had a whole bunch of columns it.would it would would go ahead in there.and add those there so if I wanted to.now I also want to keep this a little.cleaner for me to understand what I'm.doing later so I'm gonna go ahead an.alias this column this is my own.personal best practice I'm gonna go.ahead Elias is column as Club underscore.a kay because that is a name on the.destination so one of the things I.personally like to do in a data.warehouse load is to alias two columns.as their destination name their reason.why is a lot of these wizards are going.to see the names match and they'll go.ahead and line things up for me.automatically because you're gonna open.and close that wizard all the time and.you don't want to have to map those over.and over and over again as Bob is.pointing out right now okay so let's go.and hit OK so the sir club table I'll go.ahead and just be I'll be nice and just.do SRC clubs so we have the naming.conventions for this first first.component here all right so with that.now done let's go ahead.and and drag over are slowly changing to.mention transform I'm gonna wire it up.into this and let's double click on it.to configure it alright first question.it's going to ask this is this is native.to SSIS by the way and it's not their.party component here and we'll cover the.pros and cons available later now if I.take my data warehouse is my my.dimension Trello now I have a little dpi.issue as you're seeing right here this.is a resolution issue but just trust me.that says dim club it's a little hard to.see but it looks like dim club to me at.least okay normally if it's it's the.higher resolution monitors where they.didn't really plan when it they have a.few bugs on it with sis where it's a.it's a little tough to see okay so a few.questions we have out there already just.for those that are already going there.that are not under school related we are.going to I want to post these these.slides on to our blog on a pragmatic or.explode later today so you'll find those.there there are some comments in there.as well you can kind of use for for.hints later or if you want to want to.kind of brief your team or our news pack.things that's one of the questions I'm.saying over and over again but we will.we'll have have a haven't had that okay.so we have our dim club table mapped so.again that's so far all we've done is I.have my club table coming in and I want.to send that to my dimension table now.so I've mapped dim club here and these.are my columns that it's finding in in.my day that my tea to flow right here.these are my columns right here from my.club table not my dim club table on the.middle here is my data warehouse columns.so this is my OTP this is my data.warehouse based on the mapping I have.right here this is why I Elias a column.though because now it's red have club ID.it has club okay it Maps up beautifully.and that is my primary key across both.systems if I hit this drop-down box and.select business key I'm basically saying.this is my alternate key this is my.business key this is whatever that the.column that max across those two systems.okay now Club is just a regular column.these other columns you're seeing here.will be used later those are going to be.my.I use for type 2 dimensions a little bit.later so let's go and hit next now we're.going to define a this one column I had.left and I would have a row here for.every column I had left like it's only.one column left what is that is it a is.it a type 0 is it a type 1 or is the.type 2 and you'll see right here in from.them on the left here changing.attributes are type one historical.attributes are type zero or have to.excuse me so let's keep it as a changing.attribute for the time being and I'm.gonna run this wizard through twice here.so you can kind of see how it looks.different from a type one side and a.type 2 SOT let's hit next and then the.next question is alright well this is.kind of a goofy question I think and.this is probably the longest property.you'll ever find inside of a Microsoft.product the property in the checkbox is.change all matching records including.outdated records when changes are.detected in a changing attribute if that.doesn't drive you a little nutty I'm not.sure what will so here's what this means.this property means and I Shaw know how.they I don't know how much how much.alcohol was being consumed when they.came that property name but it had to be.a lot it had to be a Friday night a.debate I don't know who knows so let's.imagine for example we have a a mixture.of type 1 and type 2 columns so let's.imagine we've got a customer ID.I'll call cust ID r sk cust ID i've got.a first name i got a last name in there.also and maybe i've got a I don't know a.city alright and I'll call this an.active flag over here also.okay so I have customer ID one right.here who is whose ID is ABC and they're.her first name is Jen Jones who lives in.New York City now she is currently at.active record but something happens she.gets married.alright so she's no longer the active.record.customer 101 5150 is now the active.record is still ABC she's now known as.gin night which is type 1 so no new.high-tech - scuse me all right.so she is now the active record all.right so let's imagine though that the.city is a type 2 column so in the city.column if somebody were to change that.how you want that to behave well you.expect first of all that we would change.this to the new city this type 2 to.Jacksonville for example I misspelled.that there we go now with that checkbox.unchecked that you saw you previously.saw this is the behavior you would see.it would I would change gen city to.Jacksonville for the active record.however if we were to check that box it.would basic go back and it would say oh.she's always live in Jacksonville so it.would make her live in Jacksonville here.as well so the question really comes in.is when a type 1 update happens do you.want to expire or do you want to change.all the expired records also so that is.that is it is is that is one of the.options you have there so I haven't.almost never seen a reason to check that.box so it's up to you but it's a very.very rare reason that I found to find to.do that the last list do you have.inferred numbers keep on inferred.numbers art that are the the late.arriving facts that we had before this.is having am I missing an employee ID.Emmitt unemployed I have a sale for an.employee who doesn't exist do we have.that no so I'll go ahead and uncheck.that I'll hit next and then finish now.it's going to happen is it produced the.code you're seeing below the SCD.transform slowly changing dimension.transform so it produced this code and.it has some pros and cons to it so if I.were just go ahead and run this as is.first of all let me make some changes.here let's see a little crazy here on.dim club let me go over to here and let.me go to club scuse me all right.find something to make a change on real.quick to make it a little bit.interesting here okay.so let's go ahead and let's change the.glee club to the Glee fan club the.little fox show that used to happen.either the oldest sitcom that was out.there so we should test that is record.11 here so let's go back over to our.queries and let's look at a new query.against my dimension it's a select star.from dim Club and let's look at where.club a K is equal to it was 11 and I am.the wrong database warehouse there we go.so right now we have a we have a type of.mixture here type 2 and type 1 that's.fine but we have this this glee club.that we have here that's our active.record oh we have both are both active.oh so let's do quick a quick cleanup.here delete from dim club where active.and date is not no is not oh I guess the.foreign keys in there all right whatever.all right so if I want to run this we'd.hope to see one of those other that turn.to fan clubs let's go ahead and run this.we're gonna see is our new records go to.the left and our updates go to the right.so we'll have one row that went to the.right is only one update and we'll see.that update now here the Glee fan the.Glee fan come Club - you get updated now.and that are both active so what we're.seeing here is new data goes this way.old data goes us this way and that's.what's doing it's basically handling all.that forest now there are actually more.than just the two lines you're seeing.right here there are actually more lines.as well so if I wanted to let me to send.something to this this just dead end.right here for a time being now this.will actually show us what other lines.we have available to us so we also have.you know unchanged attributes now this.is kind of handy to know how many.duplicate records has a sources system.sent us today how many records that are.already in our data warehouse.did the source and the source system.send us today we can count those and log.that into a table somewhere okay so.let's make this a little more.interesting now there's some other.things we'll some performance things.we'll cover a little bit later so you're.seeing here first of all that the only.IV insert what's happening here so it's.inserting all my new data using this to.a classic Ola baby destination here now.the problem that we have here is the.wizard produces some code that's not so.optimal so for example it did table or.view well table review is essentially.going to insert row by row by row into.our demo table so instead of doing that.I want to do this as a batch based.somewhat changes to a fast load and it's.going to batch the data in vs. are doing.a row by row kind of insert okay so.we're gonna see a bolt insert now ended.m-club we'll come back to these settings.right here a little bit later when we.get the performance tuning and stuff.it's when I hit okay.that will speed up our component quite a.bit on the right side we're seeing an OE.B and pan transform so if I open up this.this is a now this like the editor we're.seeing right now is called the advanced.hole editor for already become and now.this is the advanced editor is because.glued I wrote this component doesn't.really want you to use it because they.did not build you a UI if there's no UI.inside of SSIS it shows us this.component right here is advanced editor.we're seeing right here so we want to.find them we want to hopefully find a.some interesting things around this so.so the as you're seeing here is.basically giving an update a row by row.update so listen this this already.become an transform is essentially the.patron saint to all things that are.sucky there's a few reasons why the the.only two become and transform.essentially will do a row by row update.so it does a row by row cursor.essentially against our system but.not only that but it is going to open.and close the connection for every row.going through it so it's going to open.and close and open a connection run the.update close a connection open a.connection run the update close a.connection and if I had that five.thousand times we're going to be.pounding the heck out of our table so.the one way of fixing that is there's a.as one low property that we can set to.tune this a lot better so that's setting.is we can go ahead and select this this.component right here that's this desk.this destination right here this is data.warehouse and the property you can set.which is an advanced property over here.is retain same connection if we were to.change that the true ultimate it's gonna.happen this only the command transform.is gonna still suck but at least it will.suck a little bit less it's going to.keep the connection open during the.whole running of the package now there's.a pro and con as as a few guys are.pointing out every Perl of the con right.the pearl of this of setting that.setting it keeps the same connection.open and it's going to speed it up quite.a bit the con is it keeps the same.connection open so this package runs for.three hours and this is only a twenty.minute process right here about three.hours it's keeping the connection open.and could potentially cause some.problems that's why they said that that.property to false by default let's go.into this guy the insert destination I.had a few people here for like Bob for.example made some paid some comments and.then come back these in a second but.just note I got a stable lock property.on it will still it can cause some havoc.in there if I have a lot of rows going.into that also so the pro is it bulk.inserts the data end the con is it's.it's gonna escalate my locks quite a bit.in this case so it's gonna do it much.faster but it will and it's all or.nothing as well right to a few rows fail.I could have some issues there as well.it's like it's basically a bulk insert.command so just like bulk insert command.has the same flaws of the bulk insert.command so every pro has a con in SSIS.just like the rest of technology right.alright so let's go ahead and I've tuned.that now by changing that one property.in the property.is the destination right here one click.on it and you'll see retain same.connection you'll set that the true now.you only want to do that when you have a.whole bunch of olivine command.transforms in your package okay.that's yeah and you're basically putting.lipstick on a pig it's the prettiest pig.out there but still Pig.so let's go ahead and change this guy to.a type two dimension instead and let's.kind of watch what happens to it so.let's go ahead and uh bollec on it the.only change I'm gonna make I'm gonna go.ahead and hit the next button only.changing of the make is when it changes.from a changing attribute and to.historical attribute so I now care if I.change the club name do I get more.people because of that that's the whole.reason to keep track of history right so.is is this person having more sick time.now that he's married now is already.having less sick time for example so you.might have two versions that employee.right the unmarried version of the.employee and the married version deploy.our customer and how is that customers.our employees buying patterns different.now versus after he got married or she.got married okay so kind of some.interesting stuff that can do with.analytics around that so when I hit next.it's going to insert a brand new screen.inside this the new screen alright so.the new screen is going to show us it's.gonna ask alright what is the in do you.want to expire the record do you have.for example an end date like this start.dead end date or do you have an active.flag like you're seeing here in the red.alright so we have a little bit of both.in our case I'm going to start just with.the start date end date because you can.only do one this wizard at a time and.they'll have to customize everything.else so let's do this start date end.date that's them but that's a hardest.one to do so also like my start they.call them my end they call them so it's.reading these two column names from my.dimension table you see right here okay.it didn't make that didn't goes up now.what date do I want to set these to this.start date end date too.well I want to set it to the container.start time it's the most granular I have.there's a few things you.want to avoid here's it's showing us all.the variables that are date times so the.variable you care about here is the.start time in a package or the container.start time make sure you never select.the creation date of the package.otherwise this is going to expire the.record - whenever you created this.package not - when the package ran the.container start time is when this task.began so it's the most grantee granular.you have as other ways we can set this.off so we'll show it will show later.let's go ahead hit start are not excuse.me I'm gonna miss buzzy the rest of.wizards the exact same wizard now it.creates some code now that's slightly.different but it's ultimately a little.different angle here so what we have now.you have our type two updates going on.the left side our new records go down.the right side so what we're seeing here.is in this case if I want to expire a.type 2 record an expiry is really an.update plus an insert right I think I.have to either go ahead and set this.this record you're seeing right here.this glee club right here to 0 and then.I want to create the new record after.that so yes when I do an update I'm.gonna see an update here.right there and then an expiry undernea.insert down here so here's what all the.drive column right here gonna say all.right well what do you want to go ahead.and expire what this is - my container.start time you just saw so you could.customize this to be whatever you want.here the next piece is it's going to go.ahead and expire the record so here's my.update right here I'm seeing case start.me one update this again and open this.up here so we're seeing it's saying ok.so it says update set into indeed equal.to question mark and that question marks.me past in and then I'm going to set the.active flag since I'm expiring this to.zero this is how you kind of customize a.little bit more here alright I'll hit OK.so I've now expired the record that's.gonna come it's going to merge back in.with the new record it's a new data and.then now I'm going to set the start date.here's my start date right here equal to.the start time of this package or this.container and I'm gonna I'll do active.flag here and in this case to be true.now so now I'm going I'm turning on the.new record coming in to put alright then.I go and insert the data right here now.look what the color I'll come back that.sec out there so you'll see his start.date end date looks good let me go ahead.and drag active flag over so we can map.it and then we're done so I now have.both the start date end date and the.active flag keep by the end date will be.null right now because I'm turning this.record on in this case now liquid also.happened it went ahead and turned it.back to table our view so essentially.every time you go through the wizard it.is going to drop all the code you did.and then recreate it all over again so.I'm not to go ahead and go too fast.loads that's one of the biggest cons of.this Wizards is that it's it's.ultimately going to drop and recreate.the code big big con they're huge con.alright let's go ahead and run this.again we'll and we'll see almost nothing.happening right now alright it's gonna.go ahead and run that there we go all.right get 114 rows came in nothing came.out let's go ahead and make a change.real quick let's go back up to our club.table here and let's change this back to.let's make this the Brian fan club.versus a Glee fan club let's run that.again so now we should see hopefully a.record go through this left side and.then out the right side let's see what.happens alright there we go.so there's our one record that was.expired and our one record that was.inserted so let's go back and see what.happens here now there we go so it's a.little bit confusing here because I have.a bug in my data right here but let's.assume these two records are the only.active records that we have here let's.ignore that guy right there alright so.we have the Glee fan club that was.expired and the new Brian fan club that.was created that is now the active.records the active record as you can see.it has an active final one and it has an.end date of null so a question around.our version of sequel server I'm running.sis 2016 however the the same UI applies.really 2005 and greater they have not.altered the UI and any sizable fashion.for this component in an sis 20 2015 so.look it looks uh looks pretty lame right.or is it you I goes the same flaw in.since 2005 apply here as well ask your.question right off a few other questions.we have here as a system version okay so.we have we have how the SK get assigned.those from the questions I'm Scott this.SK is actually identity column so it's.getting auto assign by by my my table.here in inside of inside of that so.there's other options we have as well we.have a a product called task factory.which has a surrogate transform that's.one of the free components of that that.you can actually assign them in in sis.if you want also but grow the identity.column is probably the best way to.assign those Scott question here from.about a history table so Robert what if.you wanted to insert into a dim history.table that's essentially I see the point.of dim history tables that is.essentially what we have here is where.we essentially have the probe they need.the before and after image of this some.people prefer to have that into a.separate table also showing this if.that's the case you can branch off right.here in spirit watching you can branch.off and do an insert more easing some.conditional splits into that left side.also okay.why asked Microsoft not updated this.tool.since then so good question I wish I.think well you know we build a third.party component called task Factory and.we have a a refined version of this.whole logic because some columns.couldn't call it a dimension merge that.we built so that makes us about nine.hundred times faster fill and the reason.why Microsoft hasn't to Mitch this.submission merge right here that we have.is we have some dirty pictures on on to.the developers at Microsoft and there.are a few we have some incriminating.evidence and they won't improve it.because of that it's a bad bad joke.anyways it's it's uh I don't know.unfortunately they have base they said.we have a solution here for you guys.there's some third-party solutions out.there and because of that it is what it.is I think we're almost at breaktime.Devon are you there okay okay we are at.at breaktime now so let's go ahead and.do a quick five minute stretch break and.let me go ahead and pop in real quickly.here a five minute break and we'll come.back and we'll we'll finish up this.example and a few others I'll put five.minutes on the clock now go stretch your.legs get some water and I'll see you in.five thanks a lot guys.you.hey Devin either I am can you hear me.else I can are you sharing let's see.thank you.alright good deal so as we come back.from break here brains gotten a good.start on loading a data warehouse and I.have some good news so I saw some people.in the chat talking about you know.should I stick around here if I'm pretty.new to SSIS and kind of wondering you.know is this a good fit for them so what.let me make sure I'm showing the right.screen here here we go what I like to.show you guys briefly here is a little.bit about our on-demand training we're.going to jump right back into the.webinar that Brian's doing so don't.don't worry I'm not going to talk for.too long here our on-demand training is.actually something that we're gonna be.sending out to everyone in the webinar.today a special offer on and it's our.subscription model that we have for.training so you're attending some free.training right now that we do on a.weekly basis usually it's not three.hours like today but we also have paid.training which is much more in-depth the.topics that Brian's covering today we.also do much more in depth and even we.have a full class that we're going to be.designing around loading a data.warehouse and we're almost close to.wrapping up a course on designing a data.warehouse and and really doing more of.the database design component so if.you're interested in some of that I.encourage you to take a look if you go.to our website at pragmatic works comm.you'll see on the homepage where you can.get a trial to our on-demand training.and it's very very nice there their.lengthy courses you can see as you go.through any one of the courses they.range from a three to four hours to some.of the courses actually are up to about.14 hours so we have a variety of.different links just depending on the.topic and you'll likely see if you work.with Microsoft sequel server and bi.there's a lot of topics in here that.really fit into what you're doing so I.encourage you to take a look at the.trial it's a fully functioning trial.that allows you to go through everything.that we do in the courses and you can.actually take a look and they're all.video based training along with a quiz.assessments that happen after each one.of the modules so I encourage you to.take a look at that if you have any.questions you can also email training at.pragmatic work.I'll be happy to answer any those and.I'll leave it at that when I make sure.you guys have enough time for new.material here yeah and this is a this is.actually part of our masters it's sis.class that we're teaching right now.so exactly exactly yep absolutely all.right all right cool oh my god when take.over make me present there we go make.myself the presenter.all right well make it make welcome back.guys hope you got a good bio break there.and then let's let's get back into it so.we left off we were loading our.dimension table and we have a boatload.of questions I'll I'll go ahead and.watch people that are already already.issue subscribers that looks like also.that's good so the dimension table we.have so far this this this data.warehouse load we have has some pros and.cons to it well so the pros are and a.few of them a chat window right now also.the pros are it's easy right we were.able to pump this out in what 30 seconds.but next next next next next and it.finished its job the cons are if I went.next next next next next it's the one to.wipe away all my code and then we add it.again that's one of the big cons the.other con is when you look at this and.first of all this component right here.it's doing a row by row update we have.this guy right here is putting rows into.buckets and it is it is a very very slow.process because of that puts new rows on.the one bucket type to updates into one.bucket type one updates into one bucket.and so on and so on and so on so it.creates a it's a very slow process.because of that so this scale has some.scalability issues so you look at the.the overall the weakness of it that.needs 12 you saw me it has as a.maintenance issues and it has some speed.and scalability issues so this will.scale to about depending on this it's.all depends on the hardware you're.running it on and all that but generally.no more than about 50,000 rows that are.going through this you'll see it's.really sort of struggle you'll see it.like take take you know potentially.hours to run versus seconds like you saw.mine run so and.send every single row in the base as.well so it has a lot of issues but it.might solve about 80% of your problems.by having this and maybe 80% will be a.pretty big savings on your so you can.you can pump out you know a dozen.dimensions a day that are the easy ones.but in that big products 2 dimension.every company here that's on the phone.right now has that big old dimension.that are built able that you all groan.about.when you think about you know what we.have coming up when we're designing.designing some work so those ones need.an alternative design and there's a.number of ways of doing that so I'll.show you some some ways of kind of.building your own you can of course to.do some T sequel and and use as some of.my friends on Chatwin are saying right.now you can course to that as well.I use ask or procedure or instead of a.CD using the merge syntax for doing that.but it definitely it's nice you know.this is kind of self documents so let's.kind of go into this alright so one of.the alternative designs is using things.like a lookup transform to find out is.the road there or not now this is only.for a type 1 update here but it works.fairly well we're basically talking.about the up serve pattern so the absurd.pattern is looking at our data so rows.come in and we say alright do I have.this row or do I not have this row and.we decide do i if I have the row it must.be an update or might be an update if I.don't have the row it must be an insert.so essentially we'll have a series of.lookup transforms and a conditional.split to kind of split those rows so.let's kind of look at that what that.model looks like I'm gonna open up this.up cert item you're seeing right here.and I'm not gonna build this from.scratch I'm gonna kind of open this up.and kind of walk through it instead I.have a whole bunch of transactions let's.go ahead and you'll see I'm pulling this.production transaction history table.here I'm just gonna give me all the rows.from that table let me go ahead and make.some drastic changes to that table first.of all so I'm going to delete oh let's.help so I'm in the right database here.there we go I'm going to delete about 80.1000 records and I'm going to update.about 800 records here in this case.okay so we made some pretty sizable.changes here just to kind of make things.move move around a little bit here so I.had to get all those all those rows sink.back back down here and you're saying my.new rows are going left my updates you.want to write and we'll come back to.that in a second here so I'm sending my.my new data my source table is going.into my lookup transform and you can of.course use merge lookup it's essentially.doing the same thing in this case if I.go to my connection this is my.destination table in this case it's.called CDC transaction history that's my.destination table and it's also in the.higher ended way so it's not only a.full-blown don't that bone data.warehouse so this is going to apply to.really do this trying to sink our eyes.and you up certs will so whether it's.type 1 dimension or whether it's just an.old-fashioned transaction table it's.really matter the up cert pattern.applies a lot on different things.I'll go to columns and we'll see I'm.mapping based on my primary key so the.left side is my soror system the right.side is my target table and notice I'm.aliasing all the tape all the columns.are linked in the right table as target.underscore so I can see well here's what.the value wants to look like here's what.the balance here - the value must look.like over here here's the value looks.like right now.so this says target underscore I know.it's the destination now one thing.you'll notice if I go back a screen here.it says that if I if I can't find a.match is going to ignore the failure.right here this essentially does a left.outer join or essentially it's going to.say if I can't find a match set it to.blank so that the null so if I if I try.to look up the cuspid the transaction ID.and I can't find it on the right side it.will essentially null it out all the way.down so all of my target underscore.columns will be blank.all the way down and that's how I can.tell if the record is already if it.record is not in that target system okay.so in this case I'll look over here and.this is my conditional split so this.should be three conditions here either.I've got the record it's even or either.it's a new record so if that target is.null but I just talked about then it.must be a new row.if it if this column is not equal to.this column or that's a no that's an or.right there this column is not equal to.this column or or or or or and a hand.cramp later if all those columns are.unequal then it must be a update.otherwise it's a duplicate record that's.my old condition right here okay so.there we go let's go ahead and hit OK.now in this case by the way is that this.this this this lookup transfer my hand.here this transaction ID would represent.my surrogate key in this example.alright so my new data goes this way.okay classic little fast load turned on.everyone's better there and my old Leiby.command goes this way for my updates so.I made a whole bunch of updates let's go.ahead and run these and let's see what.happens all right let's turn all my.tasks inator choose we can kind of watch.the performance my portal laptop here.all right there we go.we're eating up a lot of CPU right now.about 80% of my CPU about 20% of it is.on is being used by the consumed by.sequel server and look what's happening.I have about a hundred thousand records.total to update but it is just spinning.and spinning and spinning right now.nothing is happening now I want to stop.it here in a second but this trust me.this takes a long time to run and.there's some reasons why now a forced.course first of all you have this uh.this notion right here of a cable lock.happening so let's let's let's turn that.off first of all this one kind of.demonstrate that how big a mission that.could be so let's go ahead and turn off.table lock I need my updates again there.we go alright let's spend this bad boy.again let's see how we do this time.there we go we're still spinning though.but it's not a locking issue so the.challenge we're having is this old lady.be command transform again is a patron.site of Pincus eighth of all things that.are sucky it is it was written by the it.was written is a terrible idea maybe a.bar bed or something.it is ultimately doing a row by row.update at the same time to try to pound.the table so it took it did finish but.it took about what 27 seconds or so to.finish and once I turn off the table.lock there's got to be a better way now.the alternate rule I want you to get out.of this is you should avoid using this.only to be command transform if at all.possible because it is a very very slow.process to do so the way around that if.I were to go to my control flow again I.have as a whole other concept here let.me open this up real quick it looks it's.the exact same code you just saw but.this time the only difference is there's.no only two command transform this time.the only DD command transform has been.replaced with a old lady B destination.so I'm inserting this time into a.staging table in this case and then once.I inserted the staging table I've landed.my records and then I can do a big set.based update with good old fashioned T.sequel so this should look familiar for.most you guys just go old fashioned T.sequel just doing a an update to that.same table and I'm saying it will.Quentin truncate the stage after I'm all.done so it's a 1 2 3 it's a two-step.approach so the lesson learned here is.you want to get the update out of the.control flow and get them into start to.have a data flow and into the control.flow so by doing that it's going to.speed it up immensely so we're at 27 28.seconds before let's see what happens.this time alright again a hundred.thousand this time new records 800.updates let's run the whole container.here all right we're at 2007 before I'm.guessing we'll be a little faster now we.hope so take a wild stab when write down.a little sticky note or something how.fast you're good if you think is going.to be much much faster moving the only.the command transform off its gonna make.this it's kind of make a mental guess to.yourself and we'll slide that here.alright here we go.hope your side bets are in let's go.ahead and run this container I have a.private betting problem I guess there we.go it's doing my ends my updates already.done so my updates of our.and my scent based update occurred and.all that occurred in 2.9 seconds so much.much faster process to do a big a big.update in this case so again the theory.here is you want to get those only be.commands commands out of there and.you'll find it much much faster now this.is such a common pattern that we.ultimately we decided to create a.component all around this and.essentially this all you do is you just.kind of whack all this this out here.kill that and this one show you how how.does how easy this real quickly here I'm.a dragon up cert transfer mode oh it's.part of task factory wire it up and when.I watch how easy this is I'm on a small.spend 30 seconds on it's so easy I'm.gonna create a connection in parts.that neck connection here so I'll just.point over to my my same database I have.I say 30 seconds it's kind of good no.helps if I can type it in there we go.alright I tie if I wouldn't hit ok it.okay I point to my table I want to load.look.there it goes wait on my table which was.a transaction history I wire up check my.primary key right there and then I'm.done so all that code has turned into.that one component I'll run it and this.scales really really well it's about 800.times fast from the previous example we.have I have an example the way I pull.about you know about 120 million records.do this this this component now what's.happening here it's doing the exact same.thing as that previous example was doing.it's essentially landing the data it's.doing the big set based update but all.inside of one component step so that's.that's a little task factory officer.component but it's doing the same thing.as what you saw back back here undo undo.and undo so same thing as this but.waning the data just from one big.component versus versus two components.okay all right so all right so the.question again was a so how does a scale.for how it scales a whole bunch of.questions you're welcome to kind of come.back to the so this is this is a way of.kind of causing your dimension table.your ups or patterns to kind of scale a.little better.now hopefully if you're doing that.staging table you have an exclusive.locks on the stadium table at that point.and that should really make it where.you're you're you're speeding it up.quite a bit.okay so additional pieces of we can kind.of tweak it's really around you saw a.while was doing this this this component.right here when I was doing that.conditional split I was typing you know.if this is equal to this if this and.this equal of this and so on and so on.and so on I was getting a hand cramp.over time so this whole process right.here doing that for every single column.becomes extremely boring to type in you.guys definitely a headset kind of moment.right there so really be paying the.button so one of the options we can do.here is we can use something called hash.bytes to kind of look at this and see.what what what we can do so there's a.few ways of doing that let me go ahead.and open up open up the table here let's.open up and let's see here the club.table again okay I'll pop-ups do this.table let's see we got into the table.all right so here we go so one of the.ways we can do this let's this grab give.me the first name and last name and I'll.take the gender somebody that and I want.to find out when is something changed.between these two these columns here I'm.going to send this data set in and I.want to find out hey how does it look.now so one of the ways we can do that is.we can go ahead and and wrap all of this.into a big old check son so if I were to.do this right here and wrap it in like.this and run this again look what.happens what am i get out of this this.is one of two ways of doing this we get.a number that's all the way down and.will tell us what is the value of that.these three columns unique about these.three new key columns so is there the.pro of this is it gives us one number.that represents these three columns.combined the calm of it is it sometimes.not guaranteed to be unique so we can do.is we can go ahead and type in a hash.bytes and so this time we're gonna go.ahead and append to all the columns.together alright a little bit of your.logic.and this the pro love this one is it is.it's got a really high granularity being.unique here.so we're basically hashing the data much.like you you see with a lot of data.stored in the database so there's now.hashed and because of that gets grid.back then a squid is pretty much.guaranteed to be unique so you don't.have a collision in this case the common.hash my it's a little bit slower to get.a hash than their to get a checksum so.what you would do in this case is you.would have actually have an example of.us actually hash bytes so in this.example it's absurd example you'll see.it might oughta be source I'm getting.the hash bytes back right here so you'll.see it right here where I have there's.my hash bytes right there hash bytes in.my only be sourced and I'm doing a pipe.delimited there to make it to make it.where it can and we can see all the.columns to combined so I have my hash.bytes they're all my source and I do the.same thing and I look up transform.you're seeing right here it's a hash.bytes there I still want to I'm still.gonna connect based my primary key mice.my alternate key here and then I'm.getting back the hash by column right.here so ultimately in my destination.right here all I'm doing is comparing.hey is my source hash the same as my.destination hash if it is I'm golden so.it's a way of it makes this scale a.little bit easier and there are some.some dirty habit and you get task.factory has this hash transform right.here I think this may be at one of the.free ones also there it is right there.and one of the things that does it is it.hashes data for you automatically.you don't have to worry about hashing it.man look man you like I did so we can.hash a flat file you Ashley I think cell.spreadsheet whatever it doesn't really.matter.there it is hash transform so I believe.that's proper Community Edition right.there as well so that's one way of doing.it and and why shouldn't when is hash.fight that she available I've been using.it since 2005 I have a not used before.then but I assume it's been available.for a long long time because of that so.it's it's been around for a long long.time it's a hash fights makes it we are.not not having to.to find that there's lots of other.options you have around this that we'll.cover a little bit later also okay.so let's jump into our next slides okay.here we go so the last piece here is.really around a fact table look how do.we do a fact table load so if we're.getting data to load a fact table what.we're going to what's going to look like.is we're gonna have all the alternate.keys so they keep on our source system.is sending us all right I have a sale.for Brian and here Brian was for $45.here's Brian's alternate key here's the.primary key from our sale system for.example and here's a product he bought.and so on and so on and so on what it's.not going to hold is it's not going to.hold the siree keys so a fatter table.load is essentially a combination of a.whole bunch of look of transforms for.every servic key you have you're going.to have a look of transform there and.then it's any other derived column.transfer at the very very end which it's.job is going to be to to find a to say.oh I couldn't find this record what do I.do now do I go ahead and do an inferred.number of patterns or do I set the.negative one so is it a couple patterns.like that now you might have to.aggregate the data up but maybe you're.at the product level and just want to.see the the lineup that the total sale.level oh sorry so let's go ahead and try.that real quick I had an old sound there.it kind of scared the heck out of me.there all right so here's what I got and.when I post on on our blog later today.is let me go ahead and go to the.location okay I have a script in here.called okay where to go I'll post this.today is called I'm looking rather as.theorists create unknown records for.procedure this is a really cool store.procedure so you'll notice that when I.look at my dem student table for example.okay.the right date awareness today some of.the money to warehouse when you go to my.data warehouse and you look at them.student the first record and all my.tables is this negative one guy here.this is my catch-all record so if I get.a transaction for somebody who I don't.recognize I'm going to go ahead and set.it to negative one to somebody I don't.know I don't know who this is.alright so because of that I need to go.and create this record and all my tables.this becomes a big pain in the butt.can you imagine Steve had a had a table.with 400 columns in it and can you.imagine going typing the word unknown.400 times for every single column in his.tables that would be a nightmare so one.of the things we can do is you can cheat.with aspergers teacher called USP crate.on the record what is it what's it gonna.do is it's going to point your club.table it's going to go ahead and create.that unknown record if it doesn't exist.or not so it did it has a created one.row was there now now I have nothing.there there's not going to be response.back now so what it did is it basic rig.that unknown record for me if I were to.type the word script here instead it.will ultimately show me the code in this.to this Ram so it he says a cogent tool.for you so I'll pull this on the blog a.little bit later today he'll be on.pragmatic works calm underneath the blog.section on pragmatic course calm this.was developed by us a long time ago but.it helps you know pretty a lot it's code.for you so you don't worry about it so.now if I were look at dim Club okay.den Club there we go.you'll see there's our negative one.record for dim club with a club name of.unknown okay so that is one option here.for our fact table so now I want to go.ahead and load a fact table so let's go.ahead and hop back over to our slides.not outside but our our demo here let me.open up this fact table here alright.okay let me.and start from scratch here this you can.kind of see see what it looks like so.I'm going to load the fact list club.table so so far all I've done is I went.ahead and I just have my everything's.aliased Club ID student ID its aliased.as what they are in the data warehouse.and keep mine these are these are the.primary keys from the source system the.alternate keys in other words but it's.kind of my source system not from the.data warehouse so now I need to look up.for every trance every certain key that.I have so I have to sort keys understand.a word how stable the data warehouse.table looks like this guy here it's.called fact list club if I can find it.here mind the rights yes I am not in the.right table that's a problem.so here it is I hear fact less Club.there it is and their columns I've got a.club SK and a student SK there's no.measures in this a set that's why it's.called a faculty law it's just that it.is showing the mapping table which.students are what a partner which clubs.so if I go into my I'm going to add to.lookup transforms okay all right so I'm.sorry does look up and I looked up again.and we went why are these upper quick.here we go.I'm gonna send this to the matching.output of this to go the next one all.right so let's let's go and look at this.I'm gonna ignore the failures so if I.can't find a match for my club table for.example I'm going to ignore it and let's.go ahead and point to my club table.again I'm never going to select this top.option in this case I'm just gonna do it.the old-fashioned way so I want the club.a K and the club SK drum I didn't Club.table Mizzou middle closer so you guys.can see that it looks like this let's.get an error there isn't why a 10-point.might in a warehouse there we go.so it's one more piece I'm missing here.one big big gotcha that I forgot and.that is I forgot to only get the active.records so I need where where in date in.my case is null that's to tell me that.it's as an active record so now I'm only.getting the active records so I have my.club a K I Club SK.and I'm gonna go ahead and might get my.columns here and map those up Club aka.the club okay.again the left is my input data for my.source system the right is my dimension.table I'm gonna get out that's Ricky and.I'm gonna go ahead and hit okay that's.one down one to go so I'm gonna just go.and label that so I can keep track of.which ones which here I'll call best is.Club and then I've got my last one which.is gonna be students so same exact.process this is the type one to mention.I'm gonna got one to ignore the failures.this one's a faster one will be lazy.here and select the clock the student a.K okay and students SK from dim student.all right.let's go ahead and go to columns here.how much you doing something wrong there.and I again I am picking the wrong.warehouse that's where all right.student okay the students make a and get.up yes K so for every search you have.you have the same exact pattern.boom-boom-boom-boom over and over and.over again.so this is where staging data might.actually make a lot of sense and I'm.going to show you some examples of that.in a moment also because we if you have.20 different joints here to do that's.gonna be a really really long process so.our last step here is to go ahead and.what if there was no Club in one case.what if I got an oil back well in that.example I want to use a derived column.transform to set that null to a negative.one it's going to wire that up.okay matches again all right it's been.double click all that you know so well.why do I want to say all right so if the.club comes back is null.go ahead and set it to negative one so.is null club SK so if it comes back as.null yeol and this is the old-fashioned.expression language for us is a little.bit insane whoever came up with this so.it is a if it's null then go ahead and.replace it with negative one otherwise.much else is gonna be : go ahead and.keep whatever the club has.right now so this is an if-then.statement if if it's if the Clubman.skeins come back as an all and make it.negative one otherwise else keep the.existing value have there and not of the.same thing down here for the student s K.as well and negative one L student s K.okay so one two three punch now we.should not be inserting any kind of.nulls in the system.let's go ahead now and send this to our.destination okay there we go so our.destination and then our fact table is.all done so we got to point to the fact.list up in the data warehouse Brian into.the fact list club and then we wire up.all the mappings looks good hit OK and.let's run this bad boy now you'll see.this process it scales fairly well as.long as those dimension tables are are.under under you know twenty thirty.million records it's going to scale.pretty well because I'm only getting.those two columns the alternate key a.certain key out of each of these data.warehouses now this also is happening.all in parallel so what the way science.works keep in mind is 10,000 rows are.time for coming through this so as I'm.pulling Road 90,000 right here row.roughly keep my row 80,000 is being.worked on right here and row 70,000 is.being worked on right here and then row.60,000 la Ronn excuse me row 60 k is.being worked on right here so all that.is happening at the same exact moment in.time okay so this happened pretty fast.there's some cons oh right every pearl.of the con and the con this time is a.pretty big con let's run this again.let's see if you notice it what is it.con here well if you haven't guessed yet.my biggest con is I just doubled my.sales here so I ran it again I have.eight thousand customers the first time.I have eight thousand customers again.the next time and every time I run this.I am adding there eight thousand.customers to my bottom line so.which sales were that easy right it's.run it and load it and get the get the.sales out.unfortunately sales aren't that easy.right so that leaves this pattern you.have to do a few things right you have.it's either truncate the fact table.ahead of the load which is which is a.big problem in some cases or we need.some kind of incremental load pattern a.some sort so the other alternative.so the and I'll show you the incremental.load pattern in just a second here I.forget that it's saying and getting this.as well.before I leave the back table though.there's one last option here and task.factory has that back table destination.you're tired of doing this ourselves I.wire up the two the two look-up tables.hails my type two dimension and a.handles mine negative 1 record formula.automatically for me here when I run.this bad boy it will basically view all.that logic forming and it also handles.if the row already is there it will.ignore the duplicates so I can run this.five ten times and it won't insert the.same record over and over again they saw.it it's a pretty fast component also and.it's doing something very somewhat.elastic comments I see in the and the.chat window right now it is staging the.data and doing one big fat merge syntax.on the destination so it's staging the.data I'm doing ten thousand rows at a.time versus one row versus one you know.one big bulk statement okay so that's a.fact table destination all right so.let's go back to our slides again so we.covered some of the some of the good.things about the back table load but the.biggest gotcha we had there is is we.need to basically find a way to only.send the data that's changed in so it's.a few options we have their questioning.from Bob up the task factory where is it.in a staged or works we staged a data it.is staged them to a temp table in this.case on the destination side so that way.it can use one big nap bulk statement.and even you can change that of course.you lent you that with that with a.statement all right so the incremental.load side so I don't want to be lied.enta Phi which rows have changed between.a certain time between this time this.time so imagine for example this applies.to whether you're pulling data out of.Salesforce.I will you're pulling data out of sequel.server it doesn't matter the pattern is.going to be the same.or what so what I want to show you here.is how do we avoid having to send all.8,000 rows every single time through our.process instead all I care about are the.15 rows that have changed or in the in.the 5 rows that are new so to do this.pattern we're going to have to have a a.there's three steps to it but we're.gonna have to have a last modified date.or a create date or a ride in any column.of some sort well need something to kind.of key on to make this pattern work so.this is the incremental pattern and.let's kind of show you what this looks.like so essentially then you can just.draw this whiteboard it's real quick.we're gonna have three tasks all right.here we go one two three so what we're.gonna do is we're gonna build a table.called our control table out here in.blue all right so let's call this.control table okay that control table is.going to have a role in it for every.entity we have so let's call this.customer here and the last time I loaded.customer so let's let's see here I'll.call this entity name and then I'll have.last load date so we'll haven't the of.customer and its last load was on 1 1 or.1 15 you know 2017 at whatever 4:00 p.m..and then etc excentric sent all the way.down so customer we'd have you know.sales we'd have etcetera etcetera.I have never loaded sales before so.it'll be 1900 in this case ok so this.this 1900s our default value in this.case meaning we have never loaded this.table before so these three steps my.first step is going to be a sequel.statement a sequel execute sequel task.staying with the last step here as well.well the first step does it says okay I.want to load the customer entity when's.the last time I loaded that entity it.will run a query saying hey but ones.last to my load of this it's gonna.return the date you're seeing right here.and it's gonna put that date into a.variable that we'll use in a second so.I'll put that call out you know last.load date.okay so it puts that date into a.variable I can store for later use now.mirandy go ahead and load my data.warehouse or my table or whatever I want.so I have this kind of flow task here.now instead of flow task is gonna run a.query the first step is gonna run a.query like CEO a select star from.customer you know where last modified is.you know greater than at last load date.some like that now you're typically.going to do between cause of some sort.we'll come back to that in a second the.last step is say alright I'm all done I.have loaded successfully go ahead and.update this table right here and no.longer is the last load date 1:15 it is.now 2:15 so I went set that to 2:15.versus 1:15 the next time I load it it.will know to go ahead and pull any road.it was updated since 2:15 not 1:15 and.so on so on and so on so that's how this.whole process was going to work but.those three steps now there's a whole.bunch of ways we can do these three.steps but essentially whether using.things like change due to capture or.what are you using your roll your own.it's still the same brief steps this is.this is the roll your own example here.now by rolling your own example like.we're doing right here this control.table you're seeing on the right could.be something that we store in sequel.server for salesforce.com.for example so for example for.salesforce they have a last modified.date and i have a list of all my.entities on the right and then when i.pulled it out of salesforce I checked.this table so I'm not pulling over all.my Salesforce records every single time.in the stage I'm only pulling the 40.records that have changed same thing.apply as a dynamic CRM more even things.like other kind of restful sources which.I'm kind of curious out there guys so.with the tomate salesforce.com and our.dynamics or maybe some cases you might.have some other kind of source on the.web I'm kind of curious does your.organization have any kind of systems on.the web also so we'll pull out there so.the last system modified date yet so.yeah salesforce has this like last mask.modified data's in Salesforce other ones.have some systems in the web also looks.like most of you guys here have.something on the web it looks like.you're hitting now most entities out.there that are on the web most restful.sources have a last modified date you.can't eat you can key in - there are.some some like that like ragas that is.pointing to one called cyst modified.date which is what what things like the.revocation uses also we can also use.things like CDC which will cover all.close in a moment also it looks like.more than half you guys about 59% of you.guys actually have actually have that.kind of ADA so say yeah baby out and.Salesforce happy - somewhere on the web.that you have to get in this into your.databases we're finding this being a big.big trend out there in the world right.now also as we built-in tasks factory.but we build a rest source and it sells.for source because we ourselves wanted.data out of things like GoToMeeting we.want to find out how many people.actually went to this webinar and how.many will care about this topic so we're.using the no rest for stressful sources.in sis to get data out of go to meeting.so okay so that's the biggest.requirements we need that last modified.data some sort.I'll show you other ways if you don't.have that last modified date how can we.get around that in the moment also so.we'll cover that in just a second but.you want to avoid loading all six.terabytes of data every single time so.let me hop out real quick and let's open.up this control table example so again.this the same three steps you're seeing.here are very similar steps you see in.most components the first step is hey.tell me the last time alright show me.the last load date from the control.table when my the table I want to load.is equal to question mark that's a.variable in sis question marks.represents some kind of placeholder I'm.going to pass in in my case I'm changing.that question mark under parameter.mapping and to a variable called table.name string table name Stacy saying.substitute that question mark with this.this table name variable now we'll see.that table name variable right here.it's called mice mice or stables called.source table great appropriate name here.right and oh sorry I click on that and.so if I look at that control table right.now if I were to look at my control.table just go ahead and get that real.quick out let's go take a peek at what.looks like now there we go so if I were.to take a peek at the control table you.go to one actually has it already open.there we go we're seeing that the source.table has was last loaded a this morning.go figure I'm actually kinda preparing.for a session it was last loaded or last.night scuse me last night at 8:59 p.m..so it's time to load again and typically.what I do is I also store things like.hey the number of rows I loaded in this.this extract and all that as well so the.next step is I'm now stored this answer.so I stored the answer coming up in this.query into a result set see the result.say here single row I'm storing that.into this variable here called DT last.load right there so that's not that's my.variable I'm storing it into right there.so now I want to use that variable.somewhere so let's go ahead and use that.inside of our data flow okay so inside.our data flow right here we are going to.run our query there we go you'll see now.in this case I have a little bit.different query so I'm say going to give.me the column didn't cut the column.names from my source table and I want to.get where the creation date was between.question mark and question mark now the.reason I'm doing between clause in this.case I went and open up parameter so you.can see it the reason why I want two.dates versus just one day I'm saying.give me all the rows that have come in.to the system between the last time I.ran the package which is this one right.here and then when I hit the Go button.on this package I'm basically creating a.window of time here and the reason I'm.creating a window of time which is which.is kind of keyed up a start time right.here is I didn't want to I didn't want.to run this pattern matches package.takes three hours to run.well what happens with the Rosic that.have been inserted while this packet is.running I want to make sure I put a box.of time in place to where I want all the.roads from the last time random package.until when I hit the Go button and stop.there and I'll catch those next rows on.the next run time okay.so that's why I'm creating this box this.window I count the rows that are coming.through me counting the rows right here.with the row count transform and then.lastly I insert them into a destination.so those three little simple possible.components I might do some perks I'm.cleansing or whatever in there as well.lastly I'm going to go ahead and update.the control table and set its last load.date and the rows insert it so I'm.setting as last load date as you can.imagine maybe to be equal to the start.time of the package so that now I've.kind of closed the window on the package.run all right so let's go ahead and run.this package and see how how we look so.right now I'll the gate we should see.hopefully no records coming through okay.we'll see zero records coming through.because there was nothing in there but.you see over here now I'm looking at.Valentine's Day at 12:45 so let's go.ahead and insert a few more rows into -.my source table here alright it's good a.higher end and go to my source table.well it's called source table somewhere.it's too easy to see the problem there.we go let's go ahead those rows alright.let's go ahead and do the beauties boom.boom boom oh I'd do about eight or nine.inserts here there we go our ten however.many and let's go ahead and run this.again I don't do that I just bring that.one component let's do this again I made.a mistake there okay here we go I made a.few inserts there.and let's go ahead and run the whole.package I am getting click happy today.looks like yeah cancel that.alright so 19 rows were inserted we can.now see in the control table that we had.19 roads inserted the last load day was.here so if I run the package again we.should see no rows inserted there we go.we'll see no rows coming down this time.hopefully beautiful so what I will do is.basically only get the rows set up.better to have happen to come through.now there is there's a few issues that.we'll have with this the biggest issue I.pull this open a source open again from.Raghav and myself here you'll notice.what happens if a row is deleted here so.we're not going to be able to handle.deletes so occasionally to Gary's.question here and it's a great great.question we're gonna have to have some.kind of reconciliation that we do once a.week once a month once a day whatever it.might be we say give me all the rows.that are over on the right side that are.not on the left side now we have again.we have some third party components and.tasks factory to handle all those kinds.of things but it depends like in a data.warehouse you typically aren't going to.handle do deletes you're gonna handle.things like you might do soft deletes.where you flag the records inactive but.you may not do deletes there's a less.important on datawarehouse side but very.important as Gary's pointing out on OTP.side so we'll both records will remain.in our reporting system because even.though they've been deleted which is.what you may want in most cases you're.gonna want that it's okay and it's real.quickly recap here I give the data from.my source system.I go to my parameters I say my first.question mark is gonna map to the last.load data of this package my second.question mark maps to why I had the Go.button on this package okay again the.sessions also be recorded so if you want.to play it back over again you can see.it over and over again there as well and.also all these examples and all our par.masters ass last class it's been part of.our training platform to feature curious.right so this is the control table.example it's very very commonly used the.route throughout for example when I load.salesforce.com.I have these three tasks over and over.ago over and over again into a sequence.container so I'll just have a container.like this and I've got an entity for.every entity in Salesforce I have these.three components and I'll put the.variables all inside of that and and I'm.gonna have it I just you know copy and.paste copy paste copy paste over and.over and over again if I can at least.get these guys in there I'll have all.three of these components it is refusing.to go in there isn't it oh well you get.the idea so I have all three of these.components all inside over and over and.over again for each of these components.so kind of a handy way of doing a doing.a Salesforce load if you have Salesforce.or half system on the web you would have.one per each entity that you have okay.all right so good good good spot there.let's go ahead and jump back into our.slides there's one more way of doing.this which we're not going to cover 100%.of right now as well let you know what.allude to it being there for you as a.tool and it's kind of covered the.architecture at least and that is that.is a change due to capture component.that you have in sis so change data.capture or CDC gives you the ability to.where you can go through and it.basically requires Enterprise Edition of.of sequel server now they're doing some.stuff to make a little bit easier also.now this is essentially using.replication behind the scenes and what.it does is when you turn on CDC on a.given table change you to capture on a.given table you're telling sequel server.watch this table for any kind of changes.that might occur and if a change occurs.it will copy those changes into another.table a CDC table and it will tell you.hey this is a new record this is an.update this was similarly trying to make.the lead of this records but keeps track.of those deletes for you in case those.those kind of events happen the CDC is a.really really handy way of doing those.kind of temporal kind of views of your.data it uses agent behind the scenes.sequel server agent.the scenes and it's a really really good.example for that I have an example in my.exam over here now it scales very very.well and uses less than a half a point.of CPU to do all this mess I really have.never noticed a big big difference on.CPU but you'll find that that SS is.actually has built-in components for CDC.that make it easier to do this now this.these are actually components they got.from a vendor called a tunity and it's.built into SSIS now and you'll see once.it comes up I don't know how she can go.over these but you'll see a a basically.same three steps right I want to I want.to start the package I'm gonna load that.load the data and that one a market as.I've done it's doing this same stuff.that we did with all the three.components but using CDC instead so.you'll see it right here since it's an.example right here right so I'm saying.give me the range of data that I have to.to load right there so CDC right here oh.come on what it's locked up right now.what's what's validating all this so it.says you'll give me the range of data.that I can process load the data and.then mark that the table that I'm.actually done this now so really quickly.that's going to show one piece of this.to you though and you'll notice that.instead of a control table I have a.table a different table here called CBC.States it is exactly the same thing.though so the same three steps that we.have here are all done the same way so a.question that we often often get asked.here is how do we deal with changes from.a CSV file for example or an excel file.or something that's not a normalized.system and those examples you're a.little bit more at the mercy of those so.you're gonna require that you use more.that absurd pattern that I showed you a.little bit earlier so let's imagine.extend yeah this of this being a a table.this was a this was a CSV file or an.excel file in that case you would do a.lookup transform against here at your.table and then you would send updates.one-way inserts the other way and.duplicates out to the drain the other.way so there's lots of ways you can do.that but the absurd patterns.the way you're gonna ultimately solve.that problem for the people they're.asking right now much Chatwin do that.one and that's that's also why you know.the up sir the officer transform with.how we built that component also was for.making it easier to do that as well as a.question Familia unless I actually.answer a few questions from next two.minutes here while we're getting ready.for our next break let's get what.happens the source 2 does not have a.modified our creation dates that is the.is a question the ultimate question.there right so if your source system.does not have a modified data or.creation date we're either gonna have to.do CDC like you saw and I pulled up an.example in the slides here right here so.we can do CDC on the table and it will.watch for that and it kind of it'll.create that that in that example I had.nothing enough questions he actually.tried try to show what the code looks.like real quick here I'm gonna go ahead.and create a database here call it CDC.demo and it's something a basic a basic.table here real quick and I'm gonna load.a few rows into it here just to kind of.get some stuff going here okay.my it's all done dirty sequel.unfortunately okay so so I'm gonna go.ahead and turn on CDC on that database.and then I'm going to the button this it.gets stored procedure here CDC enable.regular turn it on the database then I'm.going to turn it on the table here and.this and turning on this person address.table here okay.Oh agents not running so it's telling me.hey then we need to turn on sequel.server agent so let me go ahead and turn.that on there we go alright give that a.few.it's to start and it's in the background.telling me are you sure you want to.start yes let's go ahead and start that.all right with that now done it's going.to ultimately create two jobs for me.it'll create a job to capture the.changes and want to clean up a changes.so it keeps it keeps those changes out.there for a number of days you can.change the number of days you want and.then there's it's a cache of changes in.real time essentially so now if I go.ahead and select against this new table.at current here called person address CT.this is it this is a system table of.critter inside the CDC schema it could.it formerly when I turned on CDC notice.there's no data out in that table so if.I were to go ahead and insert a few new.records into the table there we go I'll.insert three records now I want to.update two records okay let's go ahead.and query that now so now let's do no.data out there yet I'm gonna everything.against a few seconds later.now data shows up in the table so we.have a new see operation here the.operation a 2 means it's an insert a 3.means 3 to 4 is an update combo 3 being.the before the update and four being.after the update what the roads look.like before you updated it and what it.look after you updated it and it shows.us here's the row right here here is the.operation and you could even see over.here it's a way of actually getting the.actual columns that were updated also so.you guys she find out was it a type 2.column was amount of time to calm so.this all this all comes down to the.question of what if I don't have I have.vendor system and I don't have a last.modified date this is so it's one of the.ways you can do that now almost every.database platform Oracle db2 they all.have is system like a CDC example out.there it gets very elaborate here and go.into a lot much longer I'm not going to.it much longer than this but just note.that it ultimately every database.platform out there pretty much has a CDC.kind of concept out there so this is.roughly how you can do it so you can.wire up to that table inside of SSIS.right here.okay now sorry right into that CDC load.right here now the question we get asked.is you will only have one CDC States.table it's just like your control table.earlier as well oh the CC states table.basically it keeps a row in there for.every table that you're monitoring.essentially this like the second source.table we had also so this is an example.we go into deeper in our master SSIS.class there's also some there are some.performance consequences of it yes there.are some great white papers we do have.white paper in our corn cereal that we.have a little later we'll share but it's.it's not that bad I have almost never.seen a performance gripe about CDC so.let me go ahead and uh we have three.minutes left.Devon do you want to do quick uh after.the break down before the break yeah.let's go ahead and do it really quickly.before the break well we have everybody.here I'll support myself for just a.moment because what I'd like to do with.you guys before we go into our last.five-minute break of the little workshop.here the virtual workshop is to talk a.little bit more about our on-demand.training I know I gave you an idea about.what our on-demand training was.previously if you happen to join us in.our previous break but if not it's it's.training that allows you to actually go.much more in-depth than anything we can.possibly offer in a webinar even a.three-hour webinar for that matter but.what I really be curious from getting.from you guys now that you're sitting in.here with us.is what kind of topics you're interested.in so we have quite a few core bi topics.as well as sequel server like a sequel.server high availability DBA.fundamentals and then we have a Cius our.SAS but I'm curious in what you'd like.to see in the future so I'd love to have.you guys help us shape what our next set.of courses are life so I what I'd like.to do that is by telling you briefly.about what we have on our roadmap so.what our roadmap looks like as far as.new content and then you guys can kind.of help me understand what's probably.the best next to the courses that we can.do so just to give you an idea of what.we have in our roadmap I mentioned a.couple of these previously some people.are actually post in the chat window.they were interested in a few for.example we're gonna be working on an.enterprise data modeling course that.enterprise data modeling course will.cover not only topics that you heard.about today like how to build out a data.warehouse.it'll also cover building out.transactional systems or OLTP systems.we're also going to be dual developing.some more Excel content so things like.building out Excel pivot tables so if.you have end users that are being that.are actually using and developing pivot.tables off of analysis service cubes or.anything like that.we'd loved it we'll have a course for.that coming very soon we'll also be.doing an excel modeling course which is.more around things with some of the key.functions of Excel and how you can model.it inside of Excel and then some courses.we'll have in the future I mean ones.like an introduction to tableau so if.you have any folks that are actually.using tableau an organization will have.a course for them loading a data.warehouse and master data services and.data quality services and then.PowerShell as well so we have quite a.few that are coming and then I'd love to.hear from you guys and you're welcome to.put this in the question box if you'd.like or the chat is let us know what.other courses you're interested I saw.one person asked about power bi courses.we actually have four separate power bi.courses already but there's a good.chance we could come out with some other.so if there's a very specific topics.with inside of power bi you'd be.interested in please let us know so as.we're going into the break room to put a.five-minute timer up on the screen.feel free to as you have any other ideas.of courses that you'd like to see or.that would help your organization even.when if if it veers slightly away from.Microsoft's as long as it deals with.data we are interested in working on.those so please give us some feedback in.the end the question and I'm gonna go.ahead and like I said put a five-minute.timer up and I'll be reviewing those as.those come in I appreciate a lot thank.you.hey guys as we're coming.a few seconds here I appreciate the.feedback that's actually really really.helpful so if you have any other course.ideas that you're interested in please.keep putting them in you can also if you.think of one later just email training.at pragmatic works comm and that'll also.be helpful I appreciate that.well welcome back guys I appreciate.appreciate it also for you guys my back.we are down to our last section of the.day so actually some really interesting.course examples here looks like they.also saw a few guys ask for demos of.more tasks factory pieces yeah if you.want that please feel free to email in a.CE moment send me an email excuse me.it's a benighted pragmatic works or hit.sales that pragmatic work someone we can.do a custom demo for you guys also.that's not a problem okay we have a lot.of questions here I'll get to you right.now I'll leave some of those so the.first one was when do we use a do dotnet.versus old lady be this is from mr. Khan.movies con that's a great question.typically you're going to use old lady.beat it performs slightly faster than a.do dotnet slightly because they're.getting they're catching up but it's.only a slightly different right now so.the ad unit net connection is required.for some of the newer components so I.love a task factory components the CDC.ones you see here is when the Microsoft.ones all require more of an ad or net.connection so if you can get away with.it do old lady be if not then idiot.Annette's going to go with I saw about a.five percent decrease in performance by.going ad about net long time ago that's.been a while since I tested that let's.see here.I'm not sure what these are topics I.guess excuse me.alright let's see I'll clean this up a.little bit here I had a question about.the CDC states table this table here.that that we're storing we're going.against here it's the same kind of table.that we did with the control table we.would have a row in this table for every.source that we want to pull data from so.those are that's an inert example right.there as well.alright well looks like that's most of.question I'm getting lost in questions a.little bit because they have all the.class examples as well so Oh what is it.okay here's a good question from Patel.here per Bowl it's a preferable.connection that you okay so what's.better as far as performance ODBC or a.link server you should almost never use.a linked server for point data up.because it is it's not as reliable as we.will find and things like sis you'll.find.you get occasional timeouts get things.like that that might cause occasional.problems be careful on link servers ODBC.or a lady would be preferred eventually.Microsoft is has stopped investing in a.lady be so you're gonna see ODBC being.the primary vehicle going forward but.it's not we're not there yet so it's.kind of got some time before they.actually get there okay.yes Andy Court are correct in 2016 they.actually did lower the version that CDC.required it required Enterprise Edition.2012 2014 now it is actually actually a.lot lot more ability now okay so a lot.of your questions here okay alright so.let's go and jump into I have so many of.them actually it's hard to keep up let's.come back into our slides again ok there.we go ok so if you've been with us if.you've been an IT for longer than you.know call it 10 years or so you've.noticed that actually all these.questions were getting right now or.actually pertain the section it's good.timing but he Ryan's question this got.there the a lot of these been an IT for.longer than 10 years you know that if I.were to tell you 10 years ago that we're.gonna put a terabyte data warehouse in a.sequel server back in the 90s you have.been laughed at right it's not a good.thing in time yeah it's banana days it's.pretty very very common all the day.based platforms are roughly the same.thing they all they all can support you.know multi terabytes but they also all.have a you know a much much condensed a.window to load data now before you might.have been able to load the data and give.it you know five minutes to ever have.five five hours at night now at law.cases a lot of hospitals have a.maintenance window of 20 minutes 15.minutes to get to the end for a probe.warehouse load so it's a lot some big.issues we have that's what this section.is all about it's all about how do we.load data faster how do you live more.data faster basically well I want you to.use cases around around that okay.so let's start with let's start with.some of the data source side how do we.tune the data sources.well the biggest the biggest bane of sis.is going to be doing things like sorting.of date and we'll cover that why but why.that is in a moment but the biggest.thing to keep in mind is don't be afraid.to use t sequel so things like sorting.data joining data filtering doing where.clauses if you know the old term is if I.had a hammer everything is a nail so you.want to avoid that whole concept when.you're looking at when you're walking.around sis trying to load data you want.to make sure that you're just not using.sis because you can use this is.sometimes it makes sense to do things.like a seating table in many cases so.let's cover some of the ways of doing.the sorting here for example.so you'll notice let me go and hop out.over to over to here okay so if I were.to open up my CDC example right here.okay okay so first of all if I were to.business this end pick your component.right here requires that the data be.sorted that's going into it so if I were.to do that right now I wanted to put a.sort transform between these two.components but I think let's just I'm.gonna hop in and I should do that real.quick so I would write a sword over here.drag a sword over here and why are those.guys up I would sort by the club a kay.and you see I was paying a lot of time.on this but it is taking a little bit.time to do this and it's not necessarily.the best way of doing it here's here's.our problem here and things like the.merge Joline requires you to be sorted.there's other ones that require to be.sorted in that case also the sort.transform is a fully blocking component.think about a deck of cards and I hand.you a a card and I say I want you to I.want you to sort this deck cards you.can't do that until you're holding all.the deck of cards in your hand so as.data is coming out of these two.components it has to have entire data.set and numerate before I can move.forward so imagine this I've got a whole.lot let's let's take the sword out here.let's imagine I'm squinting so we don't.get confused here everyone delete the.sword here so imagine I have a whole.string of components right here okay.looks looks pretty bad here because I.have terabytes and terabytes loops.everyone run way.I have terabytes and terabytes of data.I'm trying to get from here all the way.down at the bottom so what happens in.sis is baby comes out in ten thousand.row increments or 10 Meg's whichever.comes first and while this is happening.as everything is coming down here the if.I'm working on row 100k right here I.might be working on road 90k right here.real ATK right here Andros Tiffany.Canyon right here so everything is all.working in parallel to make this work so.this works beautifully if everything is.synchronous now if we throw a smart.transient form out here now I don't what.I had I need a 10 20 30 40 Meg's before.to make this that's two terabytes a day.to load into sis now I need two.terabytes of RAM before I make this work.because a sword transform has to see the.entire deck of cards before I can do.things so that's why you want to avoid.using a sword if at all possible now the.way you can avoid it is of course oh but.going up here adding an order by Clause.into your component now this is not good.enough the order by Clause is not good.enough an sis.you have at LSSI us today is sorted also.because this could be a store procedure.it could be a view it could be all those.types of things so if I were to open.this up and go to the advanced editor.this is how we tell SSIS that they've is.sorted I'll go to input/output.properties and on the source right here.the computer this is the advanced editor.if I right click then one of the.advanced editor this is going to show.I'm specifying that at the the green s.is my green line blue line this is my.red line.and I change on my blue line I say the.data is already sorted by specifying.that it now knows it is sore by.something then under output columns.under under the club a K I'll specify.that I'm sorting by this column first.now I could say I'm sorting in a second.column second by saying - on the next.column more that is an eighth sending.sort right now for this positive one I.can also say make it a descending sword.as well it's a negative one so this is.an easy way to make the sorting happen.and so that science requires a little.more work but the difference is a huge.gap if you're but if you're pulling.millions and millions of rows through.the difference might be a effort for 100.million rows it might take you 10.seconds with an order by clause but it.might take you 8 to 10 hours to do a.sort transform so it's a huge difference.in time to watch out for.ok so try to avoid using a sort.transform and instead use T sequel for.what it's good at and letting si us know.if what it was to do that as well and.the other option is for a flat file.there's this notion of a fast parse okay.so the fast parse option it's basically.saying that all right sis.I don't want you to parse the data so.what happens let me open up this real.quick and open up fast parts where'd he.go it's somewhere under right in front.of my face here there it is right in my.face I thought so ok so let's look at.look at this connection manager that we.see right here has through a couple.million records inside of it and what's.going to happen in SSIS is it's going to.parse all these columns to make sure.that it's actually a a true number or.true a true date so if this applies and.numbers and dates so if you have a file.that rattle arch millions and millions.of rows and and depth this.setting applies to you if you don't.don't worry about it so what this is.gonna do for us is it's going to say.alright I got the data from the database.already I trust that this data is good.so it's what it's looking for is it's.the parsing is looking for saying hey I.do you have the value of a coming in.this integer column or do you have a - -.2002 29 February 29th coming in so what.kind of bad date of you have trying to.filter that bad data up front versus on.the back side so if you trust your data.that's a big big if right if you trust.your data that we can do there we go.then we could do is we can turn on fast.parse now let me show you what it looks.like with for without fast parks on.bottom we go ahead and run this this.this data flow here with vast parts.turned off which is the default behavior.okay so you see it's it's cool it's.loading pretty fast.all right there we go so it ran the.million rows fairly fast not most people.can be upset by seven seconds neck you -.applies too much much larger data sets.here in this case it used to be this.example was when I tried if I didn't.have a solid-state drive the million.rows have taken about 30 seconds to run.it was much more impressive but let's.look at this how we turn it on with vast.parts on the first thing we have to do.is exact same package and then we're.going to go ahead and go to the advanced.editor again the advanced editor is all.the stuff that Microsoft doesn't.necessarily want you to see it's a stuff.that is kind of hidden behind the scenes.that wouldn't be very useful inside of a.user interface so in this case what.we're gonna do is we'll go to the.input/output properties we'll pick some.columns is Colin right here for example.and as you select each column we could.turn the fast parse property on right.there by turning that on what's going to.happen is it's going to skip the.processing of its get the parsing of.that and it's going to say about 15%.per column by doing that see they have.large flat files this will save you a.huge amount of time by doing that so.let's go ahead and see what happens now.all right let's go ahead and run this.bad boy and we were at what half laughs.poor farmer force eight seconds roughly.we'll call it.now if fast parts turn on let's see how.fast we do now.okay much much faster let's see how I.felt faster at least let's go back over.to our progress pane.so what from eight seconds down to about.three seconds so you can only pulled it.out so fast from that from a flat file.but it looks like at least as far as sis.times going it's it's speeding it up.quite a bit okay so so this is an easy.way to get a connection to go a little.faster now the other piece is if you're.pulling data out of a large database.whether it be db2 or a sequel server.excuse me well you pointed out db2 or.sequel server there is a whole process.around tcp/ip so let's imagine we have.on the left side we have db2 on the.right side we're trying to get data into.a sequel server over here now think.about all the stuff that's between those.two servers right now so between those.two servers we have it at a very minimum.we're going to have a switch right there.right all right now what happens here is.SSIS is going to get data out and my sis.is running right here on the sequel.server in this case and it's saying all.right give me to give me some data I'll.do this in green here I have green I.can't see it there we go it's gonna say.give me some data out all right so first.the first inquiry I'll give you - I say.all right welcome how fast can you talk.to you - can you can you go how big of a.bucket can you see me data it do you -.can respond saying all right here is.some data I acknowledge your request and.here is your first four.boom back and forth I acknowledge I got.the data.all right here's unit so this back and.forth back and forth that process takes.a long time and the default amount of.packets that we'll do is for K at a time.so if you're getting terabytes and.terabytes of data out of db2 and a.sequel server it will take a long time.to load that data so we need a better.way of doing this and one of the better.ways of doing this is to increase our.network packet size right here the.packet size and by doing this it's.ultimately going to give us bigger.buckets over the network than the small.buckets we'd normally get in 4k so let's.go over first of all to where we see it.so let me open up any package here has a.connection there we go size of.connection so let's imagine we're trying.to pulled it out of a pie read right.here now if I open up this connection.here you'll notice under the all tab.there's this one property under the all.tab alright it is called okay oh there.it is packet size it's right here in.green and by default it's set to zero.which means don't set it on or roll out.whatever the server tells us is is what.once so the application is not going to.request any more than 4k so we need a.better way of doing them so one way of.doing that is we can bump it if you have.a lot of data to the highest number.possible 32767 that's a highest number.we can drill it up in that value right.there and now this is going to be.sending a lot less packets over the over.the wire from from our sis so the.connection spring is going to change if.I do this let's going to show you the.connection string I'll put this 32767.I'll hit OK now my connections tree now.looks a little bit like this you got a.notepad here so you can see it there we.go and now I'm guessing the connection.string has a packet size equals right.there 32767 that same property works for.db2 and Oracle as well something similar.to that each of those path platforms.now by doing that what's gonna happen is.we'll be sending about much bigger.buckets over the network and so when we.didn't--we to the setup and a big.customer had db2 for example we were.pulling about two terabytes a night over.the wire our extract just my networking.perspective went from about about a.45-minute load to about a 15 minute load.so it was it was as close a thing to a.syllable as we can find to get data over.this now and now it required a lot of.work and the work it required is this.you can't you can set this connection.stream all you want but the network card.might not comply by it so in other words.let's look at my own network are now.this is a laptop on a server mind you.but let me look at my network card here.I'm plugging in the I am okay and let's.go to my network properties here made.for the adapter now even though my.connection string is saying you know.fully fully load me up 32k it's the.lowest common denominator is always.going to win so for example if I have a.have an impressing day a Ferrari down.dirt road is going a good with dirt road.pace and that's what we have here so if.I were to go to configure my network.card here and look at the advanced.properties one of those that property is.called jumbo frame and you notice in my.case is that he turned off on this.network card I keep mine this is a.laptop network card not a not a big.server one well that into that property.turned off right now is going to ignore.I'm asking larger for larger packets and.downgrade me to the lowest common.denominator if db2 had the same setting.or the switch had the same setting.turned off it is going to lower it'll.decrease you to lowest common common.common denominators so switches.absolutely have to allow you to have to.move jumbo frames turned on otherwise.it's no good this is no good at all.there's no no value at all doing this.setting so here's a bad part you got to.work with your network administrator so.you got to bring him some bring him or.her some beer and and ask them to work.with you on setting these settings.correctly at least on a local private.network here to where you can get data.out it out of your.systems faster okay but once you get it.it will make your system run much much.more efficient it oh I would only do.this if you have large amounts of data.to hundreds of many millions of rows to.get over the over the Wi-Fi or over the.network okay so that's called the never.packet size property you set it on on.the destination and on the switch and on.course your machine at your secure.sequel server as well you want to get it.on all three settings otherwise you.won't pulled it over in a possible level.okay the other piece I had a question.the channeling a little bit ago.hopefully its sleeve that thing after I.answer your question there I had another.question in the window earlier which is.actually pretty pertinent now how does.compression of that SSIS well.compression is is utilized all over the.place and SSI and sequel server now.where was a yeah I I would go to a class.and I'm gonna ask how many people have.here actually using compression.typically I would get maybe one hand out.of the hundred that would go up.compression does some amazing things.though compression will ultimately allow.you to pulled it out of us of a sequel.server so so much faster so come with.compression turn-on there's two types of.compression one is a page compression.which will compress each page kind of.like as if as if program does and also.one is a real compression where it.compresses the data type so I have a var.car 20 I'm only storing five characters.in there it'll compress that down for me.immensely or I'm storing the value of.one into an integer give a compress you.know three bytes of that to give you.only one byte out so page compressions.one that most people to use it I have a.repeating value Brian Brian Brian Brian.Brian over and over again it compresses.that into one value and has a check bite.in there for you so the pro is you can.get a huge impact on your select queries.so pulling data out of these systems is.much much faster for you all right so.that con is insert performance goes goes.down by about 20 to 30 percent so so.like performance goes up I have that one.big example I mentioned with.frames earlier there by turning on.compression we got about a hundred and.seventy five percent gain in so in query.performance and a polling data out of.our data warehouse such a fact tables.however pushing data into those.Waterhouse's tables went down by about.thirty points so it's important you know.that that when you do this.SSIS performance will go down on one.hand however you go up and actually on.the other hand as you're pulling it out.okay alright okay so as we go through.this let's kind of go through this one.more time here so really quickly to.compress this data and Jennifer I got.you I got your message I will answer.that question in a moment here to turn.on compression really really simple.all you have to do is you just right.click on a table and you go to manage.storage manage compression and then you.can basically build a script here say.I'm going to turn on page compression.hit calculate that shows you the savings.you're going to get from it in my case.it's about oh gosh pretty good savings.in that in size me about half the size.to typically you give up about a three.to one compression ratio is pretty.typical for this now when you compress.the data you can also that the middle.ground could be to have a partition.table where the current data current.month current week whatever is.uncompressed.and all the old data is compressed so.that's a middle ground you can do to see.you're not getting data you know in both.both both both areas okay so what's hot.back over here now we talk about the.does transform performance before about.loading data above and above and.escorting it from step by step a set now.this all applies if the data is.synchronous you'll notice that there's.actually three buffers being worked on.and even in a given point in time if a.data is synchronous all those buffers.are all are all being worked at Union.point time however it's not it's.synchronous there's a few components in.s is that are not synchronous it will.cause an accident like you see on your.on your screen right now so.an asynchronous component means it's.blocking the data somehow now there's.two types of blocking components one is.a partially blocking and one is a full.block the put a full block will be.things like the sort transform think.about that deck of cards right I can't I.can't do the sorting that you want me to.do unless I have all the cards in my.hand right now another one could be like.an aggregate transform where I need to.sum all the cards.well I can't submit I can't sell all.those cards without having every card in.my hand also so that isn't the two.challenges we have those components can.give you grief around that so there.aren't also support lis blocking.transforms so the partially blocking.transforms are where it basically is.going to empty the data into a new.buffer a new memory bucket and send that.new buffer on its way down so it blocks.a memory buffer at a time if you find a.buffer of data it's 10,000 rows or ten.mix whichever comes first.so that so that partially blocking.transform will be things like a Union.all transform where it has to create new.buffers on the way out because the the.metadata might have changed there okay.so well this might look like is this if.I use this partially blocking transform.right here you will see in the Union all.transform right here I was a hundred and.five seconds before this this case study.here I asked about 100 million records.and it went and when the performance.went down by about 15 seconds by me.adding that Union all transforms so the.you know transform it's not you should.avoid them but if there's a way I'll.turn the coding pattern around it and.try to do that if you can and my.alternative pattern here is basically to.ignore the look of failures and do an.if-then logic inside this dr.colin.transform okay so now we're getting the.destination side first of all let me go.back here on this one piece here the.question often times comes up can you.open up a your so this will work here.it's kind of pretend that we have a data.flow here so there's a few compelling.people properties here I want to show.you to see you know they're here.all right here.all right this is what controls how big.the buffers of memory are going to be.for you remember it's it's 10,000 rows.or 10 Meg's which there are accounts.first this is in bytes right here by the.way those 10 Meg's or 10,000 rows the.number you know which week is is the.10,000 number now I only tweak it if.you're seeing problems in performance 10.the right number is usually between.8,000 and 12,000 it's a probably the.right sweet spot for most companies but.so that's why 10,000 is right in the.middle it's a perfect number to start.with but if you have a packet of taking.more than 10 minutes to run but I look.try adjusting that 10,000 rows maybe.8,000 the wider the table and I mention.that 400 400 column table to come before.the wider the table below where that.number should be me what I'm gonna try.to get that good those in that buffer.going as fast as we can a narrower table.that higher the number should be we have.some spreadsheet guidance on our blog I.can walk through kind of here's what the.schema looks like here's how many rows I.have coming in how do I adjust the last.setting here is engine threads it should.be a pretty much a black box setting but.there's a few reasons why this might.come into play here let's imagine for.example I have me copy and paste a few.times all right oh all right here we go.I have a whole bunch of sources when.they use things like the important.export wizard for example it looks like.this doesn't it it creates a whole bunch.of sources it has a line going from here.to the destination over and over and.over and over and over again each of.these sources each of these lines here.they're coming out it's going to require.a memory buffer so let's go back this.despite tree dimension here again am i.they're actually they're already excuse.me all right so if we kind of look at.this right here something simple like.this we have one buffer being used right.here and then we branch off so now we're.using a second buffer here and a third.buffer here and then we can't join the.fray again so we're never a new buffer.being used here because this is.partially blocking and we still use that.over for right here and down and we.still use my four two right here.all right it's number two but one so as.you can see we're using about four.buffers for this four memory buffer to.forty minutes of data for this example.right here now well that's four buffers.in total flight here anyways the.challenge will have is if you have a.whole bunch more branching so let's.imagine we branch this one hey we branch.this way and we do a whole bunch of junk.and almost art threads right here so now.we're down to we have 1 2 3 4 5 6 7 8.yes it's so on and so on so if you have.a package that looks like this where.it's doing branching and branching and.branching and branching so on and so on.you're gonna it's gonna stall at some.point and you may notice the behavior.behaves this way things are working all.beautifully all of a sudden and then all.of a sudden spots one day you get a call.on the night for that processed on.what's happening if you've run on the.buffers and it's waiting for a buffer to.free up and it's caught in a loop.because of that so if you ever see stuff.get hung up like right here I'm.highlighting right here and it's not.moving on it's because you've run on a.buffers so you may want to adjust the.data flow property over here called.memory buffers I'll just end by default.at least in this version of equals.server so to think about that don't just.set unless you really see a reason to.adjust it but by adjusting it it will.occupy more resources more CPU threads.to do that but you want to okay very.rarely have to occasionally do that and.if you have a lot benching code logic.real large data flow for example okay.the other piece in the control flow in.the control flow property alright let's.go down here the other example here on.the control phildee they may speak of.one of them and control flow is max.concurrent executables this is telling.us how many tasks can run in parallel.inside the Vestas is and you're noticing.it it's right now set to negative one if.I were to put the value of four in there.and I had you know boom.boom-boom-boom-boom I had five tasks.running in parallel.these first guys here would run first.and this last had to wait for a thread.to open up before I can actually run its.run its task so this is a way that sis.can control to make sure it doesn't eat.too much the time on the on the on the.server the pounder server too hard in.other words I got cocky one time I was.pulling that up a mainframe you're like.oh I've got plenty of resources here sis.hardly being being touched right now I.haven't won at 10% utilization I want.more and all the tables for the.mainframe had no relationships involved.so what I did is I I said that that that.max concurrent executables about 25 it's.been kind of copy here well it ran.beautifully on the sis ID my utilization.went to like 60% I was pulling over all.the mainframe tables at the same time.however I was killing the mainframe the.mainframe was going with sneezed and I.didn't even know I was doing it so I at.least my process was so again I have it.I got a knock on the door or shortly.thereafter and asked me one stop.whatever I was doing so this setting is.is a one it's me you know kind of.careful be careful with this sometimes.but it is kind of useful to get a whole.bunch of things that are running like a.Salesforce load for example and they.gonna pull they down sales force and you.got a whole bunch of unrelated.components to an employer as fast you.can then set that setting but negative.one means is it means I want you to run.however many come in too many cores you.have so I have I have four cords most.laptops or four cords are gonna run and.currently plus two so if six tasks can.run in parallel so it's it's 2 plus a.number of cords that you might have.that's what uh that's what the negative.one means so it's a dynamic number so as.you go from dev to QA to prod and your.server gets bigger and bigger and bigger.you can run more tasks and parallel.because of that ok I had a question also.a few questions about that's all snow.earlier is tunnel amount less protection.level property right here so it's a.number one the thing that we see people.make mistake in at pragmatic works and.also at the stat.at ourselves we make the same mistake.also here's the behavior you're gonna.see here the package works great in your.environment and and dad the QA it works.beautifully and and dev it works.beautifully as long as you're honest.you're down you're down then and then.the minute you deploy this to QA a.package ceases to work so how how is.this how was what's broken here this is.the question from Jennifer and other.things as well.so she's in a team environment this is.what the problem she has you want.guidance on this and I had a few other.questions about the same thing here so.they encrypt sensitive both user key.what that means is it's going to encrypt.the package all the protections all the.secret stuff inside my package.I think passwords any kind of encryption.keys all those kind of things are locked.down with my user key my my credentials.my windows credentials on my machine so.it works great on my machine I deployed.a QA the package fails and you get a.login failure essentially saying it.can't connect to Oracle or db2 or our.sequel if you're using sequel invocation.so that's that's a symptom you had to.get around so encrypt sensitive user key.is not a good option in a team.environment unless everything you have.is using windows authentication and no.passwords restored anywhere does package.if that's not the case for a team.environment I prefer to use either.encrypt all of password or encrypt.sensitive with password and then share.that password a simple password with.entire teams and that password is stored.right here that password can be reseeded.whenever you go to deploy these packages.to production and all it's going to do.is whenever you open the package up or.whatever you schedule the job to run.it'll ask you for a password.and then and then you're all set your.schedule it or it'll and if all the.encrypted data is now available for.people to see now also if you don't do.that I pass you a package that has.encrypt cents into a password it will.wipe out all the sensitive data every.time you open that package and so I'll.go to your desk and type it in again or.until you type it in and hit save now I.can't touch it that's why the only.option really for most people are those.those two options I have box right now.okay Jenna.hope I answered your question I feel.that quite a bit unfortunately it's it's.a pain in the butt from a team.environment perspective all right so.let's Cooper destinations a little bit.and our last out a little bit here we.have so my destination side how do we.make this run a little faster office or.a lot faster hopefully we mentioned the.fast load being an all-or-nothing thing.as well now of course all the old T.sequel concepts all still apply if you.have if you're inserting into a table of.no indexes for example are no keys it's.going to load much faster and with with.keys so keep mine fast loads or all or.nothing thing there's lots of things we.can do around that run that fast load.one thing to keep mind is where you run.your package might matter also so if I'm.running on my laptop and I'm moving at.data from sequel server one this equals.2 or 2 the data is actually going to.come over from size one and be crunching.all of that data on my laptop before it.sends it this equals number two now that.move that over to sequel server 1 now.it's gonna cash it there or cache it.there it's up to you so where are you in.a package might matter that's a lot.easier now that it used to be before you.can now you can deploy to a server and.run it run it through agents if I were.to open up agent in 2012 and and fire up.a package it's no longer gonna run on my.laptop it's gonna run it on that server.now but if I'm running a visual studio.it is running on my laptop so something.to keep in mind there okay so let's go.the destination real quick and come back.to here again so a few a few good points.you guys made earlier about the.destination me let me find my data flow.here I want to clone that way too many.times here all right first thing we.noticed that is a fast that was turned.off let's turn it on okay.now if you have full control of a table.the table lock makes a lot of sense we.can save a lot of lob making this thing.a lot faster by turning on table log if.we have full control of our system but.the other setting that's really.important here is this max insert commit.stocks so let's imagine an example here.where we're inserting.10 million records sorry let's let's say.100.records into a table so I'm inserting a.hundred million records from db2 and the.sequel server in its example with the.default setting set right down you see.it right now what's gonna happen is my.sequel store they look all happy and I'm.inserting 100 I'm gonna turn his you.know 100 million records I'm inserting.50 hundred million records into my.sequel Sarah why is it happy right now.why is it at 2 percent utilization well.that's one of the big God shows in sis.what's going to happen is it is s sequel.server is going to be all happy-go-lucky.until that hundred millionth record goes.root and then it'll be taken to its.knees and the reason why is because it's.Mac's insert commit sucks what this is.showing us is how often should I do a.commit statement so it's going to be how.happy-go-lucky and the very last second.it's going to it's going to issue one.big fat commit statement versus lots of.little commit statements and it's gonna.take your server down to its knees.so instead of doing that if you have.millions and millions of rows change.that setting to somewhere around a.hundred thousand try try to be number.two eighty thousand one hundred twenty.thousand somewhere between 80 to 120.thousand might be the right answer.depending on how wide the table is if.you change that you'll see your sequel.server blip and as it commits a hundred.thousand records it comes back down then.blip again and then down again blip.again down again so it's going to it's.going to make it much much faster.because it's doing all those in one fell.swoop.versus all at once now every pro has a.con right now the con in this case is if.we if we were to commit all that data if.we were to fail on road 90 million then.90 million rollers will be committed and.ten million rows or in some weird state.right now this is where transactions.come into play and all those kind of.things that are that are covered in our.masters of science class at never.mentioned it earlier so but by default.it's all one big fat transaction so you.want to adjust that if you have lots and.lots and lots of rows yeah for most your.tables leave it as is don't worry about.it now but for big big tables it makes a.lot of sense to cheat to adjust okay.right so let's jump into our last topic.for today and that's all around parallel.design now Microsoft has made that's a.lot easier and in the next version of.sequel server as we say in our industry.right tech industry the next version.will be great right so the the next.version of sequel server which they're.doing a very very quick build cycle is.going to have scale-out built into it.but until then it's not it's not not.possible so let's find a way to raw our.own kind of system what I want to do is.imagine for example we have we have a.hundred we have ten dollars actually a.real example from Power Company we have.a smart grid example and I want to get.ten I want ten thousand files loaded an.hour into my database or you might have.let's say for example you might have a.you know ten billion rows which is.literally an example we had recently we.had ten billion rows to get from I.should close to 200 million rows gives.me ten billion rows to get from here to.a new database well running one big fat.select statement is not gonna work so.that luckily the data most ten billion.rows was partitioned so I had our two.billion rows excuse me I had I had.different chunks of data here and it.partitioned by by by day and each.partition had about you know had about.ten million rows in each partition this.is a big bank in case you're curious so.each one had ten million rows and I.would have liked you know I had like a.different date different date ranges.inside this this might be Monday Tuesday.Wednesday Thursday so wouldn't it be.great if I could have a package that.runs on five servers each one taking a.chunk of data from each of these.partitions and bring it over chunk by.chunk by chunk well that's what we can.do with this whole this whole pile as.it's all padding might show you is.called a work pile pattern the word.pirate pattern will basically say I.create a table that shows me here's my.queue of work I want you to at a tackle.it might be ten doubt it might be a row.in here for every file but I have the.process.well the similar schema it might be it.might be a row in here for every slice.of that ten billion row table last I.came out for 100 million row table.excuse me in this case what happens is I.fire off two packages to run at the same.time each one of those grabs a chunk of.work they process it and then they say.all right give me the next chunk of work.and so on and so on and so on until all.the work is at the very very end and.there's no more work if you had so this.is a great way to kind of distribute the.workload over that time there was no way.we could have loaded the file that Edna.says cost ten thousand files or those.two hundred million rows without this.work pal pattern we try we had a weekend.to do it and we could not make it happen.so the only way we found was into this.now here is what our art our lab.environment looked like earlier if I.have one process firing off for eight.files it takes me 64 seconds to get.through all these files with two of them.firing off in parallel each process.takes a little bit longer because I only.have to cool I have two cores here but a.fork or excuse me.but now we're down to 36 seconds versus.64 so we almost have linear growth and.time now if we do it in four processes.now we're down a mountain rich.diminishing returns right we have 28.seconds now and we only get a one second.state by going to aid because we've run.out of course now so ideally what we.want to do is want to give each core of.CPU would have its own unit of work to.work on now this was a two core machine.that ran us examples that's why we're.seeing it all pretty fast here so let me.show you this looks like real quick I.have this this package here called.worker non DTS X and what is I going to.do is it's going to go out to my Higher.Ed database and it's got a lot of stored.procedure saying give me some work so.that's all it's doing it's going to go.as table called okay it's called worker.I'm gonna find it here.oh there's task queue here alright so I.when run this real quick you can see.what looks like here's my task queue.this case each one of these is a file.here.and it's my but I have a file for each.each one of these two up the process so.I now do the work.all right here actually process a file.and then I check the work back in so.essentially is a check-in checkout.process.I check the work out I do the work I.check it back in again so if I were to.go ahead and fire this off I'm gonna.know I wanted one with some multiple.cores here so let's go ahead and really.make this thing make a screen screen.here.I'm gonna drop over to Doss and I want.to go ahead and go out to my folder here.and let's go look at what the script.looks like and this is pretty well.document I have this I have this on any.of my blog also but let me want to do a.spawn worker CMD and this is what this.looks like you don't have memorize this.just steal my code on my mouth on my.blog each one of these it's going to.kick off the package and number of times.I'm gonna I'm gonna go ahead and.truncate the table my cue table I'm dead.ignore all the stuff up top here but.basically I'm gonna pass the number from.one to twenty or two whatever if I saw.the package that number of times okay.it's gonna run it in silent mode only.show me ears so if I do spawn worker one.obviously watch this much of our test.our CPU here there we go alright there.we go.so we're firing off the package you're.seeing it run right here and I'm on this.uh this look down here okay there.did it run no I don't think it actually.did not run yeah they're opening up and.they're merely closing so must be I.wasn't wrong link in there what's what's.gonna happen is what we'd see each of.those fire off like you're seeing it.look on the bottom right of my system.tray there you see good boot boom and it.actually goes away so if I were to open.up spawn worker I've CMD here we go.what's wrong right now is we have this.path here it's pointing at 2008 I'll.painted 2012 and then just make sure.let's make sure actually had the right.path here for this guy that's a problem.alright my guess is I got the problem.all over the place but let me just try.to stress one thing here and just see if.we get lucky if we don't then we did we.no worries at all.alright so save that let's go ahead and.fire it off one time oops sorry that's.not what there we go.it did something for a second so it.process a file and went away immediately.cuz it's still the package if she.pointed wrong wrong wrong folder there.also that's okay so what we can do here.there's a whole bunch of ways we can do.this some of you guys are actually.pointing out to your service broker.could actually see service service.broker you need some kind of queuing.mechanism ultimately so this right here.is going to communicate a service broker.or whatever you're queuing mechanism is.and this mq or whatever it might be say.give me some work it doesn't work and it.checks the work in or deletes it out of.the queue there's a whole bunch of ways.of doing it this is just showing you a.few kind of dopey getting some ideas on.how to do it this is on my blog and all.the code examples all out there for that.but a great way to kind of scale out.your sis package alright so let me go.ahead and open up our our last bit here.we have a lot of five minutes left in a.long journey here I know a lot of you.guys have a lot of questions yet not.gonna answer it because so many went.over there were 600 on this this webinar.so we had 70 people on here it was.hurting into all the questions.Barbara we're definitely a sent you a.discount code that's gonna hopefully.discount that they are the elite.training so you get that I think.tomorrow morning sometime we the.question kitty here should I use.compression on tables that are accessed.by SSRS and as SAS that is even better.idea of kenny so for a cube processing.your cube processing will global tile.that will go down immensely for it for.for processing cube it's also going to.increase your select performance.immensely for SSRS so those are two.great examples especially for fact.tables and big transactional tables.there's a lot of replication of data.you're going to see a huge gainer.formats you will see a slight tick up in.performance for CPU time that's the only.downside of that is the CPU time okay I.see here is there a way to see how many.buffers your.a sized package container could have.mail about what's next buffers yeah then.actually there are some counters out.there we have in a product called bi.Express will just show you how many.buffers are being consumed.it's a few tract and event handlers.Kenneth also you'll find it there.there's a whole bunch of operation stats.that are out there if I had more time I.can show you but shoot me an email if.you want to learn more about that whole.bunch of questions about task factory.yeah against in us send us an email.my email address on here if you like a.demo of task factory actually if you.download it there's no trial it's.actually all free so just go in and.download the trial and it works Visual.Studio perpetually for free it's only.when you want to go to a server run.outside of Visual Studio does it.actually require a license a lot of the.poets are actually free and test factory.all right Devon have anything else you.want to share before I wrap up must been.here alright I guess that's a negative.all right cool well guys thank you so.much so we are going to be sending a.discount code tomorrow for for the.on-demand training so if you have any.questions there please feel free to.reach out to us you'll be you're gonna.get a special code for for you guys and.so stay tuned tomorrow morning for that.and thank you so much for attending this.webinar I really appreciate I hope hope.you found this interesting and we'll be.doing a lot more of these time goes on.thanks a lot and have a great day.you.you.[Music].

How to generate an electronic signature for the Zions Cross Sell Mortgage Certificate Elite online

You must focus on a flexible solution to electronic signatures for Zions Cross Sell Mortgage Certificate Elite. CocoSign will provide you with what you have been Reaching out, a single online software that does not need any many installation.

You just need to have a efficient internet connection and your preferred platform to use. Follow this tips to e-sign Zions Cross Sell Mortgage Certificate Elite easily:

  2. Click to the category 'My Signature'.
  5. Download the form after signing.
  6. You can also send it through email.

CocoSign makes electronic signatures on your Zions Cross Sell Mortgage Certificate Elite more flexible by providing more choices of merging two documents, adding additional fields, invitation to sign by others, etc.


How to create an electronic signature for the Zions Cross Sell Mortgage Certificate Elite in Chrome



Here are the basic tips you need to follow:

  3. On your Zions Cross Sell Mortgage Certificate Elite, right-click on it and go to open with option. From there, choose CocoSign reader to open the document.
  5. Insert it on the page where you require it.
  6. Select 'Done'.

How to create an electronic signature for the Zions Cross Sell Mortgage Certificate Elite in Gmail?


  4. From the sidebar, drag 'Sign'.
  5. Write your electronic signatures.
  6. Create them in the document where you need to.
  7. Select 'Done'.



How to create an e-signature for the Zions Cross Sell Mortgage Certificate Elite straight from your smartphone?



  4. Select 'My Signatures'.
  6. Select 'Done'.
  7. Get the document or directly share through email.

That's it. You will be done signing your Zions Cross Sell Mortgage Certificate Elite on your mobiles within minutes. With CocoSign's remote signature features, you no longer need to worry about the price of your electronic signatures and use our product of your choice.

How to create an e-signature for the Zions Cross Sell Mortgage Certificate Elite on iOS?


Below guides will help you to e-sign your Zions Cross Sell Mortgage Certificate Elite from your iPad or iPhone:

  1. Download the CocoSign software on your iOS device.
  3. You can also sign in through Google and Facebook.
  7. Save the changes and save your Zions Cross Sell Mortgage Certificate Elite.


How to create an electronic signature for the Zions Cross Sell Mortgage Certificate Elite on Android?

Recently, Android gadgets are handy used. Therefore, to help out its customers, CocoSign has developed the software for Android users. You can use the following guides to e-sign your Zions Cross Sell Mortgage Certificate Elite from Android:

  1. Download the CocoSign app from Google Play Store.
  4. Choose the area you want to put your signatures.
  7. Save changes and save the file.
  8. You can also share this signed Zions Cross Sell Mortgage Certificate Elite with other people or upload it on the cloud.


Get started with CocoSign today